.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

How to filter comparing two date attributes?

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

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.

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

comparing date values

ScenarioI have records that have an initial value of 'blue' and stay 'blue' until after 2 days then they turn to 'green', Turn 'orange' after 15 days and then after 20 days turn 'red'.The results will be pulled into a gridview.What I'm having difficulty is; what's the best way of calculating the days, in sql server or in c#?Trying to get construct date examples is proving to be a bit of a pain: DateTime TodaysDate = DateTime.Now; DateTime SelectedDate = Calendar1.SelectedDate; TimeSpan result = SelectedDate - TodaysDate; int days = result.Days; lblCalculateDays.Text = days.ToString(); TextBox1.Text = TodaysDate.ToString("d"); TextBox2.Text = SelectedDate.ToString("d"); protected void Calendar1_SelectionChanged(object sender, EventArgs e) { putDateInBox(); } protected void putDateInBox() { lblCalDate.Text = Calendar1.SelectedDate.ToShortDateString(); }has anyone done something similar and found a good way of doing this?

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?

Calculated Measure Help - Comparing Months from two Date Dimensions

Hello:   I have a Measure Group that has two Date dimensions, a Reporting Date and a Fee Paid Date. The Reporting Date and the Fee date is based on the same Dimension, which has a standard YQMD hierarchy. It's very typical. The calculation is called "Fees Paid In Same Period", and it would display the Fees paid if the Reporting Month equals the Fees Paid Month.   How do i do this?   Thanks,

comparing a date in string format to date


I have a program that is using credit card info and the expiration date is selected by the user clicking a dropdownlist for the month and a dropdownlist for the year.  I want to show a label message if the user tries to proceed while the month and year are evaluated as being earlier that the current month/year.  Can someone help me determine how to evaluate this? 

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.


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?




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


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

Need optimisation in Date filter

HI All,
I have to create report on the basis of date filter and i am using SSAS 2008 R2 Cube  for that report.
Now i want to show only Sundays of last six week in that filter may be in drop down manner.

Kindly suggest how to do the same.


Dimension Date filter SCD


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

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 ? 

Date Range filter?


I'm trying to find or build a date range filter template that works with .NET 4.0?

I tried making one based on the decimal range filter from http://dynamicdatafiltering.codeplex.com/, but it looks like these DLLs were built with .NET 3.5 assemblies so I get an error: Type 'ASP.dynamicdata_filters_datetimerange_ascx' does not inherit from 'System.Web.DynamicData.QueryableFilterUserControl'.

Thanks in advance!!

I could probably build one from scratch if I could just see the code for the "ApplyEqualityFilter" function that the other templates are using...ApplyEqualityFilter(source, Column.Name, value)

date range filter in ssrs 2005


i have a graph report created in ssrs 2005 with more that 10 graph which looks at the trend, the report is generated based on selection date which should be a year period but some of the reports should be on current month. i have two parameter @DataFrom and @DateTo in my query and i created a formula Month = DateAdd("d",-30,Parameter!DateTo.Value) what i need is to set a filter on the monthly graph to put something like: Expression : =Field!DateRequired.Value BETWEEN Field!Month.Value to Parameter!DateTo.Value.

hope i make sense

thanks in advance

Prefixes on column headings of date attributes using shared dimension


When reusing a date dimension multiple times as in the Adventure Works cube, the attribute names are prefixed with the cube dimension name in the selector screen. However, when they are brought into the query, the prefix is dropped. So "Delivery Date.Date" becomes just "Date". This is also the case in Excel 2007 which can be confusing for users. Any way ar ound this?


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