Creating a Matrix report from an OLAP cube

Hello there
I am trying to create a Matrix report from an OLAP cube
but not having any luck. the end result I want is the
UserName along the Row section, Months of the year along
the column section (the date range will be specified in
the MDX statement), and the Data will be a count of hits
(which is my Measure) to my website on a per month basis
and Grouped By the UserName. My MDX statement looks like
this:
SELECT
{[Time].[Calendar].[2004].[May]:[Time].[Calendar].[2004].
[June]} on Columns ,
{[Business].[User].[Business Entity Id].[5611].Children }
on Rows
FROM UsageStats_Phase1
This query executed in the MDX Sample Application returns
exactly what i want. but I am having trouble getting the
same information in a Matrix or Table report. Would anyone
have any idea how to go about doing this? I'm quite new to
MDX, OLAP, and Reporting Services (not a very strong
position to be in, i know!) - so any help would be very
much appreciated!
Thanks in anticipation
MariaMaybe you need to explicit the measures (something like:
SELECT {[Measures].[<MISURE_NAME>], [Measures].[<MISURE_NAme>],...} on Columns,
{[Business].[User].[Business Entity Id].[5611].Children }
on Rows,
{[Time].[Calendar].[2004].[May]:[Time].[Calendar].[2004].
[June]} ON PAGES
FROM UsageStats_Phase1
maybe you need also to use the DESCENDANTS function for rows and pages
)
than, using a matrix control, drag the measures in the data fields.
hth
Antonio
"Jano" wrote:
> Hello there
> I am trying to create a Matrix report from an OLAP cube
> but not having any luck. the end result I want is the
> UserName along the Row section, Months of the year along
> the column section (the date range will be specified in
> the MDX statement), and the Data will be a count of hits
> (which is my Measure) to my website on a per month basis
> and Grouped By the UserName. My MDX statement looks like
> this:
> SELECT
> {[Time].[Calendar].[2004].[May]:[Time].[Calendar].[2004].
> [June]} on Columns ,
> {[Business].[User].[Business Entity Id].[5611].Children }
> on Rows
> FROM UsageStats_Phase1
>
> This query executed in the MDX Sample Application returns
> exactly what i want. but I am having trouble getting the
> same information in a Matrix or Table report. Would anyone
> have any idea how to go about doing this? I'm quite new to
> MDX, OLAP, and Reporting Services (not a very strong
> position to be in, i know!) - so any help would be very
> much appreciated!
> Thanks in anticipation
> Maria
>|||Thanks for the reply. I managed to get around it by doing
the following:
SELECT
{ Measures.members } on Columns ,
{ Crossjoin( [Time].[Calendar].[2004].[May]:[Time].
[Calendar].[2004].[July], [Business].[User].[Business
Entity Id].[5611].Children ) } on Rows
FROM UsageStats_Phase1
All the best
Jano
>--Original Message--
>Maybe you need to explicit the measures (something like:
>
>SELECT {[Measures].[<MISURE_NAME>], [Measures].
[<MISURE_NAme>],...} on Columns,
>{[Business].[User].[Business Entity Id].
[5611].Children }
>on Rows,
>{[Time].[Calendar].[2004].[May]:[Time].[Calendar].[2004].
>[June]} ON PAGES
>FROM UsageStats_Phase1
>maybe you need also to use the DESCENDANTS function for
rows and pages
> )
>
>than, using a matrix control, drag the measures in the
data fields.
>
>hth
>Antonio
>
>"Jano" wrote:
>> Hello there
>> I am trying to create a Matrix report from an OLAP cube
>> but not having any luck. the end result I want is the
>> UserName along the Row section, Months of the year
along
>> the column section (the date range will be specified in
>> the MDX statement), and the Data will be a count of
hits
>> (which is my Measure) to my website on a per month
basis
>> and Grouped By the UserName. My MDX statement looks
like
>> this:
>> SELECT
>> {[Time].[Calendar].[2004].[May]:[Time].[Calendar].
[2004].
>> [June]} on Columns ,
>> {[Business].[User].[Business Entity Id].
[5611].Children }
>> on Rows
>> FROM UsageStats_Phase1
>>
>> This query executed in the MDX Sample Application
returns
>> exactly what i want. but I am having trouble getting
the
>> same information in a Matrix or Table report. Would
anyone
>> have any idea how to go about doing this? I'm quite new
to
>> MDX, OLAP, and Reporting Services (not a very strong
>> position to be in, i know!) - so any help would be very
>> much appreciated!
>> Thanks in anticipation
>> Maria
>.
>