MDX Query - Help Needed Please

Copper Contributor

Hi all, Is this the right place to ask for help? I was thinking i'd joined a forum where I can ask subject matter experts advice? 

 

I need help writing an MDX query to sum two columns of data. Here's the background:

 

I've set up 2 x data tables

     table 1 is inventory available stock. very simple : list of part numbers and how many available.

     table 2 is a list of list works order detailing how many of each of the items is required. The complication here is that there's multiple rows as each works order requires multiple of items of inventory in order to manufacture it so it's displayed as multiple rows

I've set up a relationship between the tables

    I've created a 1 to many relationship linked by part number

I've created a pivot table which brings in the data from both the above tables

    I've brought in the part number from the available stock table, as the ROW

    I've put a formula "SUM OF AVAILABLE STOCK "(the QTY available column from the available stock table) into the values section

     I've put a formula "SUM OF QTY REQUIRED" (from table 2 which has multiple rows) into the values section as well 

 

What I can see in my simple pivot is:

.Part number.     .How many in stock.     .How many I need in order to make my various items.

This is expressed as a positive number for available stock and then a negative number for QTY required

 

WHAT I WANT TO SEE:

I want to create a new calculation column at the end of my pivot table so I can see how many I actually need. What I think is that I need to create a new SET which performs a simple calculation: 

QTY IN STOCK (ADD) QTY REQUIRED. 

All in all, my pivot table would then have 4 headings:

part number

QTY in stock

QTY required in total

QTY required to complete works order (my new calculated field) 

 

example

Part number : Widget 1

QTY in Stock : 10

QTY Required in Total : -20

QTY required to complete works orders : 10 

 

I have figured out how to add a new set but then I end up needing to write a MDX query which is what I'm not able to do because I've never used it before so I'm completely green when it comes to the syntax and expressions. 

 

Please can someone help? In my mind this should be REALLY simple because it's such a simple sum.

 

0 Replies