SSRS - SUM CHALLENGE (EXPERTS HELP!!!)

teknovation
teknovation used Ask the Experts™
on
I've been going at this for a while now and decided it was time to reach out for help!

Can someone please tell me why i cannot sum the OrigQty cell correctly?
SSRS
The OrigQty show's 174,280 as the total sum of everything below it which is incorrect. It should be 3,886 total.

340 + 360 + 10 + 375 + 378 + 371 + 367 + 360 + 383 + 357 + 352 + 192  = 3,886

Where is 174,280 coming from? How can I get it so that OrigQty value is 3,886?

The expression for OrigQty is:
 =Sum(Fields!originalnumberofQtyValue.Value, "DataSet1", Recursive)

The expression for OrigQty on the 2nd row is:
=Fields!originalnumberofQtyValue.Value
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
take out the recursive
 =Sum(Fields!originalnumberofQtyValue.Value, "DataSet1")

next it most likely has to do with the grouping. I have had similar problems.
take out all the grouping data and rebuild it.
 if that does not work I have rebuild the report working on small sections of data to find out the  report

Author

Commented:
Removing the recursive keyword did nothing.
Russell FoxDatabase Developer
Top Expert 2014

Commented:
The issue is that the SUM has no concept of the group, so I suspect that the 174,280 is the total sum over the entire data set. You'll need a way to put a WHERE clause into the expression, something like
=Sum(IIF(Fields!ProductID.Value = First(ReportItems("ProductID").Value, Fields!originalnumberofQtyValue.Value, NOTHING), "DataSet1")

Open in new window

where "First(ReportItems("ProductID").Value" points to a text box that you've named "ProductID". I'm not sure it will work. You might be better off creating a new data set that just has the totals.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hmm...what's interesting is that the row that is grouped by the Stock Id gives each row a different value when I sum the 2nd row.

=Sum(Fields!originalnumberofQtyValue.Value)

Do you have a tutorial or link to a website that can show me how I can create the value in another dataset? I assume you can use more than 1 data set on a report?

The thing is I'm using Fetch XML for the data source and not SQL. So that makes it more difficult

Author

Commented:
anyone with fetchxml experience? Need help on how to write sum a value
Russell FoxDatabase Developer
Top Expert 2014

Commented:
Yes, you can have multiple data sets, but each tablix control can only have one as its source. You can generally get around this:
Create a data set that just has the [StockID] and total for that StockID.
Create a tablix control with that data on one line and a blank line within that group below it.
Create a new report with no header/footer/border, just a tablix with the detailed data (like you have now), but have a WHERE STOCKID = @STOCKID clause in the query.
On the main report, put a subreport object in that blank second row and set the source to the new detailed subreport.
Right-click the subreprort and set the @STOCKID parameter to the [StockID] field on the main report.
Sorry, no experience with FetchXml, but SUM is an aggregation function:
Use FetchXML aggregation (MSDN)
See if you can add FetchXML as a tag on the question.

Author

Commented:
thanks Russell, I'll give your solution a try and let you know how I make out.
Database Developer
Top Expert 2014
Commented:
Bear in mind that sub-reports can run slowly, so it would be better if you can get it working with regular grouping/aggregation.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial