Crosstab or Dynamic Columns

[IanMac26]

Using CrXI on Windows XP.

I am doing a report called Profitability by Customer.

It is supposed to look something like

Customer 1 Customer 2 ..... Customer n Total
Bud Actual Bud Actual ..... Bud Actual Bud Actual
Sales $$ $$ $$ $$ ..... $$ $$ $$ $$
Credits $$ $$ $$ $$ ..... $$ $$ $$ $$
x% x% x% x% ..... x% x% x% x%
Net Sale$$ $$ $$ $$ ..... $$ $$ $$ $$
CoS $$ $$ $$ $$ ..... $$ $$ $$ $$
Grs Prf $$ $$ $$ $$ ..... $$ $$ $$ $$
x% x% x% x% ..... x% x% x% x%

then several expense lines and finishing with
NPBT $$ $$ $$ $$ ..... $$ $$ $$ $$
NP%Salesx% x% x% x% ..... x% x% x% x%
% Profity% y% y% y% ..... y% y% 100% 100%

where x% is the line above as a percentage of Sales
and y% is each customers contribution to the profit.

I can do all but the percentages in a crosstab.
I have also done a normal report with customers allocated to specific columns, but need to make it dynamic - can I define say 30 columns and then allocate the customers to columns while the report is running. Then I need to delete the surplus columns and move the total column beside the rightmost.

Cheers,
Ian

 

[CrystalizeCanada]

Hi Ian,

USING A CROSSTAB TO DO PERCENTAGES:
Doing calculations like percentages in a crosstab prior to version 12 (CR 2008) required a workaround. Ken Hamady has documented how to do percentages in a crosstab in vXI and earlier:

http://www.kenhamady.com/news0407.shtml

See if that helps

Gordon BOCP
Crystalize

 

[IanMac26]

Gordon,

That ws an interesting article but it doesn't quite answer my problems.

1) the x% are of Sales (the first row of the report). In the overall report I store the Sales amount as the Sales line is being printed - I can't see how to store the Sales for each customer.

2) The x% does not print for every row. In the overall report I have a seperate section that is suppressed unless the %s are required.

3) When the percentages are printed they are below the amounts, so the cell looks like
$Budget $Actual
x% y%
As far as I can tell you can only display the summaries horizontally or vertically, not both.

Cheers,
Ian

 

[CrystalizeCanada]

Hi Ian,

Not an easy problem.

I think using variables may still be a possibility.
Lets take only part of the crosstab which I've isolated below (to simplify things).
Customer 1 Customer 2 ..... Customer n Total
Bud Actual Bud Actual ..... Bud Actual Bud Actual
Sales $$ $$ $$ $$ ..... $$ $$ $$ $$
Credits $$ $$ $$ $$ ..... $$ $$ $$ $$
x% x% x% x% ..... x% x% x% x%
I'm assuming Budget and Actual are two summarized fields displayed horizontally. You're right - adding the third summary field(for your %) would go across not underneath. So that would not be good.
However another possibility springs to mind, instead of adding a third summary field - you could hijack the totals row (which would normally sum up the Sales and Credits) and use it's display property to display your % calculation, instead of the real totals. However this would be relatively complicated - you would have to capture the values you need into variables and know how to use various Crosstab functions like GridRowColumnValue and CurrentFieldValue.

Assuming that you got that working I was thinking you could have another crosstab underneath to display the other rows and (with suppressed column headings) - this kinda has the appearance of one crosstab.

Thats getting very complicated so one final possibility with crosstabs is to download CR 2008 (30 day trial) which has more flexible crosstabs, like allowing extra rows to be added. According to Ken, these 'somehow' still work when viewed in older Crystal.

Gordon BOCP
Crystalize

 

[CrystalizeCanada]

Hi Ian,
CREATING DYNAMIC COLUMNS:
It is possible to create dynamic columns if the crosstab route is too complicated. This is how I might approach it:

STEP ONE: Capture Customers Names Into Array
//this formula can be placed in the header or footer, details, anywhere really
WhileReadingRecords;
StringVar array Customers;//the array needed to store unique customers
if not ({Customer.Customer Name} in Customers) then (
Redim Preserve Customers[Count(Customers)+1];//make room for the customer name
Customers[count(customers)]:={Customer.Customer Name});//add the customer in the array
Join(Customers,chr(13))//just to see it works - also formulas can't return an array
STEP TWO: Create RunningTotals
Then create 30 seperate RunningTotals (placed in the footers) and choose to use a formula to evaluate (so it only sums for one customer)
stringvar array customers;
{Customer.Customer Name}}=Customers[1]//change this to 2, 3 etc
STEP THREE: Create Dynamic Headings
Stringvar array customers;
Customers[1]

There's more
Now you need add a bit more to check the number of customers you have as if you try and reference Customers[22] and only have 10 customers you will get an error.
So Just make these formulas conditional based on the count(Customers) and return a false or a 0 or whatever is appropriate.

Gordon BOCP
Crystalize

 

[IanMac26]

Gordon,

I have some code that finds the customers and have assigned them to the variables col1customer, col2customer etc so now each column will evaluate correctly - there are actually 8 groups and some tricky logic to get that to work. Now I can only fit about 8 customers across a landscape page - how can I make the report spread across more than 1 page like a crosstab does?

BTW I am currently downloading CR 2008 to have a look at the crosstab features in that.

Cheers,
Ian

 

[CrystalizeCanada]

Hi Ian,

A crosstab generates horizontal pages, which is a distinct type of page. I don't think you will be able to recreate that - you just have to widen the page by using a bigger paper size (but I'm not 100% sure).

And if I'm not mistaken in CRXI you are limited to paper size by the printer driver so some people install printers (like plotters) that allow for huge paper sizes. In 2008 I think that your paper sizes don't have to be associated with your printer.

I would post this question again - how to get a wider page or generate horizontal pages - which is what I think you want here to fit more fields into. You may bet some better ideas.

Gordon BOCP
Crystalize

 

[IanMac26]

Gordon,

I downloaded CR2008 and added a caclulated meber to my report - I think that would give the desired result although I still haven't worked out how to get "Sales" total to use in the percentage.

I saved the report an opened it with CR XI. It warned that some features would not work and so I was not surprised that the calculated member did not display.

I will mention to my client that CR 2008 may give the solution.