How to incorporate column-header click-driven sorting, and column header click-driven grou

How to incorporate column-header click-driven sorting, and column
header click-driven grouping in SQL Server Reporting Services:
Create the following report parameters with the following details (you
can change the parameter names to suit your naming convention):
(1) Name=3DprmSortBy
Prompt=3DSort By
Data Type=3Dstring
Allow blank value: checked; Allow null value: checked
Available values: Select non-queried
Default values: none
(2) Name=3DprmDirection
Prompt=3DDirection
Data Type=3Dstring
Allow blank value: checked
Available values: Label: ASC; Value:ASC
Label: DESC; Value: DESC
Default values: Non-queried, with the value "ASC" in the text box next
to it.
(3) Name=3DprmGroupBy
Prompt=3DGroup By
Data Type=3Dstring
Allow blank value: checked; Allow null value: checked
Available values: select non-queried
Default values: None
Scenario:
Suppose you have a report created using the traditional table. Let's
also suppose you have the following columns in your report: ClientID,
ClientName, City and SSN (clientID is numeric and the rest being alpha
numeric data types). You wish to sort by the "ClientName" column and
group by the "City" column.
Task: Sorting by the ClientName column:
=E2=80=A2 In the ClientName column header (that sits in a cell on the table
header), right click and select properties.
=E2=80=A2 Then click the advanced button to go to "Advanced Textbox
properties"
=E2=80=A2 Visibility tab settings:
=E2=80=A2 Initial visibility: visible
=E2=80=A2 Initial appearance of the toggle image...: collapsed
=E2=80=A2 Navigation tab settings:
=E2=80=A2 Hyperlink Action: select Jump to report and enter the name of the
report in the textbox (ex. rptClient)
=E2=80=A2 Click on the "Parameters" button to go to the "Parameters" dialog
box
=E2=80=A2 Parameters Dialog box entries:
=E2=80=A2 Here, you must list ALL of the parameters defined in your report
and assign values to them. Having done that, for our case, we concern
ourselves with the sorting and grouping parameter value settings
parameter name parameter value column
prmSortBy =3D"ClientName"
prmDirection =3DCode.DetermineDirection("ClientName",Parameters!prmSort= By.Value,Parameters!prmDirection.Value)
prmGroupBy =3Diif(Parameters!prmGroupBy.Value=3D"","None",Parameters!pr= mGroupBy.Value)
Note: "DetermineDirection" is a function we incorporated into our
report to return a sorting direction (ascending or descending) and
below is the function (if you are unsure where to incorporate the
function, highlight the table that holds the report and click on report
menu and go to properties and on the "code" tab, you enter the
function)
Public Function DetermineDirection(ByVal strColumnName as String, ByVal
strPreviousColumn as String, ByVal strDirection as String) As String
If strColumnName =3D strPreviousColumn Then
If strDirection =3D "ASC" Or strDirection =3D "" Then
Return "DESC"
Else
Return "ASC"
End If
Else
Return "ASC"
End If
End Function
Highlight the table that contains the report, right-click and select
properties. On the "Table Properties" dialog, go to the "Sorting" tab
and enter the following:
Expression =3D
iif(Parameters!prmDirection.Value=3D"ASC",Fields(iif(Parameters!prmSortBy.V= alue
Is Nothing," ClientName ",Parameters!prmSortBy.Value)).Value,0)
Direction:
Ascending
Expression =3D
=3Diif(Parameters!prmDirection.Value=3D"DESC",Fields(iif(Parameters!prmSort= By.Value
Is Nothing,"ClientName",Parameters!prmSortBy.Value)).Value,0)
Direction:
Descending
That should be it! When you run the repot and click on the ClientName
column header, it will refresh the report with the data sorted by the
ClientName.
Note: Remember that for each column you wish to sort by, you have to go
through the above drill and make sure that you set the values for all
the parameters defined on your report. This is very essential since you
do not wish to lose parameter values between sorting since the report
refreshes the data with each sort. Also you need to set the SortBy
parameter value to the column name you are sorting by and to obtain the
sort direction, you can use the function I have above. The advantage of
using the function above is that it allows you to sort the same column
in ascending or descending mode when you click the column repeatedly
(it alternates). If however you click on a new column for the very
first time, it defaults to ascending sort.
Task: Grouping by a column
In order to group by a certain column we created a small icon that we
embedded in the cell under the column header in a separate row right
below the column header. First off, we inserted a group row by using
the =E2=80=9Cinsert group=E2=80=9D menu. Here are the settings for that gro= up
(under Edit Group menu)
=E2=80=A2 General tab (grouping and sorting properties)
o Name: Gp1
o Group On: Expression=3D
=3Diif(Parameters!prmGroupBy.Value=3DFields!None.Value,"",Parameters!prmGro= upBy.Value
& ": " & Fields(iif(Parameters!prmGroupBy.Value
=3DFields!None.Value,"City",Parameters!prmGroupBy.Value)).Value)
o Sorting tab: Sort on expressions:
=EF=82=A7 =3DParameters!prmDirection.Value=3D=E2=80=9DASC=E2=80=9D; Directi= on: Ascending
=EF=82=A7 =3DParameters!prmDirection.Value=3D=E2=80=9DDEC=E2=80=9D; Directi= on: Descending
NOTE: We are using an expression for the groupBy since you can have any
column that you could group your report by. This way you can have
dynamic grouping in your report. In our dataset for the report, we
included a column called =E2=80=9CNone=E2=80=9D that returned an empty stri= ng. We
used that to default the value of the groupBy parameter when no
grouping was selected. Otherwise we would get an error each time the
report is loaded for the first time since the prmGroupBy would not be
populated.
=E2=80=A2 Moving on, for our case we would have an icon under the =E2=80=9C= City=E2=80=9D
column. Right click on that cell and go to properties.
=E2=80=A2 It will then take you to =E2=80=9Cimage properties dialog box=E2= =80=9D.
=E2=80=A2 From here, go to the Navigation tab
o Navigation Tab: Select Jump to Report radio button and enter the
report name (without the .rdl extension of course)
o Click on the Parameters button to go to the Parameters dialog as
before
o Insure that values are assigned to ALL of the remaining parameters on
the report as before.
o Below are the settings specific to the sort and group by parameters
Parameter name parameter value column
prmSortBy =3DParameters!prmSortBy.Value (note the difference between
this and the one before)
prmDirection =3DParameters!prmDirection.Value
prmGroupBy =3Diif(Parameters!prmGroupBy.Value=3D "City","None","City")
(Replace City with the name of the column you are grouping by)
Documented by raghus, from Kizan Technologies
Edited and checked by Bryan AveryHi Bryan,
I have followed all your steps below, but am getting the following error
message when trying to view the report:
1) The value expression for the textbox â'textbox3â' contains an error:
[BC30057] Too many arguments to 'Public Function
DetermineDirection(strColumnName As String) As Object'.
2) There is an error on line 0 of custom code: [BC30203] Identifier expected.
Do you know what would be causing this?
Thanks
Darren
"Bryan Avery" wrote:
> How to incorporate column-header click-driven sorting, and column
> header click-driven grouping in SQL Server Reporting Services:
> Create the following report parameters with the following details (you
> can change the parameter names to suit your naming convention):
> (1) Name=prmSortBy
> Prompt=Sort By
> Data Type=string
> Allow blank value: checked; Allow null value: checked
> Available values: Select non-queried
> Default values: none
> (2) Name=prmDirection
> Prompt=Direction
> Data Type=string
> Allow blank value: checked
> Available values: Label: ASC; Value:ASC
> Label: DESC; Value: DESC
> Default values: Non-queried, with the value "ASC" in the text box next
> to it.
> (3) Name=prmGroupBy
> Prompt=Group By
> Data Type=string
> Allow blank value: checked; Allow null value: checked
> Available values: select non-queried
> Default values: None
> Scenario:
> Suppose you have a report created using the traditional table. Let's
> also suppose you have the following columns in your report: ClientID,
> ClientName, City and SSN (clientID is numeric and the rest being alpha
> numeric data types). You wish to sort by the "ClientName" column and
> group by the "City" column.
> Task: Sorting by the ClientName column:
> â?¢ In the ClientName column header (that sits in a cell on the table
> header), right click and select properties.
> â?¢ Then click the advanced button to go to "Advanced Textbox
> properties"
> â?¢ Visibility tab settings:
> â?¢ Initial visibility: visible
> â?¢ Initial appearance of the toggle image...: collapsed
> â?¢ Navigation tab settings:
> â?¢ Hyperlink Action: select Jump to report and enter the name of the
> report in the textbox (ex. rptClient)
> â?¢ Click on the "Parameters" button to go to the "Parameters" dialog
> box
> â?¢ Parameters Dialog box entries:
> â?¢ Here, you must list ALL of the parameters defined in your report
> and assign values to them. Having done that, for our case, we concern
> ourselves with the sorting and grouping parameter value settings
> parameter name parameter value column
> prmSortBy ="ClientName"
> prmDirection =Code.DetermineDirection("ClientName",Parameters!prmSortBy.Value,Parameters!prmDirection.Value)
> prmGroupBy =iif(Parameters!prmGroupBy.Value="","None",Parameters!prmGroupBy.Value)
> Note: "DetermineDirection" is a function we incorporated into our
> report to return a sorting direction (ascending or descending) and
> below is the function (if you are unsure where to incorporate the
> function, highlight the table that holds the report and click on report
> menu and go to properties and on the "code" tab, you enter the
> function)
> Public Function DetermineDirection(ByVal strColumnName as String, ByVal
> strPreviousColumn as String, ByVal strDirection as String) As String
> If strColumnName = strPreviousColumn Then
> If strDirection = "ASC" Or strDirection = "" Then
> Return "DESC"
> Else
> Return "ASC"
> End If
> Else
> Return "ASC"
> End If
> End Function
> Highlight the table that contains the report, right-click and select
> properties. On the "Table Properties" dialog, go to the "Sorting" tab
> and enter the following:
> Expression => iif(Parameters!prmDirection.Value="ASC",Fields(iif(Parameters!prmSortBy.Value
> Is Nothing," ClientName ",Parameters!prmSortBy.Value)).Value,0)
> Direction:
> Ascending
> Expression => =iif(Parameters!prmDirection.Value="DESC",Fields(iif(Parameters!prmSortBy.Value
> Is Nothing,"ClientName",Parameters!prmSortBy.Value)).Value,0)
> Direction:
> Descending
> That should be it! When you run the repot and click on the ClientName
> column header, it will refresh the report with the data sorted by the
> ClientName.
> Note: Remember that for each column you wish to sort by, you have to go
> through the above drill and make sure that you set the values for all
> the parameters defined on your report. This is very essential since you
> do not wish to lose parameter values between sorting since the report
> refreshes the data with each sort. Also you need to set the SortBy
> parameter value to the column name you are sorting by and to obtain the
> sort direction, you can use the function I have above. The advantage of
> using the function above is that it allows you to sort the same column
> in ascending or descending mode when you click the column repeatedly
> (it alternates). If however you click on a new column for the very
> first time, it defaults to ascending sort.
> Task: Grouping by a column
> In order to group by a certain column we created a small icon that we
> embedded in the cell under the column header in a separate row right
> below the column header. First off, we inserted a group row by using
> the â'insert groupâ' menu. Here are the settings for that group
> (under Edit Group menu)
> â?¢ General tab (grouping and sorting properties)
> o Name: Gp1
> o Group On: Expression=> =iif(Parameters!prmGroupBy.Value=Fields!None.Value,"",Parameters!prmGroupBy.Value
> & ": " & Fields(iif(Parameters!prmGroupBy.Value
> =Fields!None.Value,"City",Parameters!prmGroupBy.Value)).Value)
> o Sorting tab: Sort on expressions:
> ï?§ =Parameters!prmDirection.Value=â'ASCâ'; Direction: Ascending
> ï?§ =Parameters!prmDirection.Value=â'DECâ'; Direction: Descending
> NOTE: We are using an expression for the groupBy since you can have any
> column that you could group your report by. This way you can have
> dynamic grouping in your report. In our dataset for the report, we
> included a column called â'Noneâ' that returned an empty string. We
> used that to default the value of the groupBy parameter when no
> grouping was selected. Otherwise we would get an error each time the
> report is loaded for the first time since the prmGroupBy would not be
> populated.
> â?¢ Moving on, for our case we would have an icon under the â'Cityâ'
> column. Right click on that cell and go to properties.
> â?¢ It will then take you to â'image properties dialog boxâ'.
> â?¢ From here, go to the Navigation tab
> o Navigation Tab: Select Jump to Report radio button and enter the
> report name (without the .rdl extension of course)
> o Click on the Parameters button to go to the Parameters dialog as
> before
> o Insure that values are assigned to ALL of the remaining parameters on
> the report as before.
> o Below are the settings specific to the sort and group by parameters
> Parameter name parameter value column
> prmSortBy =Parameters!prmSortBy.Value (note the difference between
> this and the one before)
> prmDirection =Parameters!prmDirection.Value
> prmGroupBy =iif(Parameters!prmGroupBy.Value= "City","None","City")
> (Replace City with the name of the column you are grouping by)
>
> Documented by raghus, from Kizan Technologies
> Edited and checked by Bryan Avery
>|||BTW: built-in support for sorting table columns by clicking on the table
headers will be available in the next release (RS 2005).
--
Regards
<<<Bryan Avery>>
"Darren" wrote:
> Hi Bryan,
> I have followed all your steps below, but am getting the following error
> message when trying to view the report:
> 1) The value expression for the textbox â'textbox3â' contains an error:
> [BC30057] Too many arguments to 'Public Function
> DetermineDirection(strColumnName As String) As Object'.
> 2) There is an error on line 0 of custom code: [BC30203] Identifier expected.
> Do you know what would be causing this?
> Thanks
> Darren
> "Bryan Avery" wrote:
> > How to incorporate column-header click-driven sorting, and column
> > header click-driven grouping in SQL Server Reporting Services:
> >
> > Create the following report parameters with the following details (you
> > can change the parameter names to suit your naming convention):
> > (1) Name=prmSortBy
> > Prompt=Sort By
> > Data Type=string
> > Allow blank value: checked; Allow null value: checked
> > Available values: Select non-queried
> > Default values: none
> >
> > (2) Name=prmDirection
> > Prompt=Direction
> > Data Type=string
> > Allow blank value: checked
> > Available values: Label: ASC; Value:ASC
> > Label: DESC; Value: DESC
> > Default values: Non-queried, with the value "ASC" in the text box next
> > to it.
> >
> > (3) Name=prmGroupBy
> > Prompt=Group By
> > Data Type=string
> > Allow blank value: checked; Allow null value: checked
> > Available values: select non-queried
> > Default values: None
> >
> > Scenario:
> > Suppose you have a report created using the traditional table. Let's
> > also suppose you have the following columns in your report: ClientID,
> > ClientName, City and SSN (clientID is numeric and the rest being alpha
> > numeric data types). You wish to sort by the "ClientName" column and
> > group by the "City" column.
> >
> > Task: Sorting by the ClientName column:
> >
> > â?¢ In the ClientName column header (that sits in a cell on the table
> > header), right click and select properties.
> > â?¢ Then click the advanced button to go to "Advanced Textbox
> > properties"
> > â?¢ Visibility tab settings:
> > â?¢ Initial visibility: visible
> > â?¢ Initial appearance of the toggle image...: collapsed
> > â?¢ Navigation tab settings:
> > â?¢ Hyperlink Action: select Jump to report and enter the name of the
> > report in the textbox (ex. rptClient)
> > â?¢ Click on the "Parameters" button to go to the "Parameters" dialog
> > box
> >
> > â?¢ Parameters Dialog box entries:
> > â?¢ Here, you must list ALL of the parameters defined in your report
> > and assign values to them. Having done that, for our case, we concern
> > ourselves with the sorting and grouping parameter value settings
> > parameter name parameter value column
> > prmSortBy ="ClientName"
> > prmDirection =Code.DetermineDirection("ClientName",Parameters!prmSortBy.Value,Parameters!prmDirection.Value)
> > prmGroupBy =iif(Parameters!prmGroupBy.Value="","None",Parameters!prmGroupBy.Value)
> > Note: "DetermineDirection" is a function we incorporated into our
> > report to return a sorting direction (ascending or descending) and
> > below is the function (if you are unsure where to incorporate the
> > function, highlight the table that holds the report and click on report
> > menu and go to properties and on the "code" tab, you enter the
> > function)
> >
> > Public Function DetermineDirection(ByVal strColumnName as String, ByVal
> > strPreviousColumn as String, ByVal strDirection as String) As String
> > If strColumnName = strPreviousColumn Then
> > If strDirection = "ASC" Or strDirection = "" Then
> > Return "DESC"
> > Else
> > Return "ASC"
> > End If
> > Else
> > Return "ASC"
> > End If
> > End Function
> >
> > Highlight the table that contains the report, right-click and select
> > properties. On the "Table Properties" dialog, go to the "Sorting" tab
> > and enter the following:
> >
> > Expression => > iif(Parameters!prmDirection.Value="ASC",Fields(iif(Parameters!prmSortBy.Value
> > Is Nothing," ClientName ",Parameters!prmSortBy.Value)).Value,0)
> > Direction:
> > Ascending
> >
> > Expression => > =iif(Parameters!prmDirection.Value="DESC",Fields(iif(Parameters!prmSortBy.Value
> > Is Nothing,"ClientName",Parameters!prmSortBy.Value)).Value,0)
> > Direction:
> > Descending
> >
> > That should be it! When you run the repot and click on the ClientName
> > column header, it will refresh the report with the data sorted by the
> > ClientName.
> >
> > Note: Remember that for each column you wish to sort by, you have to go
> > through the above drill and make sure that you set the values for all
> > the parameters defined on your report. This is very essential since you
> > do not wish to lose parameter values between sorting since the report
> > refreshes the data with each sort. Also you need to set the SortBy
> > parameter value to the column name you are sorting by and to obtain the
> > sort direction, you can use the function I have above. The advantage of
> > using the function above is that it allows you to sort the same column
> > in ascending or descending mode when you click the column repeatedly
> > (it alternates). If however you click on a new column for the very
> > first time, it defaults to ascending sort.
> >
> > Task: Grouping by a column
> > In order to group by a certain column we created a small icon that we
> > embedded in the cell under the column header in a separate row right
> > below the column header. First off, we inserted a group row by using
> > the â'insert groupâ' menu. Here are the settings for that group
> > (under Edit Group menu)
> > â?¢ General tab (grouping and sorting properties)
> > o Name: Gp1
> > o Group On: Expression=> > =iif(Parameters!prmGroupBy.Value=Fields!None.Value,"",Parameters!prmGroupBy.Value
> > & ": " & Fields(iif(Parameters!prmGroupBy.Value
> > =Fields!None.Value,"City",Parameters!prmGroupBy.Value)).Value)
> > o Sorting tab: Sort on expressions:
> > ï?§ =Parameters!prmDirection.Value=â'ASCâ'; Direction: Ascending
> > ï?§ =Parameters!prmDirection.Value=â'DECâ'; Direction: Descending
> >
> > NOTE: We are using an expression for the groupBy since you can have any
> > column that you could group your report by. This way you can have
> > dynamic grouping in your report. In our dataset for the report, we
> > included a column called â'Noneâ' that returned an empty string. We
> > used that to default the value of the groupBy parameter when no
> > grouping was selected. Otherwise we would get an error each time the
> > report is loaded for the first time since the prmGroupBy would not be
> > populated.
> >
> > â?¢ Moving on, for our case we would have an icon under the â'Cityâ'
> > column. Right click on that cell and go to properties.
> > â?¢ It will then take you to â'image properties dialog boxâ'.
> > â?¢ From here, go to the Navigation tab
> > o Navigation Tab: Select Jump to Report radio button and enter the
> > report name (without the .rdl extension of course)
> > o Click on the Parameters button to go to the Parameters dialog as
> > before
> > o Insure that values are assigned to ALL of the remaining parameters on
> > the report as before.
> > o Below are the settings specific to the sort and group by parameters
> > Parameter name parameter value column
> > prmSortBy =Parameters!prmSortBy.Value (note the difference between
> > this and the one before)
> > prmDirection =Parameters!prmDirection.Value
> > prmGroupBy =iif(Parameters!prmGroupBy.Value= "City","None","City")
> > (Replace City with the name of the column you are grouping by)
> >
> >
> > Documented by raghus, from Kizan Technologies
> > Edited and checked by Bryan Avery
> >
> >|||The following needs to be on one line:
Public Function DetermineDirection(ByVal strColumnName as String, ByVal
strPreviousColumn as String, ByVal strDirection as String) As String|||thanks for resolving this
--
Regards
<<<Bryan Avery>>
"Nick Name" wrote:
> The following needs to be on one line:
> Public Function DetermineDirection(ByVal strColumnName as String, ByVal
> strPreviousColumn as String, ByVal strDirection as String) As String
>