Detail and Summary report building

[jackie1948]

Can anyone give me a stable way to create a report that has detail lines (one to many) and a summary line on that account (one to one). I have created one but it is not stable.

 

[jage]

Hi,

Need more information, are you using Impromptu build this report? What do you mean by 'unstable'?

 

[jackie1948]

I am using Impromptu

I have created a detailed balance report that has a summary line on a header line for an account code with calculations of (prior months full_value, activity for the current month and current months full_value). This information is calculated form the balance table with a join to the element table for a description.

I then have the detail tranactions for the month on the detail line for debit and credit with totals on these tranactions in the detail line. This information is coming from the dochead and docline tables with joins and they are joined to balance table also.

My report runs okay but it will randomly double the calculations on the summary line. Never the same account number and never the same company.

If I use distinct it will run perfectly but i can not use this in an accounting report as I need to know that i am accessing all my data.

hope you can help me.

 

[jage]

Sounds like an issue with your joins. How/why are you joining your dochead and docline with your balance table?

 

[jackie1948]

the join to balance is for the account code. we have looked at the joins and can not see anything wrong there. any other suggestions. Thanks for your help

 

[achaaran]

Sounds like a join problem. The reason I say this is because you use "DISTINCT" and it works. I know this isn't the answer you want to here, but in my experience, 99% of the times, a DISTINCT would suffice.

Sorry I couldn't be of more assistance.

 

[jackie1948]

Thanks for your help. i reckon in need to take another look at the joins and see if I may have over looked something.
Thanks again