Display field's list of values

[Former Member]

Hi gurus,

Do you know how to make a list of all the values of a field in a report's header? If I drag and drop the field in header it will only show the first value. Which formula do I have to use? I tried "join" but it does not work. Any solution?

Thanks and regards,

Victor

[abhilash_kumar · Accepted solution]

Hi Victor,

Looks like you're not using a Parameter because Join should work with a Parameter.

Here's what you need to do:

1) Create two Report Header sections

2) Insert a Subreport with the datasource poiting to the table with the field you need. Place the Subreport on the 'Report Header a' Section

3) In the Subreport create this formula:

shared stringvar lov;

lov := lov + {Database_Field} + ", ";

Place this formula on the Details Section

4) Suppress all the sections of the "Subreport"

5) Go back to the Main Report and create this formula:

shared stringvar lov;

Place this formula on the "Report Header b" Section

6) To get rid of the space taken up the Subreport, make it so small that it appears no bigger than a dot. DO NOT SUPPRESS the Subreport or the Report Header a Section. Just reduce the size of the Subreport.

Let me know how this goes!

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member · Accepted solution]

Hi Abhilash, it worked! thank you very much.

Do you know how to sort the list? I tried with crAscendingOrder formula inside the below formula, but it did not work.

shared stringvar lov;

lov := lov + {Database_Field} + ", ";  

Best regards,

Victor

[abhilash_kumar · Accepted solution]

Hi Victor,

Since this is a comma separated list, one way to sort it would be to put these values into an array and sort. The issue however is, an array can hold only 1000 values at a time and hence it will work only for 1000 values or less. Here's the formula:

WhilePrintingRecords;
stringvar lov;
stringvar array lov2 := Split(left(lov,len(lov)-2), ", ");
numberVar counter1;
numbervar counter2;
stringvar temp_arr;
for counter1 := ubound(lov2) to 1 step -1 do
(
          for counter2 := 1 to counter1 - 1 do
          (
                    if lov2[counter2] > lov2[counter2 + 1] then
                    (
                              temp_arr := lov2[counter2];
                              lov2[counter2] := lov2[counter2 + 1];
                              lov2[counter2 + 1] := temp_arr;
                    )
          );
);
Join(lov2, ", ");

Another way to do this is using Subreports. You send the comma separated list to the Subreport (whose datasource is same as the Main Report's) and then filter the records in the Subreport based on the list.

Once the Subreport lists the same values as the list, you can sort the records in the Subreport using the Record Sort Expert. As soon as you have a sorted list, you can introduce another shared variable that creates a comma separated string which is pre-sorted and which can be used in the Main Report.

Hope this helps!

-Abhilash

[Former Member · Accepted solution]

Hi Abhilash, Thanks again for your reply. If I use the formula, where do I have to put it? Below the following formula?:

shared stringvar lov; 

lov := lov + {field} + ", " ;

I tried as a new formula, and I dragged and drop the new formula in the subreport, but it did not work. Do I have to change the formula in the main report?

Best regards,

Victor

[Former Member · Accepted solution]

Already solved, thanks!

[former_member615585 · Accepted solution]

Its working fine but I am getting big white space between header and the multiple numbers which I have managed after making sub-reports. I made 6) To get rid of the space taken up the Subreport, make it so small that it appears no bigger than a dot. DO NOT SUPPRESS the Subreport or the Report Header a Section. Just reduce the size of the Subreport.

Please help how to reduce the space.

[former_member615585]

Got the solution. On the sub-report I have put the font size 1 and its working fine. Thank you guys.

[former_member615585]

Its working fine but I am getting big white space between header and the multiple numbers which I have managed after making sub-reports. I made 6) To get rid of the space taken up the Subreport, make it so small that it appears no bigger than a dot. DO NOT SUPPRESS the Subreport or the Report Header a Section. Just reduce the size of the Subreport.

Please help how to reduce the space.

[Former Member]

Hi Abhilash,

I tried this method but I keep getting an error message when I run the report:

" A string can be at most 65534 characters long. "

The Field Type is String [254].

Any suggestions?

Thanks,

Noemi

[DellSC]

This one you'll probably have to use a subreport for - it's the only way that you'll get multiple rows of data into the report header.

However, if you want to show just the selected items from the LOV, they're stored in an array and you would do something like this to display them as comma-separated values:

NumberVar index = 1;

StringVar result = "";

while index <= UBound({?My Parameter}) Do

(

  if index > 1 then result := result + ", ";

  result := result + {?My Parameter}[index];

  i := i + 1;

)

result

-Dell

[jpzanin]

Thank you!

Your suggestion worked perfectly for me.

I had to make just a little chance

NumberVar index = 1;

StringVar result = "";

while index <= UBound({?Status}) -1 Do

(

if index > 0 then result := result + " - ";

result := result + {?Status}[index+1];

index:= index + 1;

);

result