Help displaying uniques with multiple conditions

[jcl5]

Hi guys

I have a table of transactions (TRANS) which holds date of transaction (trans_date) and company reference (com_ref).

I have a summary report which shows the cumulative number of companies having one or more transactions by month (cumulative) - each company can be counted only once and is made up of running totals such using the formula {@Month}in [0,1], {@Month}in [0,1,2], {@Month}in [0,1,2,3] etc. Month 0 is 01/01/2002-31/12/2003

This gives the following result:-

Month 0 1 2 3 4 5
351 373 385 392 397 411 ...and so on.

This is fine but I now want to drill down on these totals and find the unique companies each month so for month 1, I am looking for 22 companies, month 2, 12 companies, month 3, 7 companies and so on.

So what I really want is all the companies who have had a transaction in each month but not in a previous month, e.g if they had a transaction in Feb amd Mar I can't count it in Mar.

I am sure there must be a simple solution to this.

I am using CR10 and Oracle 8.

Thanks in advance.

jcl5

 

[lbass]

First group on {trans.com_ref}, and if you wish, suppress the header and footer. Create a second set of running totals for each month where you select distinctcount of {trans.com_ref}, evaluate based on a formula:

month(minimum({trans.trans_date},{trans.com_ref})) = 1 //for the Jan count

Reset never. Change the 1 to 2 for February, etc, for the February running total. These running totals would be placed in the report footer also. Not sure what you intended by your reference to drilling down on the cumulative totals to see the distinct counts. Do you not want them immediately visible?

-LB