#sqlpass #msbi online event: “The Accidental Report Designer: Data Visualization Best Practices in #SSRS”


PASS Business Analytics VC presents “The Accidental Report Designer: Data Visualization Best Practices in SSRS” by Meagan Longoria. Here are the meeting details:

Date & Time: Thu, June 19 2014 12:00 Eastern Daylight Time



Session Abstract:
Whether you are a DBA, a developer, or an analyst, there is a good chance that you will have to create reports for coworkers, company executives, or clients. As with any UI design, careful consideration should be given to your data visualization design to ensure you are effectively communicating the intended message and providing a good user experience. While the principles are applicable across reporting platforms and tools, this session will contain demos and examples implemented in Reporting Services using SQL Server Data Tools. Learn how to make information (not just data) the focus of your report and provide your audience with something better than just shiny!

Session Level:

Speaker BIO:
Meagan Longoria is a BI consultant with Valorem Consulting in Kansas City, Missouri. She has over 6 years of experience with the SQL Server BI stack and enjoys sharing her knowledge and experiences at SQL Saturdays. She is also one of the coordinators for SQL Saturday in Kansas City.
Contact URL: http://datasavvy.wordpress.com

See you there!

Paras Doshi
Chapter Co-Leader, PASS BA VC


SQL Server reporting services: How to use the Split function in the SSRS expressions to get sub-string?


Problem Statement:
How do you use SQL Server Reporting Services (SSRS) expression to get sub-string from the inputted text?

I am going to show you few SSRS expressions that you can use in your SSRS reports:

SSRS SQL Server Reporting Services Expression SPLIT

Here’s the same in a text:

Input: SSRS Expression used: Output:
[Date].[Fiscal Year].&[2008] Split(Parameters!DateFiscalYear.Value,”&”)(1) [2008]
[Date].[Fiscal Year].&[2008] Split(Parameters!DateFiscalYear.Value, “.”)(2) &[2008]
[Date].[Fiscal Year].&[2008] Split(Split(Split(Parameters!DateFiscalYear.Value, “&”)(1),”[“)(1),”]”)(0) 2008
[Date].[Fiscal Year].&[2008] Parameters!DateFiscalYear.Value.Split(“&”)(1) [2008]

In this post, you saw how to use the split function in SQL Server Reporting Services (SSRS) expressions to get a sub-string.

SQL Server reporting services: How to add a seconday axis on a chart?


Problem Statement:

Need a chart on a SQL Server Reporting Services chart with a secondary axes.

Need measure #2 on a secondary axes:

SQL Server Reporting Services Secondary AxesSolution:

1. In the Design Mode > Open the Chart Date Pane > For Measure #2, navigate to Series properties:

SQL Server Reporting Services Series Properties Chart Data2. From the Series Properties Dialog box, navigate to “Axes and Chart Area” and choose the option “Secondary” under vertical axes.

SSRS Secondary Axis Axes3. Click OK to go back to the design mode and preview the report to test it:


4. Make sure to rename the axis title of the secondary axis, format the number to make it consistent with the report layout.


SSRS Version needed: SQL Server 2008 and above.


In this post, you saw how to add a secondary axis on a SSRS Chart.

SQL Server Reporting Services: What are Drill Down & Drill Through reports?


Analyzing data at summary level is great! it’s gives business users information that they need at summary level to make informed business decisions. But often, they also need to look at details. To satisfy this business requirement, there are two types of reports that you can create in SSRS to show detail level information

1. Drill Down Report.

2. Drill Through report.

So what is the difference in their layout?

Drill Down report:

Note the presence of Toggle Items. + for expand. and – for collapse. As you can see, this makes it easy to go a level below and see the details.

SQL Server Reporting services Drill Down reportDrill Through report:

Note the presence of a formatted text. It’s shown like a hyper linked text. For this report, To see Product level detail of Bikes Category, click on Sales Amount for Bikes category & it would take you a drill through report that shows the details:

SQL Server Drill Through Reporting ServicesSo when to choose Drill Down report vs Drill Through report?

They have different feature & serves different purposes. But there are two things that help me decide in majority of the cases:

1. Is the performance of Drill Down report acceptable (especially if data is surfaced through stored-procedures/queries hitting the data source without a semantic layer in between)?

Yes? Great. If not, then try drill through reports.

2. Are there too many (30-40+) data items show if a user drill downs? Yes? Try drill through.

Related resources:
Drillthrough, Drilldown, Subreports, and Nested Data Regions (Report Builder and SSRS)

Paras Doshi

SSRS Error: “The value expression for the … contains an error. [BC30451] ‘code’ is not declared”


I was working on some enhancements on an existing SQL Server Reporting Services (SSRS) report & so before started adding enhancements to the report, I made a copy of the existing report. And after that, I tried running the report as-is to make sure everything was functioning as it should. But I got an error:

SSRS Code not declaredSo, when you get an error like this, look for missing custom code in the SSRS report.

If you look at the expression which is throwing the error, you’ll see something like code.<custom function> in the expression.

To solve the error, you’ll have to define the custom function for your report. In my case, I had to copy the code from the existing report to the new version of report that I was working on.

To do that, follow the steps:

Design View > Right click on the surface outside the border of the report > Report Properties > Code:

SSRS Custom Code reporting services

I hope this helps if you run into this SSRS error.

What is the purpose of creating Tables & Graphs?


Knowing why we do what we do is important. Stephen Few lists four reason for creating Tables & Graphs in his book “Show me the number”. I really liked them so I am posting it here for your reference:

  1. it helps us communicate. It helps present information to others.
  2. it helps us analyze data. it helps us find the insights in the data.
  3. It helps us Monitor Performance. It helps us keep track information about performance e.g. Sales Performance, Speed of Manufacturing, etc.
  4. It helps us Plan. It helps us predict and prepare for the future.

Book Review: R in a Nutshell


R is a popular tool among data scientists because it’s just like a Swiss Army knife (or may be more!) for them!

R Language Data scientist swiss army knife tool

Analogy credit: Tapping the Data Deluge with R by Jeffrey Breen

Sometime back I worked on a research project that involved writing some R code – we were searching for tools ways to pull data from multiple social networks, perform text analysis and create effective data visualizations. R seemed like a great tool & so I was searching for a book/guides that teaches me fundamentals I needed to know to get few R related things done. One of the books that I used often during the research project was “R in nutshell”. I didn’t read it cover-to-cover but it was a great reference book for me. I used to read guides online/other-books and then I used to combine information from this book to get stuff done. The section I liked the most was on Data visualization which included some great code snippets to create effective data visualization using ggplot2 library. I used to take code snippets from this book & apply it on data-sets that I had.

text analysis sentiment

Fun stuff!

Also, I liked it that the book has some end-to-end examples that cover the entire life cycle of data analysis/statistical-analysis.


I recommend this book as a “reference” for someone who started working with R.


I received a copy of this book as part of OREILLY’s Blogger program. Thanks OREILLY! If you are a blogger, you should check out that program!

Gallery: Visualizing report execution logs data to understand what’s up with slow running reports.


At my current project, We faced an issue of slow running reports. SSRS was on SharePoint integrated mode. So before a meeting was scheduled between SharePoint Team & Report Development Team, I created a Data Visualization using SSRS Report Execution Logs like the one shown below so that we all had a starting point to discuss possible troubleshooting tasks for our team:

Here’s the data visualization:

Report Execution Logs SSRS data

can you guess – which Team took action items from the meeting? :)

Tableau: Data Cleaning for Geographic Maps


Data cleaning is a major part of any analytic’s/data-visualization undertaking. If data cleaning is ignored then it leads to inaccurate data reporting & thus suboptimal business decisions.

To that end, if you create a Tableau’s Geographic map, please check the accuracy of your data by going to:

Menu Bar > Map > Edit Locations

Let me give you some examples:

Now, I have “states/province” as my geographic role for the variable and when I created a geographic map, I created a geographic map it didn’t show any state for New York State! See Before:

data cleaning geogrphic map before

So what did I do?

I navigated to Menu bar > Map > Edit locations:

data cleaning geogrphic map State

So I fixed it!

data cleaning geogrphic map Tableau

And After:

data cleaning geogrphic map after

Note that New York State is lighted up!

In the past, I’ve also have entered Latitude & Longitude if need be.  This is when it was not able to recognize few US cities and it was saying “ambiguous” – I inputted Latitude & Longitude to clean the data:

data cleaning geogrphic map city


In this post, I described how you should check the data accuracy of a Tableau Geographic Map.

Resource: 12 recorded sessions from the 24hop business analytics edition are online! #passbac #msbi


Recently, PASS hosted a 24hop business analytics event:

And now, the 12 one hour sessions ranging from data visualization, predictive analytics to Big Data are online for you to watch! They also serve as “Trailer” for what you can expect at the PASS Business Analytics conference!

Here’s the URL: http://passbaconference.com/Sessions/SneakPeeks.aspx

And I was following some of these sessions live on the event day – and I can tell you, these sessions are great resources!

Also, I participated in the twitter contest (by Microsoft BI) that was happening along w/ the event – and this is what I got for my win!

24 hop twitter contest prize

hoodie w/ embedded earphones!

That’s about it for this post. Enjoy the recordings!