Microsoft SQL Server 2016 Reporting Services by Brian Larson

Author:Brian Larson
Language: eng
Format: epub
Publisher: McGraw-Hill Education
Published: 2017-11-23T16:00:00+00:00

Figure 8-30 The Overtime report layout

42. Preview/run the report.

43. Notice the Week drop-down list is enabled but the Hub drop-down list is disabled. Select 2016–15 from the Week drop-down list.

44. Once a week is selected, the Hub drop-down list is enabled. Select Borlaron Repair Base from the Hub drop-down list. Click the View Report button. Your report should appear similar to Figure 8-31.

Figure 8-31 The Overtime report preview

45. Select 2016–11 from the Week drop-down list. Borlaron Repair Base is still selected in the Hub drop-down list. Click the View Report button. Note the text under the “Employees with over 55 hours for this week” heading.

46. Save the report.

Task Notes In this report, we used the same dataset to populate two tablixes. We got different information in the two tablixes by applying different filters on each. The filter for the upper table on the report says we only want records in this tablix where the number of hours worked is less than or equal to 55. The filter for the lower tablix on the report says we only want records in this tablix where the number of hours worked is greater than 55. In this way, we can divide the data in the dataset to fulfill the business requirements of the report.

You may have noted that we used an equals sign in front of the number 55 in the Value field for our filter expressions. This is due to the fact that without an equals sign in front of it, the Value is interpreted as a string constant. In other words, without the equals sign, the report would have been trying to compare an integer database field with the string “55”, which results in an error. When we place the equals sign in front of the value, Reporting Services interprets this as an expression. The expression “=55” results in an integer value of 55, which is just what we want for our comparison.

In addition to what you saw here, filters can be applied to data in other locations. A dataset can have a filter applied to it after it has been selected from the database. Individual groups within a table, matrix, or chart can also utilize filters.

Filters work well in situations like the one in this report where we want to use one dataset to provide a slightly different set of records to multiple data regions. They can also be useful for taking data from a stored procedure that provides almost, but not quite, the result set you need. It is usually best, however, to have your filtering done by your select query or stored procedure rather than by the report. The reason is in most cases it is considerably faster and more efficient if the database does the filtering as it executes the query or stored procedure. It does not make sense to have your query select 1000 records from the database if your report is going to filter out all but 10 of these records. Filters are a good tool to have; just remember to use them wisely.

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.