none
RDLC Table Properties RRS feed

  • Question

  • I have a report (rdlc) created with vb.net 2008.  The report is displayed using the ReportViewer.  In the report I have a table with the Table-Properties-Sorting setup.  Is there a way I can change the Sorting property in my application code so I can change the columns that sort the table?

    Thanks!
    Chuck

    Thursday, July 23, 2009 4:57 PM

Answers

  • Hi,

    You might try adding a parameter to your report to inform which column you want to sort by. Then instead of just selecting a field to sort add an expression that evaluates the parameter and returns the field you want to sort by.

    The expression would look something like this:

    =IIf(Parameters!P_SortingField.Value = 1,
        Fields!FirstSortField.Value,
        IIf(Parameters!P_SortingField.Value = 2,
            Fields!SecondSortField.Value,
            Fields!ThirdSortField.Value))

    Hope this helps.
    Thursday, July 23, 2009 6:19 PM
  • Hey Chuck,

    You can pass a parameter into the report and then change the sorting expression to something like '=Parameters!Sortby.Value'  where the parameter name is Sortby.

    The sorting expression for the table can be accessed in the report by selecting the table, and selecting the row that controls the grouping, then right click and select Edit Group, and then the sorting tab. Enter the  '=Parameters!Sortby.Value'  into the expression box.

    The sort parameter is usally just a field from your dataset.

    You can also use this method to change grouping.

    To add parameters to the report, while in or editing the report, from the pull-down menu in VS 2008, select Report, then Report Parameters.

    To pass a parameter into the report, I use something like this....

     Say my query is 'Select mydate, mystring, mynumber from mytable'

     Sortby = mystring ' sort by mystring field

    Dim p As New ReportParameter("BeginDate", BeginDate)
    Dim p1 As New ReportParameter("EndDate", EndDate)
    Dim p2 As New ReportParameter("Sortby", Sortby)
    Dim p3 As New ReportParameter("Title", GetTitle(Session("ReportName")))

     

    Dim datasource As ReportDataSource
    rptDataSetName = "PRMS_SFC541"
    reportPath = "Reports\rptReports\rptAccounting\rptSFC541.rdlc"
    ReportViewer1.ShowParameterPrompts = False
    ReportViewer1.ProcessingMode = ProcessingMode.Local
    ReportViewer1.Reset()
    ReportViewer1.LocalReport.ReportPath = reportPath
    ReportViewer1.LocalReport.SetParameters(New ReportParameter() {p, p1, p2, p3})
    datasource =
    New ReportDataSource(rptDataSetName, DSReport.Tables(0))
    ReportViewer1.LocalReport.DataSources.Clear()
    ReportViewer1.LocalReport.DataSources.Add(datasource)
    ReportViewer1.LocalReport.Refresh()

    Good Luck
    Bill

    Thursday, July 23, 2009 7:12 PM
  • In reviewing one of my Reports, it seems I have to use this format/syntax to get the sorting correct....above in my post where I stated I was using the parameter to populate the Tables Sorting Expression I stated I used  =parameters!Sortby.value, instead I am actually using ....=IIF(Parameters!Sortby.Value<> "",Fields(Parameters!Sortby.Value).Value,"")

    Sorry for the confusion...

    Bill

     

    As far as sorting multiple columns, when you insert the above formula ....IIF(Parameters!Sortby.Value<> "",Fields(Parameters!Sortby.Value).Value,"") into the sort expression, the next row down you would put the next column you want sorted. You would of course have to add more parameters to the report.

    Ie:
    IIF(Parameters!Sortby.Value<> "",Fields(Parameters!Sortby.Value).Value,"")
    IIF(Parameters!Sortby2.Value<> "",Fields(Parameters!Sortby2.Value).Value,"")
    IIF(Parameters!Sortby3.Value<> "",Fields(Parameters!Sortby3.Value).Value,"")
    IIF(Parameters!Sortby4.Value<> "",Fields(Parameters!Sortby4.Value).Value,"")



    Friday, July 24, 2009 4:52 PM

All replies

  • Hi,

    You might try adding a parameter to your report to inform which column you want to sort by. Then instead of just selecting a field to sort add an expression that evaluates the parameter and returns the field you want to sort by.

    The expression would look something like this:

    =IIf(Parameters!P_SortingField.Value = 1,
        Fields!FirstSortField.Value,
        IIf(Parameters!P_SortingField.Value = 2,
            Fields!SecondSortField.Value,
            Fields!ThirdSortField.Value))

    Hope this helps.
    Thursday, July 23, 2009 6:19 PM
  • Hey Chuck,

    You can pass a parameter into the report and then change the sorting expression to something like '=Parameters!Sortby.Value'  where the parameter name is Sortby.

    The sorting expression for the table can be accessed in the report by selecting the table, and selecting the row that controls the grouping, then right click and select Edit Group, and then the sorting tab. Enter the  '=Parameters!Sortby.Value'  into the expression box.

    The sort parameter is usally just a field from your dataset.

    You can also use this method to change grouping.

    To add parameters to the report, while in or editing the report, from the pull-down menu in VS 2008, select Report, then Report Parameters.

    To pass a parameter into the report, I use something like this....

     Say my query is 'Select mydate, mystring, mynumber from mytable'

     Sortby = mystring ' sort by mystring field

    Dim p As New ReportParameter("BeginDate", BeginDate)
    Dim p1 As New ReportParameter("EndDate", EndDate)
    Dim p2 As New ReportParameter("Sortby", Sortby)
    Dim p3 As New ReportParameter("Title", GetTitle(Session("ReportName")))

     

    Dim datasource As ReportDataSource
    rptDataSetName = "PRMS_SFC541"
    reportPath = "Reports\rptReports\rptAccounting\rptSFC541.rdlc"
    ReportViewer1.ShowParameterPrompts = False
    ReportViewer1.ProcessingMode = ProcessingMode.Local
    ReportViewer1.Reset()
    ReportViewer1.LocalReport.ReportPath = reportPath
    ReportViewer1.LocalReport.SetParameters(New ReportParameter() {p, p1, p2, p3})
    datasource =
    New ReportDataSource(rptDataSetName, DSReport.Tables(0))
    ReportViewer1.LocalReport.DataSources.Clear()
    ReportViewer1.LocalReport.DataSources.Add(datasource)
    ReportViewer1.LocalReport.Refresh()

    Good Luck
    Bill

    Thursday, July 23, 2009 7:12 PM
  • Thanks so much for the info.  In my sort, I need to sort by multiple columns at the same time.  Can I do that with the report Parameter?  Using the Table,Properties,Sorting parameter, I can list the columns to sort by in order of priority.

    Thanks again!
    Friday, July 24, 2009 11:44 AM
  • In reviewing one of my Reports, it seems I have to use this format/syntax to get the sorting correct....above in my post where I stated I was using the parameter to populate the Tables Sorting Expression I stated I used  =parameters!Sortby.value, instead I am actually using ....=IIF(Parameters!Sortby.Value<> "",Fields(Parameters!Sortby.Value).Value,"")

    Sorry for the confusion...

    Bill

     

    As far as sorting multiple columns, when you insert the above formula ....IIF(Parameters!Sortby.Value<> "",Fields(Parameters!Sortby.Value).Value,"") into the sort expression, the next row down you would put the next column you want sorted. You would of course have to add more parameters to the report.

    Ie:
    IIF(Parameters!Sortby.Value<> "",Fields(Parameters!Sortby.Value).Value,"")
    IIF(Parameters!Sortby2.Value<> "",Fields(Parameters!Sortby2.Value).Value,"")
    IIF(Parameters!Sortby3.Value<> "",Fields(Parameters!Sortby3.Value).Value,"")
    IIF(Parameters!Sortby4.Value<> "",Fields(Parameters!Sortby4.Value).Value,"")



    Friday, July 24, 2009 4:52 PM
  • Thanks so very much for all the help!!

    Chuck
    Friday, July 24, 2009 8:53 PM