| Author |
Topic |
|
dodaniel
Starting Member
11 Posts |
 Posted - 2012-08-09 : 15:14:26
|
| In BIDS I have a report that prompts for Department Number. The parameter is @DeptID and the Dataset uses a Filter for [DeptID] = [@DeptID].This works great for selecting one department but I would like a way to give the user of either entering a department ID or leaving the prompt blank and then the report would show all departments. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-09 : 15:18:38
|
| replace [@DeptID] in prompt with an expression like (use expression window by clicking fx button)=IIF(lEN(Parameters!DeptID.value)>0,Parameters!DeptID.value,Fields!DeptID.value)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
dodaniel
Starting Member
11 Posts |
Posted - 2012-08-10 : 09:28:03
|
I tested this and when I leave the prompt for department number blank the report does not show me any records. I don't how to make the FALSE part of the IIF statement below represent ALL record values. quote:
Originally posted by visakh16
replace [@DeptID] in prompt with an expression like (use expression window by clicking fx button)=IIF(lEN(Parameters!DeptID.value)>0,Parameters!DeptID.value,Fields!DeptID.value)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-10 : 10:57:44
|
quote:
Originally posted by dodaniel
I tested this and when I leave the prompt for department number blank the report does not show me any records. I don't how to make the FALSE part of the IIF statement below represent ALL record values. quote:
Originally posted by visakh16
replace [@DeptID] in prompt with an expression like (use expression window by clicking fx button)=IIF(lEN(Parameters!DeptID.value)>0,Parameters!DeptID.value,Fields!DeptID.value)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
nope it will work so far as you're passing blank and not space------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
dodaniel
Starting Member
11 Posts |
Posted - 2012-08-10 : 11:25:44
|
If I don't put anything in the Parameter box isn't that passing a blank? I have the parameter property set to Allow blank value "" but not set to allow null value but I am still not getting any records when I don't fill in the prompt with a value.quote:
Originally posted by visakh16
quote:
Originally posted by dodaniel
I tested this and when I leave the prompt for department number blank the report does not show me any records. I don't how to make the FALSE part of the IIF statement below represent ALL record values. quote:
Originally posted by visakh16
replace [@DeptID] in prompt with an expression like (use expression window by clicking fx button)=IIF(lEN(Parameters!DeptID.value)>0,Parameters!DeptID.value,Fields!DeptID.value)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
nope it will work so far as you're passing blank and not space------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-10 : 11:30:08
|
quote:
Originally posted by dodaniel
If I don't put anything in the Parameter box isn't that passing a blank? I have the parameter property set to Allow blank value "" but not set to allow null value but I am still not getting any records when I don't fill in the prompt with a value.quote:
Originally posted by visakh16
quote:
Originally posted by dodaniel
I tested this and when I leave the prompt for department number blank the report does not show me any records. I don't how to make the FALSE part of the IIF statement below represent ALL record values. quote:
Originally posted by visakh16
replace [@DeptID] in prompt with an expression like (use expression window by clicking fx button)=IIF(lEN(Parameters!DeptID.value)>0,Parameters!DeptID.value,Fields!DeptID.value)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
nope it will work so far as you're passing blank and not space------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
put a textbox on your report,give expression as=LEN(Parameters!DeptID.value)and see what it returns when you pass blank------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
dodaniel
Starting Member
11 Posts |
Posted - 2012-08-10 : 12:28:26
|
I added the textbox and it is showing 0 and all the records like it should in BIDS but when I deploy it to Report Manager it does not show the textbox or any records.quote:
Originally posted by visakh16
quote:
Originally posted by dodaniel
If I don't put anything in the Parameter box isn't that passing a blank? I have the parameter property set to Allow blank value "" but not set to allow null value but I am still not getting any records when I don't fill in the prompt with a value.quote:
Originally posted by visakh16
quote:
Originally posted by dodaniel
I tested this and when I leave the prompt for department number blank the report does not show me any records. I don't how to make the FALSE part of the IIF statement below represent ALL record values. quote:
Originally posted by visakh16
replace [@DeptID] in prompt with an expression like (use expression window by clicking fx button)=IIF(lEN(Parameters!DeptID.value)>0,Parameters!DeptID.value,Fields!DeptID.value)------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
nope it will work so far as you're passing blank and not space------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
put a textbox on your report,give expression as=LEN(Parameters!DeptID.value)and see what it returns when you pass blank------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-10 : 15:43:09
|
| then i'm sure you're not looking at right report in server or deployment was not successful------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
dodaniel
Starting Member
11 Posts |
Posted - 2012-08-11 : 12:34:19
|
I deleted the report from Reports Manager and re-deployed it and I get the same problem. It works in BIDS correctly but in Reports Manager the text box is not showing and I do not get any records when I leave the prompt box blank.quote:
Originally posted by visakh16
then i'm sure you're not looking at right report in server or deployment was not successful------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-11 : 16:45:30
|
| which is report server version?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
dodaniel
Starting Member
11 Posts |
Posted - 2012-08-13 : 10:37:20
|
It is all working correctly today. Thank you for all your help!quote:
Originally posted by visakh16
which is report server version?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
|
|
|
|
|
Leave Prompt Blank for All Record