Using Expressions to Change Report Item Properties and Behavior


Expressions are pretty basic in concept and exceptionally simple in implementation. They are the Swiss army knife in the report developer's toolbox. With the basics out of the way, it's time to create another report.

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.

Figure 8.3. Layout view of Top SalesPeople report.

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.

Figure 8.4. Resulting output after background color change.



Microsoft SQL Server 2005 Reporting Services
Microsoft SQL Server 2005 Reporting Services
ISBN: 0672327996
EAN: 2147483647
Year: 2004
Pages: 254

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net