Post reply

Default parameter values

  • March 10, 2005 at 12:05 pm

    #152359

    I need a report which has a parameter which displays a list of customers. User wishes either to report on one customer or on all customers. I've got the list of customers from which the user can select but how do I give him the option to select all customers

  • March 10, 2005 at 12:16 pm

    #546458

    create proc dbo.SearchMyCustomers @Cust_id as int = null

    as

    set nocount on

    select * from dbo.Customers C /*may need to inner join to your list of preselected customers here*/ where C.Cust_id = @Cust_id or @Cust_id is null

    set nocount off

  • March 11, 2005 at 1:19 am

    #546553

    Hi, I am newbie to reporting! After creating SP, what's next?

  • March 11, 2005 at 6:24 am

    #546595

    I've never used the reporting services. But in access what you have to do is put dbo.MyStoredProc as the source of the report, then fill the inputparameters box with the right values. I have no idea on what is the best method to accomplish that in reporting services (assuming it's the same steps).

  • March 11, 2005 at 9:31 am

    #546669

    You need to union on option for ' all customers'.

    example:

    parameter dataset:

    SELECT     CustomerName

    FROM         Customer

    UNION

    SELECT     ' All Customers'

    OR

    SELECT     CONVERT(varchar(25), CustomerID) AS CustomerID, CustomerName

    FROM         Customer

    UNION

    SELECT     ' All Customers', ' All Customers'

    (I add the space in front so that it will be on the top of the list.)

     

    Then, in the report dataset:

    add WHERE (CustomerName = @Customer OR @Customer = ' All Customers')

     

    Hope that helps. 

  • March 12, 2005 at 4:20 am

    #546762

    Thanks for the help - I used the last reply and it worked fine. It will be something I will need to use frequently

Viewing 6 posts - 1 through 5 (of 5 total)

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