For displaying drop down list for the values of paramenters

If you want that your ssrs report show drop down list for the values of parameters insted of entering a value by hand. then first create a procedure for that paramete.
eg you have a parameter Project and you want to display all projects names in drop down list on report.
then create procedure which retrieves you all projectsID's and projectNames like:
 (select Projet.ProjectID, Projet.ProjectName from Projet)
and add this procedure as a dataset in the report

then return on report select parameter from Report Data tool bar go to its properties in general properties check the box "Allow multiple values", now click on Avaliable values option from left pane.In this option check the radio button "get values from a query" Select dataset which you have creted for that paramenter(which retrieves you id and name like i have created for Project). In "value field" select id (which you want to match with query parameters eg ProjectID)
and in "Label fields" select name (which you want to display in drop down list values. eg ProjectName) then click on ok. now run the report your report will show drop down list for the values.

Comments

  1. Muhammad Jawad30 August 2012 at 22:44

    and in the main dataset use fn_split function to handel multiple values for the parameter otherwise it will show error.And set Parameter to text from its properties.

    For example
    -- use this
    @ProjectID varchar(25)



    where
    (Project.intProjectID in (select item from fn_split(@ProjectID, ',')))

    ReplyDelete

Post a Comment

Popular posts from this blog

TSQL To Get All the Names of tables and Databases whose data have changed in last 24 Hours

-- ============================================= -- Author: Muhammad Jawad -- Create date: 2/2/2017 -- Description: To Get All the Names of tables and Databases whose data have changed in last 24 Hours -- ============================================= ALTER PROC UP_FindAllDatabaseHaveChanges AS     BEGIN         DECLARE @loopStart INT= 1;         DECLARE @looend INT;         IF OBJECT_ID('tempdb..#AllDatabaseTables') IS NOT NULL             BEGIN                 DROP TABLE #AllDatabaseTables;             END;         CREATE TABLE #AllDatabaseTables             (               ID INT IDENTITY(1, 1)                      PRIMARY KEY ,       ...
Read more

To Get All the Names of tables and Databases their data have changed in last 24 Hours And Backup those databases

-- ============================================= -- Author: Muhammad Jawad -- Create date: 2/3/2017 -- Description: To Get All the Names of tables and Databases their data have changed in last 24 Hours And Backup those databases -- ============================================= Alter PROC UP_FindAndBackupAllDatabaseHaveChanges AS     BEGIN ----**************************START: To get all the tables and database names whose data have changed in last 24 hours **************************----         DECLARE @loopStart INT= 1;         DECLARE @looend INT;         IF OBJECT_ID('tempdb..#AllDatabaseTables') IS NOT NULL             BEGIN                 DROP TABLE #AllDatabaseTables;             END;         CREATE TABLE #AllDatabaseTables             ( ...
Read more

Apply paging in procedure

@intPageId int, @intPageSize int select * from ( Select     row_number() over (order by  TableA.intID asc ) r ,     TableA.intID,tableA.hName,TableB.Address  from TableA   Join TableB on TableA.intid = TableB.intId   ) A   where A.r  between ( (@intpageid-1)*@intpagesize)+1 and ((@intpageid-1)*@intpagesize)+@intpagesize The query in bold text is for paging, and the normal text query is that which returns your records. OR SELECT * from PressRelease  order by CreatedDate desc OFFSET (@PageID-1)*@PageSize ROWS FETCH NEXT @PageSize ROWS ONLY
Read more