MDX Functions in Report studio

Hi Guys.

Now i have only included screenshot.I will include with syntax and with a practice example when i do them .Example screenshots taken fron Slideshare.Nice presentation

http://www.slideshare.net/PerformanceG2/pg2-multi-dimensional-reporting-using-report-studio


 
 

In case of SQL the hierarchy can be represented by Self referrential joins or by Flattening the hierarchy. In case of Flat hierarchy we can add columns names like Country, State,City and drill up and down a level . In case of Cubes we do not have multiple columns so we need to know functions to navigate the hierarchy

If you want to move up use 

Parent -- only one level up 

ancestor -- allows you to specify level up 1,2,3,4... etc

If you want to move horizontal 

Lead 

Lag

Nextmember

Previousmember

If you want to move down use

children -- only one level

descendant -- you can specify levels 1,2,3,...etc

Current Measure

Tuple

Sets

Some Interesting time function in MDX

AS OF DATE

[sales_and_marketing].[Time].[Time].[Month]->:[PC].[@MEMBER].[20040101-20040131]

when you go for cube this value automatically gets stored
:[PC].[@MEMBER].[20040101-20040131] value is cube value for [2004Jan] this is how cube finds it .So if you put [2004] there directly it all works fine. when prompt takes [2004jan] it is actually taking this value.This is member unique name.

How last period is used as filter for Months

lastPeriods(13,[sales_and_marketing].[Time].[Time].[Month]->?P_Month?)

This will generate the last 13 periods.that is gets previous 13 members.Good for previous year.
Note - Using lead ,Lag will return you only one member and Not 13 members.So they cant be used here.

Except

IBM Defination

except ( set_expression1 , set_expression2 [ , all ] )
Returns the members of "set_expression1" that are not also in "set_expression2". Duplicates are retained only if the optional keyword all is supplied as the third argument.

How it can be used in report

If you want to show data from May 2005 to Nov 2006.Below can be used

except( periodsToDate([sales_and_marketing].[Time].[Time].[Time] , [endDate] ) , periodsToDate([sales_and_marketing].[Time].[Time].[Time] , prevMember( [beginDate] ) ) )

The first thing gives all month till end date Jan 2004 to Dec 2007, second thing all month till one before begin date Jan 2004 to April 2005.So once you do extract you are left with May.Here you could not have used lastperiod directly .May be with use of months_between.


Check out below link for some good Idea on how to use functions with Time

http://www.ibm.com/developerworks/data/library/cognos/reporting/dimensional_queries/page561.html

Generate

A common example of generate is for generating top 2 products of product line by revenue.You will find it in the MDX help guide.But we hardly come across such scenario.

IBM Defination.

generate ( set_expression1 , set_expression2 [ , all ] )
Evaluates "set_expression2" for each member of "set_expression1" and joins the resulting sets by union. The result retains duplicates only when the optional keyword "all" is supplied as the third argument.

generate ( [Product line] , topCount ( descendants ( currentMember ( [great_outdoors_company].[Products].[Products] ) , [great_outdoors_company].[Products].[Products].[Product name] ) , 2 , [Revenue] ) )
Result: Returns the top two products by revenue for each product line

We have product line and below it level product type.Here we give product line and it takes out product type for each product line and finds the top2 from product type.

How you can use it in report.

You can use number of ideas to achieve the below.Its just one example of generate.Need to show selected year and year prior to that.

[SelectedMonths] -- data item.

set([sales_and_marketing].[Time].[Time].[Month]->?Parameter1?)

There are few function that work only on set and not on member.Remember that members in hierarchy make a set . So the set that has been done here is for use in generate

generate([SelectedMonths] , lag( currentMember([sales_and_marketing].[Time].[Time]) , 12) )

This is done so that both the current and previous year come together.

union( [PreviousMonths] , [SelectedMonths] ))





order( [UnionOfSelectedSets] , [PositionOfMonthInYear] )

------------------------------------------------------------------------------------------
_firstFromSet([great_outdoors_company].[Products].[Products].[Product line],2,2)

_firstFromSet([great_outdoors_company].[Products].[Products].[Product line],2,8)

The product line has five members .firstfromset( a,max members,overflow).If the total
members of set come within the overflow and max taht is 4 then it will show all otherwise
it will show only 2 . since there are 5 members it shows only 2

Hierarchy

hierarchy ([Camping Equipment])

hierarchy ([great_outdoors_company].[Products].[Products].[Product line])

--- when you do this every member of hierarchy is taken.Consideryou take product line then every member of hierarchy till product will be shown.It find the member in the hierarchy and shows the entire hierarchy it belongs to.Both the expression return the same result


level ([Camping Equipment] )

Retuns members of that level.Here you have put product line so only member of product line will
be displayed.There are 5 product lines her

Interesting Function
---------------------------------------------------------------------------------------

member(total(currentMeasure within set [B Product List]),'BProducts','B Products',

[great_outdoors_company].[Products].[Products]) .

member(total([great_outdoors_company].[Measures].[Quantity sold] within set

[2005]),'Quantity2005','Quantity sold 2005')

member(total([great_outdoors_company].[Measures].[Revenue] within set

[2005]),'Revenue2005','Revenue 2005')


Defines a member based on "value_expression" in "hierarchy". "String1" identifies the member
created by this function. It must be unique in the query and different from any other member in
the same hierarchy. "String2" is the caption of the member; if it is absent, the caption is
empty. To ensure predictable results, it is recommended that you supply the "hierarchy".
----------------------------------------------------------------------------------------------[

How to get B list

filter([great_outdoors_company].[Products].[Products].[Product name],caption

([great_outdoors_company].[Products].[Products].[Product name]) starts with 'B')

filter ( [Product line] , [Gross margin] > .30 )

filter ( set_expression , Boolean_expression )
Returns the set resulting from filtering a specified set based on the Boolean condition. Each

member is included in the result if and only if the corresponding value of "Boolean_expression"

is true.

-------------------------------------------------------------------------

prevMember ( member )

nextMember ( member )

lag([2006/May],6)

lead([2006/May],6)

Lead ,Lag allows you to specify the number of positions that you want to move forward or backward
---------------------------------------------------------------------------

ancestor ( member, level|integer )
Returns the ancestor of "member" at "level" or at "integer" number of levels above "member".

Example: ancestor ( [TrailChef Water Bag] , [great_outdoors_company].[Products].[Products].

[Product type] )
Result: Cooking Gear

Example: ancestor ( [TrailChef Water Bag] , 1 )
Result: Cooking Gear

ancestor ( member, level|integer )

parent ( member|measure )

cousin ( member1 , member2 )
Returns the child member of "member2" with the same relative position as "member1" to its parent.

children ( member )

descendants ( member|set_expression , level|distance [ , { self|before|beforewithmember|after } ]

)

descendants allow you to specify the members at a level or at a distance including all the

members till the level specified or only the members of level specified

firstChild ( [By Product Lines] )
rootmember can be used to [By Product Lines]

lastChild ( member )

siblings ( member )
Returns the children of the parent of the specified member.

lastSibling ( member ) -------Similar is closing period

firstSibling ( member ) ------- openingPeriod ( level [ , member ] )

lastPeriods(2,[2005]) ---- from member specified back 2 period

periodsToDate ( level , member ) --- from start till member specified

total([Revenue] within set periodsToDate([great_outdoors_company].[Years].[Years].

[Year],currentMember([great_outdoors_company].[Years].[Years])))

similar is item ( set_expression , index )
Returns a member from the "index" location within "set_expression". The index into the set is

zero based.

item ( children ( [Camping Equipment] ) , 2 ) -- item returns only one member

head ( set_expression [ , index_expression ] ) -- Head returns multiple members

tail ( set_expression [ , index_expression ] )
Returns the last "index_expression" elements of "set expression". The default for

"index_expression" is 1.

Difference between tail and lastperiod is you can specify where last period start.It can actually
start from middle but tail always starts from last member


-----------------------------------------------------------------------

If you want to move up use

Parent -- only one level up
ancestor -- allows you to specify level up 1,2,3,4... etc

If you want to move horizontal

Lead
Lag
Nextmember
Previousmember

If you want to move down use

children -- only one level
descendant -- you can specify levels 1,2,3,...etc


------------------------------------------------------------

Very easy to simulate parallelperiod. Use ancestor go on top and use lead lag to move