none
How to Arrange Dynamically Column based on parameter in SSRS 2008 R2

    Question

  • Hi,

    i have below table in DB

    DB Table    
    ID Column Row data
    1 Supplier CODE 1001
    2 Supplier Name ACB
    3 Product 7K7
    4 Price 1000
    now I create   one report parameter order1
    IF I will   give order1.value=1,2,3 then Report will come like this :--
    Suppliercode Supplier Name Product
    1001 ACB 7K7
    IF I will   give order1.value=3,2,1 then Report will come like this :--
    Product Supplier Name Suppliercode
    7K7 ACB 1001
    IF I will   give order1.value=1,3 then Report will come like this :--
    Suppliercode Product
    1001 7K7
    Friday, April 19, 2013 7:17 AM

Answers

  • Hi, Sorry for many mistakes in code (never tested the code I wrote here), here is the right code.

    Public Function GetColumnFieldName(ByVal colNumber As Integer, ByVal columnOrder As String) As String
            Dim columns As String()
            columns = columnOrder.Split(New Char() {","c})
            Dim fieldName As String = "Product"
            If columns.Length < colNumber Then
                fieldName = "Product"
            Else
                Select Case columns(colNumber - 1)
                    Case "1"
                        fieldName = "Supplier_Code"
                    Case "2"
                        fieldName = "Supplier_Name"
                    Case "3"
                        fieldName = "Product"
                    Case Else
                        fieldName = "Product"
                End Select
            End If
            Return fieldName
        End Function
        Public Function GetColumnHeading(ByVal colNumber As Integer, ByVal columnOrder As String) As String
            Dim columns As String()
            columns = columnOrder.Split(New Char() {","c})
            Dim columnHeading As String = "Product"
            If columns.Length < colNumber Then
                columnHeading = "Product"
            Else
                Select Case columns(colNumber - 1)
                    Case "1"
                        columnHeading = "Supplier_Code"
                    Case "2"
                        columnHeading = "Supplier_Name"
                    Case "3"
                        columnHeading = "Product"
                    Case Else
                        columnHeading = "Product"
                End Select
            End If
            Return columnHeading
        End Function
        Private Function IsColumnHidden(ByVal columnSequence As Integer, ByVal columnOrder As String) As Boolean
            Dim columns As String()
            columns = columnOrder.Split(New Char() {","c})
            If columns.Length < columnSequence Then
                Return False
            Else
                Return True
            End If

    copy above code in code.

    First Field expression =Fields(Code.GetColumnFieldName(1,Parameters!ColumnOrder.Value)).Value

    Second Field expression use 2 (instead of 1 in the above expression), for 3rd field use 3.

    For Column headings =Code.GetColumnHeading(1,Parameters!ColumnOrder.Value)), change the value of 1 to 2 or 3 based on the column number.

    If you need sample SSRS I can send.

    Regards

    Srini


    Tuesday, April 23, 2013 1:32 PM
  • Hi,

    It is working, following is the screen shot.

    Let me know if you need the SSRS file.

    -Srini

    Tuesday, April 23, 2013 3:28 PM

All replies

  • Hi,

    For that you have to use Matrix in place of table.

    At first filter your dataset with the parameter value and create the report by using Matrix.

    Please check the below links that will help  you.

    http://technet.microsoft.com/en-us/library/ms157334.aspx
    http://msdn.microsoft.com/en-in/library/cc627441(v=sql.105).aspx

    Thanks,

    Rana

    Friday, April 19, 2013 9:10 AM
  • Hi Rana,

    Thanks for Suggestion!!

    But i need to Change column location/Order  in Report based on the Parameter value which is indirctly calling ID values fron Data base.

    so, based on  ID.value = parameter.value  than column will come that order.. it is possible? please let me know IF any other way to Do this.

    Thanks,

    Akhilesh

    Friday, April 19, 2013 10:09 AM
  • Hi,

    For that you have to use order  by in the dataset query.

    Thanks,

    Rana

    Friday, April 19, 2013 12:31 PM
  • Hi Rana,

    please check above once again my requirement, i want to change in Report column order base on parameter Value e.g. somtimes i want Supplier code on 1st position in report , sometimes i want on comes as a last coloumn in report preivew.

    plz let me know IF Still not clear the Requirement.

    Thanks!!

    Friday, April 19, 2013 1:22 PM
  • Hi Akhilesh,

    I got your requirement completely base on your description. You want to change the order of the column base on the value of your parameter, right? There is no such a feature to control the order of the column in Reporting Services currently, so I am afraid this requirement cannot be achieved.

    Thank you for your understanding.

    Regards,


    Charlie Liao
    TechNet Community Support

    Monday, April 22, 2013 3:24 AM
    Moderator
  • Hi,

    Dynamic display of columns is possible - http://sathyadb.blogspot.in/2013/01/ssrs-ismissing-visibility-function.html

    But dynamic arrangement of columns is not possible,like charlie suggested.


    sathya --------- Please Mark as answered if my post solved your problem and Vote as helpful if my post was useful.

    Monday, April 22, 2013 4:01 AM
    Moderator
  • Hi ,

    As Charlio said there is no such functionality available in SSRS to change the columns based on parameter. but you can try this Add more 6 column in the report

    Let say parameter A column order A,B,C if parameter A then these three column are visible rest 6 are hidden

                Parameter B column order B,A,C--If parameter B then these three column are visible and rest are hidden.

                Parameter C Column order C,B,A---If Parameter C then these three column are visible rest 6 are hidden



    blog:My Blog/

    Hope this will help you !!!
    Sanjeewan

    Monday, April 22, 2013 5:20 AM
  • Thanks A Lot All of You !!

    Monday, April 22, 2013 10:14 AM
  • Thanks !! Charlie :)
    Monday, April 22, 2013 10:15 AM
  • Sorry many mistakes in code, so deleted. Refer to the later part of the thread for right code.

    -Srini

    • Proposed as answer by Srini Koduru Monday, April 22, 2013 6:21 PM
    • Edited by Srini Koduru Tuesday, April 23, 2013 1:34 PM
    Monday, April 22, 2013 6:12 PM
  • Hi Akhilesh, I am not sure how many columns you want to order and the number of combinations you want them to be ordered. If you want to achieve this please write a dynamic query for the dataset with the appropriate column order for the resultset and based on what order you select from the parameter only make that dataset visible.

    Thanks...........

    Monday, April 22, 2013 6:26 PM
  • Thanks Srini!!

    i will try and let you know for further help.

    Tuesday, April 23, 2013 12:29 PM
  • Hi,

    Plz check my column name and write code IF possible for Example ..

    Thanks!!

    Akhilesh

    Tuesday, April 23, 2013 12:30 PM
  • Hi Srini,

    i getting below Error for the same.

    "GetcolumnFileldName" doesn't Return a Value on all code path. A null refernece exception could occur at run time when the result is used:

    Please help !!

    Regards,

    Akhilesh 

    Tuesday, April 23, 2013 12:44 PM
  • Hi Srini,

    i am using below code It's working only when i am giving single value like 1 and 2, than only these values is coming in all coulumn.

    Public Function GetColumnFieldName(ByVal columnOrder As String) As String
        Dim columns As String()
        columns = columnOrder.Split(New Char() {","c})
        Dim fieldName As String = "Product"
        For Each column As String In columns
            Select Case column
                Case "1"
                    fieldName = "SupName"
                Case "2"
                    fieldName = "COM"
                Case "3"
                    fieldName = "PR"
                Case Else
                    fieldName = "PR"
            End Select
        Next
        Return fieldName
    End Function
    Public Function GetColumnHeading(ByVal columnOrder As String) As String
        Dim columns As String()
        columns = columnOrder.Split(New Char() {","c})
        Dim columnHeading As String = "Product"
        For Each column As String In columns
            Select Case column
                Case "1"
                    columnHeading = "SupplierName"
                Case "2"
                    columnHeading = "Commdity"
                Case "3"
                    columnHeading = "Suppliercode"
                Case Else
                    columnHeading = "Suppliercode"
            End Select
        Next
        Return columnHeading
    End Function
    Private Function IsColumnHidden(ByVal columnSequence As Integer, ByVal columnOrder As String) As Boolean
        Dim columns As String()
        columns = columnOrder.Split(New Char() {","c})
        If columns.Length >= columnSequence Then
            Return False
        Else
            Return True
        End If
    End Function

    below is my Db Scripts:-

    CREATE

    TABLE [dbo].Supplier_Dummy(


    [Id] [int]

    NULL,


    [Col] [varchar]

    (50) NULL,


    [Fields] [Varchar]

    (50) NULL,


    ) ON [PRIMARY]


    Insert

    into  [dbo].Supplier_Dummy values  (1, 'SupplierName', 'SupName')


    Insert

    into  [dbo].Supplier_Dummy values  (2, 'Commdity', 'COM')


    Insert

    into  [dbo].Supplier_Dummy values  (3, 'Priority', 'PR')


    Insert

    into  [dbo].Supplier_Dummy values  (4, 'Suppliercode', 'SubCode')

    please check code is correct or Not? please help me out for the same !! urgent plz :)

    Tuesday, April 23, 2013 1:10 PM
  • Hi,

    Sorry, I made a major mistake in code, please wait 30 minutes, I will update the code.

    -Srini.

    Tuesday, April 23, 2013 1:12 PM
  • Hi, Sorry for many mistakes in code (never tested the code I wrote here), here is the right code.

    Public Function GetColumnFieldName(ByVal colNumber As Integer, ByVal columnOrder As String) As String
            Dim columns As String()
            columns = columnOrder.Split(New Char() {","c})
            Dim fieldName As String = "Product"
            If columns.Length < colNumber Then
                fieldName = "Product"
            Else
                Select Case columns(colNumber - 1)
                    Case "1"
                        fieldName = "Supplier_Code"
                    Case "2"
                        fieldName = "Supplier_Name"
                    Case "3"
                        fieldName = "Product"
                    Case Else
                        fieldName = "Product"
                End Select
            End If
            Return fieldName
        End Function
        Public Function GetColumnHeading(ByVal colNumber As Integer, ByVal columnOrder As String) As String
            Dim columns As String()
            columns = columnOrder.Split(New Char() {","c})
            Dim columnHeading As String = "Product"
            If columns.Length < colNumber Then
                columnHeading = "Product"
            Else
                Select Case columns(colNumber - 1)
                    Case "1"
                        columnHeading = "Supplier_Code"
                    Case "2"
                        columnHeading = "Supplier_Name"
                    Case "3"
                        columnHeading = "Product"
                    Case Else
                        columnHeading = "Product"
                End Select
            End If
            Return columnHeading
        End Function
        Private Function IsColumnHidden(ByVal columnSequence As Integer, ByVal columnOrder As String) As Boolean
            Dim columns As String()
            columns = columnOrder.Split(New Char() {","c})
            If columns.Length < columnSequence Then
                Return False
            Else
                Return True
            End If

    copy above code in code.

    First Field expression =Fields(Code.GetColumnFieldName(1,Parameters!ColumnOrder.Value)).Value

    Second Field expression use 2 (instead of 1 in the above expression), for 3rd field use 3.

    For Column headings =Code.GetColumnHeading(1,Parameters!ColumnOrder.Value)), change the value of 1 to 2 or 3 based on the column number.

    If you need sample SSRS I can send.

    Regards

    Srini


    Tuesday, April 23, 2013 1:32 PM
  • Hi Srini,

    In Rowdetails data #ERROR is coming

    =Fields(Code.GetColumnFieldName(1,Parameters!columnOrder.Value)).Value

    Tuesday, April 23, 2013 1:53 PM
  • Hi,

    It is working, following is the screen shot.

    Let me know if you need the SSRS file.

    -Srini

    Tuesday, April 23, 2013 3:28 PM
  • Hi Srini,

    For me #Error in row data. any Suggestion or plz send RDL.

    Thanks!!

    Wednesday, April 24, 2013 6:01 AM
  • Hi,

    Let me know any mail id to forward the rdl file.

    -Srini

    Wednesday, April 24, 2013 11:30 AM
  • Wednesday, April 24, 2013 1:06 PM
  • Sent RDL file by mail.

    -Srini

    Wednesday, April 24, 2013 6:46 PM
  • Thanks A Lot Srini :)

    Regards,

    Akhil

    Thursday, April 25, 2013 5:41 AM
  • Hi Srini,

    One More Help plz!!

    IF i will not give any parameter value than column should not be come in Report.

    For  Example now we have to give all three value like 3,2,1 or 1,2,3 but IF i will give 1,2 than only two column will come in report.

    now IF i give only 1,2 but all three column is coming. plz Urgent Help Needed !!

    Regards,

    Akhilesh

    Thursday, April 25, 2013 6:12 AM
  • Hi,

    I am not sure what you are saying. If 1,2 is passes only 2 columns are appearing. If any value other than 1 or 2 or 3 are passed, only product is displayed. I am not seeing the behavior of 3 columns showing up when 1,2 is passed.

    If null value is passed then report is generating error. If you need, please handle null check in code. If you need help there let me know.

    Regards

    Srini

    Thursday, April 25, 2013 10:44 AM
  • Hi Sirni,

    It's Working !! Thanks a Lot

    Thursday, April 25, 2013 11:24 AM
  • Hi Sirni,

    one more help plz!!

    can we avoid Row data Function in our Custom Code. i mean can we take vALUE based on column in report Side only like by a Expression Somthing like that bas on column name row data will Come in Report.  Any Expression which  will work for our code. plZ plz !!!

    Regards,

    Akhil

    Thursday, April 25, 2013 3:19 PM
  • Hi,

    I am sorry, I am not understanding the requirement. Can you please explain in detail, giving function names etc.?

    -Srini

    Thursday, April 25, 2013 6:04 PM
  • Hi sirni,

    we are using for Row data below Function:--

    Public Function GetColumnFieldName(ByVal colNumber As Integer, ByVal columnOrder As String) As String

            Dim columns As String()

            columns = columnOrder.Split(New Char() {","c})

            Dim fieldName As String = "Product"

            If columns.Length < colNumber Then

                fieldName = "Product"

            Else

                Select Case columns(colNumber - 1)

                    Case "1"

                        fieldName = "Supplier_Code"

                    Case "2"

                        fieldName = "Supplier_Name"

                    Case "3"

                        fieldName = "Product"

                    Case Else

                        fieldName = "Product"

                End Select

            End If

            Return fieldName

        End Function

    Can we avoid this Function while fetching data, can we take direclty in our report through any expression for example like column header value is supplier_code than field value will come 1001 with out calling this function. means through any expression based on column value we can take field value like this.

    Regards,

    Akhil

    Friday, April 26, 2013 5:14 AM
  • Hi Sirni,

    i got below Expression , it's working like a Charm!!

    =Fields(ReportItems!Textbox7.Value).Value ---> use this in your row data than no need to call Public Function GetColumnFieldName.

    Thanks A Lot!!

    For your Support.

    Friday, April 26, 2013 11:04 AM
  • Hi,

    That works because your column heading is same as field name. But if you need more meaning full column heading, then try to resolve with some hidden parameters. Any how it resolved your requirement.

    -Srini

    Friday, April 26, 2013 12:25 PM