In a list report, how to display multiple rows from the database table

[cognovice]

Hi All,
I need some assistance with how to display multiple rows from the database table as individual columns in the list report in report studio.  For a particular item code, there is a table of notes. It has sequence number as one column and notes as the second column.  Each row of the notes must be displayed as columns in the report.  sequence number 1 note is one column in the report, sequence number 2 notes is next column, and so on.

I have attached the spreadsheet which contains.

1) Database table where the values are stored
2) Report Design
3) Report Output - It shows that the values are displayed in each row for the same item code instead of one row and multiple columns

Your assistance will be appreciated.

Kind Regards
R

[MFGF]

Hi All,
I need some assistance with how to display multiple rows from the database table as individual columns in the list report in report studio.  For a particular item code, there is a table of notes. It has sequence number as one column and notes as the second column.  Each row of the notes must be displayed as columns in the report.  sequence number 1 note is one column in the report, sequence number 2 notes is next column, and so on.

I have attached the spreadsheet which contains.

1) Database table where the values are stored
2) Report Design
3) Report Output - It shows that the values are displayed in each row for the same item code instead of one row and multiple columns

Your assistance will be appreciated.

Kind Regards
R

Hi,

You would need to construct a number of calculated items in your report to do this - one for each of the columns you need.
The first one (Notational_Text for Seq. 1) would be if ([SEQ.] = 1) then ([NOTATIONAL_TEXT]) else (null)
The second one (Notational_Text for Seq. 2) would be if ([SEQ.] = 2) then ([NOTATIONAL_TEXT]) else (null)
and so on.
You would need as many calculations as necessary to accommodate all the note rows (eg 10)
You could also add an extra one as a catch-all in case you get more rows than you expect - this one (Notational_Text for Seq. Overflow) would be if ([SEQ.] > 10) then ([NOTATIONAL_TEXT]) else (null)

Cheers!

MF.

[cognovice]

Hi MF,
Thanks for your response. I have already done this and when I generate the report, for the first row item, the sequence 1 is displaying in column 1 but for other columns it is blank.  the next row displays the same item code again but this time column 1 is blank and sequence 2 condition is satisfied and note for sequence 2 is displayed and all other columns blank.  then the same item is repeated in the 3rd row and the note for sequence 3 is displayed.  one single item is displayed in 5 rows with that many measures (Qty Ordered, Qty Supplied) and so on.  How can I get around this?

Thanks
R

[MFGF]

Hi MF,
Thanks for your response. I have already done this and when I generate the report, for the first row item, the sequence 1 is displaying in column 1 but for other columns it is blank.  the next row displays the same item code again but this time column 1 is blank and sequence 2 condition is satisfied and note for sequence 2 is displayed and all other columns blank.  then the same item is repeated in the 3rd row and the note for sequence 3 is displayed.  one single item is displayed in 5 rows with that many measures (Qty Ordered, Qty Supplied) and so on.  How can I get around this?

Thanks
R

Hi,

Try setting the Aggregation for these to Maximum and see if that fixes it.

MF.

[cognovice]

Hi MF,
Thank you so much.  It worked.

Thank you again for your prompt response.  Appreciate your help.

Kind Regards,
R