Results 1 to 4 of 4

Thread: Dynamic Columns

  1. #1
    Join Date
    Mar 2005

    Unanswered: Dynamic Columns


    How can I display in Crystal reports columns that were added later on , that is after the Design of the report had been done and the application built and distributed.

    An example of such would be a user has the choice to add new characteristics to an item and would like to display this new characteristics in the Item's report. An item has one characteristics 'Color' for example and when we go to the Item report, we get this information (since at design time, we had that entry in the database) displayed as one column in the report.

    Now, later on, the end user decides to add a new characteristic to this item, let's say its 'Weight', How can he then go to Crystal report and have this Column available for him to select and display, WITHOUT having to go to the Open Code and enter the design again and added it that way.

    I wish someone can help me with this issue!
    Best regards.

  2. #2
    Join Date
    Mar 2005

    I don't think you have any options besides opening the report in Crystal and changing your field definitions there.


  3. #3
    Join Date
    Aug 2006
    If I understand you correctly, your end result will have variations like the following:

    Scenario 1...
    Item # Item Description Color Weight Dimensions
    Scenario 2...
    Item # Item Description Color Weight Unit Price
    Scenario 3
    Item# Item Description Unit Price Product Info Weight Color

    Item# and/or Item Description are always displayed with related attributes to the right (I'm assuming).

    NOTE: These instructions only work if all the attribute fields are of the same data type as they will be dynamically interchanged using a logic statement. This means, the "color" field and the "weight" field must both be Strings. I use this strategy for financial trending reports so the data type is always numeric. However, it seems most of your attribute fields will be of a String data type. You can create formulas in your report that will convert numbers to text using the ToText function. Then, simply use the formula instead of the database field. Or, if your organization uses Views or Queries as data source, convert the data type on that end.

    To create a report with dynamic columns...

    1) Determine the maximum number of columns the report page can handle. Or determine the reasonable number of attributes an end user would want to see. (Consider a report with 50 columns of attributes. It would be unreadable.)

    2) Create a string parameter for every attribute column on the report. Do not set the default values at this time (see next step).

    3) Open Note Pad (on your desktop under accessories). Type all possible attributes starting in top right corner with one below the other (see below).

    Product Info
    Unit Price

    Keep in mind, these will be listed in the pick list and displayed on the report as column headings.
    The value "None" is important. It will be used in a logic statement, later in these instructions.
    Save the file (as a text file, .txt) in a shared location all users of the report can access (or on the CE server if using Enterprise).
    Name the file similar to the .rpt file name combined with the word "attributes" or "parameter" so its easy to locate.

    4) Edit each parameter you created in step 2 and do the following:
    Click on "Set default values".
    Click on "Import pick list".
    Browse to locate the text file you just created in step 3.
    Click OK. The list is automatically populated with the values in the text file.

    5) Create empty formulas for the detail fields. The Design will look something like this...

    Item# Item Description {?Column 1} {?Column 2} {?Column 3}
    {db.Item} {db.ItmDsc} {@Col.1-Detail} {@Col.2-Detail} {@Col.3-Detail}

    Tip: Just use the Insert Fields box and create the formulas up front. Leave them empty. Place them on the Design Tab. Then use the Insert Fields box to edit them later.

    6) Edit the Col.1-Detail field. Enter the following logic...

    If {?Column 1} = "Color" Then {db.Color}
    Else if {?Column 1} = "Weight" Then {db.Weight}
    Else if {?Column 1} = "Product Info" Then {db.ProdInfo}
    Else if {?Column 1} = "Dimensions" Then {db.ItemDim}
    Else if {?Column 1} = "Unit Price" Then {db.UPrice}
    Else "";

    Save the formula changes, but don't exit the formula editor. Copy the entire logic statement, then exit.

    7) Edit {@Col.2-Detail}. Paste the logic statement from step 6 into the Formula editor. Use Find/Replace in the formula Editor to find "?Column 1" and replace with "?Column 2".
    Then save the change. Repeat this process for the remaining column detail formulas.

    8) You may also want to add conditional formatting to supress the columns that are not used.
    To do this you need to edit each field in the column (header and detail).
    Start with Column 1's heading...

    Right click on the parameter field, {?Column 1}.
    Select Format field.
    On the Common tab find Supress at the top.
    Go to the right and click on the "X+2" button.
    Enter the following logic in the Format formula editor:

    If {?Column 1} = "None" Then True
    Else False;

    Save, but don't close. Copy the statement. Close the Format formula editor.
    Repeat on the {@Col.1-Detail} field using the pasted statement.
    Repeat on the remaining columns, but remember to change the column number in the logic.

    How it works:

    1) The column heading will display the parameter value selected by the user.
    It will display just as you typed it into the default value list when creating the parameter.

    2) The detail section will display the corresponding attributes for each item record based on the parameter values provided at run time.

    NOTE: If your report uses summaries (totals, counts, etc), then you will have to create formulas for each summary by column number just as you did for details. When typing the formula logic, find the correct summary in the Report Fields window of the Formula editor. Apply the logic to the summary field just as you would a database field or parameter.
    If you're not sure on this just post a reply.

    Good Luck.
    Last edited by crystalK; 09-06-06 at 16:00.

  4. #4
    Join Date
    Mar 2005
    This is great information.
    Thank you Crystalk very much.
    I will try it and get back to you with my results.
    Thanks aagin :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts