...
It shows some Markets on columns, all the members on the Time dimension on rows and slices the data displayed by the Measure Sales. Here's what the results should look like:
We could get the value of just one of the cells in this query using a tuple, the one highlighted in red, and display it using a calculated measure:
| Code Block |
|---|
with member measures.tupledemo as '([Measures].[Sales],
[Markets].[All Markets].[EMEA], [Time].[All Years].[2003]) '
select {[Markets].[All Markets].children} on columns,
time.members on rows
from [SteelWheelsSales]
where([Measures].tupledemo)
|
What we're seeing here is a tuple which returns the value of Sales for the EMEA market in the year 2003, and it returns the same value for every cell in the query. If we remove the reference to the year 2003 from the tuple though, see what happens:
| Code Block |
|---|
with member measures.tupledemo as '([Measures].[Sales],
[Markets].[All Markets].[EMEA])'
select {[Markets].[All Markets].children} on columns,
time.members on rows
from [SteelWheelsSales]
where([Measures].tupledemo)
|
We're still seeing data for Sales and EMEA, but it's now varying by the Time dimension. By not referring to a member on the Time dimension in our tuple we're in effect asking for the 'current member' on Time, which will vary each time the tuple is evaluated within the query. Similarly, if we add the reference to 2003 again but remove the reference to the Market EMEA, we can see something similar happen:
| Code Block |
|---|
with member measures.tupledemo as '([Measures].[Sales],
[Time].[All Years].[2003])'
select {[Markets].[All Markets].children} on columns,
time.members on rows
from [SteelWheelsSales]
where([Measures].tupledemo)
|
The values are now varying by Market but not by Time. Finally, if we just refer to the Measures dimension in our tuple then we have a calculated member which returns exactly the same thing as the Measure Sales:
| Code Block |
|---|
with member measures.tupledemo as '([Measures].[Sales])'
select {[Markets].[All Markets].children} on columns,
time.members on rows
from [SteelWheelsSales]
where([Measures].tupledemo)
|
Calculating Previous Period Growth
...
| Code Block |
|---|
with member [Measures].[currentmemberdemo] as
'[Time].CurrentMember.Name'
select {[Markets].[All Markets].Children} ON COLUMNS,
[Time].Members ON ROWS
from [SteelWheelsSales]
where [Measures].[currentmemberdemo]
|
The calculated measure here returns the name of the current member on the Time dimension; naturally enough it returns the same value for each row. Changing the dimension to the Markets dimension so:
| Code Block |
|---|
with member [Measures].[currentmemberdemo] as
'[Markets].CurrentMember.Name'
select {[Markets].[All Markets].Children} ON COLUMNS,
[Time].Members ON ROWS
from [SteelWheelsSales]
where [Measures].[currentmemberdemo]
|
...shows the name of the current member on Market. The next step is to use the PrevMember function with CurrentMember and Name; PrevMember returns the previous member on the same level as a member. So, for example, the following query displays the name of the previous member to the current member on the Time dimension:
| Code Block |
|---|
with member [Measures].[currentmemberdemo] as
'[Time].CurrentMember.PrevMember.Name'
select {[Markets].[All Markets].Children} ON COLUMNS,
[Time].Members ON ROWS
from [SteelWheelsSales]
where [Measures].[currentmemberdemo]
|
Rather than use the Name function to return a string, we can now instead construct a tuple using CurrentMember and PrevMember which returns a measure value for the previous time period:
| Code Block |
|---|
with member [Measures].[Sales Previous Period] as
'([Measures].[Sales],[Time].CurrentMember.PrevMember)'
select {[Measures].[Sales],[Measures].[Sales Previous Period]}
ON COLUMNS, [Time].Members ON ROWS
from [SteelWheelsSales]
|
We can now calculate our absolute previous period growth very easily, as the difference between the tuples we've found which return the current value for Sales and the previous period value for Sales:
| Code Block |
|---|
with member [Measures].[Sales Previous Period Growth] as
'([Measures].[Sales]) - ([Measures].[Sales],
[Time].CurrentMember.PrevMember)' select
{[Measures].[Sales],[Measures].[Sales Previous Period Growth]}
ON COLUMNS, [Time].Members ON ROWS
from [SteelWheelsSales]
|
If we wanted the percentage growth instead we could alter the formula used as follows:
| Code Block |
|---|
with member [Measures].[Sales Previous Period Growth] as
'(([Measures].[Sales]) - ([Measures].[Sales],
[Time].CurrentMember.PrevMember))/([Measures].[Sales],
[Time].CurrentMember.PrevMember)', FORMAT_STRING='0.00%'
select {[Measures].[Sales],[Measures].[Sales Previous Period Growth]}
ON COLUMNS, [Time].Members ON ROWS from [SteelWheelsSales]
|
Note the use of the Format_String property on the calculated measure here, which is used to format the results returned. If you didn't specify it you'd find that the default format applied doesn't show any useful data!
...