Archive for February, 2010
MDX report failed to run when the in the report parameter is ticked
Posted in MDX on February 22, 2010| Leave a Comment »
The Equipment hierarchy already appears in the Axis1 axis
Posted in MDX on February 22, 2010| Leave a Comment »
The restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated
Posted in MDX on February 21, 2010| Leave a Comment »
MEMBER [EquipmentTypeUniqueName] AS [Equipment].[Equipment Type].CurrentMember.Uniquename
[EquipmentTypeEngishCaption] AS [Equipment].[Equipment Type].CurrentMember.Name
[CalcEquipmentType] AS [Equipment].[Equipment Type].AllMembers
{[EquipmentTypeUniqueName], [EquipmentTypeEngishCaption]} ON COLUMNS
FILTER([CalcEquipmentType], [EquipmentTypeEngishCaption] <> "All" AND [EquipmentTypeEngishCaption] <> "" AND [EquipmentTypeEngishCaption] <> "N/A" AND [EquipmentTypeEngishCaption] <> "Unknown")
ON ROWS
InSiteCube
rsMissingFieldInDataSet and rsErrorReadingDataSetField (MDX and SSRS 2008)
Posted in Reporting Services on February 19, 2010| Leave a Comment »
How to use subselects in MDX to filter a list of items based on your conditions? (MDX)
Posted in MDX on February 19, 2010| Leave a Comment »
DISTINCT measure_code
dm_measure
uom_code <> ‘NONE’
OR
measure_code IN (‘DELAY’, ‘MEETING’)MEMBER [MeasureUniqueName] AS [Measure].[Measures].CURRENTMEMBER.UNIQUENAME
[MeasureEnglishCaption] AS [Measure].[Measures].CURRENTMEMBER.NAME
CalcMeasures AS FILTER(DESCENDANTS([Measure].[Measures].[Measure], 1), [Measure].[Measures].CURRENTMEMBER.NAME <> "none")
{[MeasureUniqueName],[MeasureEnglishCaption] } ON COLUMNS
ORDER(CalcMeasures, [MeasureEnglishCaption], BASC) ON ROWS
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.
MEMBER [MeasureUniqueName] AS [Measure].[Measures].CURRENTMEMBER.UNIQUENAME
[MeasureEnglishCaption] AS [Measure].[Measures].CURRENTMEMBER.NAME
SET CalcMeasures AS [Measure].[Measures].[Measure].ALLMEMBERS
{ [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. 🙂
How to order a list by the name in ascending order? (MDX)
Posted in MDX on February 16, 2010| Leave a Comment »
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 } ] )
MEMBER [MeasurePointUniqueName] AS [Activities].CURRENTMEMBER.UNIQUENAME
[MeasurePointEnglishCaption] AS [Activities].CURRENTMEMBER.NAME
CalcMeasurePoints AS [Activity].[Activities].[Activity].ALLMEMBERS
SELECT CalcMeasurePoints ON ROWS
ON COLUMNS
FROM InSiteCube
MEMBER [MeasurePointUniqueName] AS [Activities].CURRENTMEMBER.UNIQUENAME
[MeasurePointEnglishCaption] AS [Activities].CURRENTMEMBER.NAME
CalcMeasurePoints AS [Activity].[Activities].[Activity].ALLMEMBERS
SELECT ORDER(CalcMeasurePoints, [MeasurePointEnglishCaption], BASC) ON ROWS
ON COLUMNS
FROM InSiteCube
How to add parameters into the MDX query required to build a cube report (SSRS 2008)
Posted in Reporting Services on February 11, 2010| 1 Comment »
Errors in the OLAP storage engine: An error occurred while processing the ‘Dm Item Number’ partition of the ‘Dm Item Number’ measure group for the ‘InSiteCube’ cube from the InSite_ACM_Cube database (SSAS 2008)
Posted in Analysis Services on February 4, 2010| Leave a Comment »
ReportItem value is only retrieved in the header if the Report Item is on the same page
Posted in Reporting Services on February 2, 2010| Leave a Comment »
Fields!location_cat_name.Value & " " & Fields!equipment_name.Value & " " & Fields!equipment_code.Value on the header, I created a new dataset to grab this value then pass it as a report parameter and hide it.
Tablix headers not repeating in SSRS 2008
Posted in Reporting Services on February 2, 2010| 1 Comment »
Set the KeepWithGroup property in the following way:
- For a static row that is above a group, click After.
- For a static row that is below a group, click Before.