After running Report Builder and opening your report you will see something like this;
Expand the "Datasets" node in the treeview on the left;
If you right-click the active Dataset (DataSet1 - highlighted above) and then select "Dataset Properties";
This screen is showing you a lot of technical details about the Query you have put together. Click "Query Designer ..." to get to something more meaningful. To use the "Dataset Properties" screen you will need training.
The "Query Designer" screen appears and, if you used the wizard to generate your reports, you'll remember it from them:
Click on the "Filter" icon in the bar at the top. This will display the "Filter Data" screen:
Scroll down the Field list (bottom left) until you find the field you're interested in adding as a parameter. For the sake of this example I'm going to add the "INVOICE DATE" field. When you find the field you're after you can either double-click it or drag/drop it into the "xxxx INVOICESs with:" area):
If you hover over the "equals" (next to INVOICE DATE) it will turn into a click-able link. Click it:
Select "On or After" in the list and then hover over the "INVOICE DATE" (which will also turn into a click-able link) and right-click it:
Select "Prompt" (this means the user will get a prompt).
Click "OK" to save the changes and close the dialog.
We are now back to the "Query Designer" main page and it looks like nothing has changed. Click "Run" button:
A dialog will now appear asking for a value for the "INVOICE DATE" parameter.
Click "OK" and the report will run and give you values for the specified date.
Click "OK" to save the changes to the Query and then click "OK" to save changes to the Dataset and return to the main editor.
Click "Run" at the top left:
This will switch to the report run viewer and, as there is a report, will prompt you at the top of the screen for a value:
Enter "20-APR-2010" and either hit return or click "View Report". The report will now run with the specified parameter.
3 comments:
Excelent, helped a lot. Info about this was hard to find.
Do you know whether this is more efficient than using the "Filters" tab within the dataset properties box?
Generally I have created my parameters manually (R-Click "Parameters", choose "Add Parameter...",etc.) and then added an item to the Filters set using the expression to choose my parameter.
I have suspected that this might return all rows from the DB and then filter afterwards, whereas your method might cause a "WHERE" in the query and so be more efficient. Looking at the XML semantic query, it is different but is in the same general place.
Any ideas?
Sadly the only way it can possibly work is the way you suspect; getting all rows and then applying a filter. We ended up abandoning Datamodels as a means of working with the data but I suppose with a really small select data source it would still be workable.
Fundamentally we have only about 10 people who need to write reports and it just proved to be a lot easier to give them all Report Builder and the SQL training necessary for them to generate queries. I'm sure in a larger organisation this would be different!
Post a Comment