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] )