?
Solved

how to work with parameters

Posted on 2009-04-23
7
Medium Priority
?
177 Views
Last Modified: 2012-05-06
I have installed sql server 2008 reporting services and I already have query, but I want to add parameters to that query in report, and also have date in my query if user dont enter date , it will be default to ..
here my query,
select lastname, firstname, datestarted from tblCustomer where
datestarted > GetDate()- 45

and I want to add a parameter to the report, that datestarted, for user to get prompted to enter datestarted, if it is left empty, that time it will be GetDate() - 45 defaulted..
0
Comment
Question by:1jaws
  • 4
  • 2
7 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24219599
0
 
LVL 8

Expert Comment

by:Hadush
ID: 24219697
1. you need to change the query to take parameter date (say startDate)
select lastname, firstname, datestarted from tblCustomer where
datestarted > @StartDate()- 45
2. Go to Reports-->Report parameters --> (you will see StartDate parameter listed )
Make sure the datatype is DateTime so that your users will see a calender with current date as default
Fill the report parametrs widnow as shown in the picture and make sure the default to Non-query   (=Today)
 
Let me know if this helps

DateParameter.bmp
0
 
LVL 8

Expert Comment

by:Hadush
ID: 24219704
Correction on the query, you don't need the parenthsis on the startDate
select lastname, firstname, datestarted from tblCustomer where
datestarted > @StartDate- 45
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:1jaws
ID: 24219914
thank you so much both, I am still having trouble because are we viewing same reporting tool? I have sql server 2008 2.0 report builder
 on the left side I have Built in Fields
                                    Parameters
                                    Images
0
 
LVL 8

Expert Comment

by:Hadush
ID: 24220242
In 2008 is a bit different environtment and here are the steps for that:
1. In the report data pane expand parameters (shown in fig one) and double click the parametername(@startDate)
2. General tab change the data type to Date/Time from the Datatype Drop down list
3. Default values tab  check the Specify values radio butten and under value slect the expression sign (fx) and put =Today
and ok, ok , ok
0
 
LVL 8

Accepted Solution

by:
Hadush earned 2000 total points
ID: 24220258
Sorry, I hit submit before I attached the images

DataParameter1.bmp
ChangeDataTypeToDateTime.bmp
DeafultValuesToToday.bmp
0
 

Author Comment

by:1jaws
ID: 24229367
THank you I worked! if I want to add for example country parameter I just do it the same way, but I want if user leaves empy that textbox when prompted at the report , I wanted to pull all the countries, how can I do that?
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

840 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