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

SQL Server Reporting Services|Charts|Report Builder 2.01 Comments »

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 redReport Builder Ribbonbutton, 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 fromIntroduction to Report Builder 20 Part 2 Creating a Report. This site will provide details along with images.

1) From the Report Datapane, 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.

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.

SELECTSalesTerritory,SUM([2004])ASY2004

FROMSales.vSalesPersonSalesByFiscalYears

GROUPBYSalesTerritory

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

Add Chart to Report

1) Click on theChart Wizardiconfrom within the Design Surface. This should open the New Chart dialog.

2)SelectChoose and existing in this reportoption and select Sales from the list.

3) ClickNext.

4) SelectPiefrom theChart Typelist.

5) ClickNext.

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

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

7) ClickNext.

8) SelectCorporatefrom the Styles list and clickFinish.

You should see the following:

Set Chart Width and Height

1) Click once, anywhere on the Chart.

2) Make sure the report Properties Pane is visible. If not, selectViewfrom the menubar on top and checkProperties.

3) From within the Properties Pane, scroll down and findPositionSize;expand Size.

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

5) ClickRunto 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, clickonce on your Chart. Notice theChart properties are visible in the Properties Pane.

2) From within the Properties Pane, scroll to theChartsection and clickPalette.

3) Select Custom from the Palette dropdown.

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

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

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

6) Place the color in the order you would like by selecting theMemberon the left pane and click the up or down arrowfrom the middle of the dialog.

7) ClickOKafter you have completed your changes.

8) Run the report to see your changes.

You have completed defining a custom palette for your chart.

Working with Cascading Parameters in Reporting Services

SQL Server Reporting Services|Report Builder 2.01 Comments »

Cascading parameters are used to organize and limit the number of available values for the user. For example, you may have one parameter that filters a product category, and the second parameter used to list products related to product category. In this post we will walk through the steps of building a report and with query parameters for both product category and subcategory items. Then we will develop individual datasets to provide values for the cascading parameters. We will be using Report Builder 2.0 for this demo, but you may use the concepts presented below, while designing a report using BIDS.

This article assumes you have the following:

  • SQL Server 2008 and Reporting Services installed
  • Business Intelligence Development Studio (BIDS) orReport Builder 2.0
  • AdventureWorks database
  • Experience working with Reporting Services

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 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 for your report and click OK to save and close the Save As Report window. You should see a similar view.

Create Data Source

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

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

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

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

Create Data Set and Parameters

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 "TerritorySales" 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.

SELECTST.NameASTerritory,SUM(DET.LineTotal)ASSalesAmount

FROMSales.SalesPerson SP

INNERJOINSales.SalesOrderHeader SOHONSP.SalesPersonID=SOH.SalesPersonID

INNERJOINSales.SalesOrderDetail DETONSOH.SalesOrderID=DET.SalesOrderID

INNERJOINSales.SalesTerritory STONSP.TerritoryID=ST.TerritoryID

INNERJOINProduction.Product PONDET.ProductID=P.ProductID

INNERJOINProduction.ProductSubcategory PSONP.ProductSubcategoryID=PS.ProductSubcategoryID

INNERJOINProduction.ProductCategory PCONPS.ProductCategoryID=PC.ProductCategoryID

WHERE(PC.ProductCategoryID=@ProductCategory)

AND(PS.ProductSubcategoryIDIN(@ProductSubcategory))

AND(SOH.OrderDate@StartDate)AND(SOH.OrderDate@EndDate)

GROUPBYST.Name

ORDERBYSUM(DET.LineTotal)DESC

You should have a similar view below

5) Click OK to save changes and close the window.

6) From within the Report Data pane, expand the Parameters node. You'll notice several new parameters, as shown below.

Set Parameter Data Types and Default Values

1) From within the Report Data pane, right click @StartDate and select Parameter Properties.

2) From within the left pane of the Report Parameter Properties window, make sure General is selected.

3) Under DataType, select Date/Time.

4) From within the left pane of the Report Parameter Properties window, select Default Values.

5) Select the Specify Values option.

6) Click Add.

7) Enter in 2001-01-01 and click OK.

8) Repeat the same steps for the @EndDate parameter, but set the default value to 2004-07-01

Youare finished with setting thedata types and default values.

Create Product Category Datasetand set Parameter values

1) From within the ReportData pane, select New > Dataset...

2) Make sure the Query item on the left pane is selected; Enter ProductCategoryList.

3) Select the AdventureWorks from the Data Source dropdown list.

4) Copy and paste the following T-SQL script into the Query textbox:

SELECTDISTINCTPSC.ProductCategoryIDASProductCategoryID,PSC.NameASCategory

FROMProduction.ProductCategoryASPSC

INNERJOINProduction.ProductCategoryASPCONPC.ProductCategoryID=PSC.ProductCategoryID

5) Click OK to save your changes and close the Datset Properties window.

The ProductCategoryList dataset should be visible in the Report Data pane.

Set the Product Parameter available and default values

1) From within the Report Data pane, expand the Parameters folder and right-click @ProductCategory, and then click Parameter Properties.

2) Check "Allow Multiple Values"

3) Click Available Values from the left pane.

4) From the right pane, select “Get values from a query” option.

5) For the Dataset value, select ProductCategoryList from the dropdown list.

6) Value field = ProductCategoryID.

7) Label field = Product.

6)Click OK.

Create Product Subcategory Datasetand set Parameter values

1) From within the ReportData pane, select New > Dataset...

2) Make sure the Query item on the left pane is selected; Enter ProductSubcategoryList.

3) Select the AdventureWorks from the Data Source dropdown list.

4) Copy and paste the following T-SQL script into the Query textbox:

SELECTDISTINCTProductSubcategoryIDASProductSubcategoryID,PSC.NameASSubcategory

FROMProduction.ProductSubcategoryASPSC

INNERJOINProduction.ProductCategoryASPCONPC.ProductCategoryID=PSC.ProductCategoryID

WHEREPSC.ProductCategoryID=@ProductCategoryID

5) Click OK to save your changes and close the Datset Properties window.

The ProductSubcategoryList dataset should be visible in the Report Data pane.

Set the ProductSubcategory Parameter available values

1) From within the Report Data pane, expand the Parameters folder and right-click @ProductSubcategory, and then click Parameter Properties. Click Available Values from the left pane.

2) From the right pane, select “Get values from a query” option.

3) For the Dataset value, select ProductSubcategoryList from the dropdown list.

4) Value field = SubcategorySubcategoryID.

5) Label field = Subcategory.

6) Click OK.

7)Click Run, from the ribbon above the Report Data pane.

You should see the following

8) Select an item from the Product Category dropdown and notice how Product Subcategory is populated with values.

Next, you could go back to design mode and add a Table or Matrix to the report to render data.

22

Conditional formatting in SQL Server Reporting Services 2008

SQL Server Reporting Services1 Comments »

In this post we will discuss changing the format of report items or properties based on the data in the report. We will show how build conditional expressions. For example: changing a row background color based on a field value.

To make styles conditional, we’ll an expression instead of a static value for the style properties of the item. At run time, the report processor evaluates the expression and substitutes the result for the property value, just as if you had set the property to a static value at design time. When the report is rendered, the run-time value is used.

We are using the report "SalesByFiscalYear" which we had created previously in theIntroduction to Report Builder 2.0 Part 2 - Creating a Reportpost.

Changing Font Color

To make the font color of a textbox render values in red for a field called FullName, open the Properties pane and use the following expression in the Font > Color property:

=Switch(Fields!FullName.Value = "Jillian Carson", "Red", Fields!FullName.Value ="Rachel B Valdez", "Red", Len(Fields!FullName.Value) > 0, "#4c68a2")

Note: You can consider using a IIF statement to accomplish the same result.

AlternatingRow Color

To alternate the background color for every other row in your table, set the BackgroundColor property for each textbox in the row to the same conditional expression.

Note: This method works for detail rows only in a table with no groups.

=IIF(RowNumber(Nothing) MOD 2, "Khaki", "White")

Run the report. Select all in the dropdown list and view report. Expand Northwest and you should see a simliar view:

10

Working with a Recursive Hierarchy Group in SQL Reporting Services 2008

SQL Server Reporting Services|Report Builder 2.03 Comments »

This article explains how to create a recursive hierarchy group, which organizes data in a report to include multiple hierarchical levels. This is helpful when you want to display hierarchical data in a report. For example: employees in an organizational chart, or product subcategory in a product list. The image below is an example:

This article assumes you have the following:

  • SQL Server 2008 and Reporting Services installed
  • Business Intelligence Development Studio (BIDS) or Report Builder 2.0 is installed.Note:Weare using Report Builder 2.0 for this example.
  • AdventureWorks database
  • Experience working with Reporting Services

Steps Taken:

  1. Create a new report
  2. Adda Data Source and Dataset
  3. Adda Table to the reportand include dataset field
  4. EditGroup propertiesandreference a Recursive Parent
  5. Add custom format expressions
  6. Run code

Create a new Report

1) Open Report Builder 2.0 and create a new report.

Note:If you have not done this before, please view my previous article"Creating a Report"for more information.

2) Savethe reportto your desired Report Serverlocation and name it "RecursiveHierarchyGroup".

Add Data Source and Dataset

Creating the Data Source

1) From the Report Data pane, click New and select Data Source

the data source dialog will open...

Note: You may choose an existing shared data source, if youhave previously created one.For this example, we will beusingan embedded connection.

2) Provide a name for your data source. We will use the default value provided in this example.

3)Select "Use a connection embedded in my report" option

4)Select your Connection Type from the drop down.For this example we are using Microsoft SQL Sever.

5) Click the Build button from the right of the "Connection string:" textbox. You will see the Connection Properties dialog below.

6) Enter the same values in the property fields, found in the image above.

  1. Test the connection when you are done.
  2. If test is successfull,click OK.
  3. Click OKto close the Connection Properties window.
  4. Click OK to close the Data Source Properties window.
  5. You should now see your data source name on the left Report Data pane.

Note: the value in Server Name property is a period "."

Creating the 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 "Organization" 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:

selecthr.EmployeeID,hr.ManagerID,c.FirstName+' '+c.LastNameas'Name',hr.Title

fromHumanResources.Employeeashrinnerjoin

Person.Contact conhr.ContactID=c.ContactID

you should have something similar to below:

5) Click OK to save changes and close the window.

AddTable to Report

1) In Design view, add a table, and drag the following dataset fields to display:

  1. Name
  2. Title

2) Insert a new column to the right of the Title field and name it "Level".

3) Click in the "Click to add Title" field and enter "Organizational Hierarchy"

Your design view should look similar to the image below:

EditGroup propertiesandreference a Recursive Parent

4) Right-click anywhere in the table to select it. TheGrouping panedisplays the details group for the selected table. Optionally, you can selectViewfrom menu taband checkGrouping. In the Row Groups pane, right-click Details, andselect Group Properties. The Group Properties dialog box opens.

5) InGroup expressionssection, click Add. A new row appears in the grid.

6) In theGroup onlist, type or select the field to group -EmployeeID.

YourGroup PropertiesGeneraltab should looksimilar

7) Click theAdvancedtab, from within the left pane of theGroup Propertiesdialog.

8) In theRecursive Parentlist, type or select the field to group on - ManagerID.

9) ClickOK.

10) Run the report. You will notice the list of Employee names and titles. However, we need to do some formatting to indent the hierarchy accordingly.

Add custom format expressions

Indent Hierarchy

1) Right click on the [Name] field and selectText Box Properties...

2)From the left property pane, select theAlignmenttab.

3) From under thePadding optionssection, select theLeftexpression button.

After clickingthe expression button, you should see the following expressiondialog.

4) Enter the following script in the expression textbox:

=CStr(2 + (Level()*10)) + "pt"

The Padding properties all require a string in the format nnyy, where nn is a number and yy is the unit of measure. The example expression builds a string that uses the Level function to increase the size of the padding based on recursion level. For example, a row with a level of 1 would result in a padding of (2 + (1*10))=12pt, and a row with a level of 3 would result in a padding of (2 + (3*10))=32pt.