Home / Tech Tips / Crystal Reports / Crystal Reports with Dynamic Report Field Selection – Part 2
InfoSol Blog Tech Tip

Crystal Reports with Dynamic Report Field Selection – Part 2

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:

Example 1

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:

Example 2

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

Example 3

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:

Example 4

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:

Example 5

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

Example 6

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

Example 7

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!

About Rajesh Ponnurangam

Rajesh Ponnurangam is a Senior Business Intelligence Consultant with Infosol. He is a SAP certified associate with extensive experience in design and development of Business Intelligence applications for apparel, retail, consumer goods and manufacturing industries. He specializes in Business Objects solutions, SAP HANA and BW data modeling. He has a proven track record in designing strategies for ETL, BI application development, implementation, rollout and support.

Check Also

Web Intelligence: The Future’s So Bright, You Better Wear Shades! Feature Image

Web Intelligence: The Future’s So Bright, You Better Wear Shades!

I’m passionate about Ancient Egypt and I jump at any opportunity to indulge in discussing ...

3 comments

  1. Hi Rajesh,

    I tried doing this exact thing but my formula is not being accepted in the conditional format window for the size and position option of an object. The formula that i am using is same as yous and given below:
    if ({?Select the Fields} != ‘Related Resolution’ AND {?Select the Fields} = ‘Gating’)
    then -2160
    now whenever i try to check it and save and close it throws me different errors as i tried to play around and see what is the issue, the errors i encountered are given below:
    1)A number, date,time,date-time,boolean, or string is expected here.
    2) The keyword ‘then’ is missing.
    3) The ) is missing.

    If you can help me resolve this it will be a great help. Thanks in advance!

    -Devina

  2. Hi Devina, Raj is going to email you directly because looking at this formula, on the surface it all looks good and it should work. Sorry for the delay, your comment ended up in his email spam.

  3. Hi Devina,

    The “not equal to” formula has caused this issue. You have used ‘!=’ instead of ‘<>’. Try changing the formula to
    if ({?Select the Fields} <> ‘Related Resolution’ AND {?Select the Fields} = ‘Gating’)
    then -2160

    This should resolve your issue.

    Thanks
    Raj