Archive for April, 2011

Answer: Use TOPCOUNT mdx function.

For example, to get which body part that got injured the most from 01 Jan 2010 to 13 Oct 2010:

SELECT [Measures].[Current Last YTD Injuries Growth] ON COLUMNS
 , TopCount
     [Body Parts].[Body Part Class Name].[Body Part Class Name].MEMBERS
     , 1
            , [Measures].[Current Last YTD Injuries Growth]
WHERE  StrToMember(“[Date].[Monthly].[Date].&[2010-10-13T00:00:00]”)

Read Full Post »


 , OpeningPeriod([Date].[Monthly].[Date], [Date].[Monthly].[Date].&[2010-11-05T00:00:00].Parent.FirstSiblingON ROWS


To place the above mdx into SSRS:

 OpeningPeriod([Date].[Monthly].[Date], StrToMember(@DateDate + “.Parent.FirstSibling” ))

Read Full Post »


MEMBER TodayDate AS vba![Date]()

SET CurrentDate AS Filter([Date].[Monthly].[Date], [Date].[Monthly].MemberValue = ([Date].[Date].[All],TodayDate))

SET CurrentMonth AS ANCESTOR( CurrentDate.Item(0), [Date].[Monthly].[Month] )

SET CurrentYear AS ANCESTOR( CurrentDate.Item(0), [Date].[Monthly].[Year] )

select {} on ROWS        

               {CurrentMonth, CurrentYear} ON  COLUMNS

from Cube

Read Full Post »

I have a task to set up a KPI for Injury count for my dashboard. When the injury count growth is negative, set the indicator arrow to down and red. When the growth is zero set it to point to the right direction and yellow. When the growth is positive, set the indicator arrow to up and green.

The calculated measure that I have created for this scenario is [Measures].Injury_Count_Previous_Month_Growth].

This calculated measure is used in my report dataset.

Question: how to set the Start and End range in each indicator arrow in order to meet my requirement?

After several attempts, I managed to get it to work.

This is how I got it set up properly:

For the red and down arrow, set the Start to

=Sum(Fields!Injury_Count_Previous_Month_Growth.Value, “BodyLocationOfInjury”) < 0

and the End to -1.

For the yellow arrows, set both Start and End to zero.

For the green and up arrow, set the Start to zero and the End to

=Sum(Fields!Injury_Count_Previous_Month_Growth.Value, “BodyLocationOfInjury”) > 0



Read Full Post »

%d bloggers like this: