x

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

more ▼

asked Jan 16, 2013 at 10:23 PM in Default

avatar image

jaymz69
172 11 13 17

question.txt (1.2 kB)
Jan 16, 2013 at 10:25 PM jaymz69

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?

Jan 17, 2013 at 08:41 AM Valentino Vranken
 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
 ')
Jan 17, 2013 at 04:05 PM jaymz69
     /* 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 
 ')
Jan 17, 2013 at 04:05 PM jaymz69
     /* 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
 ')
Jan 17, 2013 at 04:05 PM jaymz69
show all comments (comments are locked)
10|1200 characters needed characters left

7 answers: sort voted first

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!

more ▼

answered Jan 17, 2013 at 10:54 AM

avatar image

Valentino Vranken
1.5k 2 4 12

(comments are locked)
10|1200 characters needed characters left

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))
more ▼

answered Jan 17, 2013 at 10:28 PM

avatar image

jaymz69
172 11 13 17

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 :)

Jan 18, 2013 at 04:51 PM Valentino Vranken

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...

Jan 18, 2013 at 05:12 PM jaymz69

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. :)

Jan 18, 2013 at 05:15 PM Valentino Vranken
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jan 17, 2013 at 09:23 AM

avatar image

Fatherjack ♦♦
43.8k 79 99 118

(comments are locked)
10|1200 characters needed characters left

I get #Error in the field when I run the report. No errors at runtime.

more ▼

answered Jan 17, 2013 at 04:14 PM

avatar image

jaymz69
172 11 13 17

Have a look at the Output window (CTRL + ALT + O), it should contain error details.

Jan 17, 2013 at 05:08 PM Valentino Vranken
(comments are locked)
10|1200 characters needed characters left

I am in Report Builder 3.0

more ▼

answered Jan 17, 2013 at 05:54 PM

avatar image

jaymz69
172 11 13 17

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x782
x700
x8

asked: Jan 16, 2013 at 10:23 PM

Seen: 1540 times

Last Updated: Jan 18, 2013 at 05:15 PM

Copyright 2017 Redgate Software. Privacy Policy