Sql-server – NULL values on MDX query (Total Column has Values)

mdxquerysql-server-2008

I'm a novice with MDX, so sorry if I've missed something. (and sorry about my english too!)

The following query:

WITH 
 SET [ROWMEMBERS] AS '{([BIN_SCH_INV_GRO].&[1]) , ([BIN_SCH_INV_GRO].&[2]) , ([BIN_SCH_INV_GRO].&[3]) , ([BIN_SCH_INV_GRO].&[4]) , ([BIN_SCH_INV_GRO].&[5]) , ([BIN_SCH_INV_GRO].&[6]) , ([BIN_SCH_INV_GRO].&[7]) , ([BIN_SCH_INV_GRO].&[8]) , ([BIN_SCH_INV_GRO].&[9])}' 
SELECT {([Measures].[BIN_SCH_REC_VAL], [BIN_SCH_Year].&[2010]) 
 , ([Measures].[BIN_SCH_VAL_Real_CSP], [BIN_SCH_Year].&[2010]) 
 , ([Measures].[BIN_SCH_REC_VAL], [BIN_SCH_Year].&[2011]) 
 , ([Measures].[BIN_SCH_VAL_Real_CSP], [BIN_SCH_Year].&[2011]) 
 , ([Measures].[BIN_SCH_REC_VAL], [BIN_SCH_Year]) 
 , ([Measures].[BIN_SCH_VAL_Real_CSP], [BIN_SCH_Year]) 
 } ON COLUMNS, [ROWMEMBERS] DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON ROWS FROM [ProductsManagement]

Returns this:

BIN_SCH_REC_VAL BIN_SCH_VAL_Real_CSP    BIN_SCH_REC_VAL BIN_SCH_VAL_Real_CSP    BIN_SCH_REC_VAL BIN_SCH_VAL_Real_CSP
    2010    2010    2011    2011    ALL ALL
ALIMENTACAO (nulo)  (nulo)  (nulo)  (nulo)  2,663,657.70    1,650,353.73
ALIMENTACAO PARENTAL E ENTERAL  (nulo)  (nulo)  (nulo)  (nulo)  1,028,281.08    1,033,596.66
BRINDES (nulo)  (nulo)  (nulo)  (nulo)  163,867.06  187,183.59
EQUIPAMENTOS DE SEGURANCA   (nulo)  (nulo)  (nulo)  (nulo)  497,320.63  372,173.12
FIOS CIRURGICOS (nulo)  (nulo)  (nulo)  (nulo)  1,280,577.64    1,288,735.81
GASES MEDICINAIS    (nulo)  (nulo)  (nulo)  (nulo)  144,900.20  141,914.26
HIGIENE E LIMPEZA   (nulo)  (nulo)  (nulo)  (nulo)  1,895,881.03    1,949,619.64
IMPRESSOS   (nulo)  (nulo)  (nulo)  (nulo)  1,617,042.25    1,652,345.87
LABORATORIO (nulo)  (nulo)  (nulo)  (nulo)  6,672,048.90    6,725,001.30

I'm pretty sure that those rowmembers contain values on the specified years…

Any Ideas?

Thank you!

Best Answer

If you are an MDX novice, you should start simple and layer on complexity as you get the simple queries to work. Try getting just one of the values you're expecting. Once you get that, all the others should fall into place.

Start off with something like this:

SELECT 
[BIN_SCH_Year].&[2010]  ON COLUMNS, 
[BIN_SCH_INV_GRO].&[1]  ON ROWS 
FROM [ProductsManagement]
WHERE [Measures].[BIN_SCH_REC_VAL]

If you find values, build on this query. If you don't, take a look at your relational database and make sure the data is loaded correctly.

Boa sorte!

Related Question