Crosstabular behaviour

[akhattri]

I have a dataset where I need to create a sort of textual crosstabular behavior for the report. Basically I have 3 periods and the state codes and I want to show which states showed up in various periods(see attachment). The way I approached it was, I first created 3 data items for each of the period with the condition being:

dataitem1 :if (period='period1') then ('state value') else null
dataitem2 :if (period='period2') then ('state value') else null
dataitem3 :if (period='period3') then ('state value') else null

I then used these to create a list report with the dataitems. The problem however is that there is a separate row for each of the state when it is repeated.i.e. I am unable to show just one line stating all the states for each period. (Again refer to the attachment and you will get the picture).

Any help is appreciated.

Thanks.

[charon]

hi akhattri,

I realy would like to help, but i dont get the problem quite yet.

The problem however is that there is a separate row for each of the state when it is repeated. I am unable to show just one line stating all the states for each period

What exactly do you mean? Do you want just one line for  AAA, AE, AK and so on? Which would be filled with every time period in your example besides AS? Or do i get it completly wrong?
greets charon

[akhattri]

Charon,

You got that right. Basically, I just want one line for each state for each period(if it exists for that period), i.e. it should look like this:
2012F    2012S     2012U
AA           AA            AA
BB                           BB
CC           CC         
DD           DD         DD

I hope it clarifies a bit more.

[charon]

ty akhatri,
i understand now the challenge your facing. And honestly, at first i thought it might be trivial but it does not seem like it anymore.
Gonne try some approaches in cognos myself, il check on friday from my desk (im on journey right now) and will post in case i find a solution.
If you dont mind pls tell me what kind of data your working with, Olap, dmr or pure relational?!
thy and have a good night
cheerz

[bi4u2]

Are you able to work with the Framework Model and create a determinent on 'State Code'?

[akhattri]

bi4u2,

I was trying to design this within report studio, so am looking for a possible solution therein.

However, can you elaborate how creating a determinant on State Code provide a solution?

Thanks.

[bi4u2]

Ideally you could make the behavior happen in the report by grouping on all columns. If all that fails, you might consider a determinent.

Creating a determinent on the query subject would be like having an indexed field. You would state that the State Code is a unique identifier.

[akhattri]

In this case the grouping didn't work because I am creating a new query item for each of the columns, so the way Cognos understands it is, it tries to group for the state code for each column, which in the end results in exactly the same result set (each column duplicating the values).
Any other thoughts about approaching it in Report Studio?

Btw, I like your idea of creating a determinant on the query subject, but if you notice closely, these are calculated columns I am creating within the queries.

[Lynn]

Try setting the aggregate function for period to Maximum.

[adik]

why don't just create a crosstab and hide the row headers?