Hi all !

Is it possible to display some texts, signs, or an icon to distinguish between dimension items that are dependent on another dimension?

For example, I have two time dimensions, Year and Month.
I want to show what is Actual (the past) and what is Rolling Forecast (the future). 

This would be the desired result, showing the text "Actual" for the past and "Rolling" for the future, depending on some time flag that is set separately in a settings cube. I want to show the user to what point in time the "Actual" is confirmed.

The easy part is to create a rule to display the versions Actual or Rolling in a combined dimension item "Actual_Rolling" depending on the confirmed actuals.

Rule Example 

But the following rule in the Months dimension will not distinguish between years.

Thanks, Asgeir

Hi Asgeir,

I don't think there is a way to achieve in your current time dimensionality. You may want to consider adding another string measure named "Month Type" or something more meaningful and assign the values Actual or Forecast depending on the year and month combination.

That said, if your intention is solely to show the users what is their last actual period, I would suggest you show that as a cell widget somewhere on your report and that should just do it.

REgards,

AMin

Hi,

As Amin pointed out; because the attributes are for the Months and Years dimensions independently - "02" will always be either rolling or actual regardless of which year is referenced in a cube; it would only be the month "02" that is being looked at.

So that leaves you with a couple of options; one (like Amin suggested) is to add a new measure to the cube which you will be using and populate it in there via a rule. Because the rule is on the cube that contains both the Year and Months dimensions; it will be able to use both variables when calculating the cell result.

Your other option is to just display it within the view using MDX; this means it is not actually part of the cube but is just calculated in the front end.

If you look at the MDX for the view that you already have it will be something along the lines of:

Select
	{
		[Year].[Year].[2022],
		[Year].[Year].[2023]
	}
	*
	{
		{TM1SubsetToSet([Months].[Months], "Real Rolling", "public")}
	}
	ON 0,
	{
		[MeasureDim].[MeasureHier].[Measure1],
		[MeasureDim].[MeasureHier].[Measure2],
		[MeasureDim].[MeasureHier].[Measure3],
		[MeasureDim].[MeasureHier].[Measure4],
		[MeasureDim].[MeasureHier].[Measure5],
		[MeasureDim].[MeasureHier].[Measure6]
	}
	ON 1
FROM
	[YourCubeName]
Where
	(
		[TitleDim1].[TitleHier1].[TitleElement1],
		[TitleDim2].[TitleHier2].[TitleElement2]
	)

You can keep the MDX which already exists (or if you feel comfortable you could rewrite it all, as the generated MDX can be a bit ugly) and add a "WITH MEMBER" section at the top to say how you want your new field to be calculated (this essentially creates a "virtual" measure) and then in the row definition (the stuff before "ON 1") you add the additional new field/measure to the ones that you were already showing.

It would then look something like below:

WITH
	MEMBER [MeasureDim].[MeasureHier].[Period Flag]
	AS IIF ( 
		[Period_Lock].(
					[Year].[Year].CurrentMember, 
					[Months].[Months].CurrentMember, 
					[Period_Lock_Measures].[Period_Lock_Measures].[Stored] 
			) = 1,
			"Actual",
			"Rolling"
		)
	,FORMAT_STRING = '@@@@@@@'
Select
	{
		[Year].[Year].[2022],
		[Year].[Year].[2023]
	}
	*
	{
		{TM1SubsetToSet([Months].[Months], "Real Rolling", "public")}
	}
	ON 0,
	{
		[MeasureDim].[MeasureHier].[Period Flag],
		[MeasureDim].[MeasureHier].[Measure1],
		[MeasureDim].[MeasureHier].[Measure2],
		[MeasureDim].[MeasureHier].[Measure3],
		[MeasureDim].[MeasureHier].[Measure4],
		[MeasureDim].[MeasureHier].[Measure5],
		[MeasureDim].[MeasureHier].[Measure6]
	}
	ON 1
FROM
	[YourCubeName]
Where
	(
		[TitleDim1].[TitleHier1].[TitleElement1],
		[TitleDim2].[TitleHier2].[TitleElement2]
	)

Thanks,
Declan

------------------------------
Declan Rodger
Technical Director
Spitfire Analytics
------------------------------