Dynamic Columns

[Former Member]

I am new to crystal reports. I have a report in actuate which needs to be converted to crystal reports. This report is based on the dynamic parameters it takes, it creates a dynamic sql and display the report output with columns which are dynamic. so in this report, the sql and the columns are generated dynamically. Say for one set of parameters, i can have 4 columns to be display and for another set of parameters , ihave 6 columns to display. Just wondering if this can be done in crystal reports.

Thanks,

Kiran

[Former Member · Accepted solution]

Kiran,

Assuming that you have already created the parameters that these dynamic columns will depend on:

1. Right click the heading of the column that you want to make dynamic and select "Format Field"

2. In the resulting "Format Editor" dialog box check the "Suppress" option and click the "X+2" button beside it to define the formula

3. In the resulting Formula Editor dialog specify an If condition similar to this

if {?Parameter1}="Yes" then true else false;

In your report replace parameter and value as required. click ok and close.

4. Repeat steps on other columns.

Regds,

Ryan

Edited by: Ryan Moore on Apr 15, 2009 3:51 PM

[Former Member]

Right now we are using Actuate and we have over 100 reports which is driven by one design file. There are a bunch of parameters that the report takes in both mandatory and optional parameters. Based on these parameters, the SQL gets generated which can have multiple columns to display. So report 1 can have 5 columns, report 2 can have 8 columns and so on. We are moving to Business Objects,so just wondering if this can be done in Business Objects. Since crystal reports is a part of BO, so trying to fiure out if crystal report has the capability to generated dynamic columns in a single report design file. So basically i wont have the columns placed on my design file and suppressing it based on parameters.

Here based on the SQL, the columns need to be created from scratch. so if you look at the design file, it would be blank and then create the columns based on the SQL.

Thanks for all your help.

Kiran

[Former Member]

HI Kiran,

What type of columns you are trying to pull using dynamic parameter ?

For example if you are pulling a year based on some parameters i.e. your Year field contains 4 years or 6 years. Is it some thing like ?

Thanks,

Sastry

[Former Member]

Can I just check something? From the way you describe it, your query could be either doing two totally different queries (example 1 below), or just essentially the same(ish) query, but with less/more columns (example 2). Can you let us know which you want to do?

Example 1:

If you enter a parameter value of say, "FruitSales", you might output the following columns output:

SaleDate

FruitType

Total

but if you used "FavouriteThings", you would generate a completely idfferent query with columns of:

ThingName

ThingColour

Example 2:

Use the parameter "FullDetails" to get:

SaleDate

FruitType

Quantity

PricePerUnit

CustomerName

GrandTotal

or use the Parameter "MinimalDetails" to get:

SaleDate

CustomerName

GrandTotal