Thursday, March 22, 2012

Couldn't make Filter Option of Report to work

I am trying to figure out how to create report using dynamic parameter and filter from dropdown. I saw a webcast that allow to drag and drop the filter functionality in the dataset and generate report. I have the SQL Server Beta 2 and VS 2005 Beta installed in my machine.
But I didn't see any option in data design that allow me to do the filtering. I can add report parameter but was unable to bind the parameter value to filter expression. I am really confuse and any help will be appreciated.

Are you using a relational database as your data source or Analysis Services? Only Analysis Services has the drag and drop filter functionality. If you are using relational, you have to add the query parameters and then the designer will autogenerate report parameters.

Take a look at some of the AdventureWorks sample reports and walkthroughs it should help.

|||Thank you Brian for your reply. It cleared lots of thing for me. Yeah I was try to get the filter from relational database.

I saw a demo of AdventureWorks sample in one MSDN event. I thought I can do the same with all the database.

I hadn't work with Analysis service before. I tried to get the AdventureWorks sample in my computer and I am SQL Server Beta 2(full including analysis and reporting service) and VS 2005 Beta 2. But I couldn't figure out how to get the sample working. I couldn't get the analysis service to get the AdventureWorks database hooked up. May be I have find more about analysis service. I am building a reporting project and may be it is a better solution..

But currently my problem is creating dynamic report from parameter selection. I tried the MSDN solution but I have more complex query. I am kind of lost using Dataset filters property. I have a paramter declared using Report Paramter. In my sql I have group by clause. I want to filter the record depending on the parameter selection at run time. Therefore, I was trying to use filters tab from dataset. But I don't know how to set the expression and value. I know I have to get the value from form paramter and the expression should be the field name but it is not working.

But thanks for showing me the right direction.|||Just install the relational database samples for AdventureWorks. There are several examples of dynamic parameters.

For any filter with a dynamic value list, you need two queries. The first brings back the list of values. The second uses a single value and has a parameter that is bound to a report parameter populated from the first query.

There is a tutorial in the Books Online called "Using a Dynamic Query in a Report" that has an example of this.

There are also several really good books on Reporting Services that might help. There is a list at http://www.microsoft.com/sql/reporting/techinfo/books.mspx.

No comments:

Post a Comment