I hate Crosstabs but I still have a question

[ucfmike]

Along the top of my crosstab I'm going to have two levels, that breakdown into the 12 months of the year.  So the top will look something like this:

Jan Revenue
-----------------
Jan Sales

Jan Revenue and Jan Sales are each a query item, so I'll actually need to build text boxes in the top row to show this data.  My question is, what is the best way to store the calculations in the measure area.  Cognos wants to have one universal measure, but for me Jan Revnue is associated with a value as is Jan Sales, Feb Revenue, Feb Sales etc..

Should I be moving away from the crosstab?

[whastings]

Don't drag your measures into the measures area.

Drag your "months" into the columns area. Next, drag your "revenue" measure directly below the months in the column area, not the measures. You will see "Revenue" appear as a label under month. then drag your "sales" measure into the column area below the month and beside your revenue label. You should see a bar appear that is the height of the revenue measure label and under the month dimension. this will put sales and revenue side by side under each month.

If you want sales under revenue, you can do exactly the same only in the rows area.

[ucfmike]

I still need to drag something into the measurement area so it does calculations though, correct?

Right now it's not spitting out any measurements/calculations when the report is ran.  Maybe something wrong with the model?

It's not dimensionally modeled, in fact the model is against a flat table.

[VisioX]

If it's just a flat table, use a list-report. Make the "MONTH" the first column, select the "month"-column and click on "Section" from the menu.

[whastings]

No, you do not need to put something directly in the cross tab measure area. Cross tabs are extremely intuitive that way.

[whastings]

Regarding your second issue about not showing any data: if you drag the revenue to the measure area, do you get a report? Conversely, if you only drag the sales measure to the measure area, do you get a report?

All of our reports area against a transaction database and I've used crosstabs quite a bit. You can get pretty complex, but the more dimension or attribute information you put in the rows or columns, the slower your response time will be. Crosstabs are actually very powerful. I've done one with revenue, area, and weight sold by month (columns) by sales rep (rows), where the "measures" were children of the sales reps.

[ucfmike]

Thanks for all the help.

Turns out the crosstab wasn't showing data because of a setting in the database table.  We are working against a very raw test table and my facts cells were set to nchar in the database.

Upon further review, I'm not sure the crosstab is going to do what I want.  We may have to remodel the data to give the client the report in the format they want.

thanks again for the help.