question

jaymz69 avatar image
jaymz69 asked

Calculate between the two in SSRS

I have a job that runs through three steps to populate a table on SQL Server 2008 R2. Here is the table structure: CycleCountReport ( Co NUMERIC(1,0) NULL ,Loc NUMERIC(2,0) NULL ,[Date] DATE NULL ,Measurement VARCHAR(15) NULL ,[Value] FLOAT NULL ) Here is a sample of some data from the table: Co Loc Date Measurement Value 1 50 2013-01-16 Exceptions 8 1 80 2013-01-16 Exceptions 8 1 50 2013-01-16 Net -24.182 1 80 2013-01-16 Net 42.078 1 50 2013-01-16 LinesCounted 113 1 80 2013-01-16 LinesCounted 30 I want to perhaps at the time I run the SSRS to have another column called 'Accuracy' that does the math for ("LinesCounted".Value - "Exceptions".Value) / "LinesCounted".Value co loc date Exceptions LinesCounted Net Accuracy 1 33 1/15/13 10 100 78.90 90 1 22 1/15/13 5 100 -22.63 95 1 25 1/16/13 2 100 2.00 98 1 33 1/16/13 0 100 0 100 I also attached the question in a word doc in case the format is messed up in the question box. It will not let me attach the .doc file
sql-server-2008-r2ssrscalculated-column
6 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

[link text][1] [1]: /storage/temp/ 589-question.txt
0 Likes 0 ·
question.txt (1.2 KiB)
Please share some details on how you've built your report. What does your dataset look like (query)? And how did you build your tablix (matrix?) to get to that layout?
0 Likes 0 ·
I run these three in a nightly job and INSERT to my table /* Count the ITEMRECH irsrc 'I' transactions as an exception */ -- Insert data into DB table on Server from AS400 INSERT CycleCountReport (co,loc,[date],Measurement,Value) -- Query the daily data from the AS400 SELECT irco AS Co ,irloc AS Loc ,irdate AS [Date] ,'Exceptions' AS Measurement ,ExceptionCountColumn FROM OPENQUERY(GSFL2K,' SELECT irco ,irloc ,irdate ,COUNT(iritem) AS ExceptionCountColumn FROM itemrech ir WHERE ir.irsrc = ''I'' AND ir.irdate = CURRENT_DATE AND ir.irco IN (1,2) AND ir.irloc IN (4,44,64,50,52,41,57,60,42,59,80,81,84) GROUP BY irco ,irloc ,irdate ')
0 Likes 0 ·
/* Count the OOLRFUSR olrtyp 'I' transactions as lines counted */ -- Insert data into DB table on Server from AS400 INSERT dbo.CycleCountReport (co,loc,[date],Measurement,Value) -- Query the daily data from the AS400 SELECT olrico AS Co ,olrilo AS Loc ,olrdat AS [Date] ,'LinesCounted' AS Measurement ,LinesCountedColumn FROM OPENQUERY(GSFL2K,' SELECT olrico ,olrilo ,olrdat ,COUNT(olritm) AS LinesCountedColumn FROM oolrfuser hst WHERE hst.olrtyp = ''I'' AND hst.olrcyrc != ''Y'' AND hst.olrdat = CURRENT_DATE AND hst.olrico IN (1,2) AND hst.olrilo IN (4,44,64,50,52,41,57,60,42,59,80,81,84) GROUP BY olrico ,olrilo ,olrdat ')
0 Likes 0 ·
/* Count the ITEMRECH irsrc 'I' transactions for total Net $ */ -- Insert data into DB table on Server from AS400 INSERT CycleCountReport (co,loc,[date],Measurement,Value) -- Query the daily data from the AS400 SELECT irco AS Co ,irloc AS Loc ,irdate AS [Date] ,'Net' AS Measurement ,NetColumn FROM OPENQUERY(GSFL2K,' SELECT irco ,irloc ,irdate ,SUM(irqty * ircost) AS NetColumn FROM itemrech ir WHERE ir.irsrc = ''I'' AND ir.irdate = CURRENT_DATE AND ir.irco IN (1,2) AND ir.irloc IN (4,44,64,50,52,41,57,60,42,59,80,81,84) GROUP BY irco ,irloc ,irdate ')
0 Likes 0 ·
CO LOC DATE Measurement VALUE 1 50 2013-01-16 Exceptions 8 1 80 2013-01-16 Exceptions 8 1 50 2013-01-16 Net -24.182 1 80 2013-01-16 Net 42.078 1 50 2013-01-16 LinesCounted 113 1 80 2013-01-16 LinesCounted 30 ** Data when I query the table in SQL Sever
0 Likes 0 ·
Valentino Vranken avatar image
Valentino Vranken answered
Here's the solution in case you've used a **Matrix** with Measurement on the Columns. To add an additional column to the right, right-click the grey header of the Measurement column and select **Insert Column > Outside Group - Right**. Then use the following expression inside the detail cell of the new column: =(Sum(IIF(Fields!Measurement.Value = "LinesCounted", Fields!Value.Value, 0)) - Sum(IIF(Fields!Measurement.Value = "Exceptions", Fields!Value.Value, 0))) / Sum(IIF(Fields!Measurement.Value = "LinesCounted", Fields!Value.Value, 0)) *This assumes that the sum of LinesCounted is never equal to zero.* **Also note that the hardcoded strings are case-sensitive!**
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
In your report add a column in your table and in the table header type the column name "Accuracy". In the detail row of this column edit the function of the cell to be **=(LinesCounted.Value - Exceptions.Value) / LinesCounted.Value** This should calculate when the report is viewed.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jaymz69 avatar image
jaymz69 answered
I get #Error in the field when I run the report. No errors at runtime.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Have a look at the Output window (CTRL + ALT + O), it should contain error details.
0 Likes 0 ·
jaymz69 avatar image
jaymz69 answered
I am in Report Builder 3.0
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jaymz69 avatar image
jaymz69 answered
OK I created a project in VS and this is the error in output ------ Build started: Project: Report Project6, Configuration: Debug ------ Skipping 'CycleCountReport.rdl'. Item is up to date. Build complete -- 0 errors, 0 warnings ========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ========== [rsAggregateOfNonNumericData] The Value expression for the textrun ‘Textbox5.Paragraphs[0].TextRuns[0]’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data. Preview complete -- 0 errors, 1 warnings
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jaymz69 avatar image
jaymz69 answered
I think I see some of the issue. Measurement is the string name while the Value Column in the Table holds the numeric Value to the "Measurement" text, TABLE Co Loc Date Measurement Value Num Num date VARCHAR FLOAT
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jaymz69 avatar image
jaymz69 answered
Now that I see the error reason I change the expression to this: =(Sum(IIF(Fields!Measurement.Value = "LinesCounted", Fields!Value.Value, 0)) - Sum(IIF(Fields!Measurement.Value = "Exceptions", Fields!Value.Value, 0))) / Sum(IIF(Fields!Measurement.Value = "LinesCounted", Fields!Value.Value, 0))
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sorry for the confusion, that was indeed what I wanted to suggest. Something went wrong when I modified my test expression to match the names of your fields (copy/paste...). My answer has now been corrected. I'll give yours an up vote for effort as well :)
0 Likes 0 ·
Thank you, you pointed me in the right direction and with VS I could debug and work out the logic. Report Builder 3 just was not helpful to me...
0 Likes 0 ·
I almost never use RB. It's a good tool to quickly do something but for real development BIDS/VS is much more useful, as you've noticed. :)
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.