Export (0) Print
Expand All

Creating the Report Server Project and the AdventureWorks2008R2_Base Report (SSRS)

SQL Server 2008 R2

In Business Intelligence Development Studio, you can create multiple shared data sources, shared datasets, and reports, and deploy them all in one step. In this tutorial, you will create a Report Server project and solution, and a base report. For each of the AdventureWorks sample reports, you will make a copy of the base report as the starting place for building each individual report.

In this tutorial, you will learn how to do the following:

  • Create and configure a Report Server project

  • Add a blank report to the project.

  • Add a shared data source to the project and a shared data source reference to the report.

  • Add a boolean report parameter.

  • Add and configure the page header with logo and report name.

  • Add and configure the page footer with the report description.

  • Set conditional visibility to enable a user to show or hide content as needed in two ways: based on a text box toggle or on a boolean parameter.

  • Add description text with conditional visibility that can be set by the user based on the boolean parameter.

  • Add description text to the report Description property. The report description is displayed on the report server.

  • Add scenario text with conditional visibility that can be toggled by the user. Scenario text is custom text that describes the questions that the report is designed to answer.

  • Set the page size to export the file to pdf format.

  • Preview and verify the report.

  • Deploy and verify the report.

Estimated time to complete this tutorial: 15 minutes.

To create a solution and set project properties

  1. Start BI Development Studio.

    NoteNote

    If User Account Control (UAC) is supported and enabled on your operating system, to deploy reports to a report server, you must run BI Development Studio under elevated privileges. For more information, see Prerequisites for AdventureWorks 2008R2 Sample Reports (SSRS).

  2. Create a new Report Server project named AdventureWorks 2008R2 Sample Reports.

    NoteNote

    If you have installed the AdventureWorks 2008R2 Sample Reports from CodePlex, use a unique name for the Report Server project to avoid naming conflicts.

  3. Open the property pages for the project. Do the following:

    1. Set TargetDatasetFolder to AdventureWorks 2008R2/Datasets.

    2. Set TargetDataSourceFolder to AdventureWorks 2008R2/DataSources.

    3. Set TargetReportFolder to AdventureWorks 2008R2.

    4. Set TargetReportPartFolder to AdventureWorks 2008R2/ReportParts.

    5. Set TargetServerURL to your report server or SharePoint site. For example, http://<servername>/reportserver.

    6. Verify that TargetServerVersion is SQL Server 2008 R2.

  4. Accept the other default values and create the project.

To add a blank report

  1. In Solution Explorer, right-click the Reports folder, point to Add, and then click New Item.

  2. In Templates, click Report.

  3. Set Name to AdventureWorks2008R2_Base.rdl.

  4. Click Add.

To create a shared data source for AdventureWorks data

  1. In Solution Explorer, create a project shared data source for the AdventureWorks database.

    1. Set Name to AdventureWorks2008R2.

    2. Build a connection string that is similar to the following:

      Data Source=(servername); Initial Catalog=AdventureWorks2008R2

    3. Specify credentials.

  2. In the Report Data pane, add a report data source reference that points to the project data source.

  3. Change Name to AdventureWorks2008R2.

  4. Select Use shared data source reference, and click AdventureWorks2008R2.

A report parameter is created automatically for every dataset query variable, stored procedure input parameter, or shared dataset parameter. You can also manually create a parameter.

In the base report, you will create a Boolean parameter so that a user can show or hide optional information in the report, for example, the report description or expression results.

To add a Boolean parameter for conditional visibility

  1. From the Report Data pane, add a parameter named ShowAll.

  2. On the General page, change Prompt to Show all information?

  3. Change Data type to Boolean.

  4. On the Default Values page, select Specify values.

  5. Add a value and set it to false.

After parameters are published to a report server or SharePoint site the first time, you cannot overwrite parameter properties from BI Development Studio. By design, published parameters are managed only on the report server. To change a property value after it is published, you must update the property directly on the report server, or delete the report and deploy it again.

To add the page header, the logo, and the report name

  1. From the Report menu, add a page header.

  2. In the Report Data pane, add an image. Navigate to the project folder and select AdventureWorksLogo.jpg.

  3. Drag the image to the page header.

  4. In the Image Properties dialog box, set Name to Logo.

  5. Verify that the image source is Embedded.

  6. After you add the image, drag the image handles to expand the size to about 2.5in by .5 in.

  7. In the Report Data pane, expand Built-in Fields, and drag Report Name to the right side of the page header. Format as needed.

  8. Drag the bottom of the page header up to remove extra white space.

You can add a description to the report in the page footer and conditionally hide the content. The user can decide whether to display the information. To display a description on the report server, add the information to the Description property for the report.

To add the page footer and a description that is hidden by default

  1. Add a page footer.

  2. In Footer Properties, clear the option Print on first page.

  3. Add a text box with the following text: <Replace with description.>.

  4. Change the Name of the text box to tbDescription.

  5. Expand the size of the text box to fill the page footer.

  6. Select the text box.

    NoteNote

    The first click in a text box puts you in edit mode. To select the text box after you are in edit mode, press ESC. For more information, see Report Design Tips (Report Builder 3.0 and SSRS).

  7. In Text Box properties, on the Visibility page, select Show or hide based on an expression, and add the following text: =NOT Parameters!ShowAll.Value.

Use the NOT operator because visibility sets the Hidden property on the text box. For the user, the parameter @ShowAll is defined as a positive action. When @ShowAll is true, the text is not hidden.

To add the description as a report property

  1. Click the report background to display Report Properties in the Property pane.

  2. Locate Description, and copy and paste the description text from the previous step.

NoteNote

The maximum number of characters in a description is 512.

After description text is published the first time, you cannot overwrite the report Description property from BI Development Studio. To change a property value after it is published, you must update the property directly on the report server or SharePoint site, or delete the report and deploy it again.

Scenario text describes the purpose of the report. In some reports, this can provide guidance to the user about features in the report. By default, the scenario text is hidden but the user can toggle it on and off.

To add a toggle for text that has conditional visibility

  1. At the top, left of the report, below the page header, add a text box with the following text:

    Click + to view the scenario story.

  2. Change the Name of the text box to tbToggle.

  3. Style the font as needed.

To enable showing or hiding a text box based on a toggle

  1. At the top, right of the report, below the page header, add a text box.

  2. Change the Name of the text box to tbScenario.

  3. In the text box, enter the following text: <Replace with the scenario story for this report.>.

  4. On the Text Box properties, Visibility page, in When the report is initially run, select Hide.

  5. Select Display can be toggled by this report item.

  6. From the drop-down list, select tbToggle.

In later lessons, you will create scenario text for each report, and add links from specific words in the text to bookmarks on related report items.

Report design is not WYSIWIG. You do not see the rendered report until you preview or run it. By design, each data region will automatically expand to accommodate all the data from its related dataset. White space on the report design surface is preserved.

If you plan to print a report or export it to a print-compatible format, to avoid multiple horizontal pages, you must explicitly set the page width and eliminate white space in the report.

To set the page size for .pdf or other print formats

  1. Remove or minimize extra white space in the report by making report items adjacent and dragging the edges of the report next to the edges of the report items.

  2. Right-click the background area of the report design surface, and then click Ruler.

  3. From the Report menu, open Report Properties.

  4. On the Page Setup page, do the following:

    1. Change Width to 7.9375 in.

    2. Verify that the Right and Left margin are no greater than 1 in.

  5. Use the ruler to verify that the page is now less than 7 in.

To preview and verify the report

  1. Preview the report.

  2. Verify that the report runs automatically,

  3. On the report viewer toolbar, verify that ShowAll has True and False radio buttons. By default, the value is False.

  4. In the page header, the logo and the report name appear.

  5. One text box with a toggle appears. When you click the toggle, the scenario text appears.

  6. When you set ShowAll to True and run the report, the description appears in the footer.

To deploy the report

  1. In Solution Explorer, right-click the project, and then click Deploy. Check for errors on the status line of the BI Development Studio or in the Output pane.

    NoteNote

    Because the report depends on a shared data source, the first time you deploy the report, you must deploy the entire project. In later tutorials, you can deploy reports individually.

  2. In a browser, navigate to the report server or SharePoint site, for example, http://localhost/reports or http://SharePointsite/subsite/reports.

  3. Verify that the shared data source and the report appears in the specified folders.

  4. Verify that the description for the report appears.

  5. Edit the shared data source to use credentials that work from the report server. In the base report, you do not retrieve data from AdventureWorks so the report runs without error. The remaining tutorials require that the credentials to the data source work from the report server or SharePoint site.

  6. Verify that the report runs.

You have created a report project and a base report. You can now create a sample report. For more information, see Tutorials: Creating AdventureWorks 2008R2 Sample Reports (SSRS).

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft