Richmond SQL Server Users Group

Hello Everyone,

I am a new SQL Server  user, and am having to teach myself all the ins and outs. I am working with Reporting Services trying to create Ad Hoc reports with preset user parameters.( My boss does not want the end user to be able to use Report Builder.) I need to do this with one table. For example: Table 50: Columns: incidents, mode, jurisdiction, area, class, and year; I need to be able to create a parameter for each column and generate a report based on the parameter selections. I just can't quite get it all to work together just right.

Has any one had to create a report with similar issues; using just one table? Any pointers would be extremely appriciated!

 

Thanks,

NinjaDragonfly (aka Karen)

 

 

I just joined this community moments ago, but I have some experience with Reporting Services, and I've done several with user-supplied parameters like you are trying to do. It is quite easy to add a parameter to the Data Source query. All you have to do is add a T-SQL variable to the query statement, generally in the WHERE clause. SSRS will recognize that it needs a value to fill the parameter, and it will generate a prompt at the top of the report output page to capture the user input. It defaults to a data type of text generally, but you can further define what data type you want, and you can even provide a default value. All you have to do is go to the Report > Report Parameters menu option while you are on the Layout tab. For example you could have as part of your query: WHERE [table].YearColumn = @ReportYear Then using the Report Parameters option you can define Year as an integer value and even define the default value of @ReportYear as year(getdate()) to always default to the current year. This is the easiest way to define parameters. Hope this helps, Geoff