Archive for June, 2012

I have a report that only to display information about subcontractors. In SQL this could be easily done with WHERE Organisation NOT LIKE ‘%cube %’.

To do this in MDX:

1. Using FILTER function e.g. To display Organisations that contains “Cube” in the name

     WITH SET [Cube Organisations] AS

     FILTER( [Organisations].[Organisation].MEMBERS, InStr( [Organisations].[Organisation].CurrentMember.Name, “Cube”) > 0 )

2. Using EXCEPT function. The following returns all organisations which do not contain “Cube” in the name. In other word, if the organisation name does not contain cube then the rest of them are subcontractors.

     EXCEPT( [Organisation].[Organisation].[Organisation], [Cube Organisations] )

    Use the above in the MDX query for the report.

   SELECT [Measures].[Events Count] ON COLUMNS

                  , EXCEPT( [Organisation].[Organisation].[Organisation], [Cube Organisations] ) ON ROWS

   FROM Cube

Read Full Post »

When you have to use a specific named member in your MDX calculation for your SSRS report and if this named member does not exist, you will get nothing returned in your dataset (i.e. dataset with no fields).

For example, I have a report to display a list of Lessons Learnt events for this month but this Lessons Learnt events do not exist yet.

My original MDX to get a list of events where the event type is Lessons Learnt is below. Since it does not exist yet, the query returns with NULL Events Count.

SELECT   { [Measures].[Events Count]  } ON COLUMNS
              ,NON EMPTY  {   [Date].[Date].[Date]
         * [Class Values].[Class Value].[Class Value]                       
                * [Events].[Event ID].[Event ID]
                * [Events].[Short Observation].[Short Observation]
                * [Events].[Detailed Observation].[Detailed Observation]
                * [Event Sub Types].[Event Sub Type].&[Audit and Assessments]&[Lessons Learnt]&[Health & Safety]
                * [Not Cancelled Event Status]
                }  ON ROWS   
FROM [InX_Cube]

In order to have the dimension fields returned in the dataset, the key is to use EXISTS function and remove NON EMPTY from the query.

Another solution would be to get all events then filter the tablix to only display data where the event type is Lessons Leant.

Read Full Post »

I have a resultset returned from my MDX as below. The EventID is unique hence it should not have been displayed twice.

Solution to this is to use DISTINCT() function.


               , DISTINCT ( [Date].[Date].[Date]

                                         * [Events].[Event ID].[Event ID]

                 ) ON ROWS




Read Full Post »

%d bloggers like this: