# SSRS - SUM CHALLENGE (EXPERTS HELP!!!)

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?

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
SSRSMicrosoft SQL Server

Last Comment
Russell Fox
Emes

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
teknovation

Removing the recursive keyword did nothing.
Russell Fox

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")
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.
teknovation

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
teknovation

anyone with fetchxml experience? Need help on how to write sum a value
Russell Fox

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.
teknovation

thanks Russell, I'll give your solution a try and let you know how I make out.
Russell Fox

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts

TRUSTED BY