.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

MDX - Find last leaf member of Date dimension under selected member of Date dimension, independently

Posted By:      Posted Date: October 15, 2010    Points: 0   Category :Sql Server

Hi all

I need to reproduce the behaviour of the averageofchildren function with the exception that the function should always build the average of all members (even when the value of a member equals null). As you know average of children function always build the average at the lowest level of the time dimension.

My date dimension looks like the following:

[Date].[Fiscal] user defined hierarchy:




if context of [Date].[Fiscal].CurrentMember corresponds to a Week, I can retrieve all weeks before the current week using

In Period: MTD([Date].[Fiscal].CurrentMember)  -- PeriodsToDate also works fine

In Year: YTD([Date].[Fiscal].CurrentMember)

if context of [Date].[Fiscal].CurrentMember corresponds to a Period

How can I get all weeks (NOT Periods!) contained in all Periods before and within current period since start of year?

What would be a generic expression that retrieves all weeks before and in independently of level of selected Date dimension member




View Complete Post

More Related Resource Links

Default date dimension member


Hi All,

Is there a way to set a default member in the date dimension so that it will be automatically observed by Excel as a filter?

I have a stock cube which shows stock levels by day. At the moment, users have to change the day filter manually in the Pivot. I'd like it so that it refreshes automatically based on a cube setting so that it is centralised.

It appears I can set a default member for the date attribute, but without selecting date as a specific filter it shows the last non empty value (as defined by the cube structure). I need it to show the stock as per the specific date.

I've accomplished this before using vba in the Excel file, but wondered if there was a better method. Unfortunately named sets don't work in Excel as filters and I don't want to see the date on the rows/columns.

Many thanks,


New Dimension member not selected by default in the Pivot Report


Hi Guys,

We have excel report where the some members of a dimension is selected and the data is browsed based on that. The report works good if there are no new members added in the dimension.

When the dimension is processed and the cube is processed, if there comes a new dimension member then that member get selected by default and the report fails. I am looking for way to remove the default selection for the new members added to the dimension. Please let me know if there is a possible way to suppress the default selection of the new members in a dimension.



MDX Query - Get (Parent-Child) Dimension member regarding another Parent-Child Dimension, then, get

Hi there, I got the following MDX issue. My cube structure looks like that : DIMENSION CATEGORY (Parent-Child) --> (Many to many) FCTLESS_CategoryNode <-- DIMENSION NODE (Parent-Child) ___ DIMENSION NODE (Parent-Child) --> (Many to many) FCTLESS_NodeVariable <-- DIMENSION VARIABLE ___ DIMENSION VARIABLE <-- FACT (Supposed with only one value called VALUE) I'd like aggregate for N nodes linked to a specific category. For example, with the next content : DIMENSION CATEGORY - COUNTRY - SITE - BUILDING DIMENSION NODE US (Category Country)     - Site US_A (Category Site)     - Building US_A_A  (Category Building) FR (Category Country)     - Site FR_A (Category Site)     - Building FR_A_A  (Category Building)                                       - Site FR_A (Category Site)     - Building FR_A_B  (Category Building)                                       - Site FR_B (Category Site) 

How to create a calcuated member, which will always return the date one month before the currentmemb

I need to create a calculated member in the cube script, which will be subsequently used in various other script calculations.  It needs to return the date, which is 1 month before the currentmember of the Date dimension. I tried something like this: CREATE MEMBER CURRENTCUBE.[Measures].[LastPD]   AS           PARALLELPERIOD([Date].[Calendar].[Month], 1, [Date].[Calendar].currentMember) ,VISIBLE = 1; However when I then try to check the values with this query: SELECT [Measures].[LastPD] on 0, [Date].[Calendar].[Date].members on 1 FROM [TravCSAT] ; I only get NULLs for [LastPD].  Any idea what I am doing wrong?

Getting counts by 2nd Date Dimension Attribute with Snapshot Style Fact Table

  I have an MDX question finding hard to solve.  I have a Snapshot Fact Table with a snapshot of the records in the source system for each batch date.  All records in the fact table are assigned the batch date with the batch date key.  There are many records for each day and each batch date is an entire copy of the source records.  So, the grain of the fact table is one record for each batch date that exists in the source system.  These facts rows have another date in them for when the record was entered.  This date is different from the batch date in that the batch date is based on the day the batch was processed and the entered date is based on when the record was entered.  If a record was entered many days before, its batch date will be today but its entered date will be several days ago.  Therefore each day a copy of all the records entered the previous batch date and all the records added on today's batch date are present. Fact Table : FactSnaphshotKey (surrogate for easier administration) BatchDateKey (link to batch date dimension – date dimension, first in dimension list so it is used for semi aggregate measures) EnteredDateKey (link to entered date dimension – date dimension) Facts Count – measure for fact table - default measure from Analysis Services cube 2 Dim

can we change the properties of any dimension member as from hidden to unhidden without deploying a

hello , well i just want to known that , can we change the properties of any dimension member/measure as from hidden to unhidden without deploying a cube.? well i have a deployed and process cube and i just want to change some measure properties to visible from hidden without reprocessing the cube so can we do like this..?.. plz help me out ...

Calc'd Member Specifically for JAN, FEB etc of Chosen Tran Date

Hi.  I'd like to display same year calc'd measures [January Completions], [February Completions] ...[December Completions] next to the [Actual Completions] measure for transaction year x, transaction month y. If y is January then [January Completions] thru [December Completions] need to be overridden with zeros.  If y is February, Feb thru Dec need to be overridden with zeros and so on. I started experimenting with the query you see below before transferring the logic to my cube.  I didnt even get to the if/then logic before I saw that April and beyond of each year's [January Completions] was null.  But the correct/same Jan amount showed next to the 1st 3 [Actual Completions] for 2009 and 2010 which are the only years I've loaded at this point. [Actual Completions] itself is a calc'd measure from two different measure groups that share most dims including transaction date.  I dragged the hierarchical month name to the "on rows" clause.  I had no luck mixing hierarchical names with the currentmember function in the "with" clause so I dragged the non hierarchical tran date names there.  What am I doing wrong? with member [Overall Jan Completions This Year] as ([Measures].[Actual Completions], [Date Transaction].[Year].   Currentmember,   select {([Measures].[Actual Com

Top N by dimension member

Hi All, Adventure Works as an example, I would like to select 100 customers based on last Ship Date. There are three (August 5 2004, August 6 2004, and August 7 2005) members in Ship Date dimension which include more the 100 customers and I would like to select these 100 customers irrespective of any measure. I could not find a MDX to achieve this, please help. Thanks

MDX Date Range calculated member

Hi, I have [Sales Amount] and [Date]. How can I create a calculated member that will aggregate only the values from [Sales Amount] where month([Date]) is 'Jan' or 'Feb' and year is CurrentYear ? Then 'Mar' & 'Apr' and so on.... Thank you. D.

Running sum calculation based off date dimension?

I have a fairly simple calculated measure which does a running sum of a particular measure : --This does a running sum of the Net Measure up until the previous date (basically a "Starting Count" for a period. Sum(PeriodsToDate([Change Date].[Year - Month - Date].Levels(0), [Change Date].[Year - Month - Date].PrevMember), [Measures].[Net]) The problem is I have added a second hierarchy in the date dimension that includes quarter called [Year - Quarter - Month - Date]. Is there anyway around not having to create a seperate running sum calculated measure to use this new hierarchy? The PrevMember in the initial calculated measure works, although it reports the wrong number when used in conjunction with the Quarter hierarchy because it gets the PrevMember in the other hierarchy. What is the best practice for this situation?Craig

Dimension member based on period

Hi, I have done several SSAS cubes in the past, but this is the first time I have run into this problem. I have tried searching for an answer online, but I am unsure of how to pose my question/search appropriately. After many hours of searching (someone MUST have run into this before), I thought I'd try the forum. I will be using SQL 2005 SSAS. The issue concerns a dimension that is linked to my fact table via a middle table that creates a many-to-many relationship. This middle table contains a date that the cube would need to be filtered on (which would be a further dimension, I assume). Note that I plan on allowing reporting on the cube via MS Excel (pivot tables). My table structure (highly simplified for testing purposes, it contains upwards of 15 other dimensions) is laid out as follows: --dimension = RecordableInjury CREATE TABLE [dbo].[safe_RecordableInjury](  [RecordableInjuryID] [int] NOT NULL,  [RecordableInjury] [varchar](100) NOT NULL,  [RecordableInjuryShort] [varchar](5) NOT NULL ) --fact = Injury (the measure on this table will be a distinct count of InjuryID) CREATE TABLE [dbo].[test_Injury](  [InjuryID] [int] NOT NULL,  [IncidentDate] [int] NOT NULL ) --joiner between fact table & RecordableInjury dimension CREATE TABLE [dbo].[test_InjuryRecordability](  [InjuryID] [int] NOT NULL,  [StatusDate] [int] NOT NULL,

How to limit a Many to Many Dimension by a secondary Date dimension


Hello MDX gurus,


I have tried posting my problem before, however, I still haven’t gotten a response. Therefore I am going to try again... I am new to MDX, I have had a bit of experience, but nothing more complicating than creating simple calculated measures.


I have a data source which contains Injuries and their Status. An Injury can have it’s Status change depending on whether a doctor visit is involved. For example, an Injury can start out as a First Aid (FA) then change to a Medical Aid (MA) if the person seeks medical attention later on. It can then change to a Lost Time (LT) incident if told to take time off from their doctor. Then, if WCB declines their claim, the Injury can change back to a Medical Aid (MA). The LT/FA/MA status is called the Recordability by our business users.


Therefore, one Injury can have multiple Recordability records, and the ERD looks like this:


Injury -> InjuryStatus <- RecordableInjury


When a report is run, the Status (or RecordableInjury) that appears needs to be the latest one in the selected reporting period (based on StatusDate) for each Injury. Therefore, each Injury may be counted only once within a time period (i.e. Year, Quarter, Month, Week), with the most recent Status appearing for that time period.


Percent of Total for Each Member in a Dimension Across Another Dimension


I've got a particularly nasty problem that I've been pounding my head against that I hope someone has encountered and can help me with.  I have a hospital cube that has a Hospital dimension and a Service Line Dimension (among others).  Service Lines are such things like General Medical, Surgical, Cardiology (there are 16 total).  Each member hospital in the Hospital dimension will have Discharges associated with each Service Line.  I'm trying to construct a percent of total calculation for each Hospital and Service Line, so I can look at the percent of total of each Service Line across Hospitals.  I'm trying to create a report in SSRS based off this cube.  Here's what the dimensions look like:

Hospital Name1    General Medical
Hospital Name2    General Medical
Hospital Name3    General Medical

What I want to do is calculate the percent of total of each hospital's Service Lines, using the total for each Service Line across hospitals.  This will show the contribution of a Service Line at a specific Hospital to the overall total for that Service Line for all Hospitals.  Here's the MDX I've been working with that does not work the way I want:

Isolate only the Date part from MDX returned dimension



I have an MDX query which returns a date dimension values as below

02 Aug 2010
03 Aug 2010
04 Aug 2010

Is there a MDX function i can apply only to isolate only the date part of the values like 02 Aug, 03 Aug.

I can do this quite eaily in SQL using Datepart..but i wasn't able to find a similar function for mdx.

Thank you for your assistance.





SSAS - performance penalty standard dimension without custom member formulas VS standard dimension w


Hi all

I was wondering if there is a big performance penalty if one introduces custom member formulas to a standard dimension.


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,


SSAS - Use simultaneously unary operator and custom member formula in a PC (Parent-Child) dimension


Hi all

I was wondering (even this might a very bad idea from a logical and performance perspective) if it is possible to use unary operator and custom member formula simultaneously? Until now I have used unary operator but now some new calculation can't be expressed using unary operator. The idea is just to add the new members calculation to custom member formulas. Is it possible to make cohabitate both. If I had to switch all unary operators to customer member formulas what would be the equivalent expression for +, - , ~?

thanks for your answer


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