Altering Reporting Services Report Parameters as Hidden Automatically for a Report Folder
Microsoft SQL Server 2000 Reporting Services has many nice features although it was the first version for developers to try and get use of it in their applications. Despite its many enhanced properties, SQL Server 2k RS had the hidden parameters for a report as a problem for report designers and developers. Because you had to work manually after you deploy your reports on test or production sites in order to hide report parameters from report consumers. Because there was not a property for a report parameter to mark it as hidden. You had to open the properties tab of the report on the report server. Then you had to clear the "Prompt" textbox in order to prevent the Reporting Services to display the parameter input area on the report screen.
But with the SQL Server 2005 Reporting Services, there is a new opportunity for report designers or programmers to mark the parameter as hidden and prevent the parameter to be displayed on the displayed user screen.
Now, consider the case which I experienced for over than a hundred reports and its parameters reaching approximately 600 or more. It is not wise to open each report, and open the report properties menu item and click on every report parameter and then on the report parameter properties section make sure that the hidden checkbox is selected.
I realized by viewing the code of an .rdl report, the hidden property is given to a parameter by adding the following xml code fragment between the <ReportParameter>, </ReportParameter> xml tags.
<Hidden>true</Hidden>
So, I coded the below application in which I opened every reporting services code file (.rdl) and navigate through each ReportParameter xml sections and added the "<Hidden>true</Hidden>" text to the inner xml of the ReportParameter.
Below is a screen shot of the sample application which I have developed for this process.
All you have to do is pointing to the physical folder where the checked out reporting services project files exist. If you do not keep the Reporting Services project in a Visual SourceSafe environment then of course you do not check out them. After you set the folder for .rdl files the application begins searching for <ReportParameter> tags and adds if <Hidden> does not exists.
I placed a FolderBrowserDialog control to select the windows folder via GUI.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim folder As New Windows.Forms.DialogResult
folder = FolderBrowserDialog1.ShowDialog()
If folder = Windows.Forms.DialogResult.OK Then
TextBox1.Text = FolderBrowserDialog1.SelectedPath
FillCheckedListBox()
End If
End Sub
And a CheckedListBox is used to display the list of successfull and failed processed .rdl files.
The list of files or the file names collection can be retrieved by using the Directory.GetFiles(path as string) class method of the System.IO name space.
Dim Files As String() = Directory.GetFiles(FolderBrowserDialog1.SelectedPath)
It is better that you include the System.IO and System.XML namespaces by using the Imports
Imports System.IO
Imports System.Xml
Also if you examine the code, or as you may guess, not to process every file that are not .rdl files, I checked the file extensions by using the following line of code with the help of Path.GetExtension(path as string) class method of System.IO namespace.
If Path.GetExtension(Filename) = ".rdl" Then
Private Sub FillCheckedListBox()
CheckedListBox1.CheckOnClick = True
Dim Files As String() = Directory.GetFiles(FolderBrowserDialog1.SelectedPath)
Dim Filename As String
Me.Cursor = Cursors.WaitCursor
For Each Filename In Files
If Path.GetExtension(Filename) = ".rdl" Then
Try
replace(Filename)
CheckedListBox1.Items.Add(Filename, True)
Catch ex As Exception
CheckedListBox1.Items.Add(Filename, False)
End Try
End If
Next
Me.Cursor = Cursors.Default
End Sub
The main procedure is the XML editing section of the application which I have also copied down below:
Private Sub replace(ByVal filePath As String)
Dim add As Boolean = False
If filePath = String.Empty Then
Exit Sub
End If
Dim doc As XmlDocument = New XmlDocument()
Try
doc.Load(filePath)
Catch ex As Exception
Throw New Exception(ex.Message, ex.InnerException)
Exit Sub
End Try
Dim node As XmlNode
Dim node2 As XmlNode
Dim node3 As XmlNode
Dim nodeList As XmlNodeList
Dim nodeList2 As XmlNodeList
Dim nodeList3 As XmlNodeList
Dim root As XmlNode = doc.DocumentElement
nodeList = root.ChildNodes
For Each node In nodeList
If node.Name = "ReportParameters" Then
nodeList2 = node.ChildNodes
For Each node2 In nodeList2 ' ReportParameter Collection
nodeList3 = node2.ChildNodes
For Each node3 In nodeList3 ' Parameter Properties Collection
add = True
If node3.Name = "Hidden" Then
add = False
Exit For
End If
Next
If add Then
node2.InnerXml = node2.InnerXml & "<Hidden>true</Hidden>"
End If
'node2.AppendChild(element)
Next
End If
Next
doc.Save(filePath)
End Sub
One important note here is about the structure of the .rdl files.
Each reporting services code file has an xml declaration just like
<?xml version="1.0" encoding="utf-8"?>
Then <Report> tag is the root tag of the XML document. <Report> root tag includes <ReportParameters> tag as well as many other tags related with other aspects of the report data, design and display.
<ReportParameters> tag has one <ReportParameter> tag for each parameter declared in the report.
Here is a sample from a report definition file before and after it is edited.
Before Hidden tag is added | After Hidden tag is added |
<ReportParameter Name="Brand"> <DataType>String</DataType> <AllowBlank>true</AllowBlank> <Prompt>Brand</Prompt> </ReportParameter> |
<ReportParameter Name="Brand"> <DataType>String</DataType> <AllowBlank>true</AllowBlank> <Prompt>Brand</Prompt> <Hidden>true</Hidden> </ReportParameter> |
You can download the sample application and the codes of the application from the Files section of the site at link :Microsoft Reporting Services Hidden Parameters Project download link
Hello, if you can not download the source code files for the sample .NET project, you can use this link to download Reporting Services Hidden Report Parameters sample project download link.