Skip to content

Latest commit

 

History

History
237 lines (143 loc) · 13.7 KB

tutorial-create-a-quick-chart-report-offline-report-builder.md

File metadata and controls

237 lines (143 loc) · 13.7 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom helpviewer_keywords
Tutorial: Create a Quick Chart Report Offline (Report Builder)
In this tutorial, you create a pie chart in a Reporting Services paginated report in Report Builder. Then you add percentages and modify the pie chart.
maggiesMSFT
maggies
05/30/2017
reporting-services
report-builder
conceptual
updatefrequency5
reports, creating
tutorials, getting started
creating reports

Tutorial: Create a Quick Chart Report Offline (Report Builder)

In this tutorial, you use a wizard to create a pie chart in a [!INCLUDEssRSnoversion] paginated report in [!INCLUDEssRBnoversion]. Then you add percentages and modify the pie chart a little.

You can do this tutorial two different ways. Both methods have the same outcome-a pie chart like the one in this illustration:

:::image type="content" source="media/tutorial-create-a-quick-chart-report-offline-report-builder/report-builder-quick-pie-chart.png" alt-text="Report Builder quick pie chart.":::

Prerequisites

Whether you use XML data or a [!INCLUDEtsql] query, you need to have access to Report Builder. You can start [!INCLUDEssRBnoversion] from a [!INCLUDEssRSnoversion] report server in native mode or in SharePoint integrated mode, or you can download [!INCLUDEssRBnoversion] from the Microsoft Download Center. For more information, see Install Report Builder.

Two Ways To Do This Tutorial

Use XML data for this tutorial

You can use XML data that you copy from this topic and paste into the wizard. You don't need to be connected to a [!INCLUDEssRSnoversion] report server in native mode or in SharePoint integrated mode, and you don't need access to an instance of SQL Server.

Create the pie chart with XML data

Use a [!INCLUDEtsql] query that contains data for this tutorial

You can copy a query with data included in it from this topic and paste it into the wizard. You will need the name of an instance of SQL Server and credentials sufficient for read-only access to any database. The dataset query in the tutorial uses literal data, but the query must be processed by an instance of SQL Server to return the metadata that is required for a report dataset.

The advantage of using the [!INCLUDEtsql] query is that all the other [!INCLUDEssRBnoversion] tutorials use the same method, so when you do the other tutorials, you will already know what to do.

The [!INCLUDEtsql] query does require a few other prerequisites. For more information, see Prerequisites for Tutorials (Report Builder).

Create the pie chart with a Transact-SQL query that contains data

Create the pie chart with XML data

  1. Start Report Builder from the [!INCLUDEssRSnoversion] web portal, or the report server in SharePoint integrated mode, or from your computer.

    The Getting Started dialog box appears.

    :::image type="content" source="media/tutorial-create-a-quick-chart-report-offline-report-builder/report-builder-get-started.png" alt-text="Report Builder Get Started dialog box.":::

    If the Getting Started dialog box does not appear, select File >New. The New Report or Dataset dialog box has most of the same contents as the Getting Started dialog box.

  2. In the left pane, verify that New Report is selected.

  3. In the right pane, select Chart Wizard, and then select Create.

  4. In the Choose a dataset page, select Create a dataset, and then select Next.

  5. In the Choose a connection to a data source page, select New.

    The Data Source Properties dialog box opens.

  6. You can name a data source anything you want. In the Name box, type MyPieChart.

  7. In the Select connection type box, select XML.

  8. Select the Credentials tab, select Use current Windows user. Kerberos delegation might be required, and then select OK.

  9. In the Choose a connection to a data source page, select MyPieChart, and then select Next.

  10. Copy the following text and paste it in the large box in the top of the Design a query page.

    <Query>
<ElementPath>Root /S  {@Sales (Integer)} /C {@FullName} </ElementPath>
<XmlData>
<Root>
<S Sales="150">
  <C FullName="Jae Pak" />
</S>
<S Sales="350">
  <C FullName="Jillian  Carson" />
</S>
<S Sales="250">
  <C FullName="Linda C Mitchell" />
</S>
<S Sales="500">
  <C FullName="Michael Blythe" />
</S>
<S Sales="450">
  <C FullName="Ranjit Varkey" />
</S>
</Root>
</XmlData>
</Query>

  11. (Optional) Select the Run button (!) to see the data your chart will be based on.

    :::image type="content" source="media/tutorial-create-a-quick-chart-report-offline-report-builder/rb-designquery.png" alt-text="Report Builder Design Query.":::

  12. Select Next.

  13. In the Choose a chart type page, select Pie, and then select Next.

  14. In the Arrange chart fields page, double-click the Sales field in the Available fields box.

    It automatically moves to the Values box, because it is a numerical value.

    :::image type="content" source="media/tutorial-create-a-quick-chart-report-offline-report-builder/rb-wizarrangefields.png" alt-text="Report Builder Wizard Arrange Fields.":::

  15. Drag the FullName field from the Available fields box to the Categories box (or double-click it; it will go to the Categories box), and then select Next.

    The Preview page shows your new pie chart with representational data. The legend reads Full Name 1, Full Name 2, etc., rather than the salespeople's names, and the size of the slices of pie are not accurate. This is just to give you an idea of what your report will look like.

    :::image type="content" source="media/tutorial-create-a-quick-chart-report-offline-report-builder/rb-newchartpreview.png" alt-text="Report Builder New Chart Preview.":::

  16. Select Finish.

    Now you see your new pie chart report in Design View, still with representational data.

    :::image type="content" source="media/tutorial-create-a-quick-chart-report-offline-report-builder/rb-newpiedesign.png" alt-text="Report Builder New Pie in Design View.":::

  17. To see your actual pie chart, select Run on the Home tab of the Ribbon.

    :::image type="content" source="media/tutorial-create-a-quick-chart-report-offline-report-builder/rb-newchartrun.png" alt-text="Report Builder New Chart Run.":::

  18. To continue modifying your pie chart, go to After You Run the Wizard in this article.

Create the pie chart with a [!INCLUDEtsql] query

  1. Start Report Builder from the [!INCLUDEssRSnoversion] web portal, from the report server in SharePoint integrated mode, or from your computer.

    The Getting Started dialog box appears.

    [!NOTE]
    If the Getting Started dialog box does not appear, select File >New. The New Report or Dataset dialog box has most of the same contents as the Getting Started dialog box.

  2. In the left pane, verify that New Report is selected.

  3. In the right pane, select Chart Wizard, and then select Create.

  4. In the Choose a dataset page, select Create a dataset, and then select Next.

  5. In the Choose a connection to a data source page, select an existing data source or browse to the report server and select a data source, and then select Next. You may need to enter a user name and password.

    [!NOTE]
    The data source you choose is unimportant, as long as you have adequate permissions. You will not be getting data from the data source. For more information, see Prerequisites for Tutorials (Report Builder).

  6. On the Design a Query page, select Edit as Text.

  7. Paste the following query into the query pane:

    SELECT 150 AS Sales, 'Jae Pak' AS FullName
UNION SELECT 350 AS Sales, 'Jillian Carson' AS FullName
UNION SELECT 250 AS Sales, 'Linda C Mitchell' AS FullName
UNION SELECT 500 AS Sales, 'Michael Blythe' AS FullName
UNION SELECT 450 AS Sales, 'Ranjit Varkey' AS FullName

  8. (Optional) Select the Run button (!) to see the data your chart will be based on.

  9. Select Next.

  10. In the Choose a chart type page, select Pie, and then select Next.

  11. In the Arrange chart fields page, double-click the Sales field in the Available fields box.

    It automatically moves to the Values box, because it's a numerical value.

  12. Drag the FullName field from the Available fields box to the Categories box (or double-click it; it will go to the Categories box), and then select Next.

  13. Select Finish.

    You're now looking at your new pie chart report on the design surface. What you see is representational. The legend reads Full Name 1, Full Name 2, etc., rather than the salespeople's names, and the size of the slices of pie are not accurate. This is just to give you an idea of what your report will look like.

  14. To see your actual pie chart, select Run on the Home tab of the Ribbon.

After You Run the Wizard

Now that you have your pie chart report, you can play with it. On the Run tab of the Ribbon, select Design, so you can continue modifying it.

Make the chart bigger

You may want the pie chart to be bigger.

  • Select the chart, but not on any element in the chart, to select it and drag the lower-right corner to resize it.

Notice the design surface gets larger as you drag.

Add a report title

  1. Select the words Chart title at the top of the chart, and then type a title, such as Sales Pie Chart.
  2. With the title selected, in the Properties pane, change Color to Black and FontSize to 12pt.

Add percentages

  1. Right-click the pie chart and select Show Data Labels. The data labels appear within each slice on the pie chart.

  2. Right-click the labels and select Series Label Properties. The Series Label Properties dialog box appears.

  3. In the Label data box, type #PERCENT{P0}.

    The {P0} gives you the percentage without decimal places. If you type just #PERCENT, your numbers will have two decimal places. #PERCENT is a keyword that performs a calculation or function for you; there are many others.

  4. Select Yes to confirm you want to set UseValueAsLabel to False.

  5. On the Font tab, select Bold and change Color to White.

  6. Select OK.

For more information about customizing chart labels and legends, see Display Percentage Values on a Pie Chart (Report Builder and SSRS) and Change the Text of a Legend Item (Report Builder and SSRS).

What's Next?

Now that you have created your first report in [!INCLUDEssRBnoversion], you are ready to try the other tutorials and to start creating reports from your own data. To run [!INCLUDEssRBnoversion], you need permission to access your data sources, such as databases, with a connection string, which actually connects you to the data source. Your system administrator will have this information and can set you up.

To work through the other tutorials, you need the name of an instance of SQL Server and credentials sufficient for read-only access to any database. Your system administrator can also set that up for you.

Finally, to save your reports to a report server or a SharePoint site that is integrated with a report server, you need the URL and permissions. You can run any report you create directly from your computer, but reports have more functionality when run from the report server or SharePoint site. You need permissions to run your reports or others from the report server or SharePoint site where they are published. Talk to your system administrator to obtain access.

It may help to read about some of the concepts and terms before you get started. See Reporting Services Concepts (SSRS). Also, spend some time planning, before you create your first report. It will be time well spent. See Planning a Report (Report Builder).

Next steps

More questions? Try asking the Reporting Services forum