How to create following cube?

Question

For example...........

• Database Table:

       BatchID    BatchName      Chemical      Value
  --------------------------------------------------------
       BI-1       BN-1           CH-1             1
       BI-2       BN-2           CH-2             2
  --------------------------------------------------------
  

I need to display following cube

                  BI-1             BI-2
                  BN-1             BN-2
 -----------------------------------------
   CH-1           1                null
 ------------------------------------------
   CH-2           null             2
 ------------------------------------------

Here BI-1,BN-1 are two rows in a single columns i need to display chemical value as row of that.

What is query MDX query for this.

Could Please help me to solve this problem.

Thank You.

Answer 1

Create a cube with BatchID, Batchname and Chemical as dimensions and Value as measure.

Then use the following MDX code:

SELECT
  Crossjoin(Crossjoin([BatchID].Members, [Batchname].Members), { [Measures].[Value] }) ON COLUMNS,
  [Chemical].Members ON ROWS
  FROM [Mycube]