Auto Filter in 2005 Reporting Services

ray_newbie_SSIS





Hello Experts,
I have been playing around with RS 2005 and like it so far. I am in the process of replcating some excel reports in RS. I was wondering if there is any way to replicate the Auto Filter Functionality of Excels sheets where column is a dropdown with disticnt values of that specific column and also has Top 10 and Custom filters attached to it. So when selected from dropdown the report filters for these records.
I understand we can use parameters for this kinda report but my client has 15 columns in the report and it will be cool to give them auto filter functionality as in Excel as they are used to it.

Any help will be appreciated.

Geert Verhoeven

Hi,

Reporting Services does not have the possibilities to do this. I think that according what you listed above you need to use a cube in case of reporting services.

Greetz,

Geert

Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

ray_newbie_SSIS

I dont know abt using cubes i just wanted to see if I can programatically create dropdowns in place of textboxes in header section.I just want each column to hold disticnt values of that column (dont care abt top 10 or Custom) and when they select that column it will apply that filter and generate the report.

Any good resources you can point me to where I can see some report customiations were done by coding

Appreciate ur help in advance

Thanks for ur reply again

Geert Verhoeven

Hi,

In reporting services it is really a lot more work since you need to include each special value like 'All' and 'Top 10' in the list of parameters which means using unions etc. It is possible although not advisable. Cubes are made for these kind of things and are optimized to browse through data.

Greetz,

Geert

Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

sqlhiker


one way of doing it is...
Create a model in Report Builder where your fields are listed as attributes. Then design a excel like report using Report Builder Tabular format and deploy this rdl file on the server.

The end user can open this rdl file in report builder and pick his values of interest from the "Filter" in report builder (which I feel is very powerful) and re-run the report.

ray_newbie_SSIS

Hello,

Thats a good suggestion abt using Report Model.

1. If I can use a filter on Report Model, I can create a parametric report with default parameters and let the users choose column values from parameters but appreciate the idea though
2. Secondly, is there a way to have a standard template for Report Model where users can create ad hoc reports and all of them have a similar look.(Using RS 2005 Ent with SQL 2005)
3. Lastly, I have found this link which promises to do something similar but cannot get it to work. Does this make sense to u guys

http://blogs.msdn.com/chrishays/archive/2004/07/15/184646.aspx

Regards