Pivot dropdown list

[hema]

I'm creating a report where user will select the application type as soon as the user selects application type a country list (multi selection drop down list) is pouplated once the user selects the countries from the multi list I want the report to display the selected countries as columns to the report.
I tried to create a stored procedure but unable to Please help me its really aurgent.

[Stef]

Hi Hema,

The running value of a multivalued report parameter is an array of objects, that has to be handled accordingly in the filter or data-retrieval method.
My suggestion is:

• Use cascading parameters to allow users to select application, based on which countries are populated - How to: Cascading Parameters with applied filtering on data source level;
• Pass the multivalued report parameters to the stored procedure as follows:
  • To pass multiple values as a single argument to a stored procedure, you can set the argument as an nvarchar, which will contain the coma-separated values:
    

    USE AdventureWorks
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    -- FUNCTION TO SPLIT STRINGS.
    CREATE FUNCTION splitstring
    ( @stringToSplit VARCHAR(MAX) )
    RETURNS
     @returnList TABLE ([Name] [nvarchar] (500))
    AS
    BEGIN
    
     DECLARE @name NVARCHAR(255)
     DECLARE @pos INT
    
     WHILE CHARINDEX(',', @stringToSplit) > 0
     BEGIN
      SELECT @pos  = CHARINDEX(',', @stringToSplit)
      SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
    
      INSERT INTO @returnList
      SELECT @name
    
      SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
     END
    
     INSERT INTO @returnList
     SELECT @stringToSplit
    
     RETURN
    END
    GO
    
    -- STORED PROCEDURE.
    CREATE PROCEDURE UseMultiValues
        @SelectedValues nvarchar(150) = NULL
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;
    
       select * from HumanResources.Department
       where (@SelectedValues IS NULL) OR DepartmentID IN (select [Name] from dbo.splitstring(@SelectedValues))
    END
    GO

    This is an example of stored procedure only.
    
    
  • Add a SqlDataSource and configure it as in the SqlDataSource Wizard. At step 4 map the SQL parameter to the report parameter via expression e.g.:
    

    = IIf(Parameters.SelectedValues.Value Is Null, Null,Join(',',Parameters.SelectedValues.Value))

I hope the provided information is helpful.

Regards,
Stef
Telerik