5/7/2016
3
Focus Reports Using
Filters
TOC
Objectives
At the and of this module, you should be able to:
–Create filters to narrow the focus of reports
–Examine detail and summary filters
–Determine when to apply filters on aggregate data
76
1
5/7/2016
Create Filters
To narrow the focus of you report, you can create a filter
expression in three different ways:
Create a simple filter condition
based on selected values from
only one data item in the query
Combine filter condition based on
selected values from multiple data
item in the query, into a single filter.
Create filter condition that used
advanced calculation
(expression) based on items
from query or Data source.
77
Filter your Data with Advanced Detail Filters
Create a detail filter to narrow your focus and report on
specific data.
78
2
5/7/2016
Lab 3.1: Apply Filters to a report
79
Lab 3.1: Apply Filters to a report
Task 1: cerate the list report
– use Employee by region and Sales fact table form Sales an
Marketing (query) Sales (query)
Task 2: Add filter to show sales from 2012
–[Sales(query)].[Time].[Year]=2012
Task 3: Filter data to show only Southern European countries.
–The Southern European countries= Austria, Italy and Spain
80
3
5/7/2016
Determine when to apply a Filter with
aggregation-Before & After Auto Aggregation
Before Auto Aggregation
–Generates a Where clause (filtering
detail records)
Select Name, Revenue
From Products
Where Revenue > 10
After Auto Aggregation
–Generates a Having clause (filtering
summary records)
Select Name, Sum(Revenue)
From Products
Group By Name
Having Sum(Revenue)>1000
81
Detail Filters and Summary Filters
Detail filter
–Applies conditions to each row of data. If the conditions are true for a
row, the row is retrieved and appears in the report; else the row is not
retrieved and does not appear in the report.
–Generate Where/Having clauses in SQL (depending on before/after
auto aggregation)
Summary filter
– Filter summary data items in a report.
– Before you create a summary filter, you must
calculate the summary data items that you want
to use in the filter.
– Summary data items that you include in the filter
are calculated before the filter is applied while
summary data items that aren't in the filter are
calculated after the summary filter is applied.
– Generate Having clauses in SQL
4
5/7/2016
Lab 3.2: Apply a Detail Filter on Fact Data to a
Report
• The report include only data from
individual orders of each product type
that generated more than $100000 in
revenue.
• ²You want to display only product type
for which the total revenue for all sales
is greater than ten million dollars.
• Apply a Summary Filter to a report:
You have asked to modify a report that
focuses on product line that have
generated revenues greater than
$100.000.000.
83
Apply Pre-defined Filters
Save time and effort by applying filters published with your
source package rather than creating your own.
Defined in the package by metadata modeler
Advantages:
–Reuse
–Encapsulation
84
5
5/7/2016
Summary
At the end of this module, you should be able to:
–Create filters to narrow the focus of reports
–Examine detail and summary filters
–Determine when to apply filters on aggregate data
85
Homework #1 – Sorting & Formatting
List countries, genders, and salaries for countries
that exceeded 300,000$ in salaries of 2012
Sort countries by Salary DESC,
and their details by Gender DESC
Show salary as number, in thousands, with one
decimal place
What is the Aggregate Function of Salary?
Hints
–Package: GO Data Warehouse (query)
–Namespace: HR (query) >
86 Employee Summary (query)
6
5/7/2016
4
Create Crosstab Reports
TOC
Objectives
At the end of this module, you should be able to:
–Format and sort crosstab reports
–Create complex crosstabs using drag and drop
functionality
–Create crosstabs using unrelated data items
88
7
5/7/2016
Crosstab Reports
Add query items to rows and columns, add measures to the body
(intersection)
Usage: Analyzing and comparing summarized numeric data in rows and
columns
Crosstabs are, by design, dimensional reporting objects
Edges
Default
Measure
Intersection
(Fact Cells)
89
Add Measures to Crosstab Reports
You can add measures to either the row or column edges of a crosstab
report.
You can add a default measure that is used in cells where the measure is
not define on the row or column edge.
Use drop zones to add parents, peers, and children
90
8
5/7/2016
Create Complex Crosstab Reports
Crosstab drop zones let you cerate a wide variety of crosstab
layouts to meet your business requirements.
91
Crosstab Nodes
A crosstab node contains one or more crosstab
node members
This crosstab node
contains two crosstab
node members:
Region and City.
92
9
5/7/2016
Data Sources for Crosstabs
Relational models a basic metadata structure that looks like
tables and columns in a database
DMR models are built from relational data source, but are
modeled with a dimensional structure (like OLAP) consisting
of measures and dimensions.
Crosstabs are better suited to dimensional reporting.
Filters in a crosstab may be cause unpredictable results and
should be used only when necessary
93
Exercise 4.1: Create a simple Crosstab Report
Pivot list to crosstab
–Swap Rows and Columns
94
10
5/7/2016
Exercise 4.2: Create Complex Crosstab Report
Pivot list to crosstab
–Swap Rows and Columns
95
Format Crosstab Reports
You can formatting for cells displaying data for a specific row
or column edge item, such as Product line or Region.
96
11
5/7/2016
Add Unrelated Items to Crosstab Edges
You can create discontinuous crosstabs that have unrelated
data in the row and column edges.
97
Exercise 4.3: Sort and Format Crosstab Report
Report Items: Product Line, Product type, Branch region, Year, Total from
all years and Total from each product line.
–Sort Product line and Year by Ascending
–Sort Branch region by descending value from Revenue
–Format Report as follows
98
12
5/7/2016
Exercise 4.4: Unrelated Items in a
Discontinuous Crosstab
Rows: Product line, Year and Quarter
Columns: Branch region, Revenue and quantity
99
Summary
At the end of this module, you should be able to:
–Format and sort crosstab report
–Create complex crosstabs using drag and drop
functionality
–Create crosstabs using unrelated data items.
100
13