Dynamic Columns Question

[baycolor]

I'm using CR XI. And CR Server to batch run report.

I have a simple list report about ten columns. And the report is dealing with some one-to-many issues by concatenating data into one column.

EXAMPLE: An asset that's downloaded can have many forms I concatenate each into the Asset Forms column by doing some grouping in CR.

[tt]
AssetId DwnldDate AssetForms
13 11/1/07 PDF;HTML;Word
13 10/2/07 PDF;HTML;Word
14 9/24/07 PDF;Word
[/tt]

Now the user wants a new column called Asset Type. Another 1 to many - an asset can have many types (Presentation, Proposal, etc). Easy enough to concatenate together like I did with the Asset Forms info but the user wants the types broken out into individual columns because they want to do Excel pivot tables using asset type values. NOTE: There are about 50 different types and each asset doesn't have the same types associated with it necessarily. So I need my output to look something like:

[tt]
AssetId DwnldDate AssetForms Presentation Proposal Other
13 11/1/07 PDF;HTML;Word X X
13 10/2/07 PDF;HTML;Word X X
14 9/24/07 PDF;Word X
[/tt]

I'm already doing grouping in the report. I can leverage this to create a field with concatenated asset type info per download row like the AssetForms column. Then hide this new column. Then create 50 more columns one for each asset type and then search the concatenated asset type field for each columns specific type value and populate the new column.

Kicker is the asset types are dynamic and can change at a moments notice. Tomorrow there may be 51 asset types so there would need to be 51 columns - must modify the report.

Anyone know of a smart way of keeping the grouping I already have for asset forms, dynamically creating these type columns and populating them?

Thanks in advance!!!

 

[lbass]

What are you grouping on? Also please show the content of {@AssetForms}.

-LB

 

[LughS]

Have you looked into using a crosstab? Everything you are saying there, especially the dynamic number of columns, sounds like that's what you want.

 

[baycolor]

To lbass:

The report is grouped by asset_id, download_date and a column I didn't list associate_id.

The field {@AssetForms} contains:
[tt]
WhilePrintingRecords;
global stringvar assetformname;
[/tt]

To LughS:

Can I do that after doing the grouping that I'm doing to hanlde the asset form one-to-many relationship?

 

[lbass]

I meant: What is the content of the formula that generates the results seen in {@AssetForms} (the accumulation formula)?

-LB

 

[baycolor]

Sorry here you go:
[tt]
WhilePrintingRecords;
global stringvar assetformname;

If assetformname = '' then
assetformname := {Command.ASSET_FORM_NAME}
else if InStr(assetformname, {Command.Command.ASSET_FORM_NAME}, 1) = 0 then
assetformname := assetformname + '; ' + {Command.Command.ASSET_FORM_NAME}
else assetformname
[/tt]

 

[lbass]

The only suggestion I have is that you could set up the value of the columns in the report header using an array, as in:

whileprintingrecords;
stringvar array y := ["Proposal","Presentation","Other"];
numbervar j := ubound;

Then set up columns in the group footer like this:

//{@col1}:
whileprintingrecords;
stringvar array y;
numbervar j;
stringvar assettype; //this is the variable that you used to collect assettypes in the detail section (if instr(assettype,{table.assettype}) = 0 then assettype := assettype + {table.assettype}) with reset formula in the group header.

if j >= 1 then
if y[1] in assettype then
"X"

//{@col2}:
whileprintingrecords;
stringvar array y;
numbervar j;
stringvar assettype;

if j >= 2 then
if y[2] in assettype then
"X"

You would create as many columns as what you guess to be the maximum number of columns.

Create column labels like this:

//{@Col1 label}:
whileprintingrecords;
stringvar array y;
numbervar j;
if j >= 1 then
y[1]

//{@Col2 label}:
whileprintingrecords;
stringvar array y;
numbervar j;
if j >= 2 then
y[2]

Once you have this set up, then as you add or subtract columns, all you have to do is change the array elements in the report header formula.

-LB