CODEWITHSUNDEEP
reporting-servicesmdx
kyooryu
kyooryu

Reputation: 1509

MDX - Always return at least one row even if no data is available

I am using the following MDX to pull a parameter:

WITH
    MEMBER [Measures].[Label] AS [Dim].[Hier].CURRENTMEMBER.NAME
    MEMBER [Measures].[Value] AS [Dim].[Hier].CURRENTMEMBER.UNIQUENAME

SELECT
{
    [Measures].[Label]
    , [Measures].[Value]
}
ON 0,
    NONEMPTY([Dim].[Hier].children, [Measures].[Measure])
ON 1
FROM [Cube]
WHERE 
(
-- Criteria
)

Sometimes, when selecting certain filtering criteria the query results in empty set. Instead, I would like it to display "N/A" in both value and label. Is it doable in MDX or should I use SSRS Calculated member to count rows in resulting dataset and substitute?

Upvotes: 0

Views: 1574

Answers (1)

ic3
ic3

Reputation: 7680

Something like :

WITH
 SET [MySet] AS  NONEMPTY([Dim].[Hier].children, [Measures].[Measure])
 MEMBER [Measures].[Label] AS [Dim].[Hier].CURRENTMEMBER.NAME
 MEMBER [Measures].[Value] AS [Dim].[Hier].CURRENTMEMBER.UNIQUENAME
 MEMBER [Dim].[Hier].[All(likely)].[N/A] AS 'N/A'
SELECT
 {[Measures].[Label] , [Measures].[Value] }
ON 0,
 IIF( count( [MySet] ) = 0, {[Dim].[Hier].[All(likely)].[N/A]}, [MySet] )
ON 1
FROM
 [Cube]
WHERE 
 (-- Criteria)

Upvotes: 3

Related Questions