Parameters question

I am trying to create a set of parameters that will alow me to select data
either based on criteria from dropdown boxes or from a text box. In my
DataSet I have something like this (simpified)
if @.ItemDesc is null
select citemno, cdescript, 0.0 as price from icitem
where ....
else
select citemno, cdescript, 0.0 as price from icitem
where cdescript like @.ItemDesc
@.ItemDesc is a value from a text box. When I run the report it keeps
prompting me to enter a value into the first dropdown.
Thanks for help.On Jun 26, 7:44 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> I am trying to create a set of parameters that will alow me to select data
> either based on criteria from dropdown boxes or from a text box. In my
> DataSet I have something like this (simpified)
> if @.ItemDesc is null
> select citemno, cdescript, 0.0 as price from icitem
> where ....
> else
> select citemno, cdescript, 0.0 as price from icitem
> where cdescript like @.ItemDesc
> @.ItemDesc is a value from a text box. When I run the report it keeps
> prompting me to enter a value into the first dropdown.
> Thanks for help.
You could have a default value in the drop-down box of an empty
string. That should avoid the prompt, etc. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||And to do that I need to select default values non-queried?
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1182908600.505364.27740@.g4g2000hsf.googlegroups.com...
> On Jun 26, 7:44 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> I am trying to create a set of parameters that will alow me to select
>> data
>> either based on criteria from dropdown boxes or from a text box. In my
>> DataSet I have something like this (simpified)
>> if @.ItemDesc is null
>> select citemno, cdescript, 0.0 as price from icitem
>> where ....
>> else
>> select citemno, cdescript, 0.0 as price from icitem
>> where cdescript like @.ItemDesc
>> @.ItemDesc is a value from a text box. When I run the report it keeps
>> prompting me to enter a value into the first dropdown.
>> Thanks for help.
>
> You could have a default value in the drop-down box of an empty
> string. That should avoid the prompt, etc. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On Jun 27, 6:37 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> And to do that I need to select default values non-queried?
> "EMartinez" <emartinez...@.gmail.com> wrote in message
> news:1182908600.505364.27740@.g4g2000hsf.googlegroups.com...
> > On Jun 26, 7:44 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
> >> I am trying to create a set of parameters that will alow me to select
> >> data
> >> either based on criteria from dropdown boxes or from a text box. In my
> >> DataSet I have something like this (simpified)
> >> if @.ItemDesc is null
> >> select citemno, cdescript, 0.0 as price from icitem
> >> where ....
> >> else
> >> select citemno, cdescript, 0.0 as price from icitem
> >> where cdescript like @.ItemDesc
> >> @.ItemDesc is a value from a text box. When I run the report it keeps
> >> prompting me to enter a value into the first dropdown.
> >> Thanks for help.
> > You could have a default value in the drop-down box of an empty
> > string. That should avoid the prompt, etc. Hope this helps.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant
If you want to set the default value to an empty string in the stored
procedure/query that sources the parameter (and order it in the
dataset to show the empty string first) then you can set it as 'from
query.' If you want to just use null and check for the null in the
stored procedure/query then you can set it to non-queried. It depends
on where you want to set the default value. Hope this clarifies it
better.
Regards,
Enrique Martinez
Sr. Software Consultant|||I am setting it up to a non-queried value and I am entering ="" into a
field.
Still asks me to enter parameter when I run it.
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1182954533.677728.17440@.u2g2000hsc.googlegroups.com...
> On Jun 27, 6:37 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> And to do that I need to select default values non-queried?
>> "EMartinez" <emartinez...@.gmail.com> wrote in message
>> news:1182908600.505364.27740@.g4g2000hsf.googlegroups.com...
>> > On Jun 26, 7:44 am, "Mark Goldin" <mgol...@.ufandd.com> wrote:
>> >> I am trying to create a set of parameters that will alow me to select
>> >> data
>> >> either based on criteria from dropdown boxes or from a text box. In my
>> >> DataSet I have something like this (simpified)
>> >> if @.ItemDesc is null
>> >> select citemno, cdescript, 0.0 as price from icitem
>> >> where ....
>> >> else
>> >> select citemno, cdescript, 0.0 as price from icitem
>> >> where cdescript like @.ItemDesc
>> >> @.ItemDesc is a value from a text box. When I run the report it keeps
>> >> prompting me to enter a value into the first dropdown.
>> >> Thanks for help.
>> > You could have a default value in the drop-down box of an empty
>> > string. That should avoid the prompt, etc. Hope this helps.
>> > Regards,
>> > Enrique Martinez
>> > Sr. Software Consultant
>
> If you want to set the default value to an empty string in the stored
> procedure/query that sources the parameter (and order it in the
> dataset to show the empty string first) then you can set it as 'from
> query.' If you want to just use null and check for the null in the
> stored procedure/query then you can set it to non-queried. It depends
> on where you want to set the default value. Hope this clarifies it
> better.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

Parameters Dropdown, How to add a "all" feature

Hi everyone.
I have done SRS for 3 years and have never run across this issue until
today.
I have a simple report.
It takes a parameter called "SalesPerson" (select fullname from
sales_contact)
I use this parameter to get my data back.
ie (select * from sales where salescontact = @.SalesPerson)
That works great.
My boss wants me to add an "All Sales People" item as the 1st in the
dropdown list.
I'm not sure the best way to implement this.
I don't want to make a duplicate report as that is wasteful.
Thanks, JonHi Jon,
Use UNION in your "SalesPerson" dataset.
SELECT 'All Sales People' as fullname , 0 as sort
UNION
SELECT fullname ,1 as sort
FROM sales_contact
ORDER by sort
The sort allows you to bring the 'All Sales People' on top of the list
In your main SQL you need to do a case statement
select * from sales where salescontact = CASE WHEN @.SalesPerson = 'All
Sales People' THEN salescontact ELSE @.SalesPerson END )
hope this helps
On Jan 26, 8:41 am, jonknutsonh...@.yahoo.com wrote:
> Hi everyone.
> I have done SRS for 3 years and have never run across this issue until
> today.
> I have a simple report.
> It takes a parameter called "SalesPerson" (select fullname from
> sales_contact)
> I use this parameter to get my data back.
> ie (select * from sales where salescontact = @.SalesPerson)
> That works great.
> My boss wants me to add an "All Sales People" item as the 1st in the
> dropdown list.
> I'm not sure the best way to implement this.
> I don't want to make a duplicate report as that is wasteful.
> Thanks, Jon|||Are you using 2005? then you can use multi select, so atleast not "All sales
person", you get a word "ALL" on top of the list
so I think that should be ok with you for the selection.. accordingly you
need to change by just including "IN (@.salesperson) in your sql query.
Amarnath
"jonknutsonhome@.yahoo.com" wrote:
> Hi everyone.
> I have done SRS for 3 years and have never run across this issue until
> today.
> I have a simple report.
> It takes a parameter called "SalesPerson" (select fullname from
> sales_contact)
> I use this parameter to get my data back.
> ie (select * from sales where salescontact = @.SalesPerson)
> That works great.
> My boss wants me to add an "All Sales People" item as the 1st in the
> dropdown list.
> I'm not sure the best way to implement this.
> I don't want to make a duplicate report as that is wasteful.
>
> Thanks, Jon
>

Parameters are disabled

I've set three parameters in the reports. However, when it is deployed, the
three parameters (textbox & dropdown list) become disabled? Is there
anything that need to set to make it enable?
Please advise.On Dec 2, 10:12 pm, YeungTakYan
<YeungTak...@.discussions.microsoft.com> wrote:
> I've set three parameters in the reports. However, when it is deployed, the
> three parameters (textbox & dropdown list) become disabled? Is there
> anything that need to set to make it enable?
> Please advise.
Are you using a different datasource for the parameters than you are
for the report itself? If so, make sure that the datasource is
available in the Report Manager on the production server. Also, by
default, the datasource for the report when it is first deployed is
not set. You normally set it by selecting the Properties tab (I
believe it is) and then select Datasources and select the datasource
from the top. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Parameters and Horizontal Scroll Bar

I am experiencing a problem whereas I have a parameter dropdown that is
dynamically populated. In the case there is only one value, the horizontal
scroll bar is covering up that value and the user cannot see it to select it.
Are there other workaround other than coding for a "dummy" value in the list?On Jan 15, 11:18 am, Chriss G <Chri...@.discussions.microsoft.com>
wrote:
> I am experiencing a problem whereas I have a parameter dropdown that is
> dynamically populated. In the case there is only one value, the horizontal
> scroll bar is covering up that value and the user cannot see it to select it.
> Are there other workaround other than coding for a "dummy" value in the list?
As far as I know, there is not. A work around would be to create a
custom ASP.NET application that gives some flexibility in the sizing
of the web controls and then call SSRS via the RS web service or URL
access. Sorry that I could not be of greater assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Parameters - Dropdown list value to lookup values and pass to sp

I have a simple report where I want to have a dropdown list box that is
populated from a sql query (no problems here).
Then I want to take the value from the selected parameter to lookup another
value in a query, then pass the returned value to a stored procedure as a
parameter.
So far, I have not been successful, does anybody have a clue how to do this?
Thanks, MikeMichael,
You will need 2 datasets (2 parameters) and 2 Report Parameters (Report
Menu). First parameter (that you already have) will fetch one set of list. In
the third (main query that will fetch the reporting data) will be queried
like below:
"
Select Count(AccountNumber) as Dis, PrimaryCareProviderId ,
PrimaryCareProviderName,InpatientServiceName,Left(DateName(Month,Dischargedatetime),3) as Mon
From dbo.Portal_PrvVolumes Where Dischargedatetime >= '07/01/2004' and
dischargedatetime < '12/01/2004'
and
primarycareproviderid = @.PrimaryCareProviderId (FIRST PARAMETER)
and InpatientServiceName = @.PrvService (SECOND PARAMETER)
"
Then create the second dataset for second parameter list.
Now create 2 report parameters...
I hope this helps...
"Michael Morisoli" wrote:
> I have a simple report where I want to have a dropdown list box that is
> populated from a sql query (no problems here).
>
> Then I want to take the value from the selected parameter to lookup another
> value in a query, then pass the returned value to a stored procedure as a
> parameter.
>
>
> So far, I have not been successful, does anybody have a clue how to do this?
>
> Thanks, Mike
>
>
>