How to create following table using MDX Scripting in Sql Server 2005?

Question

Hi!

I have the following table ,

• Database Table:

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

I need to display the following table.

                 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.Could Please help me to solve this problem.

I tried it in Pivot table but i unable to get this. So is there any chance in Reporting Server MDX.

Answer 1

First, you need to create a cube with Analysis Services (MDX only works against multidimensional data sources)

Then, assuming you have a cube "MYCUBE" with "Batch" and "Chemical" dimensions and a "Value" measure, the query would be something like this (of course, you could select just the members you need instead of all the members in the dimensions):

SELECT 
    {[Batch].members * [Measures].[Value]} on columns,
    {[Chemical].members} on rows
FROM [MYCUBE]