1. | Open the AdventureWorks solution. |
2. | Add a new report called "Top SalesPeople." |
3. | Using the shared data source, create a data set using the following query: |
SELECT TOP 5 C.LastName, C.FirstName, E.EmployeeID, SUM(SOH.SubTotal) AS SaleAmount FROM Sales.SalesPerson SP INNER JOIN HumanResources.Employee E ON SP.SalesPersonID = E.EmployeeID INNER JOIN Person.Contact C ON E.ContactID = C.ContactID INNER JOIN Sales.SalesOrderHeader SOH ON SP.SalesPersonID = SOH.SalesPersonID INNER JOIN Sales.SalesOrderDetail SOD ON SOH.SalesOrderID = SOD.SalesOrderID INNER JOIN Production.Product P ON SOD.ProductID = P.ProductID INNER JOIN Production.ProductSubcategory PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID INNER JOIN Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID Group by C.LastName, C.FirstName, E.EmployeeID
4. | Name the data SalesDS. |
5. | In the layout section, add a table to the report and drop the last column. You should have two columns . |
6. | Drag the last name from the Fields collection in the toolbox, and drag the SalesAmount field from the data set onto the second column. Format the SalesAmount text box with the format string c0 . |
7. | Change the header color to Red and text color to White. Make the font Bold. |
This should give us a good starting point; see Figure 8.3.
Select the table details row. On the BackgroundColor property, enter the following expression:
=iif(RowNumber("SalesDS") Mod 2,"Gray","White")
You should now be able to preview the alternating colors on each detail row, such as in Figure 8.4.