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