none
Calculations Using Subtotals (SSRS)

    Question

  • I'm trying to create an expense report in Reporting Services. This report lists the type of expense on the Y-Axis and the financial quarters on the X-Axis. Now, I've been able to manage subtotals for groups of rows however, there are some calculations in the report that are dependent on these subtotals. How do I manage a calculation between subtotals?

    For example,

    Sales Type              Q1
    ------------------------------------
    Sales1                      20          <--- sales are grouped by "Sales"
    Sales2                      20
    SalesSubtotal            40
    Expense1                  10          <--- expenses are grouped by "Expenses"
    Expense2                  10
    ExpenseSubtotal        20
    NetProfit                   20           <--- Netprofit = salesSubtotal - expensesSubtotal, let's say.

    The underlying table looks something like this and is used to dynamically populate the records in the report:

    ReportRow      ReportGroup       Balance          HideSubtotal**     Quarter
    -------------    -----------------     -----------      -----------------     -----------
    Sales1            Sales                  20                  1                          1
    Sales2            Sales                  20                  0                          1
    Expense1        Expense              10                  1                          1
    Expense2        Expense              10                  0                          1

    **The "HideSubtotal" is a bit that is used in the subtotal row's Visibility property.

    How would one create calculations of these subtotals like in the NetProfit? This also gets suprisingly complicated when trying to do ratios/percentages. I'd appreciate any other way of coming to this same style report.


    Monday, October 05, 2009 1:55 PM

Answers

  • Chip,

    Sorry for the late reply. I guess, I wasn't entirely clear on your situation. To refer to another textbox item, you could use =ReportItems!textbox23.Value expression. Replace textbox23 in my example with the textbox name.


    Chicagoan ...
    • Marked as answer by chip_levis Tuesday, October 06, 2009 7:15 PM
    Monday, October 05, 2009 6:00 PM
    Answerer

All replies

  • Hi Chip,

    It seems like a straight forward report. If it only have 2 report group types (sales & expense), then to get net profit is do-able. If it has more than 2, then it could get more complicated. Obviously, you add a group and group it by Fields!ReportGroup.Value. You may want to sort them descending if you want group Sales to be on top because E will be sort on top than S if sort alphabetically. Then, at group footer, to sum the balance, add expression =sum(Fields!Balance.Value). At the table footer, add this expression to get net profit:
    =sum(iif(Fields!ReportGroup.Value="Sales",Fields!Balance.Value,0) - sum(iif(Fields!ReportGroup.Value="Expense",Fields!Balance.Value,0)

    You may try removing any trailing spaces just in case:
    =sum(iif(trim(Fields!ReportGroup.Value)="Sales",Fields!Balance.Value,0) - sum(iif(trim(Fields!ReportGroup.Value)="Expense",Fields!Balance.Value,0)

    Also, if the Balance field is a float or money type datatype, then try converting it to double.
    =sum(iif(trim(Fields!ReportGroup.Value)="Sales",cdbl(Fields!Balance.Value),cdbl(0)) - sum(iif(trim(Fields!ReportGroup.Value)="Expense",cdbl(Fields!Balance.Value),cdbl(0))

    Good luck.
    Chicagoan ...
    Monday, October 05, 2009 2:13 PM
    Answerer
  • Hi Isham,

    You're right. My report has many more calculations than this simple sum calculation. I have ratios and dependend calculations much like an intense excel report. Thankfully all these are just stacked vertically, and not horizontally. Is there some way that I can maybe, create a table of calculations and dynamically switch out the expressions in the calculations?

    Currently my report layout is as so:

    Row1: ReportRow.value
    Row2: "Total" + ReportGroup.value     VISIBILITY: Hidesubtotal.value

    Column_Group1: year
    Column_group2: Quarter

    measures: Balance.value

    now, i can create another row that can use a dynamic text from a table based on report group. However, could i use the same for the formula expression? Is there some way I could set this up, then I could just control everything using a table and also, if the user wanted more totals/calculations, it should be easy to add/remove them.
    Monday, October 05, 2009 3:21 PM
  • Maybe I should make this clearer:

    Is there a way to take a formula stored as a varchar field in a table, and use this field in an expression to calculate something in the report?

    so in the table, you'd store for example this formula:
    SUM(fields!field1.value)+SUM(fields!Field2.value)

    and then in the report, somehow use the expression editor to use this varchar field to actually calculate somtehing?
    Monday, October 05, 2009 5:04 PM
  • Chip,

    Sorry for the late reply. I guess, I wasn't entirely clear on your situation. To refer to another textbox item, you could use =ReportItems!textbox23.Value expression. Replace textbox23 in my example with the textbox name.


    Chicagoan ...
    • Marked as answer by chip_levis Tuesday, October 06, 2009 7:15 PM
    Monday, October 05, 2009 6:00 PM
    Answerer
  • I didnt know you could refer to a textbox this way. This is good to know.

    When I said table in my earlier reply, I meant an actual SQL relational table. I was hoping that the formula could be stored as a varchar in this relational table and then an expression could be written in a reporting services table that could use this formula.

    Is this possible, Isham?
    Monday, October 05, 2009 7:29 PM
  • Unfortunately, I don't think you could do that. If you store those formula in the table, i.e. =sum(fields!a.value), when referring them in the RS expression, it would end up just displaying the actual expression in that textbox.
    Chicagoan ...
    Monday, October 05, 2009 7:46 PM
    Answerer
  • Ok, thank you for all your help!
    Tuesday, October 06, 2009 7:15 PM