Friday, March 11, 2011

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)


            'Add the namespace for Report definition
            nsmgr.AddNamespace("de", "")

            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
            xmlTableDetailCellsNew = xmlTableDetailCells.Clone
            xmlTableColumnsNew = xmlTableColumns.Clone
            xmlTablixColumnHierarchyNew = xmlTablixColumnHierarchy.Clone

            '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)
                        xmlNode = Nothing

                        xmlNode = xmlTablixColumnHierarchy.ChildNodes.Item(0)
                        xmlNode = Nothing

                        xmlNode = xmlTableDetailCells.ChildNodes.Item(intCounter)
                        If Not (xmlNode Is Nothing) Then
                            xmlNode = Nothing
                            xmlNode = xmlTableColumns.ChildNodes(intCounter)
                            If Not (xmlNode Is Nothing) Then
                                dblTotalWidth = dblTotalWidth + CDec(xmlNode.SelectSingleNode("./de:Width", nsmgr).InnerText().Replace("in", ""))
                            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
            'Take out the existing Details node and add New Details node
            xmlParentNode = xmlTableDetailCells.ParentNode
            xmlParentNode = xmlTableColumns.ParentNode
            'Take out the existing Table columns node and add New Table columns node

            xmlParentNode = xmlTablixColumnHierarchy.ParentNode


            '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

1 comment:

Loren said...


Thank you for posting this. This is an awesome bit of code and will form the base for many things I do in SSRS in the future I am sure.

Thank you!