Reporting Services Advanced Features

I am looking to find more information regarding advanced filtering/report parameters available in SQL Server 2005 Reporting Services. From what I can see, the user can set values for each report parameter and the query defaults to "where x = (param 1) and y = (param 2)" etc. Is there a way that a user can pick parameters that structure a more complex where clause? For example, say I want to filter by all clients enrolled in all of these 3 service programs and have a dog or clients not enrolled in one of the following 3 care programs or clients who live in one of the following cities? So it would be like this:

where (((sp = 1 and sp = 2 and sp = 3) and animal = dog) or ((cp not in (1, 2, 3)) or city in (a, b, c, d)))

In addition, I am looking for more information regarding programmatic capabilities in Reporting Services. Can anyone point me to a good place for this information? I would like to know whether we could structure part of a query in VB.NET code and then pass this query to Reporting Services to join with the basic report query. Also, I would like to know Reporting Services' capability to interact with stored procedures.

In addition, is Reporting Services more for internal business reporting, or do companies add it onto their existing software package to sell to external customers? Does Reporting Services allow one report to be created, and then users (our customers) on different servers who use different databases to run the report by giving Reporting Services their credentials (database, SQL Server login, etc)?

Basically, I have just been following the tutorials on Microsoft's site, but they are all pretty basic so I haven't gotten a good feel for whether this product is good for advanced reporting needs.

Thanks!

Anonymous wrote:

I am looking to find more information regarding advanced filtering/report parameters available in SQL Server 2005 Reporting Services. From what I can see, the user can set values for each report parameter and the query defaults to "where x = (param 1) and y = (param 2)" etc. Is there a way that a user can pick parameters that structure a more complex where clause? For example, say I want to filter by all clients enrolled in all of these 3 service programs and have a dog or clients not enrolled in one of the following 3 care programs or clients who live in one of the following cities? So it would be like this:

where (((sp = 1 and sp = 2 and sp = 3) and animal = dog) or ((cp not in (1, 2, 3)) or city in (a, b, c, d)))

You can add pretty much any type of WHERE clause you'd like, since you control the query that is being issued. The default interface is best suited to modifying the value portion of a where clause, not adding new columns or changing operators. It is possible, but it is not as easy to implement.

Anonymous wrote:

In addition, is Reporting Services more for internal business reporting, or do companies add it onto their existing software package to sell to external customers? Does Reporting Services allow one report to be created, and then users (our customers) on different servers who use different databases to run the report by giving Reporting Services their credentials (database, SQL Server login, etc)?

It is used for both. Microsoft uses Reporting Services in the Dynamics products, for example.

|||Can you point me to some tutorials and/or articles that provide more details? For the query above, I want the user to specify the columns, values, and operators for the where clause and then be able to generate the query from their specifications.

How can Reporting Services be used to allow a user to log into our application, select the databases to work with, and then have the report generate according to user login?|||

If your requirement is that the user by allowed to create their own where clauses, you might want to investigate Report Builder as an option. It provides all the GUI for accomplishes this, and can allow users to build their own reports on the fly. There are other third party tools that allow this as well.

The other path is to build your own front end to manage the creation of the where clause. It's more work, but you have full control. If you want to go this route, I'd recommend checking out a few books on Reporting Services, as many of them have chapters that deal with this to some degree.