cross tab vs list report

[cogcurious]

Could someone help me identify when to use a cross tab against a list report. Below is my understanding. Let me know if any of it is wrong.

Use crosstab when you are into the business of comaprative analysis. For e.g., measuring sales with city in rows and months in columns. But then i could as well put it in a list with the first column as city and subsequent columns being sales calculations for each month. Is the later bad design?

Use crosstab when at a summarized level rather than when you are looking to much detailed data. Employee name, age, sex address, etc would probably be list.

Use crosstab when column are dynamic or have a pattern

Thanks!!

[cogcurious]

Anyone

[CognosPaul]

My general rule of thumb is to try to fit things into a list when working with relational data, and into a crosstab when working with dimensional.

Generally you are correct about crosstabs. When you need data, such as months, going across the columns then it needs to be in a crosstab. You could get away with manually adding data items to a list like: case when [MonthID]=1 then [Measure] else 0 end and sticking it in a list, but when you have a variable number of items, that is impossible.

One of the strengths of crosstabs is the ability to nest and add data items as peers.  You could show all product types in the rows, with all countries below.  You should not attempt to add too many nested items to a crosstab though, especially on a dimensional model.

Lists also have their strengths. For example, you can add as many other data containers inside a list cell as you need, joined with a master detail.

It's worth mentioning that lists can summarize data just as well as a crosstab can. After all, the statement "select field, sum(measure) sales from fact group by field" will return a list.