August 29, 2013 at 1:31 pm
I want so badly to use SSRS but every time I try its like pulling teeth. Nothing makes logical sense and what’s left is vague.
I have a very simple report that’s using a very simple query from a SQL Server DB. All I want to do is add to the report a few optional filter choices and every method I try fails with some kind of error.
My query is as follows:
SELECT BookID, BookAuthor, BookName, PublicationDate
FROM BOOKS
The first field is a numeric value (the Primary key), the next 2 are VARCHAR fields and the last is a DATETIME value. I can get the report to work without filter/parameters but that’s a half done report. How do I add a filter that will:
A)Present the user with a list of choice in which they can select one or moany from? The list is a DISTINCT list of BookAuthor from the query. This needs to be optional so if they don’t want o filter by Book Author they don’t have to. I’d also prefer to use a description/label for the filter option that is more user friendly. Something like ‘Author’ is better then BookAuthor.
B)I want to add a Date Range filter option that is applied to the Publication Date. The user can enter a FROM Date and a TO date or just one or neither and the report will filter accordingly.
C)The query in the report does not contain every column from the BOOKS table. One of these columns is BookStatus. The value in BookStatus consists of several text or VARCHAR values. I’d like to have a filter that lets the user optionally pick one or more Status values. That means that the Filter must be multi-select and must perform a SELECT DISTINCT BookStatus FROM BOOKS to get a proper list of choices for the filter.
I believe the above 3 fully cover the various types of filters that I am unable to determine how to create in SSRS. In Crystal Reports this was easy but not in SSRS. Any help even if it’s a link to a How To would be appreciated because the SSRS How-To’s I’ve been through already all require that the filter be used or just don’t work period and I have no idea what it is I’m missing.
Thanks
Kindest Regards,
Just say No to Facebook!August 29, 2013 at 3:46 pm
It looks like the answer on how to add "Optional" filters to an SSRS report is "Switch to Crystal Reports or some other established Reporting tool" because SSRS no do optional filters.
I've been searching the net trying to get an answer to how to do this and it never occurred to me that it might not be something you can do in SSRS. When SSRS first hit the scene over a decade ago one could excuse missing features in the product even when they were features/functionality that the other major reporting tools have had for some time. After 10 years and several revisions its now (IMHO) inexcusable for SSRS to continue down this path.
Apologies if I offend any pro-SSRS users out there but I'm just so frustrated with SSRS and its anti-traditional report design approach to reporting. While using xml as the source code behind the report is a great idea it seems to me like the rest of SSRS's "reinvent the reporting wheel" approach was more about marketing/sales then building a better reporting tool.
Thanks
Kindest Regards,
Just say No to Facebook!Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply