Dividing two numbers from subtotal column to display result in same total column

• ruchi.lamba

  SSC-Addicted

  Points: 434

  More actions

  October 23, 2008 at 11:04 am

  #195055

  Hi,

  I have created a report that groups row by product and metric, and the column by date.

  I'm using an expression to get the average in the total column for the following metric:

  % of non-contracted clean claims processed/adjudicated* within 30 calendar days (<=30 days) within the reporting period

  And for the rest of the metrics, the plain totals are put into the total column.

  The expression I use is:

  =iif(inscope("matrix1_RowGroup2")

  AND Fields!Metric.Value="% of non-contracted clean claims processed/adjudicated* within 30 calendar days (<=30 days) within the reporting period",

  iif(inscope("matrix1_Date"),Sum(Fields!Value.Value),Avg(Fields!Value.Value)),Sum(Fields!Value.Value))

  However, this is not what I need in the total column. What I really need is to have the metric

  "% of non-contracted clean claims processed/adjudicated* within 30 calendar days (<=30 days) within the reporting period" have the following calculation:

  metric "# of non-contracted clean claims processed/adjudicated* within 30 calendar days (<=30 days)"

  DIVIDED BY

  metric "# of non-contracted clean claims processed/adjudicated* within the reporting period"

  Can someone please help out.

  I've attached the report for clarification.

  Thank You,

  Ruchi

• ruchi.lamba

  SSC-Addicted

  Points: 434

  More actions

  October 23, 2008 at 11:09 am

  #888759

  Sorry, wrong file. The attached is the correct one.

• Jerry Hung

  SSChampion

  Points: 12968

  More actions

  October 23, 2008 at 1:12 pm

  #888827

  to clarify, given your Excel #'s (let's take HMO 20081001)

  # of non-contracted clean claims processed/adjudicated* within 30 calendar days (<=30 days)6673

  # of non-contracted clean claims processed/adjudicated* within the reporting period6844

  % of non-contracted clean claims processed/adjudicated* within 30 calendar days (<=30 days) within the reporting period193.29

  What is the expected results in SSRS?

  6673 / 6844?

  Personally for complex operations, I sometimes do

  1. Select the calculation fields, but hide them in the report, and only show the results

  e.g. Select A, B, and C = A/B, but hide A&B, only show column C

  2. Do the calculation in Dataset/SQL, so Report just show the # as is

  SQLServerNewbieMCITP: Database Administrator SQL Server 2005

• ruchi.lamba

  SSC-Addicted

  Points: 434

  More actions

  October 23, 2008 at 1:28 pm

  #888837

  Thanks for your reply. I agree it's easier to do the calculation in sql at this point, but I am curious to find out if it's possible to do in ssrs, since it's supposed to be "powerful"...

  What I'm trying to do is have the total column display division of two numbers that are from the total column. For instance,

  for HMO, the % metric should have the result of (25,488/25,944)*100

• Jerry Hung

  SSChampion

  Points: 12968

  More actions

  October 23, 2008 at 2:04 pm

  #888860

  Mnn, darn, I haven't touched SSRS in this deep level for a while

  so are you doing this in Table or Matrix?

  If you're using Table, are you using any Group By?

  because I thought you can add a Group Footer where you can have Subtotal, or group operations

  Your layout feels more like a Matrix (group by HMO in rows, months in columns)

  Try adding a column (not from Dataset) and play in there?

  I wish this is T-SQL then I can at least play with it

  SQLServerNewbieMCITP: Database Administrator SQL Server 2005