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
