Cross tab report with summary 1

[mangledbabyducks]

I need to create a cross tab report that has a limited number of colums. This limit is required so that the cross tab will not run off the page of the report. Let's say the limit the columns is 6 but I have 8 columns of info. Can I show the first five columns and total items 6 thru 8 as one column? Also, there will be a total as the last column which totals up all columns. I am on Crystal Reports 8.5 using an InterBase database thru an ODBC connection.

Thanks,

MBD

 

[lbass]

It would help to know what your column field is and what datatype it is, but let's say it's an ID (number). Then you could create a formula like the following:

if {table.ID} in 1 to 5 then totext({table.ID},0,"") else ">5"

Use this as the column field instead of {table.ID}.

-LB

 

[mangledbabyducks]

Sorry LB,

The column header is a text string.

MBD

 

[lbass]

Let's say the column field is {table.city}. Then you could write a formula like {@city}:

if {table.city} in ["Paris","London","Oslo","Boston","Moscow"] then {table.city} else "All Others"

Then Use {@city} as your column field.

-LB

 

[mangledbabyducks]

Adding more problems to the problem. The column headings are user defined so I have no way of knowing what to include with the "in" clause.

MBD

 

[lbass]

You mean there is a parameter determining the column fields? You could create a formula like {@city} to use as the column field:

if {table.city} in [{?city}[1],{?city}[2],{?city}[3],{?city}[4],{?city}[5]] then {table.city} else "Other"

You would have to use a record selection formula of:

{table.city} = {?city}

...so that the "Other" column would show only the balance of those cities selected but not shown, and you'd probably want to show the parameter selection so that users could determine what was in the "Other" column. Use:

join({?city},", ")

...to display the parameters selected. In the crosstab, to show "Others" as the last column, click on the column field {@city} while in the crosstab expert->group options->specified order and add all possible column results from the dropdown list, selecting "Others" last. To have all options available in the dropdown, you would have to choose all parameter options when you first run the report.

-LB

 

[mangledbabyducks]

Sorry, this is the first time I've posted a question to this forum so I'm sorry for the lack of clarification.

When I said that the columns were user defined, I meant that the column description is created by the user at the time it is populated in the table of the database. The user can choose from a list of descriptions that have been previously created or type in a new one. And I have no control of the content of the list so I cannot specify the order beforehand in the report.

So what I am trying to do is pick up the first 5 descriptions for the columns and then, from column 6 on, total them in a single column.

Is there a way I can enumerate the columns as they come up but maintain the string headings and when the column number is greater then 5, dump all the others into a catch all column?

Hope this makes sense. See Below....
All
Column1 Column2 Column3 Column4 Column5 Others Total
Row1 1000 1500 1000 1500 1500 10000 16500

MBD

 

[lbass]

Okay, here are two solutions. The first one will show columns 1 to 5 with remaining columns summarized in an "Others" column. The second solution shows how to create a crosstab that "wraps" after a certain number of columns.

Solution 1:
Let's call your user defined column field {table.udf}. For this solution to work properly, you MUST first sort your report on {table.udf} (report->sort records). Then create a formula {@colbyorder}:

numbervar col;
stringvar x;

if instr(x,{table.udf}) = 0 then
(x := x + {table.udf};
col := col + 1);
if col in 1 to 5 then
totext(col,0,"") + " - " + {table.udf} else
"6 - Others"

Insert a crosstab and use {@colbyorder} as your column field. Use whatever group and summary fields you like. Place the crosstab in the report header or footer. If you want to identify what results are included in "Others", then create two formulas:

//{@detOthers} to be placed in the details section and suppressed:
numbervar col;
stringvar y;

if col > 4 and
instr(y,{table.udf}) = 0 then
y := y + {table.udf} + ", ";

//{@displOthers} to be placed in the report footer:
whileprintingrecords;
stringvar y;
left(y,len-2);

Solution 2:
First, go to report->sort records and sort your report by {table.udf}. Then create a formula {@grp}:

numbervar col;
stringvar x;

if instr(x,{table.udf}) = 0 then
(x := x + {table.udf};
col := col + 1);
if col in 1 to 6 then 1 else
if col in 7 to 12 then 2 else
if col > 12 then 3 //add other intervals as necessary

Then insert a group on {@grp}.

Next, insert a subreport. In the subreport, go to report->sort records, and add {table.udf} as the sort field. Then, still in the subreport, create a crosstab that uses {table.udf} as the column field, and then add your row and summary fields. Go to the customize style tab and check "Suppress Row Totals", since you probably are not interested in the subtotals for these columns.

Place the crosstab in the subreport report footer. Copy the {@grp} formula from the main report into a new formula within the subreport, add this field to the details section. Suppress all sections of the subreport except the subreport footer.

Link the subreport to the main report by linking the {@grp} formulas in the main report and subreport. Place the subreport in the main report group header or footer.

To show a total across all columns, you can do one of two things. In the main report, you can insert a crosstab into the report footer that uses only your row and summary fields.

Or, if you want the total next to the last group column, you can insert an unlinked subreport, again using just the row and summary fields, and place this next to your first crosstab (this assumes you have allowed room) in the group section. Then while in preview, select this new crosstab->format subreport->common->suppress->x+2 and enter:

not(nextisnull({@grp}))

This will suppress the total crosstab in all but the last group. If there will be a constant number of columns in the last group instance, you can format the total crosstab to suppress each row label and also, in the customize style tab->show grid lines->select the lines around the row labels and uncheck "draw" so that the grid only displays around the total column. Then you can align this to appear to be the last column in that group.

-LB