Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate SQL, Power BI, Fabric, and AI community-led event. March 31 - April 2. Use code MSCUST for a $150 discount. Prices go up Feb 11th.
Register todayThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
In this tutorial, you use a wizard in Report Builder to create a bar chart in a Reporting Services paginated report. Then you add a filter and enhance the chart.
A bar chart displays category data horizontally. This visualization can help to:
The following illustration shows the bar chart that you create in this tutorial. It shows the sales for 2014 and 2015 for the top five salespeople, from most to least 2015 sales.
Note
In this tutorial, the steps for the wizard are consolidated into one procedure. For step-by-step instructions about how to browse to a report server, create a dataset, and choose a data source, see the first tutorial in this series: Tutorial: Create a basic table report (Report Builder).
Estimated time to complete this tutorial: 15 minutes.
For more information about requirements, see Prerequisites for tutorials (Report Builder).
In which you create an embedded dataset, choose a shared data source, and create a bar chart by using the Chart Wizard.
Note
In this tutorial, the query contains the data values so that it does not need an external data source. This makes the query quite long. In a business environment, a query would not contain the data. This is for learning purposes only.
Start Report Builder from the Reporting Services web portal, from the report server in SharePoint integrated mode, or from your computer.
The Getting Started dialog box appears.
If you don't see the Getting Started dialog box, select File >New. The New Report or Dataset dialog box has most of the same contents as the Getting Started dialog box.
In the left pane, verify that New Report is selected.
In the right pane, select Chart Wizard.
On the Choose a dataset page, select Create a dataset, and then choose Next.
On the Choose a connection to a data source page, select an existing data source. Or browse to the report server, and choose a data source. Then select Next. You might 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 Alternative ways to get a data connection (Report Builder).
On the Design a query page, select Edit as Text.
Paste the following query into the query pane:
SELECT 'Luis' as FirstName, 'Alverca' as LastName, CAST(170000.00 AS money) AS SalesYear2015, CAST(150000. AS money) AS SalesYear2014
UNION SELECT 'Jeffrey' as FirstName, 'Zeng' as LastName, CAST(210000. AS money) AS SalesYear2015, CAST(190000. AS money) AS SalesYear2014
UNION SELECT 'Houman' as FirstName, 'Pournasseh' as LastName, CAST(150000. AS money) AS SalesYear2015, CAST(180000. AS money) AS SalesYear2014
UNION SELECT 'Robin' as FirstName, 'Wood' as LastName, CAST(75000. AS money) AS SalesYear2015, CAST(175000. AS money) AS SalesYear2014
UNION SELECT 'Daniela' as FirstName, 'Guaita' as LastName, CAST(170000. AS money) AS SalesYear2015, CAST(175000. AS money) AS SalesYear2014
UNION SELECT 'John' as FirstName, 'Yokim' as LastName, CAST(160000. AS money) AS SalesYear2015, CAST(195000. AS money) AS SalesYear2014
UNION SELECT 'Delphine' as FirstName, 'Ribaute' as LastName, CAST(180000. AS money) AS SalesYear2015, CAST(205000. AS money) AS SalesYear2014
UNION SELECT 'Robert' as FirstName, 'Hernady' as LastName, CAST(140000. AS money) AS SalesYear2015, CAST(180000. AS money) AS SalesYear2014
UNION SELECT 'Tanja' as FirstName, 'Plate' as LastName, CAST(150000. AS money) AS SalesYear2015, CAST(160000. AS money) AS SalesYear2014
UNION SELECT 'David' as FirstName, 'Bradley' as LastName, CAST(210000. AS money) AS SalesYear2015, CAST(180000. AS money) AS SalesYear2014
UNION SELECT 'Michal' as FirstName, 'Jaworski' as LastName, CAST(175000. AS money) AS SalesYear2015, CAST(220000. AS money) AS SalesYear2014
UNION SELECT 'Chris' as FirstName, 'Ashton' as LastName, CAST(195000. AS money) AS SalesYear2015, CAST(205000. AS money) AS SalesYear2014
UNION SELECT 'Pongsiri' as FirstName, 'Hirunyanitiwatna' as LastName, CAST(175000. AS money) AS SalesYear2015, CAST(215000. AS money) AS SalesYear2014
UNION SELECT 'Brian' as FirstName, 'Burke' as LastName, CAST(187000. AS money) AS SalesYear2015, CAST(207000. AS money) AS SalesYear2014
(Optional) Select the Run button (!) to see the data your chart is based on.
Select Next.
On the Choose a chart type page, the column chart is the default chart type.
Select Bar, and then choose Next.
On the Arrange chart fields page, there are four fields in the Available fields pane: FirstName, LastName, SalesYear2015, and SalesYear2014.
Drag LastName to the Categories pane.
Drag SalesYear2015 to the Values pane. SalesYear2015 represents the sales amount for each salesperson for the year 2015. The Values pane displays [Sum(SalesYear2015)]
because the chart displays the aggregate for each product.
Drag SalesYear2014 to the Values pane under SalesYear2015. SalesYear2014 represents the sales amount for each salesperson for the year 2014.
Select Next.
Select Finish.
The chart is added to the design surface. The new bar chart just shows representational data. The legend reads Last Name A, Last Name B, etc., rather than the people's names, just to give an idea of what your report should look like.
Select the chart to display the chart handles. Drag the bottom-right corner of the chart to increase the size of the chart. Notice the design surface gets larger as you drag.
Select Run to preview the report.
The bar chart displays sales for each sales person for the years 2014 and 2015. The length of the bar corresponds to the sales total.
By default, only some of the values on the vertical axis appear. You can change the chart to display all categories.
Switch to report design view.
Right-click the vertical axis, then select Vertical Axis Properties.
Under Axis range and interval, in the Interval box, type 1.
Select OK.
Select Run to preview the report.
Note
If you cannot read the salesperson names on the vertical axis, you can make your chart taller or change the formatting options for the axis labels.
You can change the category expression to include last name followed by first name of each sales person.
Switch to report design view.
Double-click the chart to display the Chart Data pane.
In the Category Groups area, right-click [LastName], and then select Category Group Properties.
In Label, select the expression (Fx) button.
Type the following expression: =Fields!LastName.Value & ", " & Fields!FirstName.Value
This expression concatenates the last name, a comma, and the first name.
Select OK.
Select OK.
Select Run to preview the report.
If the first names don't appear when you run the report, you can refresh the data manually. While still in preview mode, on the Run tab in the Navigation group, select Refresh.
Note
If you cannot read the salesperson names on the vertical axis, you can make your chart taller or change the formatting options for the axis labels.
When you sort the data on a chart, you're changing the order of values on the category axis.
Switch to report design view.
Double-click the chart to display the Chart Data pane.
In the Category Groups area, right-click [LastName], and then select Category Group Properties.
Select Sorting. The Change sorting options page displays a list of sort expressions. By default, this list has one sort expression that is the same as the original category group expression.
In Sort by, select [SalesYear2015].
in the Order list, select A to Z so that the names appear in order from largest to smallest 2015 sales.
Select OK.
Select Run to preview the report.
The names on the horizontal axis are sorted from largest to smallest 2015 sales, with Zeng at the top.
To improve the readability of the chart values, you might want to move the chart legend. For example, in a bar chart where bars are shown horizontally, you can change the position of the legend so that it is above the chart or below the chart area. This positioning gives more horizontal space to the bars.
Switch to report design view.
Right-click the legend on the chart.
Select Legend Properties.
For Legend position, select a different position. For example, set the position to the middle bottom option.
When the legend is placed at the top or bottom of a chart, the layout of the legend changes from vertical to horizontal. You can select a different layout from the Layout drop-down list.
Select OK.
Select Run to preview the report.
Switch to report design view.
Select the words Chart Title at the top of the chart, then enter: Sales for 2014 and 2015.
In the Properties pane, with the title selected, set Color to Black and FontSize to 12pt.
Select Run to preview the report.
By default, the horizontal axis displays values in a general format that is automatically scaled to fit the size of the chart. You can change it to the currency format.
Switch to report design view.
Select the horizontal axis along the bottom of the chart to select it.
On the Home tab, go to Number group > Currency. The horizontal axis labels change to currency.
(Optional) Remove the decimal digits. Near the Currency button, select the Decrease Decimal button twice.
Right-click the horizontal axis, and select Horizontal Axis Properties.
On the Number tab, select Show values in Thousands.
Select OK.
Right-click the horizontal axis, and select Show Axis Title.
In the Axis Title box, enter Sales in thousands and press Enter.
Note
While you're entering, the Axis Title box appears to be on the vertical axis. But when you press Enter, it goes to the horizontal axis.
Select Run to preview the report.
The report displays the sales amount on the horizontal axis as currency in thousands, with no decimal digits.
You can add a filter to the chart to specify which data from the dataset to include or exclude in the chart.
Switch to report design view.
Double-click the chart to display the Chart Data pane.
In the Category Groups area, right-click the [LastName] field, and then select Category Group Properties.
Select Filters. The Change filters page can display a list of filter expressions. By default, this list is empty.
Select Add. A new blank filter appears.
In Expression, enter [Sum(SalesYear2015)]. This expression creates the underlying expression =Sum(Fields!SalesYear2015.Value)
, which you can see if you select the fx button.
Verify that the data type is Text.
In Operator, select Top N from the drop-down list.
In Value, enter the following expression: =5
Select OK.
Select Run to preview the report.
If the results aren't filtered when you run the report, you can refresh the data manually. On the Run tab in the Navigation group, select Refresh.
The chart shows the top five salesperson names from the 2015 sales data.
On the design surface, select Click to add title.
Enter Sales Bar Chart. Press ENTER, and then enter Top Five Sellers for 2015, so it looks like this:
Sales Bar Chart
Top Five Sellers for 2015
Select Sales Bar Chart, and choose the Bold button.
Select Top Five Sellers for 2015, and in the Font section on the Home tab, set the font size to 10.
(Optional) You might need to make the Title text box taller, and bring down the top of the bar chart, to accommodate the two lines of text.
This title appears at the top of the report. When there's no page header defined, items at the top of the report body are the equivalent of a report header.
Select Run to preview the report.
Switch to report design view.
Select File > Save As.
In Name, enter Sales Bar Chart.
You can save it either to your computer or to the report server.
Select Save.
Events
Mar 31, 11 PM - Apr 2, 11 PM
The ultimate SQL, Power BI, Fabric, and AI community-led event. March 31 - April 2. Use code MSCUST for a $150 discount. Prices go up Feb 11th.
Register todayTraining
Module
Create and share your first Power BI report - Training
With Power BI, you can create compelling visuals and reports. In this module, you learn how to use Power BI Desktop to connect to data, build visuals, and create a report that you can share with others in your organization. You then learn how to publish the report to the Power BI service, so that others can see your insights and benefit from your work.
Certification
Microsoft Certified: Power BI Data Analyst Associate - Certifications
Demonstrate methods and best practices that align with business and technical requirements for modeling, visualizing, and analyzing data with Microsoft Power BI.
Documentation
Tutorial: Add a sparkline to your report (Report Builder) - SQL Server Reporting Services (SSRS)
Learn how to use the Report Builder to create a basic table with a sparkline chart in a Reporting Services paginated report.
Charts in a paginated report - Microsoft Report Builder & Power BI Report Builder
Use chart data regions to help readers of your paginated reports understand large volumes of aggregated data at a glance in Report Builder.
Tutorial: Add a KPI to your report (Report Builder) - SQL Server Reporting Services (SSRS)
Learn how to add a key performance indicator (KPI) to a Reporting Services paginated report in Report Builder.