Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Comparison of Amnts in 2 tables with different agg Post Reply Post New Topic
Author Message
John2Chr
Newbie
Newbie
Avatar

Joined: 11 Apr 2012
Online Status: Offline
Posts: 10
Quote John2Chr Replybullet Topic: Comparison of Amnts in 2 tables with different agg
     Posted: 26 Mar 2013 at 9:54am

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 agregate 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.

IP IP Logged
John2Chr
Newbie
Newbie
Avatar

Joined: 11 Apr 2012
Online Status: Offline
Posts: 10
Quote John2Chr Replybullet Posted: 27 Mar 2013 at 7:52am
I'm thinking running total is the answer but not sure how to implement.
IP IP Logged
Sastry
Moderator
Moderator
Avatar

Joined: 16 Jul 2012
Online Status: Offline
Posts: 537
Quote Sastry Replybullet Posted: 29 Mar 2013 at 2:02am
Hi

Can you give some sample data with duplicate details. Once we see how it is displaying data in the report then we can come out with a solution.

Edited by Sastry - 29 Mar 2013 at 2:04am
Thanks,
Sastry
IP IP Logged
John2Chr
Newbie
Newbie
Avatar

Joined: 11 Apr 2012
Online Status: Offline
Posts: 10
Quote John2Chr Replybullet Posted: 29 Mar 2013 at 3:11am

Thank You...

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

IP IP Logged
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum