Crystal Reports with Dynamic Report Field Selection – Part 2

Related Articles

IBIS 2023: The ‘Everything BusinessObjects’ Event – AGENDA is LIVE

May 30, 2023

2023 Looking to be a Busy Year for the BusinessObjects Community

January 5, 2023

BusinessObjects Solid for the Next Decade and Beyond

December 6, 2022

In my previous blog I had explained a technique for dynamic Report field selection on a crystal report. If the order of the fields selected is changed we end up having empty spaces as shown below:

 

Here are the steps to be followed to avoid these inevitable spaces, when certain fields were not displayed.

The key to this solution is to use the twips measurement.

1 inch = 1440 twips

So to move the fields 1 inch, it needs to be moved 1440 twips.  To cover the empty space between Year & City, the City field needs to be moved 2 inches (which is 2880 twips).

Step 1:  Right click on the city field of the report, and select the Size and Position menu option:

 

Step 2: In the Object size and Position dialog box press the conditional format [x+2] button for the X Option and enter the following formula:

if ({?Select the Fields}<> ‘State’ AND {?Select the Fields} = ‘City’)

then -2880

 

Step 3 : Press the [Save & Close] button

Repeat Steps 1- 3 for the heading field too

Now if you execute the report with the fields – Year, City & Store, the report looks as shown below:

 

Even though the city field has moved next to Year, the Store field remains at the same spot.

Right click on the Store name field of the report and follow Steps 1 – 3 with the formula below:

if ({?Select the Fields}<> ‘State’ AND {?Select the Fields} = ‘City’)

then -2880

else if ({?Select the Fields}<> ‘State’ AND {?Select the Fields} <> ‘City’)

then -5760

else if ({?Select the Fields}= ‘State’ AND {?Select the Fields} <> ‘City’)

then -2880

The Store name field will move its position according to the selections made. If Year, State & Store name is selected, the output display will be:

 

If Year, City & Store name is selected, both the formulas are triggered and the output will be as shown below:

 

If Year & Store name is selected the output will be:

 

The twips measurements and dynamic selection can be utilized in various report development scenarios. In a way we are bringing in adhoc reporting capability in crystal reports.  Be on the lookout for more tech tips in the future!