Solved

SSRS - Applying multiple Parameters (date) to Reports

Posted on 2008-06-15
11
1,619 Views
Last Modified: 2010-08-05
I am trying to create a report that takes Product Name & the start and the end date as a parameter...

Product name apply parameters @ report level...
Start and the end date apply parameters @ query level

The problem is that when I select date from the report, it changes the value (replaces Month with the date) before applying query

as an example... when I selected May 1, 2008 from the calender... it got changed to Jan 5, 2008 before running the query...

in my data set, I am using following parameters:

WHERE    
      convert(datetime,trans_dt,103)  BETWEEN convert(datetime,@FROM_DATE,103) and convert(datetime,@TO_DATE, 103)

in report parameters, I ve used dateTime as the data type...

Please advice

0
Comment
Question by:redgreenred
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 13

Expert Comment

by:rickchild
ID: 21791609
Are you running the query directly in the data set, or passing those dates to a Stored Procedure?

Where are you based, Convert 103 is British/French date, have you tried without the convert as it should try to match your server environment?  Could explain why the day and month is backwards as these would be reversed in British.  Still strange as you are converting both sides to get consistency.

Maybe try converting as type 121 is this is a more generic format yyyy-mm-dd hh:mi:ss.mmm
0
 

Author Comment

by:redgreenred
ID: 21793791
I am running the query directly in the data set

.   Passing one report parameter... which is working fine
.   Passing two parameters (start and the end dates) to the query in data set... This is where I am facing problems...

I've converted to type 121 but issue remains the same:

WHEN I SELECT DATES FROM THE CALENDER, DAY AND MONTH GET SWITCHED... BUT WHEN I APPLY THE FOLLOWING FOMAT MANNUALLY IT WORKS FINE (DD/MM/YYY)

Please advice...

Thanks
0
 
LVL 13

Accepted Solution

by:
rickchild earned 100 total points
ID: 21794059
Have you deployed the report to the server yet, or are you still in Visual Studio developing the report?

I have solved a similar problem in the past by deploying the report, as this enables it to pick up the Regional information from the server.

I would try deploying what you have done so far, and then trying to run it from the server.
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

Author Comment

by:redgreenred
ID: 21794418
I haven't deployed it to the server yet,... still using BIDS...

Thanks
0
 
LVL 13

Expert Comment

by:rickchild
ID: 21801591
OK, might be worth deploying the report as it stands, and then you may have more luck
0
 

Author Comment

by:redgreenred
ID: 21816128
I've deployed it to the server

Good news: It's working fine for me

Challenge: other users are getting an error message when they select dates from the calender or enter dates in the format (mm/dd/YYY)

here is the message that they are getting:

The value provided for the report parameter 'FROM_DATE' is not valid for its type. (rsReportParameterTypeMismatch)

but when they change the format to dd/mm/yyy it works for them too...

Please advice...
0
 

Author Comment

by:redgreenred
ID: 21816143
I am using following to filter in the dataset query

trans_dt  BETWEEN convert(datetime,@FROM_DATE,121) and convert(datetime,@TO_DATE, 121)

Thanks
0
 
LVL 13

Expert Comment

by:rickchild
ID: 21820414
OK, a bit closer anyway. Does it now work for you from both BIDS and the server?

This is likely down to a regional setting in the Users internet browsers, which means the default date sent is in the format mm/dd/YYYY, and this is used in the calendar too as it's the default.
It sounds like the users are set up for US, but the report server is expecting a British/European date?

Or I guess it could be the conversion, but I think you would get a different error in that case.


Steps to Reproduce:

1.) Create a report with a date time parameter. Set the initial value to =Today()
2.) Configure the Internet Explorer Language Settings to default US
3.) Open the Report through the Report Viewer (http://servername:port/reportserver)
4.) Change the date value through the date time picker

See if this reproduces (rsReportParameterTypeMismatch), and then try with British default on the browser.
0
 

Author Comment

by:redgreenred
ID: 21825611
it worked on the server
0
 
LVL 13

Expert Comment

by:rickchild
ID: 21826462
OK great, I have been caught out by this one before, and worked on it all day before deploying it on the server!
0
 

Author Comment

by:redgreenred
ID: 21827110
Thanks again
0

Featured Post

Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question