asked on 2/17/2016

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

SSRS Microsoft SQL Server

Last Comment

Russell Fox

2/19/2016

Emes

2/17/2016

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

2/17/2016

ASKER

Removing the recursive keyword did nothing.

Russell Fox

2/17/2016

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.

teknovation

2/18/2016

ASKER

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

2/18/2016

ASKER

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

Russell Fox

2/18/2016

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

2/19/2016

ASKER

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

ASKER CERTIFIED SOLUTION

Russell Fox

2/19/2016

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.

See Pricing Options

Start Free Trial