Hi,

i have below table in DB

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

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

Hi,

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

Thanks,

Rana

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!!

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,

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.

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

Thanks A Lot All of You !!

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

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...........

Thanks Srini!!

i will try and let you know for further help.

Hi,

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

Thanks!!

Akhilesh

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 

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:-

TABLE [dbo].Supplier_Dummy(

NULL,

(50) NULL,

(50) NULL,

) ON [PRIMARY]

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

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

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

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

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

Hi,

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

-Srini.

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

• Edited by Srini Koduru Tuesday, April 23, 2013 1:33 PM
• Proposed as answer by Charlie LiaoMicrosoft contingent staff, Moderator Wednesday, April 24, 2013 1:16 AM
• Marked as answer by Charlie LiaoMicrosoft contingent staff, Moderator Thursday, April 25, 2013 8:18 AM

Hi Srini,

In Rowdetails data #ERROR is coming

Hi,

It is working, following is the screen shot.

Let me know if you need the SSRS file.

-Srini

• Proposed as answer by Charlie LiaoMicrosoft contingent staff, Moderator Wednesday, April 24, 2013 1:16 AM
• Marked as answer by Charlie LiaoMicrosoft contingent staff, Moderator Thursday, April 25, 2013 8:18 AM

Hi Srini,

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

Thanks!!

Hi,

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

-Srini

Sent RDL file by mail.

-Srini

Thanks A Lot Srini :)

Regards,

Akhil

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

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

Hi Sirni,

It's Working !! Thanks a Lot

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

Hi,

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

-Srini

Hi sirni,

we are using for Row data below Function:--

Hi Sirni,

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

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

could you please email me too? shawn.McCabe@wellsfargo.com