mdx reporting

hi all,
examine the following piece :
with
member [month_list].[jan-sept] AS 'sum([month_list].[All
month_list].[January]:[Month_list].[All month_list].[September])'
As you can see, i summate the months January to September, september being
the current month. Now, is there a way to replace [September] with the month
that we are currently in, or perhaps by a parameter ?
Thanks in advanceKoen,
Here's an example of what I do...
MEMBER TIME_DIM.[YTD 2004] AS 'SUM([TIME_DIM].[2004].[Qtr
1].[1]:[TIME_DIM].[2004].[Qtr " & Datepart("q",Dateadd("m",-1,Now())) & "].["
& Month(Dateadd("m",-1,Now())) & "])'
... I do this to get data up to the previous month. I include the Qtr
because that is how my dimensions are set up. You may have to use the
Monthname function to return the full month name to match your dimension
structure.
"Koen" wrote:
> hi all,
> examine the following piece :
> with
> member [month_list].[jan-sept] AS 'sum([month_list].[All
> month_list].[January]:[Month_list].[All month_list].[September])'
>
> As you can see, i summate the months January to September, september being
> the current month. Now, is there a way to replace [September] with the month
> that we are currently in, or perhaps by a parameter ?
> Thanks in advance|||hi,
first of all, thanks for your (quick) reply.
i tried your suggestion, and i fabricated something like this :
member [month_list].[jan-sept] AS 'sum([month_list].[All
month_list].[January]:[Month_list].[All month_list].[" &
datename(month,getdate()) & "])'
but it says : Cannot find dimension member ("Month_list]. ...
perhaps there's a bad quote or something ? maybe you see it..
Thanks,
"williamericnichols" wrote:
> Koen,
> Here's an example of what I do...
> MEMBER TIME_DIM.[YTD 2004] AS 'SUM([TIME_DIM].[2004].[Qtr
> 1].[1]:[TIME_DIM].[2004].[Qtr " & Datepart("q",Dateadd("m",-1,Now())) & "].["
> & Month(Dateadd("m",-1,Now())) & "])'
> ... I do this to get data up to the previous month. I include the Qtr
> because that is how my dimensions are set up. You may have to use the
> Monthname function to return the full month name to match your dimension
> structure.
>
> "Koen" wrote:
> > hi all,
> >
> > examine the following piece :
> >
> > with
> >
> > member [month_list].[jan-sept] AS 'sum([month_list].[All
> > month_list].[January]:[Month_list].[All month_list].[September])'
> >
> >
> > As you can see, i summate the months January to September, september being
> > the current month. Now, is there a way to replace [September] with the month
> > that we are currently in, or perhaps by a parameter ?
> >
> > Thanks in advance|||Koen,
I'm assuming you are using 2000 RS and the query builder. It sucks and I
can't wait to try out 2005 mdx builder. So with that assumption... when you
build dynamic mdx statements you must surround it in quotes insert an "=" at
the beginning.
So my full mdx statement is this...
="WITH
MEMBER TIME_DIM.[SAMEAS 2003] AS 'SUM([TIME_DIM].[2003].[Qtr
1].[1]:[TIME_DIM].[2003].[Qtr " & Datepart("q",Dateadd("m",-1,Now())) & "].["
& Month(Dateadd("m",-1,Now())) & "])'
MEMBER TIME_DIM.[YTD 2004] AS 'SUM([TIME_DIM].[2004].[Qtr
1].[1]:[TIME_DIM].[2004].[Qtr " & Datepart("q",Dateadd("m",-1,Now())) & "].["
& Month(Dateadd("m",-1,Now())) & "])'
SELECT
{ TIME_DIM.[YTD 2004], TIME_DIM.[SAMEAS 2003], [TIME_DIM].[Yr].[2004],
[TIME_DIM].[Yr].[2003], [TIME_DIM].[Yr].[2002]} ON Axis(0),
NonEmptyCrossJoin([" &
Left(Parameters!Cat_1.Value,Instr(Parameters!Cat_1.Value,"|")-1) &
"].Levels(1).members, [" &
Left(Parameters!Cat_2.Value,Instr(Parameters!Cat_2.Value,"|")-1) &
"].Levels(1).members, [" &
Left(Parameters!Cat_3.Value,Instr(Parameters!Cat_3.Value,"|")-1) &
"].Levels(1).members, [" &
Left(Parameters!Cat_4.Value,Instr(Parameters!Cat_4.Value,"|")-1) &
"].Levels(1).members) ON Axis(1)
FROM [IICASE_COUNTS]
WHERE ([Measures].[Rec Count])"
... WARNING! Once you change it to a dynamic statement, the RS field list
will NOT update itself if you add\change fields. So if you need to
add/change fields, you must change it back to a static...refresh the fields
and then change it back to dynamic. I usually keep an extra copy of the
report with the static statement so I can go back and forth during debugging.
Hope this helps good luck.
"Koen" wrote:
> hi,
> first of all, thanks for your (quick) reply.
> i tried your suggestion, and i fabricated something like this :
> member [month_list].[jan-sept] AS 'sum([month_list].[All
> month_list].[January]:[Month_list].[All month_list].[" &
> datename(month,getdate()) & "])'
> but it says : Cannot find dimension member ("Month_list]. ...
> perhaps there's a bad quote or something ? maybe you see it..
>
> Thanks,
> "williamericnichols" wrote:
> > Koen,
> >
> > Here's an example of what I do...
> >
> > MEMBER TIME_DIM.[YTD 2004] AS 'SUM([TIME_DIM].[2004].[Qtr
> > 1].[1]:[TIME_DIM].[2004].[Qtr " & Datepart("q",Dateadd("m",-1,Now())) & "].["
> > & Month(Dateadd("m",-1,Now())) & "])'
> >
> > ... I do this to get data up to the previous month. I include the Qtr
> > because that is how my dimensions are set up. You may have to use the
> > Monthname function to return the full month name to match your dimension
> > structure.
> >
> >
> > "Koen" wrote:
> >
> > > hi all,
> > >
> > > examine the following piece :
> > >
> > > with
> > >
> > > member [month_list].[jan-sept] AS 'sum([month_list].[All
> > > month_list].[January]:[Month_list].[All month_list].[September])'
> > >
> > >
> > > As you can see, i summate the months January to September, september being
> > > the current month. Now, is there a way to replace [September] with the month
> > > that we are currently in, or perhaps by a parameter ?
> > >
> > > Thanks in advance