Post reply

How to filter the Subtotal

  • 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!

  • 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]

  • January 15, 2013 at 11:35 am

    #1577451

    So I have tried this one:

    =SUM(IIF(INSCOPE("Group1"), Fields!CURRENT.Value, IIF(Fields!MonthEndDate.Value = "11/30/2012", Fields!CURRENT.Value, 0)))

    but I am getting an error "#Error" on the cell.

    Any idea how to fix this?

    Appreciate your help!

  • January 15, 2013 at 5:07 pm

    #1577561

    I WAS ABLE TO FIX IT BY USING THE CODE BELOW:

    =IIF(INSCOPE("Group1"), Fields!CURRENT.Value, IIF(Fields!MonthEndDate.Value = "11/30/2012", SUM(Fields!CURRENT.Value), 0))

    NOW MY problem is I am getting 0 result only.

    I don't know what went wrong. Any help is much appreciated. Thanks!

  • January 24, 2013 at 9:50 pm

    #1580812

    If you want the Subtotal value to be the sum of Sales values only for "1/31/2013", then try something like :

    =Sum(Iif(Fields!MonthEnd.Value="1/31/2013",Fields!Sales.Value,0))

    Regards,

    Uma

    • Viewing 5 posts - 1 through 4 (of 4 total)

    You must be logged in to reply to this topic. Login to reply