Feeds:
Posts
Comments

Archive for February, 2010

If your MDX query with multiple value parameters is produced from the MDX query designer, you will notice that the mutiple value report parameters are added and configured for you automatically on SSRS. If the default value is set to {All} in the query designer, when the report is first run with the defaults, it runs successfully. When you tick the <Select All> options, the report fails to run. This is a known behaviour. The <Select All> option will list all member values plus "[Dimension].[Level].[All]".
 
For example, for Equipment dimension with Equipment Level:
If only the "All"  value is selected, the value being passed will be only the [Equipment].[Equipment].[All]
If <Select All> is selected, the value being passed will be { [Equipment].[Equipment].[All], [Equipment].[Equipment].&[UJ014-000], [Equipment].[Equipment].&[UJ006-000]}
 
To use <Select All> option, add a dataset just to get a list of the Equipment parameter and make sure that the "All" is excluded from the list.
 
 
Advertisements

Read Full Post »

This error can be misleading. You would have thought that there is something wrong with your MDXquery where you have specified the same dimension in both attribute and slice (where clause). In fact, it might not be the case.
 
This is the KEY, check the dimension hierarchy and make sure that the value being passed to the report has the same dimension hierarchy.
 
For example:
[Equipment].[Equipments].&[UJ014-000] is not the same as [Equipment].[Equipment].&[UJ014-000] even though when placed in the query, it produces the same result!
 

Read Full Post »

If you keep getting this error, try the following trick found in the following blog:
 
 
The CONSTRAINED flag requires the selected member name to be resolved to a qualified member name. Drop a textbox on your report and set it to expression:
=Join(Parameters!<parameter name>.Value,", ")   Run the report. Does the output looks like valid member names?
 
If the report parameter accepts multiple values, try selecting the top 2 values from the drop down list,  then run the report. If the same error pops out, deselect the last value from the list but select the third value from the top then run the report again. Finally, exclude these values by using FILTER function with Logical AND in the Search Condition in the MDX query.
 
Common parameter values to be excluded are: All, N/A, Unknown and blank.
 

WITH

MEMBER [EquipmentTypeUniqueName] AS [Equipment].[Equipment Type].CurrentMember.Uniquename

MEMBER

[EquipmentTypeEngishCaption] AS [Equipment].[Equipment Type].CurrentMember.Name

SET

[CalcEquipmentType] AS [Equipment].[Equipment Type].AllMembers

 

SELECT

{[EquipmentTypeUniqueName], [EquipmentTypeEngishCaption]} ON COLUMNS

,

FILTER([CalcEquipmentType], [EquipmentTypeEngishCaption] <> "All" AND [EquipmentTypeEngishCaption] <> "" AND [EquipmentTypeEngishCaption] <> "N/A" AND [EquipmentTypeEngishCaption] <> "Unknown")

ON ROWS

FROM

InSiteCube

 

 

Read Full Post »

I kept getting the above warning messages when running my cube report and the tablix produced no data which is wrong as it should display some data. I kept refreshing my dataset but the same warning messages keep popping out. This is so strange and I have not seen this in the normal SSRS report.
 
I found out a very good tip on this blog:
 
As quoted from the above blog, by default, the MDX Query Designer uses NON EMPTY in the SELECT statement.  This means that the rows where there are no values for the selected measures will not be contained in the result set.  It also means that the complete measure will be omitted in the case that there are no values for it in any of the rows, which is the reason for our problem.
 
That is the KEY to my problem.
 
One of my report parameter allows multiple values. When all values are selected, the tablix produces no data. When I select a few values from the parameter, the tablix displays some data.
 
The workaround (short-tem) for this problem for me is to specify default values in the report parameter and make sure that these default values will produce some data when the report is first run.
 
 

Read Full Post »

I have the following list of all measure codes. The ACM Item Number and Comment are meaningless and therefore must be excluded from the list. These measure codes have ‘NONE’ uom_code.
 
 
So my goal is to get a list of measure codes where the uom_code is not NONE but the DELAY and MEETING measure codes are included in MDX. The DELAY and MEETING are also the measure codes with NONE uom_code.
 
In T-SQL, this is easy:
 

SELECT

DISTINCT measure_code

FROM

dm_measure

WHERE

uom_code <> ‘NONE’

OR

measure_code IN (‘DELAY’, ‘MEETING’)

 

How to do this in MDX? First of all, check the dimension hierarchies to see how they are all related (see below as an example).
 
 
The Measure dimension has 2 hierarchies, i.e. Compounds and Measures.  The Measures hirearchy has 2 levels, i.e. Measure and Uom. In order to get to level Uom from measure, use DESCENDANTS() function and set the level to 1. To exclude ‘None’ uom_code, use FILTER() function.
 
Syntaxes:
DESCENDANTS(<Member>, <Level>)
FILTER(<Set>, <condition>)
 
To get a list of measure codes (uniquename and name) where the uom is not ‘NONE’ in MDX:
 

WITH

MEMBER [MeasureUniqueName] AS [Measure].[Measures].CURRENTMEMBER.UNIQUENAME

MEMBER

[MeasureEnglishCaption] AS [Measure].[Measures].CURRENTMEMBER.NAME

SET

CalcMeasures AS FILTER(DESCENDANTS([Measure].[Measures].[Measure], 1), [Measure].[Measures].CURRENTMEMBER.NAME <> "none")

 

SELECT

{[MeasureUniqueName],[MeasureEnglishCaption] } ON COLUMNS

,

ORDER(CalcMeasures, [MeasureEnglishCaption], BASC) ON ROWS

FROM

InSiteCube

 

The output of the above query:

As you can see, the MeasureEnglishCaption field contains uom_code. I want it to contain measure_code. To do this, use subselect. Bear in mind that subselect in MDX works on COLUMN axis only.

WITH

MEMBER [MeasureUniqueName] AS [Measure].[Measures].CURRENTMEMBER.UNIQUENAME

MEMBER

[MeasureEnglishCaption] AS [Measure].[Measures].CURRENTMEMBER.NAME

SET CalcMeasures AS [Measure].[Measures].[Measure].ALLMEMBERS

 

SELECT

{ [MeasureUniqueName],[MeasureEnglishCaption] } ON COLUMNS

,

ORDER(CalcMeasures, [MeasureEnglishCaption], BASC) ON ROWS

FROM (

SELECT {FILTER(DESCENDANTS([Measure].[Measures].[Measure], 1), [Measure].[Measures].CURRENTMEMBER.NAME <> "none"), [Measure].[Measures].[Measure].&[DELAY],[Measure].[Measures].[Measure].&[MEETING]} ON COLUMNS

FROM InSiteCube

)

 

Output:

The above query now gives me a list of measure codes where the uom_code is not ‘NONE’ but delay and meeting measure codes are included. 🙂

 

Read Full Post »

Say I have the following list in my MDX query that I would use as report parameter value:

The following is the MDX query to produce the above list. To order the list ascendingly by name, use the ORDER() MDX function with the BASC flag.  The B stands for "break hierarchy".

String expression syntax
Order(Set_Expression, String_Expression
[ , { ASC | DESC | BASC | BDESC } ] )

WITH

MEMBER [MeasurePointUniqueName] AS [Activities].CURRENTMEMBER.UNIQUENAME

MEMBER

[MeasurePointEnglishCaption] AS [Activities].CURRENTMEMBER.NAME

SET

CalcMeasurePoints AS [Activity].[Activities].[Activity].ALLMEMBERS

SELECT CalcMeasurePoints ON ROWS

,{[MeasurePointUniqueName], [MeasurePointEnglishCaption]}

ON COLUMNS

FROM InSiteCube

 
 
The above query with the sort order added:
 

WITH

MEMBER [MeasurePointUniqueName] AS [Activities].CURRENTMEMBER.UNIQUENAME

MEMBER

[MeasurePointEnglishCaption] AS [Activities].CURRENTMEMBER.NAME

SET

CalcMeasurePoints AS [Activity].[Activities].[Activity].ALLMEMBERS

SELECT ORDER(CalcMeasurePoints, [MeasurePointEnglishCaption], BASC) ON ROWS

,{[MeasurePointUniqueName], [MeasurePointEnglishCaption]}

ON COLUMNS

FROM InSiteCube

 

 
 

Read Full Post »

The are two types of the reports we can build on SSRS, depending on the type of the data source. The data source can be a relational database or a multidimensional database (cube). The query used is SQL and MDX respectively.
 
Creating a cube report is very different from the normal report, in particular, declaring and adding parameters into the MDX query. Database fields such as Fields!Date.Value on relational database becomes [Date].[Calendar].[Date].&[2009-12-01T00:00]. Making sure this format is passed through is very important.
 
Steps:
1. Set the report data source to connect to the cube
2. Add dataset. Set the name and the data source of the dataset and the query type to Text.
3. Click on the Query Designer then turn off the design mode so that you can type in the query directly
4. Write your MDX query
5. To declare and create the parameters,, select the "Query Parameters" icon from the top menu
 
 
 
6. Create the parameter such as @StartDate and @EndDate. Select the corresponding dimension, hierarchy, multiple values and Default values.
7. To use the parameters on the MDX, convert the string to member using the StrToMember function  then put this into the SELECT query.
 
StrToMember(@StartDate):StrToMember(@EndDate)  )  )ON ROWS
 
8. Example:
SELECT NON EMPTY( (StrToMember(@Equipment), ( [Product].[Products].[Batch] ) , StrToMember(@StartDate):StrToMember(@EndDate)  )  )ON ROWS
,  NON EMPTY ({  [Measures].[0.9m Split Sets] ,[Measures].[1.8m Split Sets] ,[Measures].[2.4m Split Sets] ,[Measures].[3.0m Split Sets] ,[Measures].[Mesh Square Metres] ,[Measures].[Johnsons Explosives]  } ) ON COLUMNS
 FROM InSiteCube
WHERE ([Equipment].[Equipment Type].&[JUMBO])
 
9. Run the query.
 
11. Notice that the report parameters are automatically populated into the report including the available values and default values previously set.
 
 
 
 
10. Run the report.
 
 
 
 
 
 

Read Full Post »

Older Posts »

%d bloggers like this: