Hi Tim,

In most cases this is due to an issue with dynamic periods syntax for your month level expecting the month level of your hierarchy to be in a specific format.

Did you set up the dynamic periods syntax yourself? And did you do it in the SSAS project or as a Decoration in the TARGIT Management client?

Since it did work earlier on, and now it doesn't after a data source update, my best guess is that your month level data somehow changed. My guesses include, all of which would make it fail:

- The month level now shows names instead of numbers.
- The month level now shows 2 digit numbers (eg. 06) instead of 1 or 2 digit numbers (eg. 6).
- The key of your month level changed from a simple key (month numbers only) to a combined key (year + month number).
- Other changes to the month level may also make it fail.

The best solution would be to fix the dynamic periods syntax on your SSAS project or in TARGIT Decorations, depending on where it is done by now, to match the new definition of your month level attribute.

If you need further assistance, I will need to see screenshots of your current Dynamic Periods syntax and of a fully expanded Year-Month-day hierarchy in a TARGIT crosstab.

BR / Ole

Great. Another screenshot requested, though:

Click the "Year-Month-Day" hierarchy and have a look at properties, especially the 'Description' property. This is probably where you have your Dynamic Periods syntax, something like "#|TimeDef: ...". I would be interested in seeing that, thank you.

Ok. Thanks. For sure, it is because your new data source now works with combined keys – which the old one did not.

One more screenshot, please: Show me the combined key settings for your MonthKey attribute. Look into the KeyColumn property of your MonthKey attribute. Click the small button with three dots to open up the combined key settings – this is the screenshot I need.

Not quite. If you just click the KeyColumns property, you should see the three-dotted button on the right hand side in the second column. This should bring up a 'Key Columns' dialog box. This is the one I need.

Exactly! Except that it didn't really give me that extra information I was hoping for. So, from now on I am going to do some (qualified) guessing.

I am guessing that your 'YearMonthKey' contains data like '201706' for June 2017. You may verify this by browsing your Calendar table. Likewise, I am guessing that your Day level has a 'YearMonthDayKey' key - containing something like '20170615' for 15th June 2017.

In that case, your Dynamic Periods syntax should be something like

#|TimeDef:[<>].&[<YYYY]&[MM>].&[<YYYY]&[MM]&[DD]>]|#

Give it a shot, and let me know. If it dosn't work I will need to know the contents (data) of 'YearMonthKey' and 'YearMonthDayKey'.

PS: Your Data Warehouse will need to be re-processed in order for the changes to take effect.

Edited by user Thursday, June 15, 2017 12:35:59 PM(UTC)  | Reason: PS added.

Hi again Ole

I tried your solution yesterday night, without any big succes.

But this morning i was wondering about the following:

When i look into my "Year - Month - Day" hierachy it shows the following:


And QuarterKey is made from the following expression:
Cast(CAST(Year AS VARCHAR(4) ) + CAST(QuarterNumber AS VARCHAR(1) ) as int)




So shouldn't my hierachy description be something like:
#|TimeDef:[<>].&[<YYYY]&[Q>].&[<YYYY]&[MM]&[DD]>]|#

instead of:
#|TimeDef:[<>].&[<YYYY]&[MM>].&[<YYYY]&[MM]&[DD]>]|#

These are the two existing key colums in my calendar table:

My bad earlier on. It really isn't a combined key - it is in fact just a simple key (which has been pre-combined into eg. Year and Month).

So the syntax should probably be

#|TimeDef:[<>].&[<YYYYMM>].&[<YYYY-MM-DD]>]|#

If the Day level doesn't work with this, you might try to change the KeyColumn property to 'DateKey'. In that case the syntax should be

#|TimeDef:[<>].&[<YYYYMM>].&[<YYYYMMDD]>]|#

Hello Ole.

Just an update for you.
I got my dynamic periods working with the following solution:

"Year - Month" Description:
#|TimeDef:[<>].&[<YYYYMM>]|#

"Year - Month - Day" Description:
#|TimeDef:[<>].&[<YYYYMM>].[<>]|#

"Year - Quarter - Month - Day" Description:
#|TimeDef:[<>].[Q<>].&[<YYYYMM>].[<>]|#

Thanks again for all your help!