Using Group Names in Manual Crosstab Headings

[DuncanSutcliffe]

Using Crystal V.10 connecting to SQL Server 2000 via an ODBC DSN.

I am building a manual crosstab and need to populate column headings based on the group names for group one. Group one is from a formula, as the user can select how to group the report at runtime via a parameter.

There could be between 4 and 13 column headings. I was wondering if there is a way of perhaps loading the group names into an array and then using the array elements?

It has to be a manual crosstab as I'm supporting drill-down.

TIA,
Duncan

 

[lbass]

I'm assuming that when you refer to "Group One" you actually are referring to the column field of your manual crosstab, and that you are using a different field for your group field. The following is an example using the Xtreme database in CR 8.0:

First create a string paramater {?column field}, with multiple string results. In this example, they are "Shipper","Customer ID", "Order Date". Then create the following formulas:

//{@colarrays} to be placed in the report header and suppressed:

stringvar array z;

if {?column field} = "Shipper" then
z := makearray("FedEx","Parcel Post","Purolator","UPS","Loomis","Pickup")else
if {?column field} = "Customer ID" then
z := makearray("001","003","005","007") else
if {?column field} = "Order Date" then
z := makearray(totext(Date(1996,12,06),"yyyy/MM/dd"),totext(Date(1997,01,01),"yyyy/MM/dd"),totext(Date(1997,02,26),"yyyy/MM/dd")) else
z := "";
z[1];

//{@column field}:
Select {?column field}
case "Shipper" : {Orders.Ship Via}
case "Cust ID" : totext({Orders.Customer ID},"000")
case "Order Date" : totext({Orders.Order Date},"yyyy/MM/dd")
default : "";

//{@col1label}:
stringvar array z;

if ubound(z) >= 1 then
z[1]

//{@col2label}:
tringvar array z;

if ubound(z) >= 2 then
z[2]

Create additional labels up to the maximum number in the largest array. The following are the manual crosstab detail formulas:

//{@col1amt}:
stringvar array z;

if ubound(z) >= 1 then
if {@column field} = z[1] then {Orders.Order Amount}

//{@col2amt}:
stringvar array z;

if ubound(z) >= 2 then
if {@column field} = z[2] then {Orders.Order Amount}

Repeat these formulas up to the maximum number of columns.
After placing these formulas in the detail section, you can insert summaries on them at the group and report level, and then suppress the details. Then remove the automatic column headings (Col1amt,Col2amt, etc.) and replace them with {@Col1label},{@Col2label}, etc.

-LB

 

[DuncanSutcliffe]

Thanks for another good answer, LB.

Unfortunately I need to read the array values from somewhere, I can't hard code them. The column names are to be the same as the group names from group one, but the group one names won't be known until runtime - and there are 34 possible fields that could be grouped on, each with up to 13 group members.

I can make this work in a footer by setting a counter, Redim Preserve Array[counter] and Array[counter] := GroupName({Group1}).

But then my drill down will be messed up as details will apear above the footer where the groups are.

I need to somehow read the group names into the report header. I'll keep staring at it a while!

Duncan

 

[DuncanSutcliffe]

Solved it!

You put an unlinked subreport in the report header. In this you have a shared stringvar array which you dynamically populate with the values from the lookup table on which the main report's groups are based.

Then call that shared array's members into the required number of formulas.

Duncan