Report Parameter From Query

Question

Report Parameter From Query

2Tall

SSChampion

Points: 14644
More actions
September 4, 2008 at 6:24 am

#382520

Hi I have added a parameter to a report that's values are based on another query (Supplier List).
--Dataset 1 has a following
WHERE (@Supplier = SupplierId)
--Dataset 2
SELECT SupplierId, SupplierName
FROM Suppliers
The report parameter configured as follows:
Parameter:
Supplier
Properties:
Name = Supplier
Data Type = String
Prompt = Supplier
Allow blank value = true
from Query
Dataset = Dataset2
Value Field = SupplierId
Label Field = SupplierName
The report runs OK. However I have no control over the number of rows initially displayed to the user (pre scroll) in the list box or the list box width? Also if I enter L it will display the first record of all Suppliers starting with L. However if I type LO it displays the first record of Suppliers starting with O? Any way to make the search a little smarter?
I hope that all makes sense 🙂
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

SSRS does not give you control over those properties in parameter lists.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

2Tall SSChampion Points: 14644 More actions · Answer 2

Thanks for the response Jack. To digress slightly. I have been asked to modify the query begind the parameter. The Parameter List should only display Suppliers whose SupplierId does not begin with a numerical value. The reason is the company list all internal employees as Suppliers for accounting purposes (these all begin with a numerical value) all external Suppliers begin with a Alpha character. Because of other dodgy test data the query currently looks like the following:

SELECT SupplierId, SupplierName

FROM Suppliers

WHERE SupplierName NOT LIKE '*%' AND SupplierName NOT LIKE '%DO NOT USE%'

ORDER BY SupplierName

I am aware this post is now more query based, should I re-post elswhere?

Many Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

Phil,

I think you posted in the right forum as you did have several SSRS type questions. I can say that I feel your pain as far as the dodgy test data and using a part of the supplier id to determine the supplier type. In an ideal world you would have a supplier type that would include values like Internal, External, Test and you could exclude based on that.

The only recommendations I would make would be to try to find a way to get the correct data without using NOT LIKE. The reason being that using NOT tends to limit the use of indexes so your performance will suffer. Something like:

SELECT

SupplierId,

SupplierName

FROM

Suppliers

WHERE

SupplierName LIKE '[A-Z]%' AND

SupplierName NOT LIKE '%DO NOT USE%'

ORDER BY

SupplierName

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

2Tall SSChampion Points: 14644 More actions · Answer 4

Thanks Jack. I was just thinking the other day that I read some time back that some conditional selects work more efficiently than others. Just could not remember the best approach!

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Brian Burke Mr or Mrs. 500 Points: 590 More actions · Answer 5

I've found that you can usually get a little bit more control over Reporting Services reports when you add them to a web page via the Reportviewer control.

Not sure if this is relevant here, as you may not want to go down the .aspx route for this, but you could...

Create an .aspx page with a DropDownlist (DDVar in this example) populated by an SQLDatasource equivalent to the select query in your report.

Add a reportviewer control (ReportViewer1) and point it to the relevant report on the server.

In the _SelectedIndexChanged event handler for the dropdown,

(example code...you'll need to modify it but you'll get the idea)

Dim P As New Microsoft.Reporting.WebForms.ReportParameter

Dim I(0) As Microsoft.Reporting.WebForms.ReportParameter

P.Name = "Customer" **** or whatever your parameter is called

P.Values.Add(Trim(DDVar.SelectedValue))

I(0) = P

ReportViewer1.ServerReport.SetParameters(I)

If your select query is sorted correctly (and you're using IE7), you'll be able to type the first few chars of the customer name.

Note I(0) is an array, you can add 2 params by defining as I(1) and so on...

2Tall SSChampion Points: 14644 More actions · Answer 6

Hi Brian. I am using MS Visual Web Developer 2008. I tried adding the reportviewer tool to the toolbox but it was not listed? I have posted another report related question

http://www.sqlservercentral.com/Forums/Topic565722-1063-1.aspx

I thought I could make use the reportviewer tool, but not if I can not install it! 🙂

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Brian Burke Mr or Mrs. 500 Points: 590 More actions · Answer 7

Hi Phil,

I'm using VS2005 which has the control intgrated into it.

For VWD you may need to download the redist package from

http://www.microsoft.com/downloads/details.aspx?FamilyID=cc96c246-61e5-4d9e-bb5f-416d75a1b9ef&DisplayLang=en

I think the above needs .net 3.5 to be installed first

2Tall SSChampion Points: 14644 More actions · Answer 8

Thanks Brian. Does 3.5 need to be on the server running IIS or my local deve;opment machine?

Many Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Brian Burke Mr or Mrs. 500 Points: 590 More actions · Answer 9

Brian Burke

Mr or Mrs. 500

Points: 590

September 12, 2008 at 10:18 am

#871129

Just the local machine

2Tall SSChampion Points: 14644 More actions · Answer 10

Thanks.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper