.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

Dimension Date filter SCD

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

I have 2 date fields - AuditStartDate & AuditEndDate in Dimension DimCustomer.

 This has foreign key relationship to DateKey in the DimDate dimension table

In DimDate Dimension table you have the dimension key. Datekey is derived based on date (YYYYMMDD) as integer.

DateKey Date

20101019 19/10/2010

20101020 20/10/2010

I want to filter out records in where AuditStartDate <= Current Date(Converted to (YYYYMMDD) integer) and AuditEndDate >= Current Date(Converted to (YYYYMMDD) integer)

How do I do this in MDX? Please help

View Complete Post

More Related Resource Links

Filter items with a date column with parameters start date and end date on a sharepoint list?

If I have alist with a view that has the columns Title, Status and Status Date and the view has some items. How Can I add a filter with two parameters, start date and end date, to only show records with status date equal to or greater than parameter start date and status date is less than or equal to parameter end date? This is a sharepoint online site and I can't create and deploy custom code, can use SPD though. Do I need to use SPD or is this something I can do in the list settings? Thanks in advance.   Edit: I had a look here http://www.endusersharepoint.com/2009/09/29/sharepoint-date-filter-filtering-a-list-by-greater-than-or-equal-to-date/ but I don't have the Date Filter web part.

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

How to filter comparing two date attributes?

I am trying to create a report which will display Customer, Customer Request Date, Scheduled Arrival Date and Invoiceable Sell. I want to select this information where the Customer Request Date <= Scheduled Arrival Date. I have been trying various filter combinations, but I cannot get the correct syntax.   Here is my MDX code: SELECT NON EMPTY { [Measures].[Order Lines Invoiceable Sell]} ON COLUMNS, NON EMPTY {[Customers].[Customer].ALLMEMBERS *[Customer Request Date].[Short Date Alpha].ALLMEMBERS * [Scheduled Arrival Date].[Short Date Alpha].ALLMEMBERS} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Orders Quotes Projects] CELL PROPERTIES VALUE, BACK_COLOR,FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS    

MDX query to filter on specific dimension attributes

Ok, trying to figure out how to filter on specific dimension attributes. Are there any suggestions? I have a measure that needs to be filtered by a dimension that has numerous attributes (20 attributes in total) but I only want to show that measure against 10 of the attributes and once I get that result I need filter that result set against my time dimension (which this part I have figure out)   This is what I have so far: With member [calculated_measure_name] as ( [measures].[count_of_people], { [dimelgiblestatus].[elgible].[elgible1], [dimelgiblestatus].[elgible].[elgible2], [dimelgiblestatus].[elgible].[elgible3], [dimelgiblestatus].[elgible].[elgible4], [dimelgiblestatus].[elgible].[elgible5], [dimelgiblestatus].[elgible].[elgible6], }   )   Select [calculated_measure_name] on columns, [date].[fsicalyear].&[2008], [date].[fsicalyear].&[2009], [date].[fsicalyear].&[2010], [date].[fsicalyear].&[2011] on rows from mycube   When I run it, it gets no errors but in the result set for the numbers I get "#error"; do I need to basically create a subcube in order to get the result like I would like? If so how would I start doing that?   ThanksNetwork Analyst

filter gridview from a dropdownlist with date

having trouble fing any info on what i need to be doing i have tried '{1}'  ,  '#{1}#' , '{1:MM/dd/yyyy}' and a whole bunch of combinations thereof Teh problem is when i get it to actual not have a complie error then the functionality is not correct as i am unable to "select" all from my dropdown list . The only way i have gotten anything close to working is by removinf the % from both the filter and the default value.   here is relevant code   <asp:DropDownList ID="ddlShipDate" AutoPostBack="true" runat="server" Height="16px" Width="75px" DataSourceID="adsPopulateShipDate" DataTextField="dShipDate" DataTextFormatString="{0:MM/dd/yyyy}" DataValueField="dShipDate"> <asp:ListItem Text="All" Value="%"></asp:ListItem> </asp:DropDownList> Pleas enote i removed the append databound items=true<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/JAGENT.mdb" SelectCommand="SELECT [cPronumber], [cCustomerRef], [dShipDate], [cOrigCity], [cOrigState], [dDeliveryDate], [cDestCity], [cDestState] FROM [BillingHistory] WHERE ([cCust

How do you filter a list in a Meeting workspace based on the meeting date?

I have a customer who is using a meeting workspace, and has a list on said workspace (we'll call it "Meeting Instance List"). The workspace houses this list, and of course after each meeting, the workspace, in essence, resets itself in preparation for the next meeting - so it looks like it removes the items from the list, when in actuality, they are there, just on the previous meeting instances. Some of his stakeholders are finding this limiting because they perceive it as losing historical information (you know how some folks can be, if they don't see it, they think it's gone). His solution is to replace the non-series list, with a series list, and incorporate legacy records from another older list. Only problem is, now there's not a way to filter the series list so that it shows items from a parcticular meeting date. For instance - the [Today] variable will only return a current system date value. Surely there ought to be a similar variable that would all you to filter the list based on meeting date, right?

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.


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.





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,


Changing Date format in Filter toolbar


i have added a toolbar to a Data View Web Part and enabled the Filter option. When i click on the filter, the Due Date field displays choices as Saturday, September 11, 2010. Within the DWVP I have the format set to mm/dd/yyyy, the differening formats seem to prevent the filter from working. How I can change the format in the filter dropdown ?

Thanks in advance

Dean MCTS-SQL 2005 Business Intelligence

FILTER within a Date Set


Basic question.....

This works fine

   [Measures].[Order Amount] on COLUMNS,
    [Customer].[CustId].CurrentMember.Properties("First Order")=[Date].[Date].Properties('Name')
    )  ON Rows
FROM [CustomerOrder]
WHERE {[Date].[Date].&[2007-03-20T00:00:00]}

Works fine

However when using a set...it fails


   [Measures].[Order Amount] on COLUMNS,
    [Customer].[CustId].CurrentMember.Properties("First Order")=[Date].[Date].Properties('Name')
    )  ON Rows
FROM [CustomerOrder]
WHERE {[Date].[Date].&[2007-03-20T00:00:00]:[Date].[Date].&[2007-03-25T00:00:00]}

Error : Query (5, 88) The MDX function CURRENTMEMBER failed because the coordinate for the 'Date' attribute contains a set.


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

Dynamic dimension member filter


Is it possible to set a dimension data rule for a role that will filter dimension members dynamically such that only members relating to at least one fact record will be shown?   In particular, the rule should be applied after all other filters on the fact data for the user have been taken into account.

This situation is there is a single client dimension but users should not be able to see the client information (e.g. name, address) unless it is related to a fact that the user has access to.




Calcuate duration between selected date(Filter)



I'm trying to calcucate duration between 2 date sets using paramters.

1. I do have a stored procedure with 2 filter sets. End date and duration.

2. Created SSRS report with 2 filter sets.  End Date and Duration. It worked fine.

3. Customer wants to use Start date and End Date as filter. (I cannot modify stored procedure)

4. I created a dummy parameter for Start Date.

5. Duration default expression as


6. This works as I expected but when they change any date or other parameters, duration calculation does NOT update.


Why it is not updated?



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