Drillthrough, Drilldown, Subreports, and Nested Data Regions
You can organize data in a variety of ways to show the relationship of the general to the detailed. You can put all the data in the report, but set it to be hidden until a user clicks to reveal details; this is a drilldown action. You can display the data in a data region, such as a table or chart, which is nested inside another data region, such as a table or matrix. You can display the data in a subreport that is completely contained within a main report. Or, you can put the detail data in drillthrough reports, separate reports that are displayed when a user clicks a link.
A. Drillthrough report
C. Nested data regions
D. Drilldown action
All of these have commonalities, but they serve different purposes and have different features. Two of them, drillthrough reports and subreports, are actually separate reports. Nesting is a means of putting one data region inside another data region. Drilldown is an action you can apply to any report item to hide and show other report items. They all are ways that you can organize and display data to help your users understand your report better.
I stumbled upon this blog post by Jason Thomas, which is a walkthrough on how to add QR-codes to your reports in SQL Server Reporting Services (SSRS):
It’s actually quite simple, you’ll need to use an expression such as:
="http://qrcode.kaywa.com/img.php?s=8&d=" + Fields!name.Value + Constants.vbcrlf + Fields!URL.Value + Constants.vbcrlf + Fields!mob.Value
Where the dataset looks like:
SELECT 'Jason Thomas' AS name, 'www.road-blogs.blogspot.com' AS URL, '00447574713732' AS mob
SELECT 'Jay Thomas' AS name, 'www.beyondrelational.com' AS URL, '67891' AS mob
SELECT 'Jean Elizabeth' AS name, 'http://twitter.com/de_unparagoned' AS URL, '12345' AS mob
The full blog post can be found here:
Combine Data from Various Sources with Power Query and Create a Killer Visualization with Power Map, thanks to Christina Storm
Unfortunately, she didn’t leave the source datasets (in Excel or CSV) for us to play with 😦
Perhaps next time?
The full demonstration on YouTube:
Join us at LinkedIn!
Don’t forget to join the ‘Microsoft Power BI‘ group on LinkedIn and stay updated!:
I’ve written a handy scalar-valued function to remove any ‘special’ characters from an input-string, such as:
Tab -> CHAR(9)
Line Feed (LF) -> CHAR(10)
Carriage Return (CR) -> CHAR(13)
-- Author: Clint Huijbers
-- Create date: 20130930
-- Description: I've created this SVF to remove all 'special characters' (e.g. CR, LF, TAB, etc) from a string.
CREATE FUNCTION [dwh].[svf_RemoveSpecialCharacters]
-- Declare the return variable here
DECLARE @ReturnString NVARCHAR(500)
Tab -> CHAR(9)
Line feed -> CHAR(10)
Carriage return -> CHAR(13)
SET @ReturnString = REPLACE(REPLACE(REPLACE(ISNULL(@InputString,''),CHAR(9),''),CHAR(10),''),CHAR(13),'')
-- Return the result of the function