INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

MDX query, normalize output for Matrix Report

MDX query, normalize output for Matrix Report

(OP)
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!

RE: MDX query, normalize output for Matrix Report

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.

RE: MDX query, normalize output for Matrix Report

(OP)
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!

RE: MDX query, normalize output for Matrix Report

This is what I meant:

CODE

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.

RE: MDX query, normalize output for Matrix Report

(OP)
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.

CODE

           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!

RE: MDX query, normalize output for Matrix Report

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:

CODE

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.

RE: MDX query, normalize output for Matrix Report

(OP)
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!

RE: MDX query, normalize output for Matrix Report

(OP)
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!

RE: MDX query, normalize output for Matrix Report

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close