SSRS field expressions using ReportItem refrences have unexpected results when exporting.
I have a report built in BIDS 2008 that uses refrences to report items in field expressions. As an example: =(ReportItems!CCPrgYTD.Value + ReportItems!CCIndYTD.Value) It is a pretty basic sum. This field expression is located within several nested groups and the cells it references may or may not be there if the group they are in have data or not. This expression always produces the correct results viewed in Report Manager or in BIDS, but as soon as it is exported to any format the formula produces inconsistent results. When exported and one of the cells refrences are not there the formula seems to grab a random cell refrence from a previous group that does have the value available. Has anyone seen this? Or have any words of wisdom? The report where this has been noticed is a confidential report and it is ver complex, I am trying to reproduce the problem with a simpler report and dummy data. I will post the project files once I have reproduced the problem. Any help would be greatly appreciated.
February 17th, 2012 2:04pm

I recreated a small RDL and i have excell sheets with sample data. Anyone that would like these please respond and I will send a zip file. I thought i could post files on the forum, but obviously not. Here are a few screenshots. One of the data: and the PDF showing bad results: As you can see from the data above Secondary should have a Grand total of 14. It does when viewed from BIDS or Report Manager, but not when exported. I do not understand why, or how, this happens. Thanks, Brad Aune
Free Windows Admin Tool Kit Click here and download it now
February 17th, 2012 4:49pm

hey brad, I'm assuming you use table/tablix for this and you have an inner group Cat (probably short for category). Now, why would you use ReportItems to sum this. Can't you use =sum(Fields.Value.Value) for that group header? try that. May be you want to explain the grouping part. is the grand total is in page footer, or it's at group footer for group secondary? Once a gunners always a gunners
February 17th, 2012 5:15pm

hey brad, I'm assuming you use table/tablix for this and you have an inner group Cat (probably short for category). Now, why would you use ReportItems to sum this. Can't you use =sum(Fields.Value.Value) for that group header? try that. Once a gunners always a gunners Hi i_h, Normally i would just use the sum aggregate, but because each group is filtered the Sum doesn't always return a valid result. In this simple report the grand total could be an aggregate, but in my more complex report for the client, it doesn't work because the report is much more complex and the total they are looking for is not a simple aggregate of the dataset. Thanks for your response. If you wou like the sample RDL and data i have created it may make things more clear. Also, this scenario was to show that report manager and bids understand what i want to do and it provides the correct results (as shown in the image below), but that when exporting the "ReportItems" refrences look at incorrect values when the actual item it is refrencing is not there. Brad
Free Windows Admin Tool Kit Click here and download it now
February 17th, 2012 5:25pm

Well first off this type of discrepancy between previewing a report and exporting certainly seems to point toward a bug in SSRS. Shocking, I know. I would be interested in looking at the example you've developed to see if it's broken for me too, though I think I'm working in 2008 R2 here. Have you tried applying the updates/services packs to your program/server? peitzza@yahoo.com Also, as a rule, SSRS tends to break more easily when you are trying to get around some of it's seemingly querking functions/limitations. Referencing report items, especially to do math is generally the last resort from my experience. Have you tried using some combination of IIF, SUMIIF, and INSCOPE functions to get to the desired level of aggregation for this field you're having problems with? Here's a great blog on the subject of scopes (if you're using a matrix): http://munishbansal.wordpress.com/2008/12/23/customizing-subtotal-functionalityusing-inscope-function-in-matrix-reports-ssrs/
February 18th, 2012 10:46am

Well first off this type of discrepancy between previewing a report and exporting certainly seems to point toward a bug in SSRS. Shocking, I know. I would be interested in looking at the example you've developed to see if it's broken for me too, though I think I'm working in 2008 R2 here. Have you tried applying the updates/services packs to your program/server? peitzza@yahoo.com Also, as a rule, SSRS tends to break more easily when you are trying to get around some of it's seemingly querking functions/limitations. Referencing report items, especially to do math is generally the last resort from my experience. Have you tried using some combination of IIF, SUMIIF, and INSCOPE functions to get to the desired level of aggregation for this field you're having problems with? Here's a great blog on the subject of scopes (if you're using a matrix): http://munishbansal.wordpress.com/2008/12/23/customizing-subtotal-functionalityusing-inscope-function-in-matrix-reports-ssrs/
Free Windows Admin Tool Kit Click here and download it now
February 18th, 2012 10:46am

Well first off this type of discrepancy between previewing a report and exporting certainly seems to point toward a bug in SSRS. Shocking, I know. I would be interested in looking at the example you've developed to see if it's broken for me too, though I think I'm working in 2008 R2 here. Have you tried applying the updates/services packs to your program/server? peitzza@yahoo.com Also, as a rule, SSRS tends to break more easily when you are trying to get around some of it's seemingly querking functions/limitations. Referencing report items, especially to do math is generally the last resort from my experience. Have you tried using some combination of IIF, SUMIIF, and INSCOPE functions to get to the desired level of aggregation for this field you're having problems with? Here's a great blog on the subject of scopes (if you're using a matrix): http://munishbansal.wordpress.com/2008/12/23/customizing-subtotal-functionalityusing-inscope-function-in-matrix-reports-ssrs/
February 18th, 2012 10:46am

I managed to rewrite the field expressions using sum and iff and removing all the report item refrences i could. Some had to remain, but these could have been replaced by a calculated column. This was far more complicated than it should have been. Considering BIDS understands the report item refrences, but the exporting renderer doesn't, I would consider this a bug. I have run into many issues with the report renderer going to excel and other formats. Perhpas in next version of SSRS some of these bugs will be addressed. Thanks to Peter for his valuable comments, they lead me down the right path to avoid the bug.
Free Windows Admin Tool Kit Click here and download it now
February 21st, 2012 1:32pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics