The report you just created is a bit ugly. Switch back to
the Layout tab and drag columns around as needed to make things look a bit
nicer. You can keep toggling back and forth between Layout and Preview mode as
needed just keep entering "20" in as the location each time to get
the report to render.
Once you have formatted things to look a bit nicer, it is
time to address the issue of entering 20 into the location field. If you look
at the SQL that you used to generate the report, you will notice that it used a
@location parameter to specify the location of parts
for the query. Each time you go to render the report, SSRS detects that a
parameter is needed and prompts you for it before you can view the report. In
this case, "20" just happens to be the ID for the Frame
Welding "location" at AdventureWorks.
To make this a bit more user-friendly you can do the following.
1.
Switch to the Data tab in the Designer.
From the Dataset dropdown at the top of the Data designer, select the <New
Dataset> option. In the Dataset dialog that
ensues, give your new datasource a Name of LocationLookup and enter the following code for the Query string value.
Listing
2
SELECT
LocationID,[Name]
FROM
Production.Location
2.
Click the OK button to save your changes.
3.
Now, select the Report | Report
Parameters menu option in Visual Studio. The left hand side of the Report Parameters designer shows location as one of the
parameters. If multiple parameters were needed in this report, you could
define their behaviors and characteristics with this tool. In the Properties pane, specify Inventory Location
as the Prompt and then select the From
query radio button in the Available values
section of the designer. Then select LookupLocation
as the Dataset you wish to use for this parameter and
configure it to use the LocationID for the Value field and the Name column for
the Label field. Your results should look similar to
those in Figure 3. Click OK to save your changes.
Figure
3
Now when you switch to the Preview
pane, you are presented with a dropdown that lets you specify which location
you wish to see inventory levels for. Just select one from the dropdown and
you will get a tailored report.
In Visual Studio, save all of your changes once you are done
modifying the formatting and then right click on your Report in the Solution Explorer to click on the Deploy
menu option. Your report will now be up on your specified Report Server.
