.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
david stephan
Gaurav Pal
Post New Web Links

Not able to drill down on a dimension with hierarchy

Posted By:      Posted Date: April 10, 2011    Points: 0   Category :C#


I created a dashboard with some dimensional hierarchy. But  on sharepoint 2010, it does not drill down automatically. I always have to right click, drill down to choose the dimension I need to drill down. But I already have the hierarchy set up in the MS SQL analysis server 2008. When I do the same on proclarity desktop professional, it seems to work perfectly fine. I can just click on the graph and it drill to the next dimension in the hierarchy and so on. But on the sharepoint 2010 dashboard, it does not seem to work.  

I am using the report of type "Anaytics Chart" report type in the Sharepoint Designer. 

Am I missing some step here? Thanks for your help and support. 





View Complete Post

More Related Resource Links

display non measure, non dimension fields in drill down

Hi All, I created my fact table with more fields than just the foreign keys linking dimensions and the field(s) to be used as mesures.  I did this hoping that on drill down I would be able to see the extra fields so that the user would have access to detail information on the records making up the measure amount.  The extra fields do not appear on drill down.  How can I make them appear, or am I on the wrong track? Thanks for any help

SSAS - How to get other members from dimension that has Parent Child hierarchy?

I have a Sales Territory dimension that has employee and parent employee attribute on which parent child hierarchy is defined and it gives below hierarchy while browsing - - Mark Rolls --- Lumin Jacs ----- Larry Gomes ------- Messica Owens ------- Tom Ted ----------- Jackson Lopez ----- Matthew Ron --- Fred jacob - Jason Ron --- Jecy Pedro   But beside this parent-child hierarchy I have other attributes like employee address, email and telephone. My facts related sales transaction is tagged to lowest level. For example here facts are available only for Jackson Lopez (being Field Executive). When use below query I get complete sales reporting hierarchy result with some measures like sales amount, sales volume. But while accesing other attributes like address or email, it's repeating address/email/telephone of jackson Lopes everywhere to whom fact record is linked, Actually I want address/email/telephone of each sales employee from that dimension within hierarchy. How do I get it? The query I used is: Here Parent Terr ID is parent child hierachy. SELECT ( Descendants( { [Dim SalesRegion].[Parent Terr ID].[Employee Level 01].&[538018] /* here Mark Rolls is 538018 */ }, 0,AFTER), NONEMPTY([Dim SalesRegion].[Emp Address].[Emp Address].Members), NONEMPTY([Dim SalesRegion].[Emp Email].[Emp Email].Members) ) ON ROWS , { ([Dim Date].[The Year].[The Year].[CY-2010], [Dim

The 'Cost Code' dimension contains more than one hierarchy, therefore the hierarchy must be explic

I have a problem with my MDX query as i am newbie. Please help me to solve the problem, even the slightest hint would be very appreciated. I omitted some part of the query to make it more understandable. with  member [Cost Code] as case when [Equipment].[Equipment Type].&[Loader] then [Measures].[Cost Code].&[30321] else [Equipment].[Equipment Type] end select [Date].[Calendar].[All] on columns,  non empty   [Equipment].[Equipment Group].&[BGC]   *  [Equipment].[Equipment Type].children  *  [Cost Code]  *  [Equipment].[Equipment].children  *  {[Measures].[Dam Working Hours],   [Measures].[Gatsuurt Working Hours],   [Measures].[HL Working Hours],   [Measures].[Pit Working Hours],   [Measures].[Plant Working Hours],   [Measures].[Rehabilitation Working Hours],   [Measures].[Total Working Hours] }  on rows from  [Cube]

Replace pre-calculated Measures at "All" Level depending upon the Dimension Hierarchy Level

Have an interesting Challenge; thought will reach out to you if you can help Currently I am working on SSAS 2008 OLAP Cube, whereby most aggregate level measures are pre-calculated using the C# Code as part of ETL and are stored in Data Warehouse as well as Measure Groups in Cube (MOLAP). These are mostly non-additive measures have to choose this approach for performance reasons, considering the complexity of the aggregations and data volumes.  Data  Volumes are Huge (about 300+ Million Rows/ per day),  98 % of measures are non additive / semi additive.  Cube is used primarily for advance analytics and will be eventually used for data mining like time series , what if analysis and scenario analysis  Excel is used as front end . Question is how can we replace the aggregate level data for various dimensions attributes (Totals and Grand Totals) from pre-calculated measures, those are also available in the Cube as measure groups? We are currently using Scope and Root statement which is not working as expected SCOPE ([D1].[H1].[A1], M1) Root (D1) = <Get Pre-calculated value for M1 from related Measure Group for D1].[H1].[A1],  > EndScope; SCOPE ([D1].[H1].[A2], M1) Root (D1) = <Get Pre-calculated value for M1 from related Measure Group for D1].[H1].[A2] > EndScope;   Thanks in Advance   Best Regards,   Dave

MDX Calculations error - The 'Date Calculations' dimension contains more than one hierarchy, there


Hi all,

I am using http://www.obs3.com/pdf/A%20Different%20Approach%20to%20Time%20Calculations%20in%20SSAS.pdf to make a seperate dimension for the data calculations, which works in my Sales cube. But when I use the same dimension in an other cube (Called 'Procurement') and copy the MDX in the calculations tab, I am getting some errors:

Error    57    MdxScript(Procurement) (9, 8) The 'Date Calculations' dimension contains more than one hierarchy, therefore the hierarchy must be explicitly specified.        0    0   

But the 'Date Calculations' dimension does not have a hierarchy!

Now I don't know why I am getting these errors, could you guys help me out so I can understand them and fix the problem?

Thanks you in advance,


Key Columns property when creating dimension hierarchy in Analysis Services 2008


HI All

I have 3 dimensions in our Data Warehouse, dimCampaign, dimCampaignList and dimCampaignTarget
Think of dimCampaign as a marketing campaign, that contains one or more Campaign Lists (offers) for which many customers (dimCampaignTargets) receive.  The are foreign key relationships between these 3 tables, and it is the dimCampaignTarget ids that are written to associated fact tables

I have created a single dimension in Analysis Services 2008 that contains these 3 tables and their respective relationships were set up in the DSV so that I could do this.  Rather than use the 'referenced' dimension method, I wanted a 'single' dimension containing the 3 tables and their snowflake relationships in order that I can create a hierarchy that contains attributes from differing tables (namely the Campaign Description from dimCampaign, and Campaign List Description from dimCampaignList). 

My hierarchy has those 2 levels, top level is Campaign (description), and the 2nd level is Campaign List (description).  The Campaign Targets are not shown in the hierarchy, their attribute is only used in the dimension as the join to most of the fact tables.

Problem is when I set up my hierarchy, I go to change the Key Columns property of the Campaign List attribute to make it a collection of keys (Campaign and Campaign List) as I did in

Restrict SSAS dimension hierarchy to show based on role


I am having an issue that involves SSAS and Sharepoint.  I don't think I can fix the issue in Sharepoint, I think it has to be in my ssas cube.  THe issue is that in sharepoint I have a ssas filter webpart that displays the geography hierarchy based on the role that is defined in SSAS.  So if I have a user that only has permissions to Switzerland than they will see the geography hierarchy as (Region, Sub Region, Area, Country)

All Sales Region


         Eastern Europe



What I want to know is in SSAS can I restrict the hierarchy to only show country if the user belongs to a certain role.  So what I want to basically say is if the user belongs to SSAS_CH then the hierarchy should just show Switzerland, not All Sales Region > Europe, etc....

Can this be done?



Percentage calculation across a dimension hierarchy



I guess this is pretty simple for most of you but I am new to MDX and not able to implement this. I am trying to calculate percentage across the members and levels of a dimension hierarchy,[Assignee Organization Hy]. I have created a calculated member and am trying to calculate the percentage of Service Requests resolved within 72 business hrs across the members and levels of a dimension hierarchy, [Assignee Organization Hy]. I have a measure which gives the Resolution Business Hrs for each Service Request. The MDX expression I have tried is provided below :


         [Assignee Organization].[Assignee Organization Hy].currentmember.children,

         Iif([Measures].[Accumlated Resolution Business Hours]<=72,1,NULL)




        {[Measures].[Accumlated Resolution Business Hours]},

        {[Assignee Organization].[Assignee Organization Hy].currentmember.children}



where [Assignee Organization] is the dimension and [Assignee Organization Hy] is the hierarchy.

Hierarchy ID: Model Your Data Hierarchies With SQL Server 2008


Here we explain how the new hierarchyID data type in SQL Server 2008 helps solve some of the problems in modeling and querying hierarchical information.

Kent Tegels

MSDN Magazine September 2008

Test Run: The Analytic Hierarchy Process


Most software testing takes place at a relatively low level. Testing an application's individual methods for functional correctness is one example. However, some important testing must take place at a very high level-for example, determining if a current build is significantly better overall than a previous build.

James McCaffrey

MSDN Magazine June 2005

JIT and Run: Drill Into .NET Framework Internals to See How the CLR Creates Runtime Objects


There's lots to explore in the .NET Framework 2.0, and plenty of digging to be done. If you want to get your hands dirty and learn some of the internals that will carry you through the next few years, you've come to the right place. This article explores CLR internals, including object instance layout, method table layout, method dispatching, interface-based dispatching, and various data structures.

Hanu Kommalapati and Tom Christian

MSDN Magazine May 2005

Organization Hierarchy - Manger not showing


SharePoint 2007 SP2 on Server 2008 R2

I am doing a custom profile import using AD and a BDC connection.  All the data comes in and populates correctly, including the 'Manager' field which is being populated from the BDC entity. All main fields are being populated from the BDC entity.  Fields are; First name, last name, email, office and cell phone, and manager.

However, when viewing the Organization Hierarchy, the users 'Manager' is not showing up in the Hierarchy. Nothing above the current user is shown in the Hierarchy.  The Manager field is populated with a valid user.  If I display the Manager field in the 'Details' section, I can see the name and click on it to see their profile page as well, so it is all valid and accurate.

Colleagues ( users with the same Manager ) are listed in the Hierarchy, but none of their hierarchy's show the manager either.

I have deleted every profile and then done a full import several times with the same result. Incremental import does ont have any affect either.

Any suggestions or advice on how to get the Manager to display?

Thanks to all!


Video: SharePoint 2010 Object Model Hierarchy

This video describes the hierarchy of the most commonly-used objects in SharePoint 2010. (Length: 2:18)

Time Dimension Enhancement with Business intelligence Issue

Hi all, I want to add a year over year growth using the BI wizard (Time diemsion enhancement) but when I try to add this enhancement via the wizard then this last one has the button next disabled with a waning that says   A time dimension is required to enable this functionality. Ensure that you have a dimension of type Time, that contains at least one hierarchy with a level flagged as a time period. Inspite of the fact that I added that time dimsension with one hierarchy Time hierarchy Calendar Year Calendar Semester Calendar Quarter Time Key(With namecolumn defined as a named calculation that repsents the day with this format  yyyy, dd mm ) Me personaly I have a doubt about the last condition of the warning (with a level flagged as a time period) but I dont know exactly 1. If my doubt is right 2. What shoud I do to enhance the cube in this context using the time dimension enhancement The complexity resides in the simplicity

MDX to get a count on a hierarchy

Hi I want to get the number of customers that placed orders for items from more than 2 Product Category in a month in the Adventure Works 2008 DW database. Can someone help me with the MDX expression? thanks

Dynamic Dimension with Aggregate Values

Hi, I have an specific requirement to make the measure value as an dimension. Let me explain my problem in brief. I have a fact table with dimensions like Time, Products etc and having single fact table with two measures. I have to create a calculated measure which shows the average of Measure 1 (here used calculated measure because there are couple of other calculations involved). And other two calculated measures. when I drill down with Products dimension for Calculate measure 1, it shows the average value for each products. Now, I want this calculated measure values (includes Product dimesnion drill down) as a Dimension and based on this value, I need to show the value of other two measures. For example: when the dimension products is used for drill down the values displayed will be like this and in this I need CM1 to be another dimension Products CM1 CM2 CM3 P1 0.10% 20 1 P2 0.20% 40 2 P3 0.30% 80 3 P4 0.40% 70 4 P5 0.50% 30 5 P6 0.60% 110 6 P7 0.70% 120 7 P8 0.80% 130 8 P9 0.90% 86 9 P10 1.00% 65 10 when CM1 is used as a dimension it should show the value like this CM1 CM2 CM3 0.10% 20 1 0.20% 40 2 0.30% 80 3 0.40% 70 4 0.50% 30 5 0.60% 110 6 0.70% 120 7 0.80% 130 8 0.90% 86 9 1.00% 65 10 How can we create the dynamic dimension with the aggregated values? Any assistance will be greatly apprec

Create a dimension based on 2 fields

Hello I have a table as follow: No   Placestart    Placeend 1      DK                USA 2      UK                USA 3      USA              DK Now, I want a dimension called Country, which selects either of the rows where a value exist In SQL it would be ex (SELECT * FROM table WHERE placestart = 'USA' OR placeend = 'USA) So, when I select USA in the dimension all 3 rows are listed, as USA is included either in placestart or placeend. If I select DK row 1 and 3 is selected etc... Is this somehow possible?      
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend