SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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.

Reporting Services

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.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.