.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

Calcuate duration between selected date(Filter)

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


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?




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.

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    

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?

Validate ASP.net calendar date selected with Javascript?


Is there any way I can use javascript to make sure the user has selected a date from my asp.net calendar? I have the calendar under an update panel:

                        <asp:UpdatePanel ID="UpdatePanel2" runat="server">
                                <asp:Calendar ID="Calendar1" runat="server" BackColor="#FFFFCC" BorderColor="Maroon"
                                    BorderWidth="1px" CssClass="calendar" Font-Names="Verdana" Font-Size="9pt" ForeColor="Black"
                                    Height="200px" Width="305px" DayNameFormat="Shortest" ShowGridLines="False">
                                    <SelectedDayStyle BackColor="maroon" BorderColor="Black" BorderWidth="1px" Font-Bold="true" Font-Size="11pt" />
                                    <SelectorStyle BackColor="#FFCC66" />
                                    <TodayDayStyle Font-Bold="true" Font-Size="16pt"/>
                                    <OtherMonthDayStyle ForeColor="#CC9966" />

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.


select record based on / match selected date

with the sql statement below, i try to select the records based on the selected date, it return no rows from adapter filled datatable. 
however, when i run the query in sql query in sql server studio management, it returns records. 
date format = 09/28/2010

string[] splitDT = txtSelectedDate.Text.Split('/');

            string strRearrangeDT = splitDT[0] + "/" + splitDT[1] + "/" + splitDT[2];

using (SqlConnection sqlconn = new SqlConnection(connstring))
                DataTable dt = new DataTable();
                string strSQL = "SELECT userclaim_details3.id, userclaim_details3.userclaimid, userclaim_details3.userid, userclaim_details3.childcategorytableid, userclaim_details3.statusid, " +
                         "userclaim_details3.amount AS amountname, userclaim_details3.remark AS remarkname, userclaim_details3.datecreated AS datecreatedname, " +
                         "userclaim_details3.datemodified, approvaltable.id AS Expr1, approvaltable.typename AS statusname, usertable2.id AS Expr2, usertable2.name AS uname, " +
                         "childcategorytable.id AS Expr3, childcategorytable.categor

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.


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




SharePoint DateTime control returning current date when no date is selected

In WSS 3.0, I am using sharePoint DateTime control. I retrieve the date selected by the user using SelectedDate property of the sharePoint DateTime control.

The issue is that even when the user has not selected any date in the sharePoint DateTime control, the SelectedDate property of the sharePoint DateTime control returns current date. I guess it should return DateTime.MinValue or null when the user has not selected any date in the sharePoint DateTime control.

Kindly clarify.


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

How do I get the last date if selected does not exist?


Hi All,

My table

Date, Currency
10.10.2010 EUR
19.10.2010 USD
20.19.2010, EUR
21.10.2010, USD

If I have a date 9.10.2010, choose the nearst date 10.10.2010 EUR

If I have a date 18.10.2010 (this date does not exist), choose the nearest date 19.10.2010

If I have a date 25.10.2010, choose 21.10.2010 USD

How can you help me?


show selected date in calendar


Hi all,

I've got this terrible brain freeze today ;-)

I have an asp:Calendar (not ajax). When I set the selectedDate from codebehind to a date in another month, I'd want the new selected date to show (that is, show the calendar "page" with the new month. How do I do this?

calFromDate.SelectedDate = myDate 'works fine

calFromDate.ShowSelectedDate's Month 'pseudocode


The calendar resides in an updatepanel for speed purposes, but I don't think that has anything to do with anything...


Thanks a lot!


Clear Selected Filter on SharePoint List Filter / Choice Filter Webparts


Hi All,

   I am using a 'choice filter web part' and a 'Share Point List Filter web part' on a page to filter other web parts ( Lists / Data view web parts).

   The filters are working fine, but once a filter is selected there doesn't seem to be a way of showing all contents back ( that is clearing the selected filter) . The selected filter remains even if we refresh the page !!!

 Any suggestions as on how we can have the option of 'show all' or 'clear filter' for  Choice Filter web part .


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