 |
BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7  | This is the forum to discuss the Wrox book Professional SQL ServerReporting Services by Paul Turley, Todd Bryant, James Counihan, George McKee, Dave DuVarney; ISBN: 9780764568787 |
|
Welcome to the p2p.wrox.com Forums.
You are currently viewing the BOOK: Professional SQL Server Reporting Services ISBN: 0-7645-6878-7 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

July 8th, 2004, 11:21 AM
|
Authorized User
|
|
Join Date: Jul 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
optional parameters in SP
I have a store procedure accepting 3 parameters (year, month, date), the "date" is optional, inside the store procedure, I will detect if the user enter the "date" parameter or not, if entered, it returns a daily sales report, if not, it returns a monthly sales report. The store procedure works fine in query analyzer, and the daily sales report (when all three parameter entered) works fine, but I get nothing return when I ignore the "date" parameter. I configured the "date" as "allowing null". Would some give me some advise how I get this work? I don't want to create two different reports (monthly, daily). THanks.
|

July 12th, 2004, 09:26 AM
|
Authorized User
|
|
Join Date: Jun 2004
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
If I had to guess the problem is in the stored procedure.
When you set the input parameters how did you declare the date? Did you set a default value for it?
Sandy Murdock MCP
|

July 12th, 2004, 10:38 AM
|
Authorized User
|
|
Join Date: Jul 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Yes, I did, I set it to "null". The procedure is fine. I tested it in query analyzer. The problem is the report service did not pass in a "null" value for the "date" parameter when I did not choose a "date" value in the "date" drop down box. I end up used a union query to partially fix the problem. I used :
select 0
union
select dd from ......
so, when the date drop down box will show data like this : 0,1,2,3,.... I changed my store procedure to detect the "0", when it is "0", I return the monthly report, when it is not 0 I return the daily report. Now I need to convert the "0" into something user like to see, like "All", so the date drop down will show up like "All, 1,2,3...." Is there a way in the reporting designer to set teh value of the drop down data different from the display value? When I have the "all" display, I want it to pass in "0" as the value.
|

July 12th, 2004, 11:38 AM
|
Authorized User
|
|
Join Date: Jun 2004
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Assumption:
Your dates are in a dropdownlist
Your dates are integers for the month (IE: 1 = january)
Your values are not coming from a db but were set using the parameters property of the report.
IF all these assumptions are correct, then you can open the report in visual Studio (design view). Right click on the vacant space around the report. Select Parameters.
For available values you would have non-queried checked. Currently for the values you would have (since the value seems to equal the text) an integer under the label and an integer under the value.
Change the Label to be the text string you want. Leave the value alone.
If that isn't how you did it, but you are not going to change the way the date works (and my assumption about there being 12 values, one for each month, is correct) then you may want to consider doing it this way.
My assumptions could be way off.
Hope that helps.
Sandy Murdock MCP
|

July 12th, 2004, 01:20 PM
|
Authorized User
|
|
Join Date: Jul 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Thanks for your response.
The first two assumptions in your response were right, but the third one is not. I am only filling the date data that has order. For example, if there are only three days (3,5,20) has orders in March, the date drop down will only has "All, 3, 5, 20". so it is a "from query" parameter. I used a query to return the dates:
select "0"
union
select date from order where it has order
What is confusing me is, when it is a "from query" parameter, there is no way to make the label field different from the value field. I want be able to use a expression: if data return ==0, then display "All" . Is there a way to do that?
|

July 12th, 2004, 01:22 PM
|
Authorized User
|
|
Join Date: Jul 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
"expression" is not an option in thew label field drop down in the parameter dialog box.
|

July 13th, 2004, 09:10 AM
|
Authorized User
|
|
Join Date: Jun 2004
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
How about this...
select 0 as value, 'All' as Name
union
select dd as value, cast(dd as char(3)) as Name....
This will put the phrase 'All' beside 0, and the numbers beside numbers for the rest.
You can then assign name as your label and value for your value
Sandy Murdock MCP
|

July 13th, 2004, 03:28 PM
|
Authorized User
|
|
Join Date: Jul 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
Thank you very much! Your suggestion works perfect!
|
|
 |