How to filter the Subtotal

• quasar_phoenicis

  SSCommitted

  Points: 1625

  More actions

  January 14, 2013 at 4:34 pm

  #287596

  Hi Guys!

  I need your help on this one. I have an RS report that is grouped by Client Name and CLient Type that looks like below:

  Client Name Client Type Month End Sales

  A typea 11-30-2012 20

  12-31-2012 30

  1-31-2013 10

  typeB 11-30-2012 0

  12-31-2012 60

  1-31-2013 90

  Subtotal ??????

  Total 210

  In the Subtotal, I need to display the total sales for the curent month which is 1-31-2013. So in the sample report above, The subtotal should only add10(typea, 1-31-2013) and 90(typeB, 1-31-2013) and should show 100.

  Is there a way to do this in SSRS? Please refer to the attached file for better view of the report above.

  Thanks a lot for the help!

• mister.magoo

  SSC-Forever

  Points: 47068

  More actions

  January 14, 2013 at 5:35 pm

  #1577124

  I haven't tried this, but I would look at using an expression that did the following:

  Check if the value is inscope of your grouping

  if it is use whatever expression you have now

  if it is not inscope then test the date column for the current month (however you identify that) and if it is equal,

  use the expression you have now

  otherwise use zero

  something like:

  = IF(INSCOPE("mygrouping"),Fields.Sales.Value,IF(Fields.DateColumn.Value = currentmonth, Fields.Sales.Value, 0))

  Please excuse errors and typos, as I said, I haven't tested it...

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]