List report, filter by letter

Question

I am building a summary report which looks at users that are marked in a database as having a common trait i.e return all users by name in the last 5 years whose contract contains an end date.

This generates a figure e.g. [250]

I drill through on that figure to give a list report of names, first name and surname.

I have ordered the list A-Z

I would like to add a row of 26 letters at the top of the report and have each letter return only the names with the corresponding starting letter.

Would this need to be a further drill through or is it possible to refresh the existing list based on a user-driven selection?

Answer 1

Just add a custom parameter to your report. Right click on Parameters and Add Parameter.

(and so on...). Default value should be All then.

Then simply add the following filter to your tablix:

'Expression:
=Switch(Parameters!YourParameterName.Value = "All", True, Parameters!YourParameterName = Left(Fields!YourNameField.Value, 1), True)

Answer 2

If you are just using SSRS as it comes, then you can't refresh report content without running the report again, so you would either

1. re-organise your current report.
2. need another drill through report.

Depending on your layout you could group the data by the first letter and then have an collapsible row group to show/hide that group of names. To do this simply add a parent row group, set the value, group value and sort value all to =LEFT(Fields!myNameField.Value). Next set the visibility of the details row group to hidden and set the toggle visibility property to point to the cell that contains the letter from the parent group you just created.

If you have too much data to make this efficient the you might have to go down the additional subreport route.

To make life a bit easier, and to save you having to create 26 links and parameter values, I would do the following...

1. Create a table that stores the list of letters
2. Add a matrix to your report, that has a column group grouped on the letter value from this table. This will act as your 'filter bar'
3. Set action on the matrix cell to "go to report", point to the final drill thru report and pass the [myLetterColumn] value as the parameter.

There's no need to set available parameters assuming this sub-report will be hidden and only called from the parent report.