Transcript Scorecards and Dashboards with SQL Server Reporting Services

BI Tips and Tricks with SQL
Server Reporting Services
Adam Aspin
SQL Bits XII, 19th July 2014
What we are here to see…
• Solutions to:
– Impress the boss
– Stun your colleagues
– Save time and money
• By:
– More fully exploiting the potential of existing technology
– Leveraging current skills
– Avoiding costly and time-consuming alternatives
What we are discussing
• Corporate BI – using SQL Server Reporting Services
• “Canned Reports”:
– Regular delivery / Agreed, predefined metrics
– Limited interactivity
• Add a “BI” flavour to SSRS:
– Scorecards
– KPIS
– Dashboards
• SSRS as a Corporate BI delivery platform
– Basic parameter-driven interactivity
– No attempt at interactive development
KPI and Scorecard basics
• Key Performance Indicators
–
–
–
–
Value
Goal
Status
Trend
• See what a result is, how it maps to target and
where it is going (Context, Threshold, Evolution)
• Anything that can be measured of strategic or
tactical importance
Dashboard Basics
• Many possible overviews of multiple aspects of a
business
• Can include targets and objectives
• Composed of multiple elements “Widgets”
–
–
–
–
–
–
–
Scorecards
Tables
Charts
Gauges
Sparklines
Maps
Etc…
How do we Apply SSRS?
• Design element
– Appreciation of the diverse elements which can make up a dashboard
and/or a scorecard
– Understand which type of presentation element gets the information
across best
• Technology aspect – essentially applied to
the Interface
– Parameters
– Expressions
– Datasets
Design Elements
• Presentation Choices
– Tabular, text-based, graphical – or hybrid.
– Effective – and different - Charts and Gauges
• Design Choices
– Backgrounds, borders, images and text
– Colours to alert and display thresholds
• Figures or chart/gauge?
– Tooltips to display data
Presentation – Classic Scorecard
• Indicators (Status and Trend)
• Standard tabular format
• Background Image
Presentation - “Graphic” Scorecard
• Pointers for
Actuals and
Target
• Colour for
Threshold
• Trend as
Indicator
Presentation – Text-based Scorecard
• Allows for multiple KPIs
• Sparkline for Trend analysis
Design Tricks: Gauges
• Vary Pointer Types
–
–
–
–
Needle for single elements
Bar for one or two elements
Marker for multiple elements
Mix pointer types to hierarchize
• Mix Pointer Types
• Group Gauges
• Rarer Gauge Types
– Thermometer
– Multi-Scale / Multi-Gauge
• Gauge Enhancements
– Overlay Data as text
– Multiple Ranges – colour sensitive
to thresholds
– Add Legends
Design Tricks: Charts
•
•
•
Appropriate Chart Types – Read the classics (Few, Tufte)
Add Markers
Less Traditional Charts
–
–
–
–
•
Radar
Pyramid
Bubble
Waterfall
Remember less used, but “Out of the Box” techniques:
–
–
–
–
–
Order chart elements
Striplines
Vary Category Colours
Add markers
Order charts in a table
Design Tricks - Images
• Images as backgrounds
–
–
–
–
Tablix
Textbox
Chart
Gauge
• Images as borders
– 8 images to make an extensible border (BackgroundRepeat)
• Text as Image
– Vertical or Diagonal
• Swap images for effect:
– Selection (Slicer and Highlight)
– Visual Alert
Design Tricks: Maps
• First find your (geographical) data!
– .Shp (Shape) files (Ex: BoundaryLine – Ordnance Survey)
– Shape2SQL – convert shape file data to SQL Server
geographical data types
• Store in database not Report for speed of rendering
• Cache reports containing Maps
Layout Tricks
• Force vertical alignment of charts and gauges using
tables
• Nested tables
• “Dummy” tables for images
• Invisible lines and rectangles to force vertical
alignment across a page
Putting It All Together – One Example
Putting It All Together – Another Example
Interface Tweaks
• Revamping the user interface
– Replace the tired UI for parameter selection
– Define limited recordsets for efficient paging
• Enhance Interactivity (well – to some extent)
through more modern interface techniques:
–
–
–
–
Slicers
Tiles
Highlighting
Recordset Paging
Technology I - Hidden Parameters
• “Passback” – an attempt at viewstate
• Have to be applied everywhere there is
interactivity!
• Uses:
– Replace the parameter ribbon
– Replace the toolbar
– Example:
• Slicers
• Selection
• Menus
Technology II - Expressions
• Clunky - but the key method of enhancing BI reports
• Possible with nearly every object and Property
• A few simple elements do all the work:
–
–
–
–
IIF
Lookup
Choose
Lookup/Lookupset/Multilookup
• Can test on data and parameters!
• Some uses:
–
–
–
–
Apply Colours
Add Images
Show/Hide elements
Example – printer-friendly output
Mobile BI using SSRS
• Only from 2012SP1 onwards
• Report Server not Report Manager URL
• Manage reduced screen space:
– Consider revamping the report access interface
– Fewer elements - drillthrough-based
Technology II - Data Tricks
• Use a single dataset to calculate and return
maximum values for gauges
• Link datasets (Lookup function)
• Share Datasets
• Cache datasets
• Linked SSAS server to return data to T-SQL complex
queries (target data for instance)
• Paged data sets (2012)
To Finish
• Much, much more that could be shown, for
instance:
– Optimisation techniques
– Standardisation approaches (templates, styles etc)
• Any questions?
• Happy BI Development with SSRS.
Adam Aspin
• IT consultant and Contractor
• Over 30 years experience
• 25 years in databases and reporting
(now “Business Intelligence”)
• Author:
– SQL Server 2012 Data Integration Recipes
– High Impact Data Visualization with Power
View, Power Map and Power BI
– Many articles for SQLServerCentral.Com
• [email protected]