How to use prompt values in a tuple expression

This golden nugget can save you 2 days of work - that's how long it took me to figure out the syntax.

In the tuple() you can use a member to specify which value of the measure you want. This will work much like a filter... just better.

Lest say you have a Sales measure and a Time dimension with Date at the lowest level.

You can write something like this:

tuple([Sales], [Time Dimension].[Date hierarchy].[Date]->[all].[1/1/2008])

this will get you the Sales on 1st Jan. 2008

This is great, but you want to make this a parameter, so the user can specify the date.
Maybe you have a reporting interval called ?ReportStart? and ?ReportEnd? and you want to get the Sales on the last day of the reporting interval.

You can do this:

tuple([Sales], [Time Dimension].[Date hierarchy].[Date]->[all].#sb(prompt('ReportEnd','date'))#

You can use this expression in a crosstab or graph to give you the Sales measure on the end of the reporting interval.

In more generic terms this is the syntax to use prompt parameters in tuple() expressions.

How to write a report on a dimensional model - part2

This is part 2 of my little essay about how to get the values that you want show up on the report where you want them.

The first part  talked about the tuple() function. If it wasn't clear enough, it will be... 

Crosstabs and tuple() are so related it's difficult to explain one without the other.

If you've used pivot tables in Excel then roughly that's what you can expect from a crosstab.

Crosstab is a table with values in the middle and headers along the edges. 

These headers can be 

• labels for the numbers we see in the middle of the table
  
• members of a dimension so that we give some context to those numbers.

Tables are the best way to "flatten" the dimensional data since most of us are not comfortable looking at 5 dimensional Rubik cubes :)

Crosstabs do provide a way for displaying multi ( > 2) dimensional data in a way that we can digest.

I'll provide examples going from the simplest case towards the more difficult.

A crosstab axes contains either a member set or a tuple() expression. (Even when it looks just like a simple measure.) Member sets are "expanded" during runtime - thereby creating multiple rows or columns. tuple() expressions are evaluated for every cell in the row or column.

I will use the same example as in part one:

Sales - measure

Branch, Product, Time - dimensions

1., one dimension - one measure

measure   |

----------+-------

dimension1| <1234>

The crosstab will work much like a simple list. When you think you need only a list use this instead. Lists are not meant for dimensional model.

You may notice that the horizontal axes is left empty. That's OK.

The upper left corner of the crosstab ddisplays the default measure. You can drag a measure there or into the "content" area.

Most people get confused by this and think that the crosstab can only display one measure.

It's not true. As you can see later a crosstab can display as many measures as you want.

The default measure is used to evaluate tuple() expressions where the measure is not specified.

I'll show examples of that later. Just keep it in mind.

e.g.

Sales |

------+-------

Branch| <1234>

"Sales" really becomes tuple(Sales)

Sales                    | 

-------------------------+---------

Richmond AutoMall        | 450,000

-------------------------+---------

North Vancouver AutoMall | 129,000

-------------------------+--------- 

Kingsway Nissan          |  39,000

-------------------------+---------

The dimension is expanded.

The tuple expression is evaluated in the context of the crosstab like this

tuple(Sales, currentMember(Branch), defaultMember(Time), defaultMember(product))

The defaultMember practically means Product and Time resulting in rolled-up values along these dimensions.

2., two dimensions - one measure

measure    | dimension1

-----------+-----------

dimension2 | <1234>

This is the "classical" crosstab.

e.g.

Sales | Time

------+------

Branch| <1234>

Note that "Sales" is still tuple(Sales)

that gets evaluated like

tuple(Sales, currentMember(Branch), currentMember(Time),  defaultMember(Product))

Both dimensions will be expanded resulting in something like this:

Sales                    | 1/1/2008 | ... | 1/31/2008

-------------------------+----------+ ... +----------

Richmond AutoMall        | 20,000            17,000

-------------------------+----------+ ... +----------

North Vancouver AutoMall | 23,000                 0

-------------------------+----------+ ... +----------

Kingsway Nissan          | 18,000            24,500

3., one dimension - many measures

The crosstab can have multiple measures. One of them is the default.

measure1   | measure1 | measure2  ...  measure_n

-----------+----------+---------+ ... +---------

dimension1 | <1234>   | <1234>  | ... | <1234>

In this example measure1 is the default measure.

Having a default measure we can do something like this:

fusion_sales := tuple([Product]->[Ford Fusion])

Sales  | Sales | fusion_sales

-------+-------+-------------

Branch | <1234>| <1234>

Note that the tuple() expression only specified the product dimension.

Cognos will figure out from the context that we are talking about Sales; because Sales is the default measure.

fusion_sales will be evaluated like this:

tuple(Sales, currentMember(Branch), defaultMember(Time), [Product]->[Ford Fusion])

It will give the total of Ford Fusion sales per branch.

The arrow ``->'' is Cognos's notation for specifying a member.

Try dragging in a member from a dimension... you'll see.  

(It's a powerful tool to create "custom measures" so to speak. So we won't have "ford Fusion Sales" as a separate metric in our cube..)

4., three or more dimensions - one measure

When we need more than 2 dimensions we have to nest them into one another.

measure                 | dimension3

------------------------+-----------

dimension1 | dimension2 | <1234>

           +------------+-----------

           | dimension2 | <1234>

-----------+------------+-----------

e.g.

Sales            | Time

-----------------+-------

Branch | Product | <1234>

       +---------+-------

       | Product | <1234>

-------+---------+-------

as the dimensions are expanded all combinations will be created

Sales                                    | 1/1/2008 ...

-----------------------------------------+---------

Richmond Autmoall        | Ford Fusion   | 

                         +---------------+---------

                         | Nissan Maxima |

                         +---------------+---------

                         | Honda Civic   |

-------------------------+---------------+---------

North Vancouver Automall | Ford Fusion   |    *

                         +---------------+---------

                         | Nissan Maxima |

                         +---------------+---------

                         | Honda Civic   |

* Sales of Ford Fusion at North Vancouver Automall on 1st Jan

Note how the nested dimension (Product) is repeatedly expanded for every member of the outer dimension (Branch).

5., many dimensions  - many measures

Using nesting you can go wild!

Measure1                | dimension1          |

                        +---------------------+

                        | measure1 | measure2 |

------------------------+----------------------

dimension2 | dimension3 | <1234>   | <1234>   |

           +------------+----------+-----------

           | dimension3 | <1234>   | <1234>   |

           +------------+----------+----------+                

In the next part I'll show some practical examples and talk about member sets.

Just to wet your apetite... think about not having the entire dimension along the axes, only some specific members you want...

How to write a report on a dimensional model - part1

by using crosstabs and the tuple() function of course!

Let me show you how...

First of all: what is a tuple?
Tuple is basically the way to address a value in an n-dimensional array, the OLAP cube.
It's basic form is simple:
tuple(measure, dimension1, dimension2,...)

Tuple is really a coordinate.
To get the value of the cube at this coordinate we need to use the value() function.
value(tuple(measure, dimension1, dimension2,...)
Cognos does this automatically. Search for Coersion Rules in the Report Studio users guide.
It's good and bad... makes things easier, but causes confusion too... anyways.

In order to specify our coordinate we need to specify a member in each and every dimension that applies to the measure.

We don't need to write it all however. Cognos fills in the missing values but it is very important to understand how...

• measure: defaults to the "default measure" - see upper left corner of crosstab

• dimension: defaults to the current member of the dimension coming from the context - i.e. if the dimension appears on one of the axes in the crosstab, otherwise defaults to the default member
  The default member is the root "(All)" member. By defaulting to this we are in effect rolling up that dimension. In other words dimensions that are not in the context of the crosstab are rolled up. Makes perfect sense.

If you don't want this default behavior you can just edit the expression and specify exactly what you want. This is very cool, it's the most powerful tool a report writer has.

example
Sale is a measure that is associated with three dimensions:
- Time
- Product
- Branch

to fully specify a sale we need to supply a member of all three dimensions:
Sale(1/23/2008, 'ford fusion', 'Richmond Automall')

tuple(Sale) will add up all the sales

tuple(Sale, 1/23/2008) will add up all the sales on Jan 23 for all products at all branches 

tuple(Sale, 'ford fusion') will add up all the sales where the product was a Ford Fusion 
etc.

We need to understand that the tuple is executed in the context of a crosstab.

Dimensions that are not explicitly mentioned will be searched for among the axes to see if they have a current member.

This gives a whole new meaning to the examples above... 

Taking the last example for instance:

tuple(Sale, 'ford fusion') | Time

---------------------------+------

Branch                     | <1234>

This crosstab has Branch and Time along the axes which will suply the context for the tuple expression. This crosstab will display the Ford Fusion sales broken down per day and per branch.

If we don't want our expression to depend on the context of the crosstab we can use the completeTuple() function.

Every dimension that is not explicitly defined will default to its default member - meaning it will be rolled up.

In the next part I'll write about crosstabs. 

Using tuple() expressons in crosstabs you can do pretty much anything.

And here is the good news. Charts work exactly like crosstabs.

If you can get the data to show up in a crosstab you can create a chart plotting the same data by setting it to the same query.

By the way... forget about list. When you work with a dimensional model you don't need list.