Home Forums SQL Server 2005 Business Intelligence How to SetItemParameters for a report in SSRS 2008 using web services through SSIS package Script Task RE: How to SetItemParameters for a report in SSRS 2008 using web services through SSIS package Script Task

  • THANK YOU SO Much Ravi -- it totally worked! :-)I was able to assign value to the first parameter of a (freshly created) Linked Report and then 'hide' this parameter so that when user pull this report they can only select form 2nd parameter onward --

    ......

    parameters[0].DefaultValue[0] = "My_dbName_1";

    parameters[0].PromptUser = false;

    rs.SetItemParameters(report, parameters);

    This is a critical step for our solution since we have over 20 different SSRS reports and against 10 clients -- each client is a database on the same SQL Server engine (My_dbName_1, My_dbName_2 ....with Identical Schema) so we've created the 20 reports in a generic way by having DB Name as very first parameter and deploy to a "Master" location on report server, and then, using the "Script Task" in a SSIS package via Web Service we create a report folder for each client and then generate 20 "Linked Reports" for each DB from 'master" to that folder -- the last step would be assign the dbName as default value for all reports for each client/database so that user can't see other client's data (security controlled by URL permission) ..... Now with your help our solution is completely automated 🙂 Thanks again!

    This pretty heavy stuff for a Sybase/SQL Server DBA 😎 But I'm glad it worked !!