.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

Named Sets vs. Calculated Members and the Filter Field Conundrum (Year-to-Date measures by company c

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

I’m going to describe our SSAS solution a bit and what I have in it so far in order to ask the question regarding “year-to-date” sales. This is part example and part question, I suppose. "The Filter Field Conundrum" sounds like it should be a high-tech Encyclopedia Brown case or something. Can't seem to find a similar situation through searching, although perhaps I'm wrong and this has been done before.

We have a data warehouse that contains our transactional detail for the last nine or so years. Tables are somewhat analogous to the AdventureWorks solution, with a table for Sales, a table for Customers, Geographic information, etc. The data warehouse has a time table that contains many of the usual bits of information one would expect:

Full Date (mm/dd/yyyy)
Month Number
Month Name
Day of Month
Day of Week
Day of Week Name
Day of Year
Week of Year
and maybe a few other typical fields.

Then we also have:
Working Day (bit, with a 1 indicating that this was a scheduled working day according to our company calendar, and a 0 indicating that it was not)
Working Day of Month
Working Day of Year
(these fields indicate that a day is the nth scheduled working day, according to our company calendar, for a given month or year)

So, for example, 5/1

View Complete Post

More Related Resource Links

Calculated date field displays differently in List View and Data View web parts

In SharePoint GUI I created a simple custom list with a calculated Date/Time field (Due).  The calculation is =[Created]+7.  I'm trying to display this data on a page using a DVWP.  But first, for testing purposes, I also created a second calculated Date/Time (TestCreate) field in my custom list that is simply =[Created]. In the List View from within SharePoint, the dates show as expected (for example): Created = 8/12/2010 3:45 PM Due = 8/19/2010 3:45 PM TestCreate = 8/12/2010 3:45 PM In SharePoint Designer, I added a Data View web part and the above three data elements (each configured to display mm/dd/yyyy hh:mm:ss tt).  I see the time displayed for the example list item as: Created = 8/12/2010 3:45:30 PM Due = 8/19/2010 10:45:00 AM TestCreate = 8/12/2010 10:45:00 AM I've tested a couple scenarios and the calculated fields always display five hours in the past (along with dropping the seconds).  It seems like the DVWP is returning the date in a different time zone (with no seconds).  The lcid is set right (1033).  I didn't see any time zone settings for the DVWP specifically.  I'm basically stuck.  Could someone help me figure this one out. Thanks.

Why can I not group by calculated field in Date format?

After migrating to SP2010 Foundation, I've discovered that for some reason I can't group a list by a calculated date field. Ex: Create a list with a date column A. Create another column B with a simple calculated date of [A]+1 and use the field format type 'date' Modify view to group the list by column B.  You're list will load, but when you attempt to expand one of the groups you'll be greated with an exception error. Why?? Am I doing something wrong? Note: You can group fine if the data type for column B is anything except 'date' format, but I really need to group items by a calulated date. Ultimately, what I'm trying to do is group my list items by week by calculating the end of the week using a simple formula =[Must ship by]+7-WEEKDAY([Must ship by])  then I want to gropu by the [Must Ship by] column like I always did in SP 2007, but now it errors out.  I also wanted to create my own timesheet template since the Fab 40 templates aren't available for 2010, but I can't really do that effectively without being able to group timesheet entries by week.  

Discrepancy in Calculated Field using Date/Time



I have a calendar item as part of my MOSS 2007 site and would like to show the events coming up in the next 30 days on my home page.  After some research I understand that I cannot filter on the Start Time field when I try to create a view, so I've created a new field called Event Begin that is a calculated field set to =[Start Time].  I can then create my view for the upcoming events by filtering on the calculated Event Begin column.


This works well, except for events that are marked as All Day.  In this case, the value of the Start Time shows midnight as the time (i.e. 1/11/2008 12:00 AM) which is fine, but the value of the Event Begin column shows the time 5 hours earlier (i.e. 1/10/2008 7:00 PM).  I am not subtracting anything when calculating the Event Begin value, so my initial thought is that I have a conflict between Time Zone settings somewhere.


I am Eastern Standard Time here in the US which is GMT-5:00, which would explain the 5 hour difference.  Unfortunately, I cannot figure out where the incorrect Time Zone setting might be.  Within my site the time zone is correct (under Site Settings >

how to create calculated measure in cube that always gives value on Year level in Date hierarchy



I'm using SQL 2008 standarrd. Probably a simple question,

But I want to creata a calculated measure in a cube that always displays a measure(e.g. total sales) on the year level of the Date-dimension-hierachy.

So wether I choose Year, Quarter, Month or Day, it always shows the measure value  (sales) on the Year level. How to do that?

Regards, Hennie

how to create calculated measure in cube that always gives value on Year level in Date hierarchy



I'm using SQL 2008 standarrd. Probably a simple question,

But I want to creata a calculated measure in a cube that always displays a measure(e.g. total sales) on the year level of the Date-dimension-hierachy.

So wether I choose Year, Quarter, Month or Day, it always shows the measure value  (sales) on the Year level. How to do that?

Regards, Hennie

Report Builder Filter shows Field as Date Type =Text instead of Date


My Data was created by retrieving ODBC data - using PowerPivot for Excel 2010, and has Birthdate defined as a Data Type = "Date".  This was uploaded to SharePoint, and a New Dataset was created to point at it.

I'm using Microsoft SQL Server 2008 R2 Report Builder 3.0.  I tried to filter this data on Birthdate, but when I go into the Filter, and specify the field that was defined as DataType = "Date" in PowerPivot - Report Builder changes the type to "Text", and gray's out the field so that I can't change it to "Date".

If I create an Expression as Cdate(Birthdate.Value), it lets me change the Data Type to "Date", and works appropriately, but we don't want to have to do this with every date field.

Why doesn't Report Builder see this field as a Date ? 

Calculated Date Fields

I'm trying to calculate whether a delivery is on time or late and by how many days. Is there a way to format the column so it show's the result in the number format as opposed to showing up as a date? Negative numbers don't export into excel well.

overriding "grand totals" for calculated members

hi all, is there a workaround for overriding "grand totals" for calculated members?  (I cannot use the workaround whereby I create the calculation in the DSV or view directly due to it being a pretty complex Descendant parent/child calculation).   thanks much for any thoughts, Cos

Calculated measures, calculated cells, and custom rollups issue

Hi,  I'm a little bit confused according to the purpose of Calculated measures, calculated cells, and custom rollups as they lead practically to the same goal, is there any differnce between those modes from a technical point of view. And for the last one, the custom roll up the question how to perform this, I need a walkthrough that lead me to undersand the way to set a custom roll up to a given dimesion, I visited microsoft site but the inforamtion that I have is poor, so is there any other source that exposes that as a step by step example? Thank youThe complexity resides in the simplicity

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.

how can I make field with date in format: dd/mm/yyyy ?

I have in model:[DisplayName("Publication date")] [DisplayFormat(DataFormatString = "{0:dd/MM/yyyy}", ApplyFormatInEditMode = true)] [Required(ErrorMessage = "Publication date is required.")] public DateTime publication_date { get; set; } And in view:<div> <%: Html.LabelFor(model => model.publication_date) %> </div> <div> <%: Html.TextBoxFor(model => model.publication_date, new { @class = "datepicker" })%> <%: Html.ValidationMessageFor(model => model.publication_date) %> </div> But when I write in this field date: 30/11/2009 I see validation error: The value '30/11/2009' is not valid for publication_date.

Updating a List item field value in a SPD Workflow is setting Date fields to null

Hello everybody,I faced a very weird behavior while creating a workflow in SPD against a Document Library.Here is the case:1- Create a document library in sharepoint site.2- Add a custom column of type DATE.3- Upload any document to the library4- Edit the item metadata and set the custom date field to any date you want.5- Now using sharepoint designer, create a very simple Workflow with only one action that updates the current item "Title" field to any value. (Note: In my case I set the workflow to start manually and not automatically)6- Save the workflow and then start it on the uploaded item.7- Now check the value of the date column after the workflow is finished.8- You will see that the Date field is Empty... (very weird)Notes:   1-This behavior will not appear on a standard list. it is only appearing in a Document library.  2- I am using WSS 3.0 SP2 + MOSS 2007 SP2. Also SharePoint designer 2007 SP2Did anyone face this situation and does it have a fix, or am I doing something wrong here... Please help.Regards,Maheed

Use Sharepoint Calendar Start Time field in calculated column

I'm trying to limit the calendar view to current week using the Sharepoint defined field Start Time. However, when I display the serial number behind the Start Time it looks like it is actually giving me the serial number for the Created Date field. I believe it is because the appointment is reoccurring but I'm hoping there is a way around it. I created a new testdate field and manually entered the date and set those appointments as all day events however they are not reocurring appointments. The testdate returns the correct serial number however the start time returns an incorrect serial number. SerialNumber1=DATE(YEAR([Start Time]),MONTH([Start Time]),DAY([Start Time])) SerialNumber2=DATE(YEAR([testdate]),MONTH([testdate]),DAY([testdate])) Start Time  SerialNumber1 Testdate  SerialNumber2 8/31/2010 40420 8/31/2010 40421 8/31/2010* 40405    9/1/2010* 40405    9/2/2010* 40405    9/7/2010 40427 9/7/2010 40428 9/13/2010* 40405    9/14/2010 40434 9/14/2010 40435 9/14/2010* 40405    9/15/2010* 40405    *These are reoccurring appointments scheduled as all day events. The others were entered as one time appointments scheduled as all day events.

Showing highlighted text (HTML) in calculated field?

Greetings all,   We're porting our 2007 list to 2010. In Sharepoint 2007, we used the following Calculated Column code to display a highlight over items that had different statuses:   =IF(Date="","<DIV style='background-color:#ff6666'>Not <b>Yet</b> Set</DIV>", IF(Finalized=TRUE,"<DIV style='background-color:#66ff66'>Finalized</DIV>", "<DIV style='background-color:#fffa91'>Tentative</DIV>"))   When we use this in Sharepoint 2010, it doesn't seem to apply the DIV structure. Any ideas of how we can change our code to make this work? Thanks! --Dave   No matter -- I need to add a code snipped ( http://marijnsomers.blogspot.com/2010/01/write-html-code-in-sharepoint-via.html ) to the page... thanks!

How do I format a date time field in a CQWP to YY-MM-DD ?

Hi. I have been trying to get the date-time to look good for some time now, but have failed. I have a Content Query web part that picks up article pages from a page library. The pages contains a field called 'article date'. I want to show just the 'Title' and the 'Article date'. Doing this default gives me: My title 10-09-03 00:00:00 I want it to be: My title 10-09-03 Is this possible? I'm starting to realize that I probably have to edit the xsl styles for this webpart but if thats the answer, how do I do that? ThanksThomas Balkeståhl - Technical Specialist - SharePoint

Weird Date vs Date/Time issue using a calculated column

I'm attempting to use the fab 40 attendance template. I don't need the time to show - Im able to hide that on the forms with jquery (endusersharepoint.com thank you!!) I WAS ATTEMPTING to create a calculated column called Start Date where the formula simply reads '=[Start Time]' When it's set to display 'Date Only' the date is off by a day. If I switch it to 'Date & Time' I get the correct date. Huh?

Created Calculated Measures Permenantly with MDX

I did some research on how to create a calculated measure in MDX, i found that by using MDX to create a Calculated Measure, the measure will not be persistent in the cube but only query-scoped or session-scoped. is it possible to create a persistent calculated measure using MDX? if not, where is a good point for me to start looking into xmla to create the calculated measure?
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