Reporting Services 2005 – Dynamic Parameter List

I have seen developers writing reports in Reporting Services and taking a lot of time adding a section to the report to list all the parameter selection(s) to be displayed in the report. To address this issue dynamically (my way) you will need your reports deployed to the reporting services server, and have access to the reporting services database. Here you will find some template reports which include the Dynamic Parameter List (http://www.box.net/shared/zi7m4z62tg). Reporting services stores everything about the report in the database back-end. Gain access to the back-end and you will be able to do all sorts of things. For instance maybe create a report of your complete catalog of reports or even write some report metrics by looking at the report Execution log.

Here is my Query for obtaining the report parameter information, from the reporting services back-end:
SELECT
  [Order] = ROW_NUMBER() OVER (ORDER BY tmp.Name),
  ParameterName = Params.p.value('Name[1]','varchar(255)'),
  ParameterLabel = Params.p.value('Prompt[1]','varchar(255)'),
  DefaultValue = Params.p.query('DefaultValues/Value'),
  ParameterXml = Params.p.query('.'),
  ReportName = tmp.Name,
  ReportPath = tmp.Path
FROM
  (SELECT Name, Path, ReportParams = CONVERT(XML,Parameter) FROM dbo.Catalog WHERE Path = @ReportPath) tmp
  CROSS APPLY ReportParams.nodes('(Parameters/Parameter)') AS Params(p)
WHERE NOT Params.p.value('Name[1]','varchar(255)') IN ('ReportPath','ReportNumber')

The parameter “@ReportPath” is defaulted to “=Globals!ReportFolder + “/” + Globals!ReportName” this is how the report is referenced in the catalog.

Now that I have the parameter collection, I have a matrix control at the top of the report to display the parameters.  I am using some custom code to get my parameters to be displayed horizontally in a pre-determined number of columns:

Shared Function SetColumn(ByVal row As Integer, ByVal column As Integer) As String
  While row > column
    If column >= row Then Exit While
    row = row - column
  End While
Return row
End Function
Shared Function SetRow(ByVal row As Integer, ByVal column As Integer) As String
  Dim i As Integer = 1
  While row > column
    i = i + 1
    If column >= row Then Exit While
    row = row - column
  End While
  Return i
End Function

 
I group my rows using the two methods mentioned above, for instance if i want a 2 column list the columns group contains expression “=Code.SetColumn(Fields!Order.Value,2)” and the rows group contains expression “=Code.SetRow(Fields!Order.Value,2)”

 

The Templates in the zip at http://www.box.net/shared/zi7m4z62tg contain multiple features:

  • Horizontal Record display
  • Using Images in Page Header without causing Merged Cell issues in Excel
  • Templates for Landscape and Portrait you can build from
  • Dynamic Parameter List
  • Using Reporting Services database to your advantage
  • Using Custom Code in RDLs
  • Using Parent Groups and Padding based on levels
  • Using rectangles to create Page breaks so different section of the report when exported to excel display on separate worksheets
Advertisements
    • felix
    • October 22nd, 2012

    Ryan, really liked your approach of creating dynamic SSRS template and parameter display.
    One question though, multivalue parameters are displayed nicely. The issue i have is to display single value parameter.
    I even tried to modify the statement to display NOTHING and still goves me #error.
    =IIf(Parameters(Fields!ParameterName.Value).IsMultiValue,
    Join(Parameters(Fields!ParameterName.Value).Label, “,”),””)

    What do you think can be a problem.

    Thanks
    Felix

  1. What version of SSRS are you using? I will play around with it and let you know what I find out.

    Thank you,
    Ryan Duclos

    • felix
    • October 24th, 2012

    Thanks Ryan for reply.

    SSRS 2008 r2.

    Interestingly single parameter can be displayed with
    Parameters(Fields!ParameterName.Value).Label)

    I also tried added MultiValue to Parameters dataset as
    MultiValue = Params.p.value(‘MultiValue[1]’,’varchar(255)’)
    and then used it in IIF but still didn’t have any success.

    At the end I have 2 text boxes, one single parameter and other for multivalue. However multivalue box still produces #error if single parameter is used.

    Thank you

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: