Rolling Field

[mgregory24479]

We have a field titled Date and a field titled amount. There can be multiple rows for these two fields. Someone has requested that we create a column in a report for each individual that states the following and they be on the same row. We want the columns to automatically be added as payment amounts and dates are added in new columns.

ID          Name                   Scheduled Payment 1 Date   Scheduled Payment 1 Amount          Scheduled Payment 2 Date                   Scheduled Payment 2 Amount
123456  Jane Doe               11/01/2019                        100.00                                            12/01/2019                                        100.00

Typically the schedule payment 1 Date and Amount would be on one row and subsequent schedule payment information (i.e., scheduled payment 2 date and amount, etc) will be in additional rows.

I know how to handle these if data wasn't going to be added so I need some help. What is the best way to accomplish this.

[mgregory24479]

Should add, I want the data to roll like a cross tab column but the column header to be schedule payment 1 date, schedule payment 2 date

[Francis aka khayman]

can you show us what you want to accomplish? how your data will look?

[mgregory24479]

Thank you for responding. I included a snippet of what we were wanting in my OP; however, I have attached a specific layout of what we have now and what we want.

[Francis aka khayman]

since you know how to handle it if data wasn't going to be added... the best way to accomplish what you want is through stored procedure in the database

[mgregory24479]

Handle it as SP if the data is automatically being added?

[Francis aka khayman]

transform your table using SP (or ETL) then use the transformed table in cognos