Posts Tagged ‘ Custom Code ’

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