Unorthodox Grouping

  • Hi, I have created a table like below that sums journals into accounts (or objects).

    GroupIssue

    What I would like to do is group and sub-total the a_object with the below parameters.

    • Operating Revenues (400000 - 469999 and 480000-489999)
    • Personnel Expenses (501000-501999)
    • Materials & Services (502000-502999)
    • Capital Outlay (503000-503999)
    • Operating Expenses (500000 - 503999 or simply Personnel Expenses + Materials & Services + Capital Outlay)
    • Operating Net (Operating Revenues - Operating Expenses)
    • Transfers In (470000 - 479999)
    • Fund Balance Carryover (490000 - 499999)
    • Non-Operating Revenues (Transfers In + Fund Balance Carryover)
    • Total Revenues (Operating Revenues + Non-Operating Revenues)
    • Debt Payments (506000 - 506999)
    • Transfers Out (970000 - 979999)
    • Reserves (507000 - 507999)
    • Non-Operating Expenses (Debt Payments + Transfers Out + Reserves)
    • Non-Operating Net (Non-Operating Revenues - Non-Operating Expenses)
    • Total Expenses (Operating Expenses + Non-Operating Expenses)
    • Total Net (Total Revenues - Total Expenses)

    So the report should look something like below.

    TargetGroup

    Any assistance you can provide would be much appreciated.

  • It's helpful if you post data in a consummable format, as in the DDL to create a table and the DML to insert data.

    What I might do here is create a computed column that loads a value based on those ranges.

    ALTER TABLE dbo.BudgettoActual 
    ADD AccountGroup AS CASE
    WHEN accountid>= 400000 AND accountid < 489999 THEN 1
    WHEN accountid>= 501000 AND accountid < 503000 THEN 2
    WHEN accountid>= 503000 AND accountid < 504000 THEN 3
    WHEN accountid>= 507000 AND accountid < 508000 THEN 4
    ELSE 5
    END

    then I can group by these in a query.

    SELECT
    CASE
    WHEN ba.AccountGroup = 1 THEN
    'Operating Revenues'
    WHEN ba.AccountGroup = 2 THEN
    'Personal Expenses'
    WHEN ba.AccountGroup = 3 THEN
    'Materials and Services'
    WHEN ba.AccountGroup = 4 THEN
    'Reserves'
    END 'Object'
    , SUM (ba.budget) AS Budget
    , SUM (ba.actual) AS actual
    FROM dbo.BudgettoActual AS ba
    GROUP BY ba.AccountGroup;

    I could also order these in my computed column so that ordering by the AccountGroup would put htem in the proper order.

  • Thank you so much. This gave me the idea to group these appropriately by creating another field called Account Group and using your logic (IIF AND and IIF OR statements) to group them as such.

    However, I am unsure how I can add other things in rows such as Operating Net and Total Net. I think I might have to get the view like I have shown in the snapshot by breaking Object (or Account) further into its subsections (such as Personnel being one) and then stitching the view together via multiple tables. Thoughts? That was how I did it in Tableau at my previous workplace but I don't know how good Report Writer is in appropriately spacing info where it needs to go with separate tables.

    Thank you. Your help is most appreciated.

  • And probably use Sum (IF ... + Sum (IF ... to get Total Revenue Budget, Total Revenue Encumbrance etc.

    Likewise Sum (IF ... - Sum (IF ... to get Operating Net Budget, Operating Net Encumbrance etc.

  • We don't have an IF really, but you can use CASE.

    For sum of operating expenses, you could include a case when (materials or personal expenses) else 0 for the value. Using numbers in the computed column makes this easier.

    Ultimately this will be a complex query. I don't know the best way. I might include a separate table that maps the computer column to some title, just because that's easier to alter if you change any names.

    Of course, you could use a procedure and then insert the data from multiple selects in as rows in a temp table. That's an easy way if this isn't run constantly.

  • Yup, I think we are talking the same approach now. I was hoping there was an easier way but looks like making separate tables and stitching them together is going to be the way to go about it.

  • Thank you Steve Jones for the comments, based on which I was able to find the solution.

    This solution requires a lot of manual work but basically you have to create a ton of different tables and place one underneath another.

    Here is a conceptual example with just three groups (Operating Revenues, Operating Expenses and Operating Net).

    Table 1: Operating Revenues

    SELECT

      gl_detail.fiscal_year

      ,gl_detail.fund

      ,gl_detail.org

      ,gl_detail.object

      ,gl_detail.budget

      ,gl_detail.actual

    SET

      gl_detail.remaining = gl_detail.budget - gl_detail.actual

    FROM

      gl_detail

    WHERE

      gl_detail.fiscal_year = @fiscal_year

      AND gl_detail.fund = @fund

      AND gl_detail.org = @org

      AND ( (gl_detail.object >= N'400000' AND gl_detail.object <= N'469999') OR

      (gl_detail.object >= N'480000' AND gl_detail.object <= N'489999') )

    Table 2: Operating Expenses (in this table, you will choose to hide the header row)

    SELECT

      gl_detail.fiscal_year

      ,gl_detail.fund

      ,gl_detail.org

      ,gl_detail.object

      ,gl_detail.budget

      ,gl_detail.actual

    SET

      gl_detail.remaining = gl_detail.budget - gl_detail.actual

    FROM

      gl_detail

    WHERE

      gl_detail.fiscal_year = @fiscal_year

      AND gl_detail.fund = @fund

      AND gl_detail.org = @org

      AND (gl_detail.object >= N'501000' AND gl_detail.object <= N'503999')

    Table 3: Operating Net (in this table, you will choose to hide the header row and the cell rows; only leave behind the aggregate or grand total row as visible)

    SELECT

      gl_detail.fiscal_year

      ,gl_detail.fund

      ,gl_detail.org

      ,gl_detail.object

      ,gl_detail.budget

      ,gl_detail.actual

    SET

      gl_detail.remaining = gl_detail.budget - gl_detail.actual

    FROM

      gl_detail

    WHERE

      gl_detail.fiscal_year = @fiscal_year

      AND gl_detail.fund = @fund

      AND gl_detail.org = @org

      AND ( (gl_detail.object >= N'400000' AND gl_detail.object <= N'469999') OR

      (gl_detail.object >= N'480000' AND gl_detail.object <= N'489999') OR

      (gl_detail.object >= N'501000' AND gl_detail.object <= N'503999') )


    ~~ With the following calculated fields and expressions:

    ~~~ Adaptive_Budget = iif (gl_detail.object < "500000", gl_detail.budget, -gl_detail.budget)

    ~~~ Adaptive_Actual = iif (gl_detail.object < "500000", gl_detail.actual, -gl_detail.actual)

    ~~~ Adaptive_Remaining = gl_detail.Adaptive_Budget - gl_detail.Adaptive_Actual

     

Viewing 8 posts - 1 through 8 (of 8 total)

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