MDX query, normalize output for Matrix Report 1

[Crowley16]

I have a standard mdx query, along the lines of:

select
crossjoin
(
Product.[Hierarchy]
, Measures.[Measure]
) on rows
, Location.Hierarchy
on columns
from
cube

however, I'm trying to use this as the source of a SSRS report and SSRS is trying to hard code all the location columns.

I think what's needed is this mdx to produce normalized results, i.e. :

Product Location Measure Value
prod1 store1 sale 5.2
prod2 store1 qty 3

there will be multiple measures for each product/store combination.

Is it possible to do this in MDX?

Thanks

--------------------
Procrastinate Now!

 

[RiverGuy]

You need to format your query so that you have static columns for SSRS. Put your measure on the columns and your dimension members on the rows. You can then have SSRS build the location columns dynamically.

 

[Crowley16]

Hi RiverGuy,

Sorry for the late reply, have been a bit busy. Anyway...

Maybe this should be a SSRS question from now, but I've re-configured the measures to be on the columns and the dimensions on the rows, but in a matrix report, how do I actually get the measures to be displayed on the rows?

When I use the wizard for a matrix report, it only seems to allow me to place measures in the detail section since there's to value field to each measure, and I can't place anything on the rows section.

Manually, I can't seem to create a group of the measures on the matrix.

Thanks

--------------------
Procrastinate Now!

 

[RiverGuy]

This is what I meant:

select
   crossjoin
   (
      Product.[Hierarchy]
      ,Location.Hierarchy 
   ) on rows
   , Measures.[Measure] 
   on columns
from
   cube

In the matrix, you add your measure (whatever the name of it is) to your details section. Add your product to the column groupings and your location to the row groupings.

 

[Crowley16]

Hi RiverGuy,

Sorry, I was a bit unclear in my original post.

I actually have multiple measures in the full mdx and would like the measures to be displayed per product, i.e.

           Loc1   Loc2
Prod1 Sale 1.5      2.0
      Qty  5        3
Prod2 Sale 2.7      1.4
      Qty  1        2

using sql directly, I denormalised the data so that there was a Value Type column against each measure and that worked in a matrix, however, I'd like to use the cubes instead of going to the source data, so am trying to do the same in MDX.

Thanks again.

--------------------
Procrastinate Now!

 

[RiverGuy]

When you create your Matrix through the wizard, you can pick multiple measure to put into the details section. By default, this will put your measures side by side, so you will end up with:

Prod1  Loc1       Loc2
      Sale  Qty  Sale Qty

Prod2

However, you can right-click on a row, choose Insert New Row/Inside Group, and cut and paste your Qty column to the new cell. You can then right-click your Prod column to insert a new column. Then, right-click the cell it creates and choose "Split Cells." You can then cut and paste your Sale and Qty column headers into the two new cells to make row headers. Finally, delete your now empty column and row for your now empty column headers.

 

[Crowley16]

Hi RiverGuy

I've tried to click on the row or column to add new groups and it only allows me to add dynamic groups, there's no option to "split cells" or to even set up static groups.

I'm using SSRS 2005.

--------------------
Procrastinate Now!

 

[Crowley16]

Eureka!

I had to right click on the data field to create a static row, and then assign the measure values individually to each new row.

Was easier with no measures selected on the wizzard.

Thanks RiverGuy, got there in the end

--------------------
Procrastinate Now!

 

[RiverGuy]

No problem. Things are a little different between SSRS 2005 and 2008. 2005 is actually easier to develop with in most cases, but I hadn't had time to fire up VS 2005 to look at this yet.