General: should think of grouping stuff as javascript hiding/showing. Often mdx DESCENDANTS() will return a hierarchy rather than just a level of a hierarchy and without proper grouping, every member from every level will be displayed. By grouping on uniquename and parentuniquename mdx properties, the report will display a level at a time and know when a member has children so display an expand control (plus). The plus will appear after you specify 'report can be toggled by this item' in the GROUP properties. Dont forget about the group properties, accessible in the pane UNDER the design face. No amount of clicking around the tablix will get you group properies, use the pane below.
2005 drillthrough
tutorial by willian pearson
When passing params user Fields!Month.
UniqueName instead of value as uniquename will give you the fully qualified mdx path to the member in a hierarchy. This is better than exposing member hierarchies just for the sake of being able to specify a param per level of the user hiearchy.
So passing both year and month params to visible attribs of the data dimension is a bit crap compared to just passing the fully qualified
date.
year-month.
2008.
jan to a single param.
Recursive Hierarchies - parent child dimension can be displayed well on a report that has expanding group rather than an action calling the same report recursively, if you pad the groups for readability.
On a row there is no way to indent downwards in 2005 BUT 2008 has the 'SpaceBefore' padding property. The expanded column appears to the right as well as expands down with the spacebefore amount as well.
The left property of padding can contain a formula - eg:
=5 + (10 * Level()) & "pt" - this works well.
If you want to know if you are leaf level (probably to padd extra for lack of the plus sign) then use the distinctcount function in an iif:
=iif(CountDistinct(Fields!Name.Value, "matrix1_Name",Recursive) = 1, Level("matrix1_Name")* 5 + 15 & "pt", Level("matrix1_Name")* 5 & "pt"), 2pt, 2pt, 2ptTo make an expandable report just do as stated above, use uniquename, parentuniquename and set toggle visibility in group properties. Once you have configured the group properties and padded the textbox that should be it.
DESCENDANTS that VStudio uses by default for MDX params doesnt at first seem to restrict enough as all members are displayed. Its actually correct, you just have to set initial group visibility to hidden and set the 'display can be toggled by report item' to something. This is the equiv but better version of this outmoded hack with .children:
SELECT NON EMPTY { Measures.HeadCount } ON COLUMNS,
NON EMPTY {(Company.Practice - Group.Company.ALLMEMBERS * STRTOSET(@GeographyGeographyHierarchy + ".children") ) }
Do NOT use the above - it is just an illustration to show what the above mentioned visibility toggle settings will get you.
PASSING MEMBER OF PARENT CHILD DIM
The problem is not passing the uniquename to a child report but then ensuring only the children are displayed as by default all parent members are shown as well, which is crap. Trick is to use the + ".children" in the select staement and not in any of the subcube selects that the designer will create for you:
SELECT { Measures.HeadCount } ON COLUMNS,
{ (STRTOSET(@ManagementCartesisHierarchy + ".children")
* CountryRegions
...etc...
Note - children is the same as using DESCENDANTS with a parameter of 1 (level).
LIMITING DRILLDOWN PAST LEAF LEVEL
Base decision on the number of children a node has - instead of trying any kind if currentmember.children.children nonsense just create a calced member that looks below currentmember, to be returned with main reslt set.
with member Measures.NoOfChildEntities
as
Management.Cartesis Hierarchy.currentmember.children.count
Note that parameters should have available values set to 'None' to ensure the key is worked with instead of the (possibly non unique) name.
Also note that when using an action to call another report to pass the 'Parameter' object if passing a parameter through a report unchanged, instead of the 'Fields' object. =Parameters!ReportingMonthReportingMonth.Value(0). If accidentally use the fields object then you pass the first member in the dataset so instead of meaning to pass Africa you will pass Bakina Faso, which is not ideal.