Pivot columns(Not applicable Aggregate property) in List does not export to CSV

[tgs728]

Hi All,

cognos version-10
database --sqlsever
MY raw data is as like below.
Date    Country   State Represntative
11-Dec   india   ap   xyz
12-Dec   india   ap   abc
11-Dec   india   TN   xyz
12-Dec   india   TN   abc


Now i want to pivot the State field as like crosstab. My report should show like below.

Date   AP   TN
11-Dec   xyz   xyz
12-Dec   abc   abc

I cannot do this crosstab , because there is no measure for me. So what i have did is
For AP,TN created  two data items with  case statements as below (case when ]state]='AP' then [representative] end) and  (case when ]state]='TN' then [representative] end. then i drag them  in the list and it shows as below.

Date   AP   TN
11-Dec   xyz   
12-Dec   abc   
11-Dec      xyz
12-Dec      abc

then i am getting blank responses for AP and TN actually it should come in a single row. After some R&D i have put Aggregate property to

not applicable then the data is showing as required in a single row, but when i exported this into CSV i am not able to those AP AND TN Columns but in excel i am able to see.

Have any body faced this issue.
Please let us know any solution or work around for this.

Thanks,

TGS

[BigChris]

Have you tried something like

Maximum(case when [state] = 'AP' then [representative] else NULL end) and maximum(case when [state] = 'TN' then [representative] else NULL)

That should group your list by date and just show a single row for each.