none
Filters vs Parameters

    Question

  • I have a large database with > 1000 customer ID #'s and demographics.

    At times, I need to pull demograpics for specific customer ID.  It maybe 1 coustomer ID or sometimes in maybe up to 4 customer id# in one report

    Some customer ID #'s have subgroup ID #'s as well

    I would like to create a report where I can manually enter mutliple ID # to pull the demographics

    Example

    Customer ID # 12456

    Customer ID # 2468

    How can I accomplish this?

    Thank you in advance

    Thursday, August 14, 2014 3:36 PM

Answers

  • Hi MsLi,

    According to your description, you want to create a report, then you can enter multiple ID # manually to select corresponding customers. If so, we need to create a function to split parameter values. Then when we enter multiple IDs, we need to split ID by commas. For detail information, please refer to the following steps:

    1. Create a function dbo.fnSplit using the query in SQL Server Management Studio:

    Create FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000) -- List of delimited items
    , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
    ) RETURNS @List TABLE (item VARCHAR(8000))
    
    BEGIN
    DECLARE @sItem VARCHAR(8000)
    WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
    BEGIN
        SELECT
         @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX
          (@sDelimiter,@sInputList,0)-1))),  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX
          (@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
    
      IF LEN(@sItem) > 0
        INSERT INTO @List SELECT @sItem
      END
    
    IF LEN(@sInputList) > 0
         INSERT INTO @List SELECT @sInputList -- Put the last item in
         RETURN
      END
    GO
    

    2. Create a Dataset used to retrieve data, and the parameter will be created manually. Please refer to the query below:

    select demographics from table_name where customer_id in (select * from dbo.fnSplit(@ParameterName, ','))

    If you have any questions, please feel free to let me know.

    Best Regards,
    Wendy Fu

    Friday, August 15, 2014 8:45 AM
    Moderator

All replies

  • Hi,

    add new parameter when you rendering the report it will prompt the user to enter the value.


    http://gnanadurai.blogspot.in/

    Thursday, August 14, 2014 4:10 PM
  • This is actually more complicated than just creating a report. You will first need a query that will generate data set and this query will most likely need to contain a clause:

    WHERE [Customer ID] IN (12456, 2468, 33444, 54404)

    Problem is - "IN" clause cannot be parametrized in a manner similar to, say, "=" clause where you could say:

    WHERE [Customer ID] = @Customer1ID

    You will need to dynamically create query string in your code that calls the query/stored proc. Or you can generate a query inside the stored proc itself. Example for C# code is here: http://stackoverflow.com/questions/337704/parameterize-a-sql-in-clause but the same thing can be achieved in T-SQL within a stored procedure.
    Thursday, August 14, 2014 6:47 PM
  • Hi MsLi,

    According to your description, you want to create a report, then you can enter multiple ID # manually to select corresponding customers. If so, we need to create a function to split parameter values. Then when we enter multiple IDs, we need to split ID by commas. For detail information, please refer to the following steps:

    1. Create a function dbo.fnSplit using the query in SQL Server Management Studio:

    Create FUNCTION dbo.fnSplit(
    @sInputList VARCHAR(8000) -- List of delimited items
    , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
    ) RETURNS @List TABLE (item VARCHAR(8000))
    
    BEGIN
    DECLARE @sItem VARCHAR(8000)
    WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
    BEGIN
        SELECT
         @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX
          (@sDelimiter,@sInputList,0)-1))),  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX
          (@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
    
      IF LEN(@sItem) > 0
        INSERT INTO @List SELECT @sItem
      END
    
    IF LEN(@sInputList) > 0
         INSERT INTO @List SELECT @sInputList -- Put the last item in
         RETURN
      END
    GO
    

    2. Create a Dataset used to retrieve data, and the parameter will be created manually. Please refer to the query below:

    select demographics from table_name where customer_id in (select * from dbo.fnSplit(@ParameterName, ','))

    If you have any questions, please feel free to let me know.

    Best Regards,
    Wendy Fu

    Friday, August 15, 2014 8:45 AM
    Moderator