Post reply

Parameter Selection

  • 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

     

  • September 7, 2006 at 9:32 pm

    #659252

    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

     

  • September 8, 2006 at 4:19 am

    #659302

    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

     

  • September 8, 2006 at 6:29 am

    #659324

    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.

  • September 8, 2006 at 7:22 am

    #659334

    Hi David

    I have 2000 for reporting services and VB.net 2003

    Regards

    Vandy

  • September 8, 2006 at 8:56 am

    #659362

    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

  • September 8, 2006 at 9:51 am

    #659378

    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..

  • September 8, 2006 at 10:32 am

    #659399

    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.

  • September 8, 2006 at 10:56 am

    #659405

    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.

  • September 8, 2006 at 11:11 am

    #659411

    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

  • September 8, 2006 at 1:46 pm

    #659448

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

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

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