July 26, 2010 at 9:50 am
Hi, I'm new to Reporting Services and looking for some advice/tips on how to automate an existing report. I have a report that I imported from an Access database. When it converted from Access to Reporting Services, it brought over an input box and converted it into a parameter. The new parameter is called "Department". One requirement I have for this report is to automate it so that when it runs it runs the report and provides detail about a single department. Question I have is....what can I do to automatically loop thru the Department field of the Data Source and then populate this current department into the department parameter? The report has a Department section on it as a Row Group. I'm using Visual Studio 2008 to do my development and the data source is currently looking at the Access database though this will change over to SQL Server in the future. This is my first report in Reporting Services so if I'm not providing enough/correct information, please let me know. Any replies are greatly appreciated!
July 26, 2010 at 9:59 am
You will probably need to create a second dataset. You can use your primary dataset, but chances are the departments are repeated over and over again (and in no particular order). You don't want all that in your drop-down list, you want a distinct list of departments.
Go to the data tab and create a new dataset (from the dataset: drop-down list, select <New Dataset...>). In the query string, you want to generate a distinct lists of departments. Something like this:
SELECT DISTINCT Department FROM
ORDER BY Department
Go into your Report > Report Parameters. For the available values, select "From Query". Pick the dataset name as the dataset, then the department field as both the value and label field. (You can do more complicated things one you get used to how it works).
Hit OK and preview your report. You should now have a drop-down list of departments.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply