Adding a Chart and defining a Custom Palette in Reporting Services 2008

This article will provide you with a tip on how to define your own palette for charts, using Reporting Services 2008. We will be using Report Builder 2.0 for this example.

This article assumes the following:

  • SQL Server 2008 and Reporting Services installed
  • Business Intelligence Development Studio (BIDS) or Report Builder 2.0
  • AdventureWorks database
  • Experience working with Reporting Services
  • Have experience in creating both a datatsource and dataset

Create report

1) Open Report Builder 2.0 from Start >> All Programs >> Microsoft SQL Server 2008 Report Builder 2.0.

2) From the upper left corner, click the red Report Builder Ribbon button, click Save As.

3) From within the left pane, select Recent Sites and Servers. Navigate to the location of where you would like to save your report.

4) Enter a Name (SalesChartDemo.rdl) for your report and click OK.

Create Data Source

For this section, you may follow the steps from Introduction to Report Builder 3.0 Part 2 Creating a Report. This site will provide details along with images.

1) From the Report Data pane, create a new data source; select an existing shared data source.

2) Name the data source AdventureWorks. 

3) You will be using the AdventureWorks sample database for this demo, please select this db as the source.

4) Click OK to save and close the Data Source Properties window.

reporting builder 2.0

Create Dataset

1) From within the Report Data pane, select New >> Data Set

2) Make sure the Query item is selected in the left pane of the Dataset Properties window. Enter "Sales" in the Name field.

3) Select "AventureWorks" from the Data Source dropdown, or the name you gave your data source.

4) Copy and paste the following T-SQL script to the Query textbox.

SELECT SalesTerritory, SUM([2004]) AS Y2004
FROM Sales.vSalesPersonSalesByFiscalYears
GROUP BY SalesTerritory

reporting builder 2.0

5) Click OK to save and exit the Dataset Properties window.

reporting builder 2.0

Add Chart to Report

1) Click on the Chart Wizard icon  reporting builder 2.0 from within the Design Surface. This should open the New Chart dialog.

2)  Select Choose and existing in this report option and select Sales from the list.

reporting builder 2.0

3) Click Next.

4) Select Pie from the Chart Type list.

reporting builder 2.0

5) Click Next.

6) Now you will arrange your chart fields. Please place the fields to the propert location:

  • SalesTerritory --> Series
  • Y2004 --> Values

reporting builder 2.0

7) Click Next.

8) Select Corporate from the Styles list and click Finish.

reporting builder 2.0

 You should see the following:

reporting builder 2.0

Set Chart Width and Height

1) Click once, anywhere on the Chart.

2) Make sure the report Properties Pane is visible. If not, select View from the menubar on top and check Properties.

3) From within the Properties Pane, scroll down and find Position >> Size; expand Size.

4)  Enter 5.5in for the Width and 2.51042in for the Height. Your report design and properties should look similar to the following:

reporting builder 2.0

5) Click Run to view the report. You have successfully added a chart to your report.

6) After you have viewed the report, select Design from the Report Ribbon. In the next section, we will define a new Color Palette.

Define Color Palette

1) From within the Design Surface, click once on your Chart. Notice the Chart properties are visible in the Properties Pane.

2)  From within the Properties Pane, scroll to the Chart section and click Palette.

3) Select Custom from the Palette dropdown.

reporting builder 2.0

4) A few properties above the Palette property you will find Custom Palette Colors. Select the ellipsis button (...) from the right of the property name to edit the list of colors.

reporting builder 2.0

5) Click Add to add a color, or simply modify an existing color from the right pane, Appearance section.

Note: When selecting a color from the dropdown list, you have the option to use an Expression. Here, you may pass a specific color using hex values. For example, use #F0E68C for Khaki.

6) Place the color in the order you would like by selecting the Member on the left pane and click the up or down arrow reporting builder 2.0 from the middle of the dialog.

7) Click OK after you have completed your changes.

8) Run the report to see your changes.

You have completed defining a custom palette for your chart.


Pingbacks and trackbacks (1)+

Comments are closed