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.
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
can you show us what you want to accomplish? how your data will look?
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.
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
Handle it as SP if the data is automatically being added?
transform your table using SP (or ETL) then use the transformed table in cognos