How to make a manual cross tab??

[AlexHarley666]

Friends:

I need to make this report:

STORE01 STORE02 ...
SALES CHARGE SALES CHARGE
PRODUCT1 100 10 80 8
PRODUCT2 50 5 90 9

with, SALES and CHARGE, columns for report and grouping by STOREi.

I did use Cross tab, but SALES and CHARGE appears vertically and columns are not dynamics, for instance:

STORE01 STORE02 ...
PRODUCT1 100 80
10 8
PRODUCT2 50 90
5 9

Is necessary to make a manual cross tab for that one???
In this case, how I make a manual cross tab??

very thanks in advance,

ALEX.

 

[lbass]

I think you would need to do a manual crosstab, although it could be quite a bit of work just to get the horizontal layout, depending on the number of stores, etc.

There are several FAQs about how to do this, but basically, you create formulas like the following:

first column:
if {table.store} = 1 then {table.sales}

second column:
if {table.store} = 1 then {table.charge}

third column:
if {table.store} = 2 then {table.sales}

fourth column:
if {table.store} = 2 then {table.charge} //etc.

If you don't have duplicate data, you would then group on {table.product} and then insert summaries on the above formulas to get group and report level totals, and then suppress the details.

-LB

 

[Ngolem]

I usually only do manual cross tabs...it aint so bad once you know what is going on

STORE01 STORE02 ...
SALES CHARGE SALES CHARGE
PRODUCT1 100 10 80 8
PRODUCT2 50 5 90 9

In your case one thing you can to to simply things is get all of the store names first in a subreport and save them to a shared array. Put this subreport in the report header and suppress all sections, making the report header as small as possible so it isn't visible

So we now have a shared variable

Shared StringVar array Store;

MAKE SURE YOUR Store array is initialized to "" for each element of the array

Now group your report as follows

Group 1 : A dummy header (Suppress the footer)

Group on this formula ... the purpose of this header is to create the column headings

//@Group1

If 1 = 1 then
"header"
else
//doesn't matter which one as long as it is a string
//It will never be used as a group but it fools CR
{table.stringvalue field};

Group 1 : {Table.Product} suppress header
Group 2 : {Table.Store} suppress header and footer
Details : suppress

Now in Group 1 header you arrange your columns


The stores are displayed by using formulas like the following

@DisplayStore1

WhilePrintingRecords;
Shared StringVar array Store;

Store[1];

So now you arrange your columns like this

Product {@DisplayStore1} {@DisplayStore2} ....
Sales Charge Sales Charge
------------------------------------------------------

Now place the following initialzation formula in Group 2 header (the product group)

//@Intialization

WhilePrintingRecords;
if not inRepeatedGroupHeader then
//initialize the same number of elements as in array
//Stores and make them strings
(
StringVar array Sales := ["","","",...."","",""];
StringVar array Charges:= ["","","",...."","",""];
);
""; //this makes the formula legal

Now in the detail section place the following formula

//@CollectData (suppressed)

WhilePrintingRecords;
StringVar array Sales ;
StringVar array Charges;
Shared StringVar array Stores;
NumberVar pointer;
numberVar flag := 0;

for pointer := 1 to ubound(Stores) do
(
if stores[pointer] = {table.stores} then
(
Sales[pointer] := totext({table.sales},2);
Charges[pointer] := totext({table.charges},2);
flag := 1;
);
if flag = 1 then exit for;
);

This traps all the data for that product ....if a particular store did not sell this product then the vaues are null and nothing will be displayed later

now you place the following display formulas in the appropriate spots in the Product group footer

//@Store1Sales

WhilePrintingRecords;
StringVar array Sales ;

Sales[1];

//@Store1Charges

WhilePrintingRecords;
StringVar array Charges;

Charges[1];

Your footer will look like this

{Table.Product} {@Store1Sales} {@Store1Charges} {@Store2Sales} {@Store2Charges} ....

Tedious but that is how it is done

Hope this helps you




Jim Broadbent

 

[brendanj68]

Hi Ngolem,

Was tring to follow your advice on this issue but have run into some problems - unexplained eror messages, etc., wondered if you could point me a way out of them. Am running CR9/ SQL 2000.

1. array 'Store' - error Result of a formula can't be an array (?)
2. My grouping is as I as follows (again if I've understood your advice correctly)
GH1 - @Group1 (Supp)
GH2 - {Table.Skils} & @Initialisation (Supp)
GH3 - {Role.Type} (Supp)
D - @CollectData (Supp)
GF3 - Blank (Supp)
GF2 - Blank (Supp)
GF1 - Details

This is the first time I've tried manual x tabs, so forgive me if I've got this completely wrong.

Thanks in advance