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
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?
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 ')
/* 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 ')
/* 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 ')
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!**
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.
I get #Error in the field when I run the report. No errors at runtime.
Have a look at the Output window (CTRL + ALT + O), it should contain error details.
I am in Report Builder 3.0
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
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
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))
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 :)
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. :)
12 People are following this question.
