There are two ways, but will involve restructuring the script:
1) Make your main data table more "vertical"
By this I mean writing the script so that the StDate and EndDate are now in one column. You could write this easily in the script using a crosstable statement:
noconcatenate load Employee, [St Date] as Date,[St Amount] as Amount, 'Start' as Type from initaltab;
concatenate(Data) load Employee, [End Date] as Date,[End Amount] as Amount, 'End' as Type from initialtab;
This will result in the following table:
Employee Type Date Amount A Start 2011Q1 100 A Start 2011Q2 200 A End 2012Q2 550 B Start 2011Q1 300 B End 2012Q1 550
This is now very easy to pivot into the table you require.
2) The other alternative would be to create a stand-alone table of dates, eg using inline to create a field called [period] and the values 2011Q1, 2011Q2...etc
You could then use this as the dimension to base your new table on, and use if statments to match the values from the other table in it in expressions.
expression for St Dt Amount would be
= sum(if([St Date] = [period],[St Date Amount]))
Hope this helps,
Your Final report should be like this, i think your End Dt Amount is not correct or your requirement is different. Please check it and reply to me what actually you want. According to me this should be the report.
Period St Dt Amount End Dt Amount 2012Q1 1080 600 2012Q2 1220 1100 2012Q3 1480 1400 2012Q4 1610 300
For this i am attaching a example, hope this will help you to understand.
Report.zip 139.4 K