Parameter Selection

Question

Parameter Selection

Vandy

SSCommitted

Points: 1827
More actions
September 7, 2006 at 3:52 am

#172471

Hi All
I have report more then one parameter.
eg First you can select one Team then Salesperson and then particular customer.
For this i made different datasets. It's working fine but i want to make it flexible so that user can select upto any level, at present it is working only when you select all selections.
Regards

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

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

Yaniv Mor SSC Eights! Points: 936 More actions · Answer 1

Hi Vandy,

Can you please explain a bit more on your requirements? What type of parameters do you currently have and what are you trying to achieve. It is a bit difficult to understand what exactly it is that you're after.

Regards,

Yaniv

Vandy SSCommitted Points: 1827 More actions · Answer 2

Sorry for not explaning

Actually i have Sales team like Commercial ,Retail and wholesale

Every team has some salesperson and every sales person has customers.

I am running a report which shows ordernumber, Invoice number, Sale and cost.

I have three parameters for this first one when you select team it will shows you salesperson of related to that team and then customers related to that salesperson. I do have selction of date means start date and end date. I want to change my report like at present it's showing you report of only one customer but i want that if you select only one team and after that you view the report it will show you report. Now when i selct the team and click the view report button it shows a msg box that select other grop also. I want to see report at any level.

Hope this will explain what i want to do.

Thanks

Dave Klug SSCertifiable Points: 5811 More actions · Answer 3

What version of SSRS are you running? If on 2005, are the parameter selections multi-select? Depending on this, there are ways to get around this.

Vandy SSCommitted Points: 1827 More actions · Answer 4

Hi David

I have 2000 for reporting services and VB.net 2003

Regards

Vandy

mvee Hall of Fame Points: 3634 More actions · Answer 5

I'm not sure I'm understanding you either, but what I would do is make each parameter also have an option for "Select All" (see previous posts on how to add select all, you need to do a union select to get the option), then I would set the default to select all for each parameter.

Then, in the recordsource for the report (assuming it's a stored proc), I would set the parameter to null if select all was chosen.

I know I'm not explaining myself very well. If you think this option will work for you and you can't find out how to do it from other posts, I can explain more.

Good luck, Megan

DEEPAK VODETHELA SSC Eights! Points: 953 More actions · Answer 6

Chris Hays has explained a similar scenario if you are using multi-value as a property. You can find it here : http://blogs.msdn.com/chrishays/archive/2004/07/27/AllParameterValue.aspx

Hope this helps..

Vandy SSCommitted Points: 1827 More actions · Answer 7

Thanks for reply

This is my query.

SELECT NewSalesTeam.Division, NewSalesTeam.Team, NewSalesTeam.Salesperson, Totalsale.TotalSales, YS.YesterdaySales, Noofoorders.NoOfCustomer,

Noofoorders.NoOfOrders, Noofoorders.TotalOrderLine, Totalsale.TotalSales / Noofoorders.NoOfOrders AS AvgTransaction, Totalsale.TotalCost

FROM NewSalesTeam LEFT OUTER JOIN

Noofoorders ON NewSalesTeam.Salesperson = Noofoorders.Salesperson LEFT OUTER JOIN

Totalsale ON NewSalesTeam.Salesperson = Totalsale.Salesperson LEFT OUTER JOIN

YS ON NewSalesTeam.Salesperson = YS.Salesperson

WHERE (NewSalesTeam.Team = @Team)

When i view my report it shows me drop down list of Team. after selecting one value it runs above query for that articular team. i want that if you want to view all teams then without selecting any team you can run this report.

sorry for not explaning it very nicely.

Bill Scott SSCrazy Points: 2338 More actions · Answer 8

Each report parameter has properties that tell the system if it can be Blank or Null. These are checkboxes are in the Report/Report Parameters menu from the Layout mode in Visual Studio. I've used them a time or two and they work pretty well, allowing you to skip filling in that parameter. Your query/SP would have to know what to do when they don't have values but that way you could select parameters down to the salesperson and leave the customers blank.

mvee Hall of Fame Points: 3634 More actions · Answer 9

For the parameter, use something like the following stored proc:

CREATE PROCEDURE dbo.rptGetTeam AS
SELECT     TeamID, TeamDescription
FROM         TEAM
UNION
SELECT     0, 'All Teams'
GO

Then, for the report data source, use something like the following stored proc:

CREATE PROCEDURE dbo.rptGetTeamReportInfo
@Team int
AS
SELECT     lots of fields
FROM         TEAM with joins if necessary
WHERE     (@Team = 0 OR TeamID = @Team) AND (other where constraints)
GO

Does that make sense?

-Megan

Vandy SSCommitted Points: 1827 More actions · Answer 10

I have tried that but not working. I don't have any null value in my table.