Crosstab with multiple rows based on varying fields

[rrichardet]

I'm not even sure if a cross-tab is the right solution, but I need a report that looks like this:

11-2001 12-2001 Total
Open Companies 20 10 30
Open Accounts 40 5 45
Active Companies 10 8 18
Active Accounts 38 5 43

New Companies = Account Type = "C" and Status Code = " "
Open Companies = Account Type ="I" and Status Code = " "
Active Companies = Account Type = "C" and Active = "Y"
Active Accounts = Account Type = "I" and Active = "Y"

My data source is a list of accounts with the above fields (month, account type, status code, active)
The row/column intersect should be a summation of those specific categories. I do not need a Grand Total at the bottom, but would live with it if I could figure out the rest.

I have tried using custom formulas...the problem seems to be the pattern of having Company and then Accounts.

Any help is appreciated!
rhonda.richardet@bankofamerica.com

 

[chelseatech]

Use a formula {@GroupSeq} to create the right sequence... so
if {Account Type} = "C" and {Status Code} = " " then 1 else
if {Account Type} ="I" and {Status Code} = " " then 2 else
if {Account Type} = "C" and {Active} = "Y" then 3 else
if {Account Type} = "I" and Active = "Y" then 4 else 5

Use this formula as the row in your crosstab.

Then use Customise Group Name to set a description for each of the different numbers.
["New Companies","Open Companies", "Active Companies", etc...] [{@GroupSeq}]

Customise Group Name is only available in Cr8 and CR8.5

One restriction of the Crosstab approach is that each record can only appear in one column/row combination, so if an account is New in November, it might be Active in Dec - a Crosstab won't show you that. And if there is no activity for a month, or for a specific group type they will be missing from the report. CR can't create data out of nothing.

In that case, do the entire report in the report footer using Conditional Running Total Fields. See next post for that approach....

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[chelseatech]

Solution using Conditional Running Totals is a lot more work, but may give you the result you require.

First, decide how many columns you want, and write a formula for the 1st of the month for each column heading...
This month:
Date(year(currentdate),month(currentdate),1)

Month-1:
numbervar y:=year(CurrentDate);
numbervar m:=month(CurrentDate) -1;
if m<0 then (y:=y-1; m:=m+12);
Date(y,m,1)

And continue with month-2, month-3, etc.
Place these headings across the page in the report footer and format them as Mar-2001. They have to be Date fields for the next bit to work properly.

Then Create a formula for MonthVal
if {table.date} >= {@This Month} then 1 else 0

Another formula for MonthVal -1
if {table.date} >= {@month-1} and
{table.date) < {@this month} then 1 else 0

And similar formulas for each month value....

Now the conditional Running Totals are really easy.
You want to sum the value formula for that month, and evaluate conditionally using the conditons in your origianl message.

Put a description beside the running totals, and haev one for each month/row combination. A bit more work than a crosstab, but it will show missing months/rows, and an account can change status each month.


Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[rrichardet]

Thanks so much for your suggestions. I was able to get what I wanted by simply using multiple crosstabs. After I got the grouping part down, it was easy! Thanks again