Dynamically re-order the columns in SQL Server reporting services

Hi All,

After posting last post I realized that I can re-use the code they have created to dynamically re-order the columns in the table. I played around a little bit and here is what I came up with.

Just name the tablix's header cells as h{Name} and the detail cells as d{Name} and pass the columns as comma separated in the string variable. This method will remove the unwanted columns and will get you the RDL file in a byte array, you can deploy this using SSRS web service end point and then render it.This will dynamically reorder the columns for you.

{Note: In my case I had to do this for the first tablix used in the report, You can tweak this to go to any of the tablix as per your requirement }

Code : 

Private Function ModifyReportDefinition(ByVal pbytReportDefinition As Byte(), ByVal pstrRequiredColumn As String) As Byte()

        Dim objMemorystream As New MemoryStream(pbytReportDefinition)

        Dim xmlReportDoc As New XmlDocument

        Dim xmlNode As xmlNode

        Dim xmlTableHeaderCells As xmlNode

        Dim xmlTableDetailCells As xmlNode

        Dim xmlTableColumns As xmlNode

        Dim xmlTableHeaderCellsNew As xmlNode

        Dim xmlTableDetailCellsNew As xmlNode

        Dim xmlTableColumnsNew As XmlNode

        Dim xmlTablixColumnHierarchy As XmlNode

        Dim xmlTablixColumnHierarchyNew As XmlNode

        Dim xmlParentNode As xmlNode

        Dim xmlCellNode As xmlNode

        Dim xmlDocNode As xmlNode

        Dim arlRequiredColumns As New ArrayList

        Dim encoding As New ASCIIEncoding

        Dim dblPageWidth As Double

        Dim dblPageHeight As Double

        Dim dblTotalWidth As Double = 0.0

        Dim intCounter As Integer = 0

        'Create an XmlNamespaceManager for resolving namespaces.

        Dim nsmgr As XmlNamespaceManager = New XmlNamespaceManager(xmlReportDoc.NameTable)

        Try

            arlRequiredColumns.AddRange(pstrRequiredColumn.Split(","))

            xmlReportDoc.Load(objMemorystream)

            'Add the namespace for Report definition

            nsmgr.AddNamespace("de", "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition")

            xmlDocNode = xmlReportDoc.DocumentElement

            xmlTableHeaderCells = xmlDocNode.SelectSingleNode("//de:Body/de:ReportItems/de:Tablix/de:TablixBody/de:TablixRows/de:TablixRow/de:TablixCells", nsmgr)

            xmlTableDetailCells = xmlDocNode.SelectSingleNode("//de:Body/de:ReportItems/de:Tablix/de:TablixBody/de:TablixRows/de:TablixRow", nsmgr)

            xmlTableColumns = xmlDocNode.SelectSingleNode("//de:Body/de:ReportItems/de:Tablix/de:TablixBody/de:TablixColumns", nsmgr)

            xmlTablixColumnHierarchy = xmlDocNode.SelectSingleNode("//de:Body/de:ReportItems/de:Tablix/de:TablixColumnHierarchy/de:TablixMembers", nsmgr)

            xmlTableDetailCells = xmlTableDetailCells.NextSibling

            xmlTableDetailCells = xmlTableDetailCells.SelectSingleNode("./de:TablixCells", nsmgr)

            xmlTableHeaderCellsNew = xmlTableHeaderCells.Clone

            xmlTableHeaderCellsNew.RemoveAll()

            xmlTableDetailCellsNew = xmlTableDetailCells.Clone

            xmlTableDetailCellsNew.RemoveAll()

            xmlTableColumnsNew = xmlTableColumns.Clone

            xmlTableColumnsNew.RemoveAll()

            xmlTablixColumnHierarchyNew = xmlTablixColumnHierarchy.Clone

            xmlTablixColumnHierarchyNew.RemoveAll()

            'Loop through and identify the required columns

            For Each pstrRequiredColumn In arlRequiredColumns

                intCounter = 0

                For Each xmlCellNode In xmlTableHeaderCells.ChildNodes

                    xmlNode = xmlCellNode.SelectSingleNode("./de:CellContents/de:Textbox", nsmgr)

                    'If the column is in the list, then add to the report.

                    If pstrRequiredColumn = xmlNode.Attributes("Name").Value.Substring(1) Then

                        xmlNode = xmlTableHeaderCells.ChildNodes.Item(0)

                        xmlTableHeaderCellsNew.AppendChild(xmlCellNode.Clone)

                        xmlNode = Nothing

                        xmlNode = xmlTablixColumnHierarchy.ChildNodes.Item(0)

                        xmlTablixColumnHierarchyNew.AppendChild(xmlNode.Clone())

                        xmlNode = Nothing

                        xmlNode = xmlTableDetailCells.ChildNodes.Item(intCounter)

                        If Not (xmlNode Is Nothing) Then

                            xmlTableDetailCellsNew.AppendChild(xmlNode.Clone)

                            xmlNode = Nothing

                            xmlNode = xmlTableColumns.ChildNodes(intCounter)

                            If Not (xmlNode Is Nothing) Then

                                dblTotalWidth = dblTotalWidth + CDec(xmlNode.SelectSingleNode("./de:Width", nsmgr).InnerText().Replace("in", ""))

                                xmlTableColumnsNew.AppendChild(xmlNode.Clone)

                            End If

                            xmlNode = Nothing

                        End If

                        Exit For

                    End If

                    intCounter = intCounter + 1

                Next xmlCellNode

            Next pstrRequiredColumn

            'Take out the existing Header node and add New and re-ordered Header node

            xmlParentNode = xmlTableHeaderCells.ParentNode

            xmlParentNode.RemoveChild(xmlTableHeaderCells)

            xmlParentNode.AppendChild(xmlTableHeaderCellsNew)

            'Take out the existing Details node and add New Details node

            xmlParentNode = xmlTableDetailCells.ParentNode

            xmlParentNode.RemoveChild(xmlTableDetailCells)

            xmlParentNode.AppendChild(xmlTableDetailCellsNew)

            xmlParentNode = xmlTableColumns.ParentNode

            'Take out the existing Table columns node and add New Table columns node

            xmlParentNode.RemoveChild(xmlTableColumns)

            xmlParentNode.AppendChild(xmlTableColumnsNew)

            xmlParentNode = xmlTablixColumnHierarchy.ParentNode

            xmlParentNode.RemoveChild(xmlTablixColumnHierarchy)

            xmlParentNode.AppendChild(xmlTablixColumnHierarchyNew)

            'Now, since we may have removed some columns, we will have to set the new width to the report

            xmlDocNode.SelectSingleNode("./de:Width", nsmgr).InnerText = dblTotalWidth & "in"

            dblPageWidth = 8.5

            dblPageHeight = 11

            'Determine if we have to use "Letter" or "Legal" sized paper.

            If dblTotalWidth > 9 Then

                dblPageWidth = 14

                dblPageHeight = 8.5

            ElseIf dblTotalWidth > 6.5 Then

                dblPageWidth = 11

                dblPageHeight = 8.5

            End If

            xmlDocNode.SelectSingleNode("./de:Page/de:PageWidth", nsmgr).InnerText = dblPageWidth & "in"

            xmlDocNode.SelectSingleNode("./de:Page/de:PageHeight", nsmgr).InnerText = dblPageHeight & "in"

            ''set width to Report Header

            xmlDocNode.SelectSingleNode("./de:Page/de:PageHeader/de:ReportItems/de:Textbox[@Name='ReportTitle']/de:Width", nsmgr).InnerText = (dblPageWidth - 2) & "in"

           

        Catch ex As Exception

        End Try

  'Get modified xml definition

        Return encoding.GetBytes(xmlReportDoc.InnerXml)

    End Function

Dynamically Hide columns in SQL server reporting services 2008 (SSRS)

Hey All,

I just discovered one hilarious thing in a pre-existing code written by someone some years ago.. OK, first the question : How to dynamically hide/show a coulumn in a Tablix in SQL server reporting services 2005/8 ?

Ok, I will give you a clue:

Option 1: Right click on the column, Select column properties, then choose column visibility, and select "Use expression", and then give the criteria to make it visible.

Option 2: Using .NET code, read the report's RDL XML, remove the column from the Tablix, make sure that the resultant XML is as per the RDL schema standards, and re-deploy the report in to SQL server, run the report, delete the report.

If you had chosen Option 1, then you know what I am talking about.. ha ha ha..

For those who had selected Option 2, sorry, no luck today :) Do a bit of research and you will understand why I am laughing [Can't control my laughter ... ha ha ha. again...]

3DES Encryptor sample in VB.NET

Hi All,


Welcome to my blog again. I had a requirement in my project to encrypt some settings using a simple encryption. But I couldn't find a ready made solution, and from my college days, I always liked 3DES encryption. So, here is a "ready made" solution :

{Note: Of course, you'll have to change the IV (initialization vector) and the Key in the variable strCrypt }

Imports System.Security.Cryptography

Imports System.IO

Public Class clsDCrypto

    Private m_bKey(), m_bIV() As Byte

    Public Sub New()

        Dim strCrypt, strIV As String

        strCrypt = "q5AsCA64SeY+SpoJ24ERrg=="

        strIV = "VaOHJOI2J/c="

        m_bKey = Convert.FromBase64String(strCrypt)

        m_bIV = Convert.FromBase64String(strIV)

    End Sub

    Public Function DecryptString(ByVal strCipherText As String) As String

        Dim strTempDecrypt As String

        Dim memstream2 As New MemoryStream

        Dim iCryptoDecryptor As ICryptoTransform

        Dim tDESProviderd As New TripleDESCryptoServiceProvider

        Dim cryptdStream As CryptoStream

        Dim bCipher As Byte()

        bCipher = Convert.FromBase64String(strCipherText)

        tDESProviderd.KeySize = 128

        iCryptoDecryptor = tDESProviderd.CreateDecryptor(m_bKey, m_bIV)

        cryptdStream = New CryptoStream(memstream2, iCryptoDecryptor, CryptoStreamMode.Write)

        cryptdStream.Write(bCipher, 0, bCipher.Length)

        cryptdStream.FlushFinalBlock()

        memstream2.Position = 0

        Dim temp2(CType(memstream2.Length - 1, System.Int32)) As Byte

        memstream2.Read(temp2, 0, CType(temp2.Length, System.Int32))

        memstream2.Close()

        cryptdStream.Close()

        strTempDecrypt = System.Text.Encoding.UTF8.GetString(temp2)

        Return strTempDecrypt

    End Function

    Public Function EncryptString(ByVal strPlainText As String) As String

        Dim tDESProvider As New TripleDESCryptoServiceProvider

        'Dim tDes2 As New TripleDES

        Dim cryptStream As CryptoStream

        Dim iCryptoEncryptor As ICryptoTransform

        Dim memStream As New MemoryStream()

        Dim bPlainText As Byte()

        Dim strTempEncrypt As String

        tDESProvider.KeySize = 128

        iCryptoEncryptor = tDESProvider.CreateEncryptor(m_bKey, m_bIV)

        cryptStream = New CryptoStream(memStream, iCryptoEncryptor, CryptoStreamMode.Write)

        bPlainText = System.Text.Encoding.UTF8.GetBytes(strPlainText)

        cryptStream.Write(bPlainText, 0, bPlainText.Length)

        cryptStream.FlushFinalBlock()

        Dim tmp(CType(memStream.Length - 1, System.Int32)) As Byte

        memStream.Position = 0

        memStream.Read(tmp, 0, CType(memStream.Length, System.Int32))

        memStream.Close()

        cryptStream.Close()

        strTempEncrypt = Convert.ToBase64String(tmp)

        Return strTempEncrypt

    End Function

End Class

Simple XML Encoder.

Hi All,

I had frequently convert some XML / HTML text in to an encoded text [eg: If you have to post it in a blog], so I came up with this simple [very simple :)] .NET XML encoder. Enjoy!!

[Please let me know what you think in the comments section]

Public Class Form1

    Private Sub txtPlainText_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtPlainText.TextChanged

        Dim strPlainText, strXMLencoded As String

        strPlainText = txtPlainText.Text

        strXMLencoded = System.Web.HttpUtility.HtmlEncode(strPlainText)

        txtDestination.Text = strXMLencoded

    End Sub

    Private Sub cmdCopy_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCopy.Click

        Clipboard.SetText(txtDestination.Text.Trim())

    End Sub

    Private Sub btnConvert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConvert.Click

        Dim strPlainText, strXMLencoded As String

        strPlainText = txtPlainText.Text

        strXMLencoded = System.Web.HttpUtility.HtmlEncode(strPlainText)

        txtDestination.Text = strXMLencoded

    End Sub

End Class