How to write a report on a dimensional model - part3

Today (tonight actually) I'll attempt to make some further clarifications about how crosstabs work.

For simplicity's sake in part2 I mentioned that along the axes of the crosstab we place ``dimensions''.

In fact these are member sets.

A member set is simply a set of members.
When you drag a dimension - Cogno calls it "regular dimension" - what really happens is that it will be coerced into its members. Search the Cognos documentation for Coersion Rules.

The point I'm trying to make is that you are not limited to working with dimensions. You can use 

• expressions that return a set of members
  
• a certain member (e.g. a given month in the Time hierarchy)
this member will be implicitly casted to a member set with one element only

• memberset that is defined by you by picking any set of members you want
Search the Cognos documentation for Defining Member Sets

Another clarification about measures.

I think Cognos uses values for measures and these values are evaluated for every cell using tuple() function.

In other words you can have calculated measures by doing some arithmetic on tuples.

A usual scenario is a "smart" average calculation :

average_daily_something:= value(tuple(some_value_measure)) / value(tuple(some_days))

For example you want to get the average daily sales amount for a time period but the store is not open on weekends.

Sales - measure

Time - dimension

on weekends Sales will be equal to 0 but you don't want to count this into your daily average

you could do something like

Add a Workday measure that is 1 for workdays and 0 for weekends.

avg_daily_sales := value(tuple(Sales)) / value(tuple(Workday))

You'll come up with measure calculations that make sense in your business... the point is that in your forulas you can use value(tuple(...)) as operands and then use this "calculated measure" in the crosstab.