Compare Amounts in two tables with different aggregation levels

[John1Chr]

Crystal 8.5 and DataSource Type SQL

I would like to resolve without sub-reporting, if possible.


Two tables (1. Sal Projection & 2. Exp Budget) at different agregation levels and want to sum both up by

Field A : Dept Budget (CF3 linking tables by this field)
Field B : Account (linking tables by this field)
Field C : Fund (linking tables by this field)
Field D : Sal Proj Amount (from table 1)
Field E : Expenditure Amount (from table 2)
Field F : Diff Amt. : Sal Proj Amt - Exp Budget Amt

The issue is that Sal Projection Actual Paid Amt is detailed all the way down to the employee id level and Expense budget has accounting periods to sum up to get the Expenditure Amount used to compare to the Sal Projected Amount.

I can easily get one side of the equation to not duplicate by placing the detail fields A - C and D or E from one table in the detail section suppressing and grouping in the footer using max of and selecting Database/Select Distinct Records.

Is there a way to get both tables in the report with amounts summed up to compare without running into the duplicates issue? I would like to do this without subreports if possible.

 

[IanWaterman]

YOu can use a Running Total for the second table making sure you use the correct evaluate on level

Ian

 

[John1Chr]

I think you're right - running total will work. I googled a little and saw that this can be done by formula. I'm struggling with agregating both tables to the Dept Budget level so that I can compare.

I'm trying to use the running total field and I am evaluating by change on Dept Budget(CF3). I set-up two separate running totals: 1 by Sal Project Amt and the other by Exp Budget Amt. It appears to give me just one amount for one employee id and one amount for one accounting period. It is not summing but only giving me 1 amount. I cannot get the two separate tables to sum up by Dept Budget (CF3) level. I'm putting the amounts in the detail section not sure if it should be somewhere else.


Not sure if this was necessary but I added another central table (Dept Budget table) that is summarized by Dept Budget(CF3) and I'm linking from that table to table 1 and from that table to table 2. I am not joining table 1 and table 2 anymore.So I am trying to summarize both tables by Dept Budget (CF3) and using the Running total amount to evaluate on change of Dept Budget Table Dept Budget(CF3) field.

Table 3 Dept Budget Table joins to Table 1 by:
Fiscal Year
Fund
Dept Budget(CF3)

Table 3 Dept Budget Table is joined to Table 2 by:
Dept Budget(CF3)
Fund
Budget Period (same as FY)

Not sure where to go with it but I will keep chopping away.

 

[IanWaterman]

Alternatively create a view of the detail table which aggregates at the same level as your second table. Then use this view joined to second table in the report.

Ian

 

[John1Chr]

Sorry....haven't used the view (stored procedure). I want to ensure that this can be converted to crystal 11 and that I can feed parameters into the report for the first 3 characters of dept budget, fund and fiscal year and account. I didn't mention but I will also be filtering and comparing by Account field. I will compare accounts 41000 and 41030 between the tables 1 & 2 but i also want to grab accounts 41050 and 41070 and sum from the 2. Expense budget table.

So, what your saying is crunch table 1. Sal Projection and table 2. Expense Budget to equal table 3. Dept Budget in a stored procedure. Fiscal Year is the only parameter in Table 3 that would cause the Dept Budget(CF3) field to double up. There is only one unique Dept Budget Field per year on this table and it is a very summarized table. Sal Projection table is too detailed ie by employee and I need to crunch the Expense Budget table to sum up all accounting periods.


I was hoping a formula would work in the running total option. Sounds easier but might not be? What might a generic SQL based stored procedure look like to crunch the two tables?

I didn't mention but account is also something in table 1 and table 2 that I will use to compare. In table 1 I grab account 41000 and account 41030 only. In table 2 I grab those two same accounts to compare the difference but I also grab accounts 41050 and 41070 and sum out and lay it on the line.

I was thinking if I could get the two amounts summed up by one of the accounts ie 41000 I can do the rest on my own.

There are quite a few posts in this forum on running totals and stored procedures...I will try to go through.

 

[John1Chr]

Trying to solve with running total thinking that somehow I need to keep the employee ids from duplicating and accounting periods from duplicating at the Dept Budget level in those tables and then sum up.

 

[John1Chr]

Here is a more detailed explanation - I added a new table third table and am linking to the other two detail tables. 1 to Many relationship to both tables. SQL is below.

The issue that I have is that the APPROP(ie.budget) table has one DEPT_BUDGT per year but the salary proj table has the DEPT_BUDGT by employee - so multiple DEP_BDGT fields because it is so much more detail. The EXP_BDGT table has to be summed to the DEPT_BDGT level...it has many accounting periods.

I want to get by DEPT_BUDGET W_SALARY_ACTUALS W_EXPENDED_AMT and Difference

I've narrowed to focus on one DEPT_BUDGT so what I want is:


DEPT_BUDGT W_EXPENDED_AMT W_SALARY_ACTUALS DIFFERENCE
G1001 1000 600 400


I can get one side of the equation easily using running totals and evaluate but I can get both together to compare. I would like to avoid sub-reports if possible.

I think it might be able to be solved with stored procedure but running total sounds easier.



SELECT DISTINCT
APPROP."DEPT_BUDGT", APPROP."BUDGET_PERIOD",
EXP_BDGT."ACCOUNT", EXP_BDGT."W_EXPENDED_AMT",
SAL_PROJ."ACCOUNT", SAL_PROJ."W_SALARY_ACTUALS"
FROM
"SYSADM"."APPROP" APPROP,
"SYSADM"."EXP_BDGT" EXP_BDGT,
"SYSADM"."SAL_PROJ" SAL_PROJ
WHERE
APPROP."DEPT_BUDGT" = EXP_BDGT."DEPT_BUDGT" (+) AND
APPROP."FUND_CODE" = EXP_BDGT."FUND_CODE" (+) AND
APPROP."BUDGET_PERIOD" = EXP_BDGT."BUDGET_PERIOD" (+) AND
APPROP."BUDGET_PERIOD" = SAL_PROJ."FISCAL_YEAR" (+) AND
APPROP."FUND_CODE" = SAL_PROJ."FUND_CODE" (+) AND
APPROP."DEPT_BUDGT" = SAL_PROJ."DEPT_BUDGT" (+) AND
APPROP."BUDGET_PERIOD" = '2013' AND
APPROP."DEPT_BUDGT" = 'G1001' AND
SAL_PROJ."ACCOUNT" = '4100' AND
EXP_BDGT."ACCOUNT" = '4100' AND SAL_PROJ."FISCAL_YEAR" = 2013 AND SAL_PROJ."DEPT_BUDGT" = 'G1001' AND SAL_PROJ."ACCOUNT" = '4100' AND EXP_BDGT."BUDGET_PERIOD" = '2013'
ORDER BY
APPROP."DEPT_BUDGT" ASC