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
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.