none
SQL Report Builder 3.0

    Question

  • I am using SQL report builder 3 pulling data from a SQL database and have created 4 tables in SQL report build then have a main table pulling the total of each of the other 4 tables using the following expression for each table =ReportItems!Textbox8.Value. How can I sort the main table to have the highest value at the top?

    Thanks

    Adam

    Friday, September 13, 2013 12:35 PM

Answers

  • Hi Adam,

    According to your description, it seems that you want to sort the main table which pulling data from the total of each of the other 4 tables in descending order. In Reporting Service, we can use custom code to achieve the goal which use SetText function to store total value and table name to Hashtable, use Sort function to sort the four value in descending order, and then use Rank function to return the total value, finally use Text function to return the table name which correspond the total value in the Hashtable. After testing it in my own environment, we can refer to the following steps to achieve the goal:

    1. Right-click the report outside the Body pane to select Report Properties, add the following Custom code:

    Public Literals As New System.Collections.Hashtable()
    Dim values As System.Collections.ArrayList=New System.Collections.ArrayList()
    Function SetText(ByVal value As Integer, ByVal text As String) As Integer
        values.Add(value)
        Literals.Add(value, text)
        return value
    End Function
    
    Function Sort()
    Dim i as Integer
    Dim j as Integer
    Dim t as Integer
    Dim n as Integer=values.Count-1
    For i=n To 1 Step-1
                    For j=0 To i-1
                    if values(j)<values(j+1) Then
                       t=values(j)
                       values(j)=values(j+1)
                       values(j+1)=t
                    End if
                    Next j
    Next i
    End Function
    
    Function Rank(ByVal value As Integer)
       return values(value)
    End Function
    
    Function Text(ByVal value As Integer) As String
       Dim Literal = Literals.Item(value)
       If Literal Is Nothing
          Return "{" & value & "}"
       Else
          Return Literal
       End If
    End Function
    

    2. Right-click the textbox which contains the total value in your table1 to modify the expression to like this below:
    =Code.SetText(Sum(Fields!Value1.Value),”table1”)
    3. Repeat Step2 in the textbox which contains the total value in other 3 tables(table2 or table3 or table4).
    4. In the Main table, right-click the textbox which you want to contain the highest value with the expression as below:
    =Code.Sort() & Code.Text(Code.Rank(0)) & ":" & Code.Rank(0)
    5. Repeat Step4 in the following 3 textboxes (1 or 2 or 3)with the expression as below:
    =Code.Text(Code.Rank(1)) & ":" & Code.Rank(1)  

    The following screenshot is for your reference:
    Design surface:

    Result:

    If you have any more questions, please feel free to ask.

    Thanks,
    Katherine Xiong

    • Marked as answer by adam_taylor Friday, September 20, 2013 1:38 PM
    Friday, September 20, 2013 1:19 AM

All replies

  • Hi

    you can do right - clic in your main table after go to option Order and there define the sort (order) 

    if this dont answer your question comment please


    Si se solucionó tu consulta no olvides marcar la respuesta de esta forma mantendremos el orden en el Foro.

    José Carlos Barba Gutierrez
    Lima - Perú
    Saludos

    Friday, September 13, 2013 1:48 PM
  • The column that has the following code '=ReportItems!Textbox8.Value' that pulls the values from the different tables isn't listed under the sort options .
    Friday, September 13, 2013 2:10 PM
  • Hi Adam,

    According to your description, it seems that you want to sort the main table which pulling data from the total of each of the other 4 tables in descending order. In Reporting Service, we can use custom code to achieve the goal which use SetText function to store total value and table name to Hashtable, use Sort function to sort the four value in descending order, and then use Rank function to return the total value, finally use Text function to return the table name which correspond the total value in the Hashtable. After testing it in my own environment, we can refer to the following steps to achieve the goal:

    1. Right-click the report outside the Body pane to select Report Properties, add the following Custom code:

    Public Literals As New System.Collections.Hashtable()
    Dim values As System.Collections.ArrayList=New System.Collections.ArrayList()
    Function SetText(ByVal value As Integer, ByVal text As String) As Integer
        values.Add(value)
        Literals.Add(value, text)
        return value
    End Function
    
    Function Sort()
    Dim i as Integer
    Dim j as Integer
    Dim t as Integer
    Dim n as Integer=values.Count-1
    For i=n To 1 Step-1
                    For j=0 To i-1
                    if values(j)<values(j+1) Then
                       t=values(j)
                       values(j)=values(j+1)
                       values(j+1)=t
                    End if
                    Next j
    Next i
    End Function
    
    Function Rank(ByVal value As Integer)
       return values(value)
    End Function
    
    Function Text(ByVal value As Integer) As String
       Dim Literal = Literals.Item(value)
       If Literal Is Nothing
          Return "{" & value & "}"
       Else
          Return Literal
       End If
    End Function
    

    2. Right-click the textbox which contains the total value in your table1 to modify the expression to like this below:
    =Code.SetText(Sum(Fields!Value1.Value),”table1”)
    3. Repeat Step2 in the textbox which contains the total value in other 3 tables(table2 or table3 or table4).
    4. In the Main table, right-click the textbox which you want to contain the highest value with the expression as below:
    =Code.Sort() & Code.Text(Code.Rank(0)) & ":" & Code.Rank(0)
    5. Repeat Step4 in the following 3 textboxes (1 or 2 or 3)with the expression as below:
    =Code.Text(Code.Rank(1)) & ":" & Code.Rank(1)  

    The following screenshot is for your reference:
    Design surface:

    Result:

    If you have any more questions, please feel free to ask.

    Thanks,
    Katherine Xiong

    • Marked as answer by adam_taylor Friday, September 20, 2013 1:38 PM
    Friday, September 20, 2013 1:19 AM
  • hi

    you can sort data in sql query 

    select * from table order by ..

    Friday, September 20, 2013 2:50 AM
  • Hi Adam,

    You can paste the following custom code in "Report Properties" Code tab.

    Public Function IntSort(ByVal ParamArray IntArray() As Integer)
            Array.Sort(IntArray)
            Return IntArray
    End Function

    Since you have only four values to sort you can use the following expression for each of the four textboxes in main table where you want the total to be displayed.

    Put the below expression in the first text for highest value =Code.IntSort(CInt(ReportItems!Textbox8.Value),CInt(ReportItems!Textbox9.Value),CInt(ReportItems!Textbox10.Value))(3)

    and paste the below ones in the next three textboxes.

    second highest 

    =Code.IntSort(CInt(ReportItems!Textbox8.Value),CInt(ReportItems!Textbox9.Value),CInt(ReportItems!Textbox10.Value))(2)

    third highest

    =Code.IntSort(CInt(ReportItems!Textbox8.Value),CInt(ReportItems!Textbox9.Value),CInt(ReportItems!Textbox10.Value))(1)

    Lowest among the four total

    =Code.IntSort(CInt(ReportItems!Textbox8.Value),CInt(ReportItems!Textbox9.Value),CInt(ReportItems!Textbox10.Value))(0)

    This works for me. I am not a .Net developer and so this may not be a perfect code. But it's simple and worth a try.

    ----------------------------------------------------------

    Please mark this as answered if this solves your problem


    --sIbu

    Friday, September 20, 2013 3:49 AM