开发者

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

开发者 https://www.devze.com 2022-12-24 14:33 出处:网络
I have the following table , Database Table: BatchIDBatchNameChemicalValue ----------------------------------------------

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        开发者_如何学Python     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.


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]
0

精彩评论

暂无评论...
验证码 换一张
取 消