Unorthodox Grouping
-
Hi, I have created a table like below that sums journals into accounts (or objects).
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.
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
ENDthen 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 -
Glad you found a solution.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply