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

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

Isolate only the Date part from MDX returned dimension

Posted By:      Posted Date: September 22, 2010    Points: 0   Category :Sql Server


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.





View Complete Post

More Related Resource Links

Time, not just date, is part of PK--should I create a time dimension?



Should I create a time dimension if the time is part of the primary key?  I'm trying to create a fact table of successful user connections.  Since each user can connect their device more than once per day, I don't have a complete primary key.  What's the correct way to handle this?

I just thought of something: should I be using storing an aggregate by date key instead, so that each line has a "ConnectionCount" column but not a "ConnectionDate" column?

Thanks for your help,




TABLE MyFact (

Deserialization issue with returned from web service

Hi, I have a VS 2008 C#  client using a proxy generated by the Service Reference tool from a schema for a (prob Java?) ASMX web service.  I got serialisation errors when I tried connecting to the service around<xs:date> date fields, and on inspecting the incoming XML discovered the date format was yyyy-mm-dd hh:mm:ss.sss.   So 2 questions really.. 1) Is the schema I've been supplied with incorrect? 2)Is there a way to work around this, apart from manually editing the schema to xs:datetime and regenerating the classes?   Thanks MJ

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

Error rendering "*************" web part: [The remote server returned an error: (400) Bad Request.]

Hi There I am implementing a WCF based solution and all was working fine until I started to process larger quantities of data. The Solution worked well in DEV where we added large quantities of daata when we restored the site to QA we got this error . Error rendering Councillors Online web part: [The remote server returned an error: (400) Bad Request.] Can anyone propose a solution to this problem? Thank you My WCF web.config looks as follows : ======================================================== <system.serviceModel>  <bindings>   <webHttpBinding>   </webHttpBinding>  </bindings>  <behaviors>   <serviceBehaviors>    <behavior name="serviceBehavior" >     <serviceMetadata httpGetEnabled="true" />    </behavior>   </serviceBehaviors>   <endpointBehaviors>    <behavior name="web">     <webHttp/>    </behavior>   </endpointBehaviors>  </behaviors>  <services>   <service name="CouncillorsOnline.Service.CouncilMasterDataService" behaviorConfiguration=

date part of a datetime

is there an easy way to get the date part of a datetime?   I want a datetime column to show the date in yyyy-mm-dd form. thanks,  

Date format in Search Core Result web part

Hi, I have two Site Columns PublishedDate and GlobalTitle. They have datetime and string type. These site columns are added to the Custom List. I need to show these columns in Search Core Result web part when item is searched. Because of this reason I created them as Site columns and Mapped with crawl property in SSP. After doing full crawl I can access both site columns in Search core Result. GlobalTitle is displayed properly but problem is with PublishedDate. It shows System.DateTime[] only. Can any one help me to show this in any of the date format but not like System.DateTime[]. Many Thanks in advance Chirantan

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

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.


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,


MDX Date Dimension Help


rewriting my original question...

when i don't include the [Dim Date].[Month Number Of Year]  on the columns, i get the correct value for Amount which is the amount between the date range in my where clause by days.

when i include it, it returns all of the amounts belonging to the [Month Number Of Year] which may be outside the date range.

how do i write this statement so that i can return the month, but only with the amounts entered within the number of days?

do i need to convert the [Dim Date].[Day] value?  if so, how?


-- incorrect results

non empty

on columns,

non empty
   [Dim Date].[Month Number Of Year].members

on rows
from myCube
         [Dim Date].[2007/01/01]:[Dim Date].[2007/01/15]

-- correct results, but displays the days, i want it aggregated by month.

   non empty

Unalbe to create date dimension with fiscal start date 29 Sept

I am trying to create date dimension with 29-Sept as starting date of fiscal year. But the dimension wizard always displays errors related date conversion. The following is the environment I tried.

OS : Windows XP

SQL Server R2

Please help.


Thanks in advance.


Hamlin Stephen

Multiple companies and fiscal calendars in date dimension table

We are creating an application where different companies can have their own fiscal calendar starting on different dates. For example one company’s fiscal year may start in April and some others in Sep. Also fiscal calendars may start at any date. (E.g. 29 Sept).

These fiscal calendars would be created for parent companies. We have added the parent company ids to the date dimension table. Please suggest the correct way of setting the attribute relationship in the above scenario. If this is not the right method to do this, please suggest the correct design approach for achieving this.


Thanks in advance,


Hamlin Stephen

SCD Type II with Date Dimension


We have emp_elig table as mentioned below

SurKey	GroupID		ID		ID_Seq		datebegin				dateend			elig_status	

19833	100000-01		100-01		000		2006-01-01 00:00:00		2010-12-30 00:00:00		1	

19835	100000-01		100-01		000		2010-12-31 00:00:00		2025-01-01 00:00:00		0	

19837	100000-01		100-01		001		2006-01-01 00:00:00		2006-12-31 00:00:00		1	

19838	100000-01		100-01		001		2007-01-01 00:00:00		2025-01-01 00:00:00		0	

19841	100000-01		100-01		002		2006-01-01 00:00:00		2009-02-16 00:00:00		1	

19843	100000-01		100-01		002		2009-02-17 00:00:00		2025-01-01 00:00:00		0

Note: elig_status 1 = Eligible

I do have SCD in SSAS which shows the hierarchy as mentioned above. Now I want to see something like given below:

                            Q1     Q2     Q3     Q4

Emp_Elig_Count       1        2       1       1


I don't know how can I link my DimTime (Date) between DateBegin and DateEnd of the above tabl

create an index on just the date part of a datetime field


Good Morning

is it possible to create an index on a datetime field that just looks at the date part,

as I am constantly querying the table, using a where clause like this


or is there an more efficient method of achieving the same result?







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


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




Date Dimension


Hello guys,


I have created a date dimension in sql server and it is now part of my cube dimension.


The clarification I need is that, is it still necessary for me to add a dimension intelligence of type Time to this dimension before it is fully ready for use in the cube?


Thank you

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