April 27, 2010 at 1:25 pm
I have the following calculation defined on a time dimension.
It works, but only shows the results for the highest level.
For example, if i pull in the "FISCAL CALENDAR" and expand it out,
it has
2010... January .... 2010-01-01 (note my dates are at the monthly level)
Well i only see results when i expand out to the 2010-01-01 level
Why can't i see it at the January or the 2010 level?
// Prior Year to Date
(
[DIM TIME].[FISCAL CALENDAR DIM TIME Calculations].[Prior Year to Date],
[DIM TIME].[FISCAL YEAR].[FISCAL YEAR].Members,
[DIM TIME].[TRANS DATE].Members
)
=
PARALLELPERIOD([DIM TIME].[FISCAL CALENDAR].[FISCAL YEAR],1,[DIM TIME].[FISCAL CALENDAR].currentmember)
April 28, 2010 at 3:37 am
Hello Natarscha,
From you example I see that you've used the builtin time intelligence wizard. I don't know the cause of your problem, because I don't know your design,just the few lines you posted.
Anyway, in my projects I''ve never used this wizard because it's limited in its functionality and there's a much better (generic) solution on time calculations, which can be found here:
http://www.obs3.com/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf
I suggest you use the approach from this document.
Kind regards,
Cees
April 28, 2010 at 6:52 am
thanks. i've seen that post before. i'm new to ssas and only supposed to be helping out on a project that ended up being turned over to me. btw, i have a background SQL Servers and Cognos products, so all this extra work for time calculations is unbelievable to me.
i did use the wizard to get ytd, but then customized that to do a prior year calculation. i would like to see prior year no matter what level i select, but for this particular report, i'm just going to end up limiting the users to select a YearMonth to report on, so i am not going to build a custom time dimension. in the future i will look into that option, but i'm still curious as why my code doesn't work, it must be something simple i'm missing.
April 28, 2010 at 8:26 am
ok, i figured it out... i was missing:
Aggregate(
{ [DIM TIME].[FISCAL CALENDAR DIM TIME Calculations].[Current DIM TIME] }
*
....
)
I don't really know what most of the functions do and how they operate; my plan is to get the solution to work using my basic programming/copy/paste techniques and then i can go back and understand what i did.
btw, i was setting this up so that i can have mtd, prior mtd, ytd, prior ytd as columns across the top
and other dimensions as the rows, but only have two measures - revenue and transactions --- using the calculated time dimension worked.
April 28, 2010 at 8:36 am
--Off topic-- the way (Cognos) Transformer handled the inclusion/creation of special time categories *really* kicks *** over the manual approach for SSAS :S
Steve.
April 29, 2010 at 2:11 am
Hi Natarscha,
The script generated by the wizard has a section starting with Scope..., like this.
...
Scope(
{
[Measures].[Internet Order Count],
[Measures].[Reseller Order Count]
}
) ;
...
<logic>;
....
End Scope;
The <logic> is only applied to the measures identifeid in the scope, in my example the [Measures].[Internet Order Count] & [Measures].[Reseller Order Count].
Maybe this is why the other measures don't work with the relative time dimension.
Kind regards,
Cees
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply