SQL Server Reporting Services (SSRS) - Tips & Tricks

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: We are using Report Builder 2.0 for this example.
• AdventureWorks database
• Experience working with Reporting Services

Steps Taken:

1. Create a new report
2. Add a Data Source and Dataset
3. Add a Table to the report and include dataset field
4. Edit Group properties and reference 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) Save the report to your desired Report Server location 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 you have previously created one. For this example, we will be using an 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 OK to 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:

select hr.EmployeeID, hr.ManagerID, c.FirstName + ' ' + c.LastName as 'Name', hr.Title

from HumanResources.Employee as hr inner join

Person.Contact c on hr.ContactID = c.ContactID

you should have something similar to below:

 

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

Add Table 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:

Edit Group properties and reference a Recursive Parent

4) Right-click anywhere in the table to select it. The Grouping pane displays the details group for the selected table. Optionally, you can select View from menu tab and check Grouping. In the Row Groups pane, right-click Details, and select Group Properties. The Group Properties dialog box opens.

5) In Group expressions section, click Add. A new row appears in the grid.

6) In the Group on list, type or select the field to group - EmployeeID.

Your Group Properties General tab should look similar

7) Click the Advanced tab, from within the left pane of the Group Properties dialog.

8) In the Recursive Parent list, type or select the field to group on - ManagerID.

9) Click OK.

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 select Text Box Properties...

2) From the left property pane, select the Alignment tab.

3) From under the Padding options section, select the Left expression button.

After clicking the expression button, you should see the following expression dialog.

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.

Set Font Style

1) From the left property pane, select Font.

2) Under the Style section, click Bold expression button.

3) Enter the following script in expression textbox:

=IIF(Count(Fields!Name.Value, "Details", Recursive) > 1, "Bold", "Normal")

Essentially the Count function is searching the Fields!Name.Value, from within the "Details" group, recursively. If the recursive parent has children, then the parent is set to Bold, else Normal.

4) Click OK.

5) Right click on the Level field and click Expression...

6) Enter the following script:

=Level()

The Level() Function returns the current level (Integer) of depth in a recursive hierarchy.

7) Click OK.

5) Run the report. You should see similar results.

That's all there is to it.

Cheers!

In this article we will examine developing a method to handle stacked values within a field, while using a table or matrix. There may be multiple ways to handle stacked values approach, but we will focus on delivering the solution using a custom assembly. Code download is available at the end of the article.

For this example, we will be using Report Desinger, Visual Studio 2008 and ASP.NET/C# for sample code.

This article assumes you have the following:

• SQL Server 2008 and Reporting Services installed
• (optional) Report Builder 2.0
• Microsoft Visual Studio 2008 installed
• Reporting Services 2008 installed and a configured Report Server
• Experience with Reporting Services 2005/2008 
• Experience with ASP.NET and C#

Steps Taken:

1. Create a Custom Assembly
2. Copy assembly to ReportServer and PrivateAssemblies folders
3. Set Security Permissions in RSPreviewPolicy.config and rssrvpolicy.config 
4. Create a Report to render Vendor data
5. Reference the custom assembly
6. Add field with expression to call the method

Creating a Custom Assembly

1) Open Visual Studio 2008 and create a new Project >> Class Library. Name the project "rsCustomAssembly".

2) Delete the Class1.cs

3) Create a new Class... name it "Vendor.cs"

4) Copy and paste the following code to the file.

#region "***** Disclaimer *****"

 

/*=============================================================================

  File: Default.cs

 

  Summary:  This sample demonstrates creating a method that returns a list of

            Vendor Products by VendorID - returns a string value.

            The value can then be used in a table/matrix field to return

            stacked values, in Reporting Services.

           

---------------------------------------------------------------------

 

 THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY

 KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE

 IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A

 PARTICULAR PURPOSE.

=============================================================================*/

 

#endregion

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

 

using System.Data.SqlClient;

using System.Security.Permissions;

 

namespace rsCustomAssembly

{  

    public class Vendor

    {

        [PermissionSetAttribute(SecurityAction.Assert, Unrestricted = true)]

        public static String GetVendorProducts(int vendorID)

        {

            StringBuilder nRetVal;

 

            SqlConnection oConn = new SqlConnection();

            oConn.ConnectionString = "Data Source=(local); " +

                "Initial Catalog=AdventureWorks;Integrated Security=True";

            oConn.Open();

            SqlCommand oCmd = new SqlCommand();

            oCmd.Connection = oConn;

            oCmd.Parameters.AddWithValue("@VendorID", vendorID);

            oCmd.CommandText = "SELECT p.Name as 'CompanyName' " +

                "FROM Production.Product p " +

                "INNER JOIN Purchasing.ProductVendor pv on pv.ProductID = p.ProductID " +

                "WHERE pv.VendorID = @VendorID";

            SqlDataReader myReader;

            myReader = oCmd.ExecuteReader();

            nRetVal = new StringBuilder();

 

            try

            {

                while (myReader.Read())

                {

                    nRetVal.Append(myReader.GetString(0) + "\r\n");

                }              

               

                return nRetVal.ToString();

            }

            catch (Exception e)

            {

                return e.Message;

            }

            finally

            {

                // close reader and connection and return value

                myReader.Close();

                oConn.Close();

            }           

        }      

    }

}

5) Save your project. Don't close your IDE.

Copy assembly to ReportServer and PrivateAssemblies folders

1) Make your assembly available to Report Designer and Report Server in Reporting Services. To do this, you must copy your .dll to the Report Designer (PrivateAssemblies) folder and to the Report Server (ReportServer) folder.

Note: depending on your installation, the paths may be different.

• For Reporting Services 2008, copy the .dll to the following folders:
  Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies
  Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin
• For Reporting Services 2005, copy the .dll to the following folders:
  Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
  Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin

2) Since you may be modifying your custom assembly in the future, you may consider adding the following script to your project Build Events. Place the script in the Post-Build event command line: section. This will eliminate the need to manually copy and paste the .dll every time you need to publish a new version.

copy "$(TargetPath)" "C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\"
copy "$(TargetPath)" "C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\"

3) Save your changes to the project and run a build. Your .dll should be copied over the defined paths in your Post-build event command line.

Note: Sometimes you may receive an error stating the .dll file could not be copied over to the defined path. This may be due to the fact you have a report open that references the assembly, so the active assembly in ReportServer/bin or PrivateAssemblies folder is locked. You may either close the report in the design view, or simply restart your SQL Server Report Services service from the Windows Services Management Console.

Set Security Permissions in RSPreviewPolicy.config and rssrvpolicy.config 

This custom assembly will require more permissions than the default Execution level granted in Reporting Services. We need to make some security change to code access in order for this work. A common indication that your custom assembly requires special code access permissions is the "#Error" value rendered when you run your report, instead of the expected result.

Note: before making any changes to your .config files, please make a backup of the files and save them to a new location. You may need to recover the backup files if you corrupt active .config files.

RSPreviewPolicy.config

1)  Navigate to the location of your RSPreviewPolicy.config ("Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\") and open the file.

2) Scroll down the file and locate the third to the last  </CodeGroup> and paste for following script under it:

</CodeGroup>  <--- third to last                                       

<CodeGroup

class="UnionCodeGroup"

      version="1"

      PermissionSetName="FullTrust"

      Name="MyCodeGroup"

      Description="">

      <IMembershipCondition

            class="UrlMembershipCondition"

            version="1"

            Url="C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\rsCustomAssembly.dll"

/>

</CodeGroup>

</CodeGroup>

</CodeGroup>

</PolicyLevel>

3) Save your file and close it. 

RSSrvPolicy.config

1)  Navigate to the location of your RSSrvPolicy.config ("Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin") and open the file.

2) Scroll down the file and locate the third to the last  </CodeGroup> and paste for following script under it:

</CodeGroup>  <--- third to last                                       

<CodeGroup

class="UnionCodeGroup"

      version="1"

      PermissionSetName="FullTrust"

      Name="MyCodeGroup"

      Description="">

      <IMembershipCondition

            class="UrlMembershipCondition"

            version="1"

            Url="C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin\rsCustomAssembly.dll"

/>

</CodeGroup>

</CodeGroup>

</CodeGroup>

</PolicyLevel>

3) Save your file and close it.

Create a Report to render Vendor data

1) Open Business Intelligence Development Studio (BIDS) - Start >> All Programs >> Microsoft SQL Server 2008 >> SQL Server Business Intelligence Development Studio

2) Create a new Report Server Project - File >> New >> Project >> Project Type: Business Intelligence Projects >> Templates: Report Server Projects 

3) Name the Project "DemoReportProject" and click OK.

4) From within the Report Data pane, add a new data source. Use the AdventureWorks database and name the data source "AdventureWorks". If you do not have the AdventureWorks database you can get it here.

5) From within the Report Data pane, add a new Dataset.

6) Name the Dataset "VendorDS".

7) Select the AdventureWorks database for the Data Source.

8) Select the Text option for the Query Type. 

9) Enter the follow script in the Query textbox:

SELECT        VendorID, Name, FirstName, LastName, Phone
FROM            Purchasing.vVendor

10) Click OK.

11) Add a Matrix to your design surface. 

12) Drag and drop both Name and Phone, from the VendorDS dataset, to the Rows section of the Matrix. Add a Textbox for the title of the report. Your report should similar to the following:

13) Click on the Company Name column so that the column has the focus.

14) Right click on the column and select Insert Column >> Inside Group Right

15) Name the column "Products".

16) Save your work.

Reference the custom assembly

1) Make sure you are in the Design surface of Reporting Services. From the menubar, select Report >> Report Properties. This will open the Report Properties dialog.

2) From the left pane of the Report Properties dialog, select References.

3) Click Add from under the Add or remove assemblies section.

4) Click the Browse tab and navigate to your ReportServer/bin location. Select the rsCustomAssembly.dll and click OK.

Your Report Properties dialog should look similar

5) Click OK.

Add field with expression to call the method

1) Under the Products column, right click the Textbox and select Expressions... 

2) Copy and paste the  following code in the Value editor.

=rsCustomAssembly.Vendor.GetVendorProducts(Fields!VendorID.Value)

Notice how the call is made to the method: <Namespace>.<Class>.<Method>

Note: If you want to use instance-based methods, you will have go back to the Report Properties >> References tab, and then define the Class name and Instance name in Add or remove classes section.

3) Click OK.

Your Matrix should look something similar 

4) Preview the report by selecting the Preview tab. Your report should render similar results.

5) Save your work.

You're done!

Cheers!

Download code here:

rsCustomAssembly.zip (27.68 kb)

DemoReportProject1.zip (32.67 kb)

This article we will describe how to enhance a Pie Chart by using the SoftEdge or Concave value, using SQL Server 2008 Reporting Services. These values are available only on a 2D Pie Chart. The image below illustrates both SoftEdge and Concave styles, respectively. 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

Set the PieDrawingStyle Property

1) Insert a Pie or Doughnut chart to your desgin surface.

2) Click on the Series section of the Chart. You should see the Series properties on the right Properties Pane.

 

3) From within the Properties Pane, scroll down to the General section and expand Custom Attributes.

4) Select the property PieDrawingStyle and select the SoftEdge value from the dropdown list.

Your chart should look similar to the image below

5) Try changing the PieDrawingStyle to Concave to test other styles.

That's it!

Cheers!  

I was looking for a way to configure Report Manager to open Report Builder 2.0, instead of the default Report Builder 1.0 application. I was able to find this tidbit from the Report Builder 2.0 online help and thought I'd share.

By default, the ClickOnce application opens Report Builder 1.0. To change the default to Report Builder 2.0, you must update the value of the Custom Report Builder URL field in Report Manager. If you are opening Report Builder 2.0 from a SharePoint site, you must update this field in SharePoint Central Administration.

Note: If you want to restore Report Builder 1.0 as the default version of Report Builder, update the Custom Report Builder URL field again. For Report Manager, use /ReportBuilder/ReportBuilder.application and for a SharePoint site, use /_vti_bin/ReportBuilder/ReportBuilder_2_0_0_0.application.

This tip assumes you have administrator privileges to SQL Server Reporting Services Report Manager.

Steps

To change the default ClickOnce application in Report Manager
1. Start Report Manager.
2. Click Site Settings.
3. Click General in the left pane.
4. In Custom Report Builder URL, type /ReportBuilder/ReportBuilder_2_0_0_0.application.
5. Click OK.

To change the default ClickOnce application in SharePoint Central Administration
On the Start menu, point to Administrative Tools and then click SharePoint 3.0 Central Administration.

On the Central Administration page, click the Application Management tab.

In Custom Report Builder URL, type: /_vti_bin/ReportBuilder/ReportBuilder_2_0_0_0.application.

Click OK.

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) or Report 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 from Introduction to Report Builder 20 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) 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.

SELECT ST.Name AS Territory, SUM(DET.LineTotal) AS SalesAmount

FROM Sales.SalesPerson SP

    INNER JOIN Sales.SalesOrderHeader SOH ON SP.SalesPersonID = SOH.SalesPersonID

    INNER JOIN Sales.SalesOrderDetail DET ON SOH.SalesOrderID = DET.SalesOrderID

    INNER JOIN Sales.SalesTerritory ST ON SP.TerritoryID = ST.TerritoryID

    INNER JOIN Production.Product P ON DET.ProductID = P.ProductID

    INNER JOIN Production.ProductSubcategory PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID      

    INNER JOIN Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID

WHERE (PC.ProductCategoryID = @ProductCategory)

AND (PS.ProductSubcategoryID IN (@ProductSubcategory))

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

GROUP BY ST.Name

ORDER BY SUM(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 Data Type, 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

You are finished with setting the data types and default values.

Create Product Category Dataset and set Parameter values

1) From within the Report Data 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:

SELECT DISTINCT PSC.ProductCategoryID AS ProductCategoryID, PSC.Name AS Category

FROM Production.ProductCategory AS PSC

      INNER JOIN Production.ProductCategory AS PC ON PC.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 Dataset and set Parameter values

1) From within the Report Data 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:

SELECT DISTINCT ProductSubcategoryID AS ProductSubcategoryID, PSC.Name AS Subcategory

FROM Production.ProductSubcategory AS PSC

      INNER JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID

WHERE PSC.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.

Cheers!

 

This is the continuation of Introduction to Report Builder 2.0 Part 1. This article will show you how to create your first report (SalesByFiscalYear) using Report Builder 2.0, and how to pulish the report to your Report Server. This article assumes the following:

• You have installed SQL Server 2008 and Report Builder 2.0 on the computer you are running this example
• You have created a shared data source and pulished the report server (optional). This article will create a data source using an embedded connection, however.
• You have the sample AdventureWorks database installed. You can get the sample database here.

Steps

Open Report Builder

1) From you desktop select Start >> All Programs >> Microsoft SQL Server 2008 Report Builder 2.0 >> Report Builder 2.0

You should see the following:

 

Configure Report Builder

1) From the upper left corner of Report Builder, click the Red Ribbon and then the Options from the lower left corner.

 

2) Select the Settings option from the left pane.

3) Enter your default Report Server location and set the other property values as desired. For example: http://localhost/ReportServer Click OK to save your settings and close the dialog box.

Creating a 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 you have previously created one. For this example, we will be using an 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 OK to 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 "."

Design Report

1) From the middle pane of the report designer click on the Table or Matix icon.

 

The New Tabe or Matrix window will open.

2) From the Data Source Connection pane, select the data source you created in the previous section Creating a Data Source. Click Next from the lower right corner when you are done.

3) You should see the Design Query view, similar to below. Expand the Sales node and then expand the Views folder. Place a check mark in the vSalesPersonSalesByFiscalYear.

4) From the top toolbar of the Query Designer, click Run Query to test the view. You lower Query Results pane should render results from the view.

5) Click Next from the lower right corner of the Query Designer window.

6) Arrange Fields the following way:

1. add SalesTerritory and FullName to the Row groups, respectively.
2. add ID2002, ID2003 and ID2004 to the ∑ Values

7) Click Next when you are done assigning fields.

8) Keep the next page default values and click Next.

9) Click Finish to complete the wizard.

Configure Properties

 This section we will apply a currency format to our sales field values. After the formatting of the currency values we will give the report a Title and run the report.

1) Place your cursor in the first [SUM(ID2002)] field. Right click on the field and select Text Box Properties. The Text Box Property window will open.

2) From the left pane of the Text Box Properties window, select Number.

3) Under the Category pane, select Currency. Adjust settings as desired.

4) Click OK.

5) Repeat this steps 1-4 for all the SUM fields.

Note: There is an easier way to do this; press and holding down the Ctrl key and select all the SUM fields with your mouse. From properties pane on the right, find the Format property and pass '$'0.00;('$'0.00) for the value.

6) Place your cursor in the "Click to add title" field.

7) Enter "Sales by Fiscal Year".

8) From the top left part of the Ribbon, click Run.

You should see a similar result.

 Publish Report

This section will show you how to publish your report to the Report Server.

1) From the upper right corner of Report Builder 2.0, click on the Save icon.

2) The Save As window will open. Select the folder you want to save the report to.

3) Enter SalesByFiscalYear.rdl for the name of the report.

4) Click Save from the lower left of the dialog.

Your report has been saved and published to the Rerport Server. Take the time to review the report in desing mode and explore the properties for each of the fields. This will help you gain a better understanding on how to format reports in the future.

Next post will discuss using parameters in Report Builder 2.0.

Cheers!