Pivot dropdown list

2 posts, 0 answers
  1. hema
    hema avatar
    1 posts
    Member since:
    Feb 2015

    Posted 05 Mar 2015 Link to this post

    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.

  2. Stef
    Admin
    Stef avatar
    3036 posts

    Posted 10 Mar 2015 Link to this post

    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
     

    Check out the Telerik Platform - the only platform that combines a rich set of UI tools with powerful cloud services to develop web, hybrid and native mobile apps.

     
  3. DevCraft banner
Back to Top