Report Studio YTD Calculation - Dimensional Model

[Cognos21]

I have a time dimension that can be seen in the image below. I have a report that prompts the end user to choose one of the members from the "Total Time" level. How would I create a calculation that would roll up the data from that period as well as the prior periods to create a year to date calculation? If the fiscal year runs from April-March, and the user selected December in the prompt, I would want to create a calculation that rolls-up a sum of April through December. I have tried to use variations of the "periodsToDate" function, but am getting different "hierarchy to level" errors. Thanks for your help!

[MFGF]

I have a time dimension that can be seen in the image below. I have a report that prompts the end user to choose one of the members from the "Total Time" level.

Hi,

You are showing the member tree here rather than the structural view which presents the levels. You only have one member at the "Total Time" level by the looks of things. I'm guessing your levels are:

Total Time
Year
Quarter
Month

Is that correct? You ought to be able to see these if you press the "View metadata tree" button above your view of the package.

How would I create a calculation that would roll up the data from that period as well as the prior periods to create a year to date calculation? If the fiscal year runs from April-March, and the user selected December in the prompt, I would want to create a calculation that rolls-up a sum of April through December. I have tried to use variations of the "periodsToDate" function, but am getting different "hierarchy to level" errors. Thanks for your help!

It sounds like you're using the hierarchy in the expression instead of the year level? Try using the expression:

periodsToDate([Your Year level from the structural view of the hierarchy], [your prompted-for month member])

Cheers!

MF.

[Cognos21]

This is the metadata view. The hierarchy is put together on a parent/child relationship. I have tried to utilize the periodsToDate function, but like I said am receiving different "hierarchy to level" errors. There is not multiple levels, which I am assuming is why I am unable to get the periodsToDate function to work.

[MFGF]

This is the metadata view. The hierarchy is put together on a parent/child relationship. I have tried to utilize the periodsToDate function, but like I said am receiving different "hierarchy to level" errors. There is not multiple levels, which I am assuming is why I am unable to get the periodsToDate function to work.

Hmmm. Ok - so you have no levels to bring in. It's a little more involved, but we can still derive the level

periodsToDate (level (firstChild ([your Total Time member])), [your prompted-for month member])

This of course assumes you are delivering a month member from your prompt :) If not, you will need to use an expression such as [Your Time hierarchy] -> ?Your Month parameter? to deliver this

eg

(level (firstChild ([your Total Time member])), [Your Time hierarchy] -> ?Your Month parameter?)

Cheers!

MF.

[Cognos21]

I appreciate your help with this issue. When attempting to use what you provided, I receive this error:

The "level" operator is not applicable for [Time].[Time].[@MEMBER].[2009] on parent/child hierarchies.

Any ideas?

[MFGF]

I appreciate your help with this issue. When attempting to use what you provided, I receive this error:

The "level" operator is not applicable for [Time].[Time].[@MEMBER].[2009] on parent/child hierarchies.

Any ideas?

Oh dear. Since you're using a parent/child hierarchy I'm afraid you're severely limiting your capabilities here. I can't think of another way to do this, sorry. You have no explicit levels to use and it seems you cannot derive levels either, so any dimensional function that requires a level as an argument is off-limits to you.

Do you have an option to change the structure to be a "regular" hierarchy?

MF.