Missing data and duplicate entries in crystal report. Will Union all

[StephenBye]

I am trying to produce a budget report for each department which details the nominal code and the respective totals for actual and budget using department as a header. The report I have produced is missing actuals where a budget does not exist for that nominal code in the other table (Audit_Split). The budget is also duplicated for lines where there have been more than one posting of an invoice per nominal code.

I am trying to write a “union all” query to avoid the missing actual figures but it does not like the statement below, any suggestions on how to rectify this?

AUDIT_SPLIT holds each transaction whereas FINANCIAL_BUDGET holds only summary information.

SELECT NOMINAL_CODE,DEPT_NUMBER,BUDGET,NET_AMOUNT
FROM AUDIT_SPLIT
UNION ALL
SELECT
NOMINAL_CODE,DEPT_NUMBER,BUDGET,NET_AMOUNT
FROM FINANCIAL_BUDGET

Is there anyway to eliminate duplicate lines from the budget figures, I have used “Supress duplicates” which does not display the duplicates but when I total the columns the duplicates are included.

Finally, we also need this data i.e. Nominal, Dept, Budget, Net Amount for a particular month and also cumulatively. So the report will have 2 sections side by side on with data for July only and the other with April to July figures on.

Any help would be greatly appreciated

 

[lbass]

I don't think you should start out assuming this is the solution. In your current report, you should be using a left join FROM the audit_split table TO the budget table, with no selection criteria on the budget table. This will allow all actuals to appear. There are various ways to handle duplication, including grouping, using running totals for calculations, and using suppression.

Regarding the two side by side sections: You either need to add the section as a subreport or you could allow records for all relevant dates into the report and then insert a crosstab.

-LB

 

[StephenBye]

Thank you for your suggestion. I have tried every combination of join types and enforcements a while ago but still could not crack it. I have joined the tables using Nominal code and also Department code still, without success. I have also tried having the tables joined by Nominal and Department.

This is a sample of the report I need to produce for each Department:

Month Year to date
Code Actual Budget Variance Actual Budget Variance
4500 50132 50132 0 150396 150396 0
4501 15467 15467 0 46402 46401 1
4502 24169 24169 0 72508 72507 1
5000 -3526 -2083 -1443 -8526 -6249 -2277
5002 -749 0 -749 -1430 0 -1430
5010 -87 -388 301 -459 -1164 705
5020 0 -42 42 0 -126 126

The budget figures are stored by Year and Period for example budget for April this year is 2012 and 01. The actual data is stored by calendar days in another table.

There may be an added complication in that Nominal_Code and Dept_Number in the Audit_Split table are named Account_Ref and Analysis_ID in the Financial_Budget table.

 

[lbass]

What fields you should join depends upon the logic of your database, so it is difficult to make a suggestion without knowing your database. The names of fields you are joining are irrelevant--it is the values of the fields that must match. I think your first step is to learn more about the tables and fields by laying them out in the detail section so you can understand better how they relate to each other.

It appears that you need help with many aspects of this report. These forums are geared more toward to providing suggestions regarding specific issues you run into as you develop a report, not assisting in developing a report in its entirety.

Your first issue is probably the joins. Assuming that you should join on department and nominal code, is the resulting problem the duplication? You may not be able to eliminate duplication directly, but instead you could work around it. First step is to show how the data looks after making these links and placing the relevant fields in the detail section, so we can see how it is duplicating. Then someone could suggest how to deal with it.

-LB