Feeds:
Posts
Comments

Archive for October, 2012

If  a report that has many graphs and tablix, when the report is exported to an Excel format, each worksheet name is defaulted to “Sheet1”, “Sheet2” and so on. To name the worksheet to the name of the graph or tablix or a rectangle, set its PageName attribute to its name or a dataset field using an Expression. Apparently this only works for 2000R2.

Read Full Post »

It saddens me to pick a dead cat up from Leach hwy. This cat is my fifth. Obviously the cat died horribly and its body was left lying on the road. I wonder.. if it is a dead man or dog would it be left on the road? What if the cat is yours? ……..

Read Full Post »

I have  a report requirement to display a list of incident statistics based on the organisation structure. The org chart has 8 levels. When Level 1 is selected, display the stats for Level 2 (i.e. one level below) etc.

The MDX function to get the next level down is DESCENDANTS() function, for example, DESCENDANTS(STRTOMEMBER(@ClassValue, CONSTRAINED), 1).

To get the level of the next level in MDX.

MEMBER [Measures].[NextClassValueLevel] AS STRTOMEMBER(@ClassValue, CONSTRAINED).LEVEL.ORDINAL  + 1

This level is used to group the matrix column dynamically. Its column cell  and column group grouping is set to:

=Fields(“Level_” & Fields!NextClassValueLevel.Value).Value

 

Example code:

WITH
MEMBER [Measures].[Last 12 Months TRIFR] AS SUM( [Recordable Injuries], [Measures].[Last 12 Months Injury Frequency Rate] )
MEMBER [Measures].[NextClassValueLevel] AS STRTOMEMBER(@ClassValue, CONSTRAINED).LEVEL.ORDINAL  + 1

SELECT NON EMPTY  {  [Measures].[Last 12 Months TRIFR]
                                      , [Measures].[Last 12 Months Lost Days Injuries FR]
                                      , [Measures].[Last 12 Months Full Shift Lost Severity Rate]
                                      , [Measures].[NextClassValueLevel]
                                   } ON COLUMNS
                   , DESCENDANTS(STRTOMEMBER(@ClassValue, CONSTRAINED), 1)
                                     ON ROWS
FROM ( SELECT STRTOMEMBER(@ClassValue, CONSTRAINED) ON COLUMNS
FROM ( SELECT – { [Event Status].[Event Status].&[Cancelled] }  ON COLUMNS
FROM [Cube] ) )
WHERE  ( STRTOMEMBER(@DateMonth) )

 

 

 

Read Full Post »

Example code is shown below:

WITH
SET [Last6Months] AS LastPeriods(6, STRTOMEMBER(@DateDate, CONSTRAINED).Parent)

SELECT NON EMPTY  [Measures].[Events Count] ON COLUMNS    
            , NON EMPTY   {  OpeningPeriod( [Date].[Monthly].[Date], [Last6Months].Item(0).FirstChild )
                          :   StrToMember(@DateDate, CONSTRAINED) } 
                         *  [Event Status].[Event Status].[Event Status]
ON ROWS
FROM ( SELECT ( STRTOMEMBER(@ClassValue, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT -{ [Event Status].[Event Status].&[Cancelled] } ON COLUMNS
FROM [Cube] ) )

 

Read Full Post »

I have report with an injury type parameter of LTI, MTI, RWI and TRI. TRI is LTI, MTI and RWI. When TRI is selected, set it to grab from [Recordable Injuries] set, else grab it from the injury type selected. To do a case statement in MDX for this requirement:

SELECT  {   [Measures].[Last 12 Months Injury Frequency Rate]
                , [Measures].[Last 3 Months Injury Frequency Rate]
             } ON COLUMNS
       
              , LastPeriods(12, STRTOMEMBER(@DateDate, CONSTRAINED ).Parent )
                 ON ROWS
 FROM ( SELECT  STRTOMEMBER(@ClassValue, CONSTRAINED)  ON COLUMNS
 FROM ( SELECT CASE WHEN InStr(STRTOMEMBER(@InjuryType, CONSTRAINED).Name,”TRI”)>0 THEN [Recordable Injuries]
            ELSE STRTOMEMBER(@InjuryType, CONSTRAINED)
                           END ON COLUMNS
 FROM ( SELECT – { [Event Status].[Event Status].&[Cancelled] } ON COLUMNS
 FROM [Cube] ) ) )

Read Full Post »

%d bloggers like this: