CrossTab - Specific Number of Columns in each Page 1

[smsaji]

Hi,

Using CR10, reporting off stored procedure. Using crosstab report. Have varying columns from 2 to 40 depending on the parameter.

Looking for a way to display 10 columns in each page.

Now using one of the solution from the posts here. It displays columns in different page instead of virtual page, using the foll. Formula

IF {?@Parm} ='X' then

(if {ProcName.ColName} in ["A" to "G"]
then 1
else if { ProcName.ColName } in ["H" to "P"] then 2 else 3)

But some Parameters bring out more than 10 columns in “H” and it runs to an virtual page. So Looking for a way to print specific number of columns, say 10 columns in each page.

Will appreciate anyone’s solution.

Thanks
saj

 

[lbass]

To use that approach, the column names must be unique to each column. If column "H" has 10 columns, then you must have two column fields?

Please see Solution #2 in my last post in thread149-943207 for another approach.

-LB

 

[smsaji]

Thanks, lbass. Will try the solution.

As for as the headings, it brings out like "HR PAyroll", "HR Admin", "HR Dept", so and so...

-saj

 

[lbass]

The solution you originally tried depends upon there being static columns, so that you can control the number of columns per page by defining the specific members of each group. If that can vary, then you need to try a solution like the one in the referenced thread.

-LB

 

[smsaji]

Hi lbass,

I tried, Solution #2 in post in thread149-943207.

Comes out like the foll:

1st Page - random 1 row with random 1 column
2nd Page - same 1 row with 8 columns
3rd Page - same 1 row with 13 columns (out of
remaining 30 columns, around 13 column shown,
others displayed nowhere)

4th Page - remaining 12 rows with 13 columns (out of remaining 30 columns, around 13 column shown, others displayed nowhere)

I made sure to follow the foll. steps.

Excerpt:
*****@@@@@@@@*******
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.

*****@@@@@@@@*******

Am I missing anything. I'm not using totals in report.

-saj

 

[lbass]

I just tested this again in CR 11.0 and it worked perfectly using one column field. Are you using more than one column field?

-LB