Creating a Dynamic List

[Former Member]

I am new so i apologize upfront for being a newbie. I have need of creating a report with dynamic fields. What i need for the report to do is if there is a value in that field to enter the title and value, if not then go to next field and enter that information if there is a value etc etc etc. Building a list as it runs.

For example of output:

     field 1     xxxxx

     field 3     xxxxx

skipped field 2 because the value was 0 but put the line in the spot that would have had field 2. I have about 30 Fields that i need to sort in a report but don't want them to show for each customer unless they have a value in the field.

Is this possible and if so can i get some detail on how to create the formula/parameter what ever i need to complete this task?

Thanks in advance!!!

[DellSC · Accepted solution]

If your fields are going to be in a single column like you show above, try this:

1.  Create a details section for each field that you want to display.

2.  Line up the fields vertically, one in each section.

3.  Go to the Section Expert (right-click in the gray area to the right of the sections).

4.  For each section

     a.  Click on the formula button to the right of the "Suppress" check box. 

     b.  If you see a drop-down box in the menu bars that says "Exceptions for Nulls", change it so that it says "Default Values for Nulls".

     c.  Enter something like this in the formula:

          {<field that's in the section>} = 0

     d.  Click on "Save and Close".

     e.  DO NOT check the Suppress check box!

If the field that you want to display is a string, change the formula from step 4c to something like this

     {<field that's in the section>} = ""

-Dell

[Former Member · Accepted solution]

well that is getting me started down the right path i think but i don't want the whole record to be suppressed if the value of the field is 0 i just don't want the field to show up in the record that has a 0 value. So if record one has a field one value of 0 but has a field two value of 10 i want the following report:

     Customer Name          Field2 10

But the next record might be:

     Customer2 Name        Field1 10

                                       Field2 10

or even

     Customer3 Name        Field3 10

Hope that makes sense...

[DellSC · Accepted solution]

What I explained will run record-by-record if you do it in Details sections.  So, for each customer, you'll only show the data where they have non-zero values.  If you put all of the data in a single details section, you can suppress the individual fields, but you'll still have spaces where those fields would be located.  If you do it with multiple details sections with one field per section, everything will move up because the whole section (NOT the data row!) will be suppressed when the section's field value is 0.

I use this technique frequently for addresses that may or may not have multiple lines.

-Dell

[Former Member · Accepted solution]

Ok i see how that will work but the problem now is that forces those details items to below all of my other data... i.e.

Customer Name

                              Field2

                              Field3

Is there a way to create details sections to the right of other details fields so they would line up?

[DellSC · Accepted solution]

That is a more difficult challenge and will require some formulas.  Crystal won't "push" data together horizontally if a field is suppressed.  In this example I am assuming that you have no more than 5 fields.  You can expand on this if you need to.

For each field, create a "HasData" formula that looks like this:

{@Field1HasData}

{Field 1} <> 0

{@Field2HasData}

{Field 2} <> 0

etc.

Then create one formula for each column:

{@Column1}

WhilePrintingRecords;

BooleanVar suppress1 := false;

NumberVar lastCol := 0;

NumberVar col1;

if {@Field1HasData} then

  (

     col1 := {Field 1};

     lastCol := 1;

  )

else if {@Field2HasData} then

  (

     col1 := {Field 2};

     lastCol := 2;

)

else if {@Field3HasData} then

  (

     col1 := {Field 3};

     lastCol := 3;

  )

else if {@Field4HasData} then

  (

     col1 := {Field 4};

     lastCol := 4;

)

else if {@Field5HasData} then

  (

     col1 := {Field 5};

     lastField := 5;

)

else  suppress1 := true;

col1

---------------------------------------------------------------

{@Column2}

WhilePrintingRecords;

BooleanVar suppress2 := false;

NumberVar lastCol;

NumberVar col2;

if lastCol = 1 then

  (

     if {@Field2HasData} then

       (

           col2 := {Field 2};

          lastCol := 2;

      )

     else if {@Field3HasData} then

       (

          col2 := {Field 3};

          lastCol := 3;

       )

     else if {@Field4HasData} then

       (

          col2 := {Field 4};

          lastCol := 4;

       )

     else if {@Field5HasData} then

      (

          col2 := {Field 5};

          lastField := 5;

      )

     else  suppress2 := true;

)

else if lastCol = 2 then

(

     if {@Field3HasData} then

       (

          col2 := {Field 3};

          lastCol := 3;

       )

     else if {@Field4HasData} then

       (

          col2 := {Field 4};

          lastCol := 4;

       )

     else if {@Field5HasData} then

      (

          col2 := {Field 5};

          lastField := 5;

      )

     else  suppress2 := true;

)

else if lastCol = 3 then

(

     if {@Field4HasData} then

       (

          col2 := {Field 4};

          lastCol := 4;

       )

     else if {@Field5HasData} then

      (

          col2 := {Field 5};

          lastField := 5;

      )

     else  suppress2 := true;

)

else if lastCol = 4 then

(

     if {@Field5HasData} then

      (

          col2 := {Field 5};

          lastField := 5;

      )

     else  suppress2 := true;

)

else suppress2 := true;

------------------------------------------------------------

{@Column3}

WhilePrintingRecords;

BooleanVar suppress3 := false;

NumberVar lastCol;

NumberVar col3;

if lastCol = 2 then

(

     if {@Field3HasData} then

       (

          col3 := {Field 3};

          lastCol := 3;

       )

     else if {@Field4HasData} then

       (

          col3 := {Field 4};

          lastCol := 4;

       )

     else if {@Field5HasData} then

      (

          col3 := {Field 5};

          lastField := 5;

      )

     else  suppress3 := true;

)

else if lastCol = 3 then

(

     if {@Field4HasData} then

       (

          col3 := {Field 4};

          lastCol := 4;

       )

     else if {@Field5HasData} then

      (

          col3 := {Field 5};

          lastField := 5;

      )

     else  suppress3 := true;

)

else if lastCol = 4 then

(

     if {@Field5HasData} then

      (

          col3 := {Field 5};

          lastField := 5;

      )

     else  suppress3 := true;

)

else suppress3 := true;

--------------------------------------------------------

And so on.

Place each column formula on the report.  Then, set the suppression formula for each like this (example is first column):

WhilePrintingRecords;

BooleanVar suppress1;

suppress1

Since we're setting the suppress value for each column to true if there is no data to display in that column and the suppression formula looks for a result of true or false, we do not need to say "suppress1 = true" because it's already true or false.

-Dell

[Former Member · Accepted solution]

OK well I have actually accomplished what I was trying to accomplish by creating a sub-report using the same format as your original suggestion and placing it within the original. I had over 30 item possibilities that I needed to have as possibilities in the report so it was making it a bit more difficult.

So the original report contains customer name, address, locations etc etc. Second report contains variable data. I inserted the second report into the first as a sub report and linked the two based on a primary key(then suppressed this key so it wouldn't show in report). The second report has all the 30+ fields in individual detail sections with the suppression suggestion that you originally sent. Now since the second linked report is a separate "field" now I can place it anywhere on the details section of original report, lining it up horizontally and accomplish my task.

Thanks for the suggestions that lead me to the outcome that I needed to accomplish my reporting needs!

-Otis