String measure value to consolidation level

[M1ndbender]

Hello,

I am wondering if it is possible/how to copy the string measure value from the leaf level to the consolidation level.

I have a date string that shows on the report at the leaf level but not at the consolidated levels of the same report. I am trying to see if it is possible and how to copy the value to the consolidated level. The leaf level consolidations and the consolidations are in 2 separate hierarchies within the same dimension and the date is related to the status of an asset. So whether it is current month or current quarter the date would always be the same for the asset.

Asset number 1 for Jan-22 shows 12/02.2022 - this is as desired
Asset Number 1 for Current Fiscal Quarter and Current Month YTD both show blank would like the same date as above to show at these consolidation levels.

Thanks
Bret

[gtonkin]

I think you want look at reading from the lowest level rather.
Adding string rules is generally not a good idea as these will fire every time you calculate the view and would impact performance.

You also have the problem of multiple children that could roll up into the C level.
Going with an attribute where you designate an N level as a reference/proxy should get around this.

There may be other solutions but something to ponder so long...

[M1ndbender]

Could you point me in the direction of some steps or something to google on for this. I am new and my searches are returning nothing useful
Thanks

[Elessar]

Hi,

Primo, beware of several dates being summed up on consolidations

Secundo,
This is much easier with dates, than if it was really a string measure, because you can convert date to number and then to string again.

Here is idea how to do this:

1. Say we have string "Date" measure where we have values at N-level
2. Make a numeric "NDate" measure. "[nDate] = N: DAYNO (DB(cube, .. , Date, ..));", do not forget to feed
3. Make reverse rule: "Date = S: IF(consolidated, nDate, continue);" (You cannot use C: for string measures)

Another idea which I like much more: make your date measure as number and format it as string

[gtonkin]

What I was thinking was adding an attribute to the Date dimension like Non-Cumulative Period.
You could then assign a leaf level element to the C levels e.g.
22-Q1 - Jan-22, Feb-22, Mar-22 as you roll months and data is available in the most recent period in the quarter.
22-Q2 - Jun-22
Jan-22 YTD - Jan-22 etc. etc.
You could then reference the comment by first retrieving the N level period from the attribute then doing your DBRW using this.

[MarenC]

Hi,

You didn't say where the report was being displayed, PAW cube view, PAX?

How is the status of an asset being updated? Is it by the user or is it imported from source system?
If it is updated by the user you could use a TI process to update the consolidated string measures at the same time the user updates the status.
If it is a source system you could incorporate some logic into the data load process which updated the consolidation string values.

Maren

[M1ndbender]

The data is in Cognos Analytics coming is in data module from a TM1 cube. The report is just displaying data results, there is no user input.

I will look at our data load processes and see what is there

[burnstripe]

Given it's for a Cognos Analytics report, if it were me I wouldn't amend anything in TM1 and would amend the query in Cognos Analytics to derive the relevant data. You can use tuple() within an expression to point to which section of the cube to use for the measure containing the date. You'd need some other expressions alongside Tuple to reference the relevant data but it's too late in the day for me to work that one out. I'll have a play next week if no one else comes up with an expression.

[burnstripe]

Is the asset element always unique? If so might be worth storing the date as an attribute rather than in the cube (if this an option). That will make it easier to report on