Report Parameters

I have a stored procedure which accepts a CustomerID parameter of type
char(3). If this parameter is null or blank, then data for all customers is
returned, otherwise just for the selected customer. This works as exepected
in query analyser.
pr_CustomerReport NULL OR
pr_CustomerReport ''
both return data for all customers
pr_CustomerReport 'FDR' returns data for selected customer.
I am trying to get a report to pass a parameter to this stored procedure
from a drop down list. If I select a parameter value in the drop down list I
get the data for the selected customer, but if I leave the parameter with no
selection I get nothing, but I KNOW the sp works correctly with blank or
null parameters.
In report parameters I have ticked both allow null value and allow blank
value. Why won't the report display anything? How can I find out if the
report is even attempting to run the stored procedure (I suspect it isn't)?
Is there anything else I need to do to get the report to run with an
unselected parameter?
Thanks for any help
KenYour drop down list is populated from the query. That query should return
null (dbnull) as one of the values so that you can select it later. You
can't just select nothing and assume it is null.
By selecting "allow null" and "allow blank" you say that your stored proc
(or query) can potentially accepts nulls (will not crash). By _not_
providing null as one of the valid values you say that null is _not_ valid
in the current situation and therefore can't be selected.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
"Ken Cooper" <_k.a.cooper_@._shu.ac.uk_> wrote in message
news:ulHvEjrYEHA.2812@.TK2MSFTNGP11.phx.gbl...
> I have a stored procedure which accepts a CustomerID parameter of type
> char(3). If this parameter is null or blank, then data for all customers
is
> returned, otherwise just for the selected customer. This works as
exepected
> in query analyser.
> pr_CustomerReport NULL OR
> pr_CustomerReport ''
> both return data for all customers
> pr_CustomerReport 'FDR' returns data for selected customer.
> I am trying to get a report to pass a parameter to this stored procedure
> from a drop down list. If I select a parameter value in the drop down list
I
> get the data for the selected customer, but if I leave the parameter with
no
> selection I get nothing, but I KNOW the sp works correctly with blank or
> null parameters.
> In report parameters I have ticked both allow null value and allow blank
> value. Why won't the report display anything? How can I find out if the
> report is even attempting to run the stored procedure (I suspect it
isn't)?
> Is there anything else I need to do to get the report to run with an
> unselected parameter?
> Thanks for any help
> Ken
>

Report Parameter to return a "NULL" value

All
I am writing a report by salesperson and have the salespersons name as
a report Parameter however in the database it can return a NULL value
is the sale was genereated by someone other than the sales team. In
order for me to get the correct totals i need to include the NULL
value but if i select NULL it returns a blank report?
Any ideas?
cheersOn Jun 18, 3:38 am, blueboy <matt_me...@.hotmail.com> wrote:
> All
> I am writing a report by salesperson and have the salespersons name as
> a report Parameter however in the database it can return a NULL value
> is the sale was genereated by someone other than the sales team. In
> order for me to get the correct totals i need to include the NULL
> value but if i select NULL it returns a blank report?
> Any ideas?
> cheers
If I understand you correctly, have you tried using a value similar to
"Non Applicable" or some other descriptor that represents the null
value in the report parameter. That way, in the stored procedure/query
that is sourcing the report you can check for "...if @.SalesPerson ='Non Applicable' begin select * from table_x where salesperson is
null... end..." Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Report parameter dropdown

I bound a report parameter to a combobox. I tried to configure default
parameter as null several ways
in report parameters properties dialog but could not.
How can i specify default value of null for a combobox parameter? thxOn Apr 17, 2:09 pm, "nkg" <x...@.yahoo.com> wrote:
> I bound a report parameter to a combobox. I tried to configure default
> parameter as null several ways
> in report parameters properties dialog but could not.
> How can i specify default value of null for a combobox parameter? thx
If the default value of null does not work in the Report Parameters
dialog, I would suggest unioning your dataset against an empty/null
record and then use a default of the dataset and make sure that the
empty/null value is listed first. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||thx!
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1176856481.241478.187380@.b58g2000hsg.googlegroups.com...
> On Apr 17, 2:09 pm, "nkg" <x...@.yahoo.com> wrote:
>> I bound a report parameter to a combobox. I tried to configure default
>> parameter as null several ways
>> in report parameters properties dialog but could not.
>> How can i specify default value of null for a combobox parameter? thx
>
> If the default value of null does not work in the Report Parameters
> dialog, I would suggest unioning your dataset against an empty/null
> record and then use a default of the dataset and make sure that the
> empty/null value is listed first. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Apr 18, 1:51 pm, "nkg" <x...@.yahoo.com> wrote:
> thx!"EMartinez" <emartinez...@.gmail.com> wrote in message
> news:1176856481.241478.187380@.b58g2000hsg.googlegroups.com...
> > On Apr 17, 2:09 pm, "nkg" <x...@.yahoo.com> wrote:
> >> I bound a report parameter to a combobox. I tried to configure default
> >> parameter as null several ways
> >> in report parameters properties dialog but could not.
> >> How can i specify default value of null for a combobox parameter? thx
> > If the default value of null does not work in the Report Parameters
> > dialog, I would suggest unioning your dataset against an empty/null
> > record and then use a default of the dataset and make sure that the
> > empty/null value is listed first. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||"EMartinez" wrote:
> On Apr 18, 1:51 pm, "nkg" <x...@.yahoo.com> wrote:
> > thx!"EMartinez" <emartinez...@.gmail.com> wrote in message
> >
> > news:1176856481.241478.187380@.b58g2000hsg.googlegroups.com...
> >
> > > On Apr 17, 2:09 pm, "nkg" <x...@.yahoo.com> wrote:
> > >> I bound a report parameter to a combobox. I tried to configure default
> > >> parameter as null several ways
> > >> in report parameters properties dialog but could not.
> >
> > >> How can i specify default value of null for a combobox parameter? thx
> >
> > > If the default value of null does not work in the Report Parameters
> > > dialog, I would suggest unioning your dataset against an empty/null
> > > record and then use a default of the dataset and make sure that the
> > > empty/null value is listed first. Hope this helps.
> >
> > > Regards,
> >
> > > Enrique Martinez
> > > Sr. Software Consultant
>
> You're welcome. Let me know if I can be of further assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>
Instead of modifying my datasource, I chose to change the parameter. A small
function injects the null value into the selected list before filtering my
data.
here's how :
http://pderop.blogspot.com/2007/05/ssrs-multivalue-and-nulls.html
cheers

Report Parameter can be null

Hi,
I have a report parameter that can be null in some cases. Currently, in
the Report Parameters interface, I have check the 'Allow null value' option
for this parameter. When I preview the report (RS2005, SP1), the report
forces me to pick a value for this parameter before the report will open (if
I try to 'View Report', it says Please select a value for parameter, '<my
parameter>'.).
Any ideas on this? Also, it looks like I cannot allow for null values and
use multi-select on the same parameter. Do you know why this is?
Thank you for your help.
--
LaurieTHi LaurieT,
You can set the default value for your report parameter to be NULL in the
Report Parameter interface. This should allow the report to run without
forcing you to set a value for that parameter.
Brian
--
Brian Knight
Quaere SQL Server Blog
http://dba.typepad.com
"LaurieT" wrote:
> Hi,
> I have a report parameter that can be null in some cases. Currently, in
> the Report Parameters interface, I have check the 'Allow null value' option
> for this parameter. When I preview the report (RS2005, SP1), the report
> forces me to pick a value for this parameter before the report will open (if
> I try to 'View Report', it says Please select a value for parameter, '<my
> parameter>'.).
> Any ideas on this? Also, it looks like I cannot allow for null values and
> use multi-select on the same parameter. Do you know why this is?
> Thank you for your help.
> --
> LaurieT|||In the Report Parameter interface, I selected the option of 'Null' for
Default values, but it still forces me to pick a value for my parameter.
When I check the box that says that my parameter can have a Null value, I
expected the parameter drop down list to change, so that it contains a <Null>
option, but it doesn't. Initially it displays "<Select a Value>", and the
other options, but no "<Null>" or blank option. So now, I have said that the
value can be null, but how can I even pick null as a value?
I am totally confused.
--
LaurieT
"Brian Knight" wrote:
> Hi LaurieT,
> You can set the default value for your report parameter to be NULL in the
> Report Parameter interface. This should allow the report to run without
> forcing you to set a value for that parameter.
> Brian
> --
> Brian Knight
> Quaere SQL Server Blog
> http://dba.typepad.com
>
> "LaurieT" wrote:
> > Hi,
> >
> > I have a report parameter that can be null in some cases. Currently, in
> > the Report Parameters interface, I have check the 'Allow null value' option
> > for this parameter. When I preview the report (RS2005, SP1), the report
> > forces me to pick a value for this parameter before the report will open (if
> > I try to 'View Report', it says Please select a value for parameter, '<my
> > parameter>'.).
> >
> > Any ideas on this? Also, it looks like I cannot allow for null values and
> > use multi-select on the same parameter. Do you know why this is?
> >
> > Thank you for your help.
> > --
> > LaurieT

Report header expression

I have create a report with date parameters -- @.FromDate and @.ToDate.
The report requested if @.FromDate is null and @.ToDate is null, will use
GetDate()-7 and GetDate().
I have included this condition into report SQL and had no problem.
My question is how to display the dates on the report header.
For example, "Rental Date from (@.FromDate or GetDate()) to (@.ToDate or
GetDate())".
Thanks a lot for any help.
JeanneOn Dec 28, 10:25 pm, "JeanneZhang" <u39996@.uwe> wrote:
> I have create a report with date parameters -- @.FromDate and @.ToDate.
> The report requested if @.FromDate is null and @.ToDate is null, will use
> GetDate()-7 and GetDate().
> I have included this condition into report SQL and had no problem.
> My question is how to display the dates on the report header.
> For example, "Rental Date from (@.FromDate or GetDate()) to (@.ToDate or
> GetDate())".
> Thanks a lot for any help.
> Jeanne
In terms of using an expression to show the conditions, something like
this should help.
="Rental Date from " + iif(Parameters!FromDate.Value Is Nothing and
Parameters!ToDate.Value Is Nothing, DateAdd("d", -7,
Now()).ToString(), Parameters!FromDate.Value.ToString()) + " to " +
iif(Parameters!FromDate.Value Is Nothing and Parameters!
ToDate.Value Is Nothing, Now().ToString(), Parameters!
ToDate.Value.ToString())
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant