Avatar of teknovation
teknovation

asked on 

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?
User generated image
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

Avatar of undefined
Last Comment
Russell Fox
Avatar of Emes
Emes
Flag of United States of America image

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
Avatar of teknovation
teknovation

ASKER

Removing the recursive keyword did nothing.
Avatar of Russell Fox
Russell Fox
Flag of United States of America image

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

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
Avatar of teknovation
teknovation

ASKER

anyone with fetchxml experience? Need help on how to write sum a value
Avatar of Russell Fox
Russell Fox
Flag of United States of America image

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

ASKER

thanks Russell, I'll give your solution a try and let you know how I make out.
ASKER CERTIFIED SOLUTION
Avatar of Russell Fox
Russell Fox
Flag of United States of America image

Blurred text
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
Microsoft SQL Server
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
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo