.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

Running sum calculation based off date dimension?

Posted By:      Posted Date: September 16, 2010    Points: 0   Category :Sql Server
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

View Complete Post

More Related Resource Links

Filtering/selecting rows based on date


I have a gridview, based on thsi datasource:


<asp:SqlDataSource ID="SqlDataSourceAllLoads" runat="server" 
        ConnectionString="<%$ ConnectionStrings:tplatz_dk_dbConnectionString %>"               
        SelectCommand="SELECT DISTINCT DATEADD(day, 0, DATEDIFF(day, 0, tblDelivered.delivered_Date)) AS delivered_Date, tblDelivered.delivered_LoadNo, tblDelivered.delivered_TrailerNo, ISNULL(tblDeliveredInfo.deliveryInfo_FirmaNavn, N'?') AS Expr1 FROM tblDelivered FULL OUTER JOIN tblDeliveredInfo ON tblDelivered.delivered_LoadNo = tblDeliveredInfo.deliveryInfo_LoadNr ORDER BY delivered_Date DESC">

Using a dropdownlist, I can filter (by changing datasource) the results based on customer names in a dropdownlist. This datasource, working perfectly, looks like this:


<asp:SqlDataSource ID="sqldataSourceKundeLoads" runat="server" 
        ConnectionString="<%$ ConnectionStrings:tplatz_dk_dbConnectionString %>" 
        SelectCommand="SELECT  DATEADD(day, 0, DATEDIFF(day, 0, tblDelivered.delivered_Date)) AS delivered_Date, tblDelivered.delivered_LoadNo, tblDelivered.delivered_TrailerNo, ISNULL(tblDeliveredInfo.deliveryInfo_FirmaNavn, N'?&#

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?      

Alert based on date

I have a list of SSL certificates and their expiration dates.  I created a column that calculates the date 45 days before the expiration and then a view that only shows those items where the (expiration - 45) > Today. I then set an alert for any changes to items in that view.  However, I don't get an alert for items that pop into the view...I guess because the item itself did not change.  Is there a way to send an alert based on date?  My SharePoint enviroment is locked down so I can't create custom workflows or connect via Designer.

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

Search based on creation date

Hello All, I would like to ask which managed property I should use for searching based on creation date. It seems I can't find a managed property to do this. I created my own managed property with the right crawled properties, which works. But I think this should be something out of the box.  Please help, thank you  Anna

Export Data to Excel Based on Date Range

I have data from multiple tables that I would like to display in a gridview via a stored procedure.  I need to select the data based on a date range and display it.  Once displayed I need to include a button that would allow the user to export the gridview data to an excel spreadsheet.  Does anyone have an out of the box solution or know of a tutorial I can use to accomplish this feat?  I am currently using 2.0 for this project. I am newish to .net. Thanks in advance. Here is my stored procedure that calls the data from multiple tables.USE [DATABASENAME] GO /****** Object: StoredProcedure [dbo].[GetUsersAll] Script Date: 09/03/2010 08:20:23 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[GetUsersAll] AS SELECT aspnet_Users.UserName, aspnet_Membership.Email, Team.Name, MemberTeamRole.InsertDate, MemberTeamRole.ManagerApprovedDate, MemberTeamRole.RegistrarApprovedDate, MemberTeamRole.RoleName, MemberInfo.firstname, MemberInfo.lastname FROM aspnet_Membership INNER JOIN aspnet_Users ON aspnet_Membership.UserId = aspnet_Users.UserId INNER JOIN MemberInfo ON aspnet_Users.UserId = MemberInfo.memberid LEFT OUTER JOIN MemberTeamRole ON aspnet_Membership.UserId = MemberTeamRole

Dynamic list item title based on date created & regional settings?

I have a list of things that doesn't *need* a title.  However, because sharepoint uses Title for links, RSS etc  I tried generating a title in an event handler.   This would work great if I wasn't basing it on a date field: public override void ItemAdding(SPItemEventProperties properties) { DateTime dt = Convert.ToDateTime(properties.AfterProperties["TestDate"]); properties.AfterProperties["Title"] = dt.ToShortDateString(); base.ItemAdding(properties); } In testing, this sets the title to "8/9/2010" when I selected 8/10/2010 in the date picker.  I figure this is because of the regional settings of my test user.  I can probably correct the title value for 'test user' but that won't fix 'test user in mongolia' who would still see two different values. So What I would like is to have the Title always display the value MyDate in the uesr's locale.  So the field needs to be dynamic not only at edit/update time, but at display time. Does anybody know if this can be accomplished, and more specifically how?

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?

Shell Dimension - Not Time based

Hi   I am creating a shell dimension to create a hierarchy.  The idea is we want to know how much revenue we make from new accounts.  So I have created an account dimension and linked that to the Revenue fact.  I also created a Fact linked to the Date and User dims that will show the date the user was created.  By doing this, I was able to create a calculated measure that will show me total revenue, plus the revenue that is only for new players for a specific month: with member NewRev as aggregate( exists(DESCENDANTS([User Account].[User Account].[All], [User Account].[User Account].[User Account Key]) , [Date].[Date - Calendar Month].currentmember , "User Registration") , [Measures].[Revenue])   select {[Measures].[Revenue]  , NewRev} on columns , [Date].[Date - Calendar Month].[Calendar Month].&[201008] on rows from [Revenue] Sorry it is a bit messy, testing at the moment. What I would like to do is create a shell dimensions so I can create a hierarchy that will show new and returning players as members.  It would have an all level, or the user could split the revenue by this dim.  I set things up like the Time calculations shell dim, but am a bit stuck with how to create the mdx so that it will work with any measure. What would I change to do this? Regards Michael

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.


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,


return records based on current date?


I tried finding a QA which already addressed this idea, but with no luck. Sorry if it's been addressed before.


I have a table where sales pricing is managed; it contains history of pricing where every price record has an 'effective date'. Currently when a user needs the 'active' price for a customer/item combination, the table is queried by the record that has the most recent 'EffectiveDate' before GETDATE(). Here is the def for table 'tim_PriceSheet'

ItemKey int, not null

EffectiveDate datetime, notnull

CurrID varchar(3)

ListPrice decimal (15,5)

The primary key is set on ItemKey, EffectiveDate

Sample Data:


ItemKey	EffectiveDate	    CurrID	ListPrice
45	2010-07-01 00:00:00.000	CAD	100.00000
45	2010-09-14 00:00:00.000	CAD	110.00000
45	2010-10-01 00:00:00.000	CAD	112.00000
63	2010-09-01 00:00:00.000	CAD	45.00000
63	2010-09-01 00:00:00.000	USD	45.00000
63	2010-09-18 00:00:00.000	CAD	40.00000
63	2010-10-01 00:00:00.000	USD	40.00000
63	2010-10-15 00:00:00.000	CAD	41.00000
63	2010-10-21 00:00:00.000	USD	41.00000

My challenge is this:

I want to return a record set which shows the current price for each distinct item and currency code; in the example below the result would be based on the date being September 16, 2010

Please Help with Defining Calculated Measure based on Dimension Members



The issue looks pretty simple yet I got stuck. I want to define the measure [NR Var] as [NR]-[F NR] for years/months/dates before 2009 and [NR]-[FI NR] for 2009 and on. I am using [Year]-[Month]-[Date] hierarchies in the cube. I have defined scope:

Scope ([Measures].[NR Var], {
descendants([Date].[Year - Month - Date].[Year].&[2004], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2005], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2006], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2007], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2008], 2, self_before_after)});
this=[NR]-[F NR];
End Scope;

but it does not work for some reason. If I get rid of DESCENDANTS function, it works but applies the scope only to the YEAR level. Another problem with using SCOPE is that it affects [NR Var] only yet I have other calcs derivative of [NR Var] which I want the scope to affect as well.

So I ideally I would like to have something like:



case when {
descendants([Date].[Year - Month - Date].[Year].&[2004], 2, self_before_after),
descendants([Date].[Year - Month - Date].[Year].&[2005], 2, self_befor

Return Last 3 Months data based on the DATE


Using the getdate() function, how do I select the last WHOLE 3 months,

So for example, for todays date (29/09/2010)

I want to return all records with the date between 01/06/2010 and 31/08/2010

Is this possible?

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

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