.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

Restricting Date Parameter Values

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

I have a daily revenue report (with 1 date parameter) that runs daily via report subscription.  Is there a way to restrict the date parameter so it can only select from 2009/1/1 up to Yesterday.  Our daily ETL process will process yesterday's data around 6:00 am, if the user picks a wrong date then the report will be incomplete.  I use =DateAdd("d",-1,Today()) for my default date parameter.

Is there a way to come up an explanation or a message  that “processing has not completed” or “data not available” if the user try to pull a report with wrong date?


Thanks, Michael Mei

View Complete Post

More Related Resource Links

Get Submitted SQL with Parameter Values (Not @Names)



I want to retrieve the full SQL that my Web Application generates and executes on the database. So when the INSERTED or UPDATED events are triggered on the SqlDataSource, I want to get that particular SQL Statement.

The  problem I've got there though is when I use "e.Command.CommandText" that gets the SQL statement with the paramter names NOT the paramter values. I specifically need the values.

So... how can I get it to display the full SQL statement containing submitted values!?



problem with passing parameter values to stored proceedure from asp.net c#

hi all, i am facing a problem while passing parameters to a stored proceedure. following is my sql query written for creating this stored procedure:   CREATE proc [dbo].[getstudent]( @wherecolumn nvarchar(2000), @wherevalue nvarchar(2000)) asbegin  declare @sql nvarchar(max) set @sql = 'select * from student2 where ' + @wherecolumn+'='+@wherevalueexec sp_Executesql @sql end   my table contains four columns: And the sql Query For the table is as follows:   CREATE TABLE [dbo].[student2]( [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [rollno] [int] NOT NULL, [class] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [section] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_student2] PRIMARY KEY CLUSTERED ( [rollno] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]   from page behind I am passing the values to parameter as follows:   SqlCommand command = new SqlCommand("getstudent", c.con); SqlDataAdapter datadapter = new SqlDataAdapter(c.cmd); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@wherecolumn", SqlDbType.NVarChar).Value = "rollno"; command.Parameters.Add("@wherevalue", Sq

Problem with date values in data-driven subscription reports

Hello together, I have a question in regards of data-driven subscription report. I already have an ad-hoc report in place which can be customized by date etc. I now want to run these reports with a subscription. My challenge is now that I don't know how I can pre-define the date. I basically want the start date of NOW() and the end date 7 days in the past. I was looking around but did not get any clue where and how I have to configure it the way it works. I found this thread but honestly do not understand it well... :( http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/fb48fd15-09dd-4f5f-a09f-d92bf304c1a2 Can someone help me out please? M.

Using date as a parameter

Environment: 2008 SSRS, 2008 visual studio We run our reports in two modes. User selects a report from a menu on a web page, the report displays and waits for them to enter some filter parameters. Or, some pages have a print button where the report is run with all the required parameter values pulled from the the data state of the page with the button. The same RDL is used in both instances. One particular report has dates as required parameters. When the report is run prompting the user for parameters it works great. When run at the press of a button is when we have problems. The pertinent sections of the RDL look like this: In the parameter section the parameter is defined like:     <ReportParameter Name="FromChangeDate">       <DataType>DateTime</DataType>       <Prompt>From Change Date</Prompt>     </ReportParameter> In the query section the parmeter is defined:           <QueryParameter Name="@FromChangeDate">             <Value>=Parameters!FromChangeDate.Value</Value>           </QueryParameter> The corresponding parameter in the stored procedure is defined: @FromChangeDa

How to Create an MDX Query Parameter to Select 30 Values from a Dimension?

Hi, I'm using SSRS 2005 to report on an SSAS cube that contains a Procedure dimension.  I don't need to use members of this dimension in my report, but rather need to select records (patients) where their chart has one or more of the codes.  I've researched this today and cannot locate the best approach.  Thus far, I've attempted to create an MDX query parameter as part of my dataset.  However, I don't know whether this is the correct approach, and how to structure the syntax so that only records with one or more procedures are included in the report?  If so, what is the proper MDX syntax for setting my Procedure code equal to the query parameter? Thanks, Sid

Passing multi values to another multi value parameter

Hi, my requirement is as follows I have a parameter1 in which i populate values from database. the user can select multiple values from parameter1. after the parameter1 i have another parameter2 which is also mutliselection value. i,e depending on the selection of parameter i need to populate remaining values in Parameter2 (which is also a multi selection value). i have splited the values from parameter1 and passed the value of parameter1 (parameter1.value) to parameter2. but i am getting the following error An expression of non-boolean type specified in a context where condition is expected, near ',' incorrect syntax near the keyword 'ELSE' can anyone help me in this Thanks, Rajesh Peethala

SSRS problem with passing multiple parameter values via URL %2c

Hi, I have a problem with passing multiple parameter values from one report (A) to another report (B).   For example I built a URL string in report (A) with the parameters like &Parameter1=1&Parameter1=2. As soon as I click on that URL the URL parameters look like &Parameter1=1%2c2 and the parameters don’t getting populated on report (B).   If I change the parameter in the URL to &Parameter1=1&Parameter1=2 and execute the URL manual the parameter on report (B) getting populated properly.   Is there a way to disable the coding like %2c (comma) or is there another solution for it?   Thanks

Date Picker Parameter

hi guys... I can't get the date picker for ssrs 08 reports to work. My underlying columns is a datetime type and as far as I know that should do it. It just shows the dates as normal entries even though I changed the Data type from text to date/time in the report parameters properties. Any idea much appricated? Thx

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?

Report Parameter Values - Can I use getdate() or now() or any other function?

Hi all,      I have a report that utilizes a calendar function but I need to make it a subscription report. Is it possible to use getdate() or now() on the Report Parameter Values? I have a text box that says Enter a Date and that is where I want to have the default value to be run with the subscription. Please let  me know if possible. Thank you.

Any way to pass Multiple Values for a single Label in the Parameter?

  I have a Report that Contains 2 Parameters, @Customer & @Area. When trying to set up the Available Values for @Area, I'm having issues using multiple values for one Label, i.e. = "4006" Or "4610" One of the Filters in the Report is an Operation number, which is the [OPERATION] field, which is setup as a filter on the Tablix referencing the @Area parameter.  PROBLEM: I cannot retrieve any data when trying to use the ‘Or’ Operator here. If I simply put “4006” or “4610” I retrieve data, but when trying to combine it returns no data. Example, I need to allow a user to select ‘Chassis Incoming’, which would include data from Operations 4006 & 4610. QUESTION: Any way to pass Multiple Values for a single Label in the Parameter? I realize the typical solution may be to use ‘Multi-Value’ selection, but in this case we want the User to select the Area and the multiple values for Filtering will be automatically determined for them. Otherwise, they are subject to not getting it correct. I have tried several different ways, such as =”4006” Or “4610”, =(“4006”, “4610”), = In(“4006”, “4610”), etc….   Note: We are using Report Builder 3.0  

Ignore a parameter values ??

Hello I will briefly describe my problem, I have a report as a matrix where the years are presented in columns and below these years we find months, the report accepts as parameter the year and month and other params, I'd like under the choice of an user to display either the years and months at a time or Only the years. I tried as a solution to add a new value = "Ignore months" to the parameter "month " but when i try to view the report, it does not display anything (because it filters month which = 'Ignore months ' and finds no result) So there is a way to ignore the month parameter values in this case??? Hope to get response as soon as possible.Thank you Regards Aya

Unble to pass date parameter to "Go to Report " ?


Hi ,

I have Report A need to go from Report A to Report B passing multivalued parameter and date parameter as well , I do not have any problem passing  Multivalued parameter but cant pass date parameter from Report A and Report B .

Report A : Date parameter properties

name : date

Data Type : Date/Time

Default value =Today()

Report B : Date parameter properties

name : date

Data Type : Date/Time

Default value =Today()

Go to Report action ,Expression used to pass date parameter  from Report A to Report B


Unable to pass appropriate date parameter if changed from default value from ReportA to ReportB .

Assistance would highly be appreciated !!

Thanks .

Rajkumar Yelugu

Report parameter values must be specified before the report can be displayed. Choose parameter value


Helllo...I am getting error "Report parameter values must be specified before the report can be displayed. Choose parameter values in the parameters area and click the Apply button." when I deploy the report in the SharePoint. The report works fine in the when I run in BIDS. we have two parameter in this report...Parameter #1 is tied filterd based on the user who is accessing the report and Parameter #2 is dependent on the value selected in Parameter #1.

Please let me know what I am doing wrong here? Is there any workaround to get past this message?

Thanks for help.


remote execution of ssis package using wcf doesn't like date parameter


I have a ssis package remotely executed by a C# windows application that runs ok in context of SSIS installed on the desktop; I created a wcf windows service that the C# application executes, creates a copy of the package with the date in the variable, but errors on the first SQL task component that requires the date. The copy executes using the SSIS utility. What is possibly the issue with wcf and the date type in the SSIS package? Here is the error:

OnError,SRVR2BLADEA3,MIC\1347259,Clear CII ImportDate,{AB3CF5BA-C114-40F6-9ECB-4C7620D97829},{8B1C10CB-580F-4BFE-BC7D-0B97C181E307},9/27/2010 3:29:56 PM,9/27/2010 3:29:56 PM,-1073548784,0x,Executing the query "" failed with the following error: "The type initializer for '<Module>' threw an exception.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Here is the code:

case @"E:\SSIS Packages\CIISCImport.dtsx":

//string dtValue = (prms[iPrms - 1].Split('=').GetValue(1).ToString()) + " 12:00";

vars["User::ImportDate"].Value = DateTim

Assign Query String Values to Selectcommand parameter in




I have two pages EventList.aspx and EventDetail.apsx. When user selects any events [basically list item] on EventList.aspx page [all events are rollup in single sote collection] in am redirected to EventDetail.aspx page with QueryString ListID and ListItemId.

On EventDetail.aspx page i am using


"<Webs Scope='SiteCollection'></Webs><Lists><List ID='{ListID}'></Li

Label of parameter = values of 2 fields



I would like to create a parameter that has as value, the combination of 2 fields of my dataset.
Say like Name and FirstName combined as EmployeeName
There is no chance I can update/change the procedure that delivers these fields.

I tried looking into the code, but didn't have a lot of success.
Anyone some ideas?


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