Problem 1: Displaying Information About Members
We want on row per quarter, but for each quarter, we also want to display
the corresponding year. Here an example with an output where we do the same
thing on the column axis with the Region dimension (displaying both the
State and City):
Solution 1.1: Displaying Information in Cells
It is not possible to add have tuples with two
members from the same dimension (here: Year and Quarter are both from the
Time dimension), so one alternative is to display the year in a cell
instead.
with
member Measures.Year as 'time.currentmember.parent.name'
select
{ Measures.Year, [Measures].[Store Sales] } on columns,
[Time].[Quarter].members on rows
from Sales
And here we have a more complex but realistic example using the same
method.
with
member Measures.Year as 'time.currentmember.parent.name'
member Measures.Quarter as 'time.currentmember.name'
select
{
{ ( [Store].[All Store], Measures.Year ) } ,
{ ( [Store].[All Store], Measures.Quarter ) } ,
CrossJoin( { [Store].[Store Country].members } , {Measures.[Store Sales] })
} on columns,
[Time].[Quarter].members on rows
from Sales
Solution 1.2: CrossJoin with Duplicate Dimension
Here we create an additionnal dimension [Year] with the years as members.
We CrossJoin [Year] with [Time] and then Join the two dimensions.
select
{ [Measures].[Store Sale] } on columns,
Filter (
CrossJoin(
{ [Year].members },
{ [Time].[Quarter].members }
),
[Year].currentmember.name =
Ancestor([Time].currentmember, [Time].[Year]).name
) on rows
from [Sales]
Problem 2: Output in a Cell a Combination of Values
In this example below we display in each cell the "sale" value along the
percentage it represents across all regions, displayed in parenthesis. We do
this with the non-standard Format() function.
with
member [Measures].[Sale] as '
Format([Region].currentmember, "$#,### ") ||
Format([Region].currentmember /
[Region].currentmember.parent, "(0%)")
'
select
{ [Period].[Period's Year].members } on columns,
{ [Region].[All Regions].children } on rows
from [Sales]
where ( [Measures].[Sale] )
Problem 3: Output in a Cell Different Values Depending on Some Condition
In the example below we add a <font color="red"> tag
around the value along with the "number of units sold" if the "sale" value
is below a certain threshold:
with
member [Measures].[Sale] as 'Iif (
[Measures].[Total Price] > 1000000,
Format([Measures].[Total Price], "$#,###"),
"<font color=""red"">" ||
(Format([Measures].[Total Price], "$#,###") ||
("</font><br/>Units: " ||
(Format([Measures].[Total Units], "#,###"))))
)'
select
{ [Period].[Period's Year].members } on columns,
{ [Region].[All Regions].children } on rows
from [Forecast]
where ( [Measures].[Sale] )
References
