How does rise of Power BI & Tableau affect SSRS?

Standard

It does affect SSRS adoption but SSRS (sql server reporting service) still has a place as long as there’s need for printer-friendly reporting and self-service vendors don’t have a good solution to meet this need.

Also, SSRS is great for automating operational reports that sends out emails with raw data (list of customers, products, sales transaction etc).

I advocate an analytics strategy where we think about satisfying data needs using “self-service”-first (Power BI, tableau, qlik) but if thats not the optimal solution (for cases like need to print it, I just need you to send me raw data in excel, etc) then I’ll mark it as SSRS project. And this architecture is supported by a central data model (aka operational data store, data mart, data warehouse) which makes it much easier to swap in/out any reporting tools that we need and we are not locked in by one vendor.

About 10–20% data requests that I see are SSRS projects and if the self-service platforms start adding features that compete with SSRS, I know I would start using those capabilities and phase out SSRS. But if that doesn’t happen, I will continue using SSRS 🙂

VIEW QUESTION ON QUORA


Let me know what you think in the comments section!

Paras Doshi

This post is sponsored by MockInterview.co, If you are looking for data science jobs, check out 75+ data science interview questions!

SQL Server Reporting services: How to display “There are NO rows” message?

Standard

Problem:

You have a SQL Server reporting services (SSRS) report that has a table which displays some records — but sometimes it can have NO rows; In that case, how to display “There are No rows” message so that it doesn’t confuse the consumer.

Solution:

  1. Open the report in SQL Server Data Tools and go to the “design” tab of your SSRS report
  2. Select your table (do NOT select a cell inside a table. Make sure that the table is selected) SQL Server reporting services NO data rows message
  3. While the “table” is selected, Go the Properties section OR you can use F4
  4. Inside the Properties section, find “No Rows” section and you should see a NoRowsMessage property:SQL Server reporting services NO data rows message v2
  5. Go to the preview tab to make sure it’s working and you should be ready to deploy the change!

That’s it! Hope that helps.

Official reference:  https://msdn.microsoft.com/en-us/library/dd220407.aspx

Author: Paras Doshi

SQL Server Reporting Services: How to Solve Divide by Zero Errors?

Standard

Problem:

you thought you wrote an if condition to handle “divide by zero” errors in SSRS but still get the run time error?

Example. my expression is:

[code language=”sql”]
=IIF(Fields!denominator.Value=0,0,
CINT(Fields!numerator.Value/Fields!denominator.Value))
[/code]

And I still get the error:
Divide by Zero Error SSRS

Note: if you do not have integers to divide and you allow data conversion to double, it would show on SSRS as “infinity” instead of “#Error”.

Solution:

Let’s understand why does it do it?

So even though you are using IIF condition, the vb IIF condition evaluates every function in every expression before it renders and that’s why the “False” condition that I have in my condition gets run which results in #Error.

Ok, armed with that knowledge, let’s solve the problem.

So here’s a modified version of the expression, have a look:

See what we did there! We added one more IIF condition in the “false” condition of the parent IIF.

[code language=”sql”]
=IIF(Fields!denominator.Value=0,0,
CINT(Fields!numerator.Value
/IIF(Fields!denominator.Value<>0,Fields!denominator.Value,1)))
[/code]

That should solve the problem:
Divide by zero fix customer code ssrs

There’s also an alternative to this especially if you have a lot of expressions that does this. You can write your custom code and call it SSRSDIVIDE or you can come up with a better name! Here’s a post that talks about how to do that: http://salvoz.com/blog/2011/11/25/ssrs-checking-for-divide-by-zero-using-custom-code/

Conclusion:
In this post, we saw how to solve the divide by zero errors in SSRS.

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

Standard

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

Solution:
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]

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

How to train your users to create their own Business Intelligence reports? #4 of 5: Sample Training Content

Standard

In part #1, I wrote about why is it important to enable business users to create their own BI reports.

In part #2, I wrote about three pre-training preparations – 1. Data 2. Tool 3. Understanding Culture.

In part #3, I wrote about 1. User Experience 2. Trainer 3. Training Content.

In this post, I am going share sample training content that uses Excel 2010. Before I share sample content, here are some tips

1. use YOUR data!

2. Show them the end goal & then walk through the steps to get there

 

Here’s a sample training content for a 4 hour-long excel training session (divided into basics & advanced) including hands on lab time.

Here you go:

GOAL:

Excel analysis services business intelligence dashboardBASIC-I

  1. Open the Template
  2. Explore the Field List
  3. Explain the concept of “dimensions” & “measures”
  4. Create a Simple Pivot Table – Row Labels & Measures
  5. Add column labels & report filters

Excel Pivot Table SSAS Step 1

Basic-II

  1. Sorting
  2. Turning off grant totals
  3. Creating a hierarchy
  4. Changing the Pivot Table Design

Excel analysis services business intelligence dashboard step 2

 

Advanced-I

  1. Remove fields from Pivot Table
  2. Add more than one pivot table
  3. Add slicer
  4. Connect slicer with every pivot table

Excel analysis services business intelligence dashboard step 3

Advanced-II:

  1. Add Pivot Chart
  2. Add one more slicer
  3. Add hierarchy structure to pivot tables
  4. Add conditional formatting
  5. Format chart

Excel analysis services business intelligence dashboard step 4

Conclusion:

In this post, I shared a sample training content that uses Excel 2010.

How to train your users to create their own Business Intelligence reports? #3 of 5: User Experience, Trainer, Content

Standard

In part #1, I wrote about why is it important to enable business users to create their own BI reports.

In part #2, I wrote about three pre-training preparations – 1. Data 2. Tool 3. Understanding Culture.

In this post, part #3, I am going to write about three more important topics before you schedule trainings. Here they are:

1. user experience

2. Training Content

3. Trainer

Let’s talk about them in more detail:

1. User Experience:

How many clicks does it take for a user to get to the data? Measure this! From desktop, It’s Ideal to have 3 clicks or less to get to the data. If you more steps that users need to follow to get to the data, the chances of them getting lost somewhere else increases. If you have a great user experience, it’s easier for users to not have to remember how to get to the system. Here’s one example of an ideal User Experience:

Click #1: Click on web browser & type the name of the BI site (or pull it from favorites)

Click #2: On a BI site, they will have a “team site” (and that would show up automatically based on windows authentication) and they will see a reports categorized by subject areas. They will click on their subject area.

click #3: Click on a template and it will download the excel based template to user’s computer. (The template needs to be pre configured to connect to the data source)

In summary, easy-to-navigate BI sites are a huge plus!

On point #2, I had mentioned a BI site. You need some place for users to collaborate with their team & share reports. If you can’t setup SharePoint BI sites, then consider some shared network folders or have it on a common web site, some place that users can use to collaborate.

On point #3, I had mentioned “templates”. They may be excel files or blank power view reports configured to connect to the data source. Don’t ask your users to enter data source credentials – who would remember hxajfafhjfdakjinstance2143452 anyways?! Have templates that are ready to consume for end-users.

2. Trainer

Who needs to train user? of course, the trainer to have decent public speaking and communication skills along with being an expert at the end-user tool. He/she will also have to understand the business value of the data that the users are being trained on.

Now depending on the demand for training,  a trainer could be hired full-time/part-time to train users.

If there’s not enough budget or training demand, IT managers can consider requesting Business Intelligence Developers/Consultants/Architects or IT analysts to train the users.

If possible, IT managers can also request an analyst from the business group to do the training.

It would be great to record the trainings in video/document format for users to review them later.

3. Training Content & Format

3a Content

There are various methods to design training content:

– Look at Frequently asked questions from user community & design training content around them

– Invite smaller group of users for “beta” testing your training content. see if they like it! And keep improving your training content iteratively as you have more training sessions.

– Look at resources available online or books, user groups, etc for best practices & samples

– build upon the work of your colleagues, your past work, ask for feedback!

– And most importantly, remember to communicate business value in your training content.

– consider including Hands on (practice sessions) content in your training.

3b. Format

There are various training format & depending on your needs you will have to decide on the format of delivery mechanisms and training schedules:

Delivering mechanisms: In person or virtual.

Time: One hour-long/2 hour-long/one-hour for three days/ etc

I have had virtual trainings with users from Asia at 9 PM Easter Time & I’ve had 6 AM Eastern time meetings for users from Europe. You’ll need to decide the format that works best for you.

Conclusion:

In this post, I wrote about three topics for training business intelligence users 1. user experience 2. Trainer 3. Training Content.

How to train your users to create their own Business Intelligence reports. #2 of 5: Pre Training Prep

Standard

In part 1, I wrote about why is it important to enable business users to create their own BI reports. In this post, part 2, I am going to share the pre-training preparations before you start training. I’ve classified into three categories: 1) Data 2) Tools 3) Culture. Let’s go through them:

1. Data

Data assets needs to be published before you start training. It should be a business friendly analytic layer on top of your data sources. It could something as simple as a Power Pivot Model to a SQL server analysis cube. As long as you have an analytics layer – you’re good! Do NOT grant access to transactional systems. I’ve seen a business analyst who was considered the go-to-business-expert of a system having issues trying to create reports using the system’s relational data source – He had challenge trying to get his head around multiple tables, keys, unfriendly field names. He got something up & running but it was hard for him! What’s the lesson here? Try to make it as easy as possible for business users to use data – create an analytic layer over your data sources.

Apart from this, Data Integrity is very important! If the users don’t trust data, they are not going to use it. Invite selected set of business users to test the integrity of the data before you publish the data assets.

Also, the analytic layer that you developed should perform well. if it takes a minute to return fairly simple result, then you will have challenge driving adoption.

2. Tools

tools Business Intelligence reporting dashboarding

What tools would you use to teach business users reporting? Of course, Excel is a top choice since many of the users are already familiar with using excel. Also, Show them a Power View using YOUR data – that may get them excited enough to learn Power View.

How about SSRS report builder & performance point dashboard designer? This is mainly targeted for IT developers so it won’t be great idea to train business users using this tools.

What about Power Pivot/ Power Query? from #1, business users face challenge trying to analyze relational data sources, so test your audience to see Power Pivot/ Power Query is a fit or not. It might work well if they want to combine from couple of spreadsheets with IT’s data assets, that would work! But don’t expect business users to spend time trying to learn Power Pivot & Power Query to analyze data. Again, test it with your audience, see if they pick it up, some of your users may pick it up, great! But usually, you’ll have to create data assets (cubes/power pivot models/tabular models) to reach the masses!

3. Culture

Data Driven Culture Business Intelligence

Image Source: Economist & Tableau

The more time that you spend understanding the culture, the more successful you are going to get in training users. It’s because the things you’ll learn while trying to understanding an organization’s culture will be useful in content creation, delivery mechanisms, target audience selection & communicating business value of data driven decisions.

let’s step back. what is culture? It is a characteristics of a group of people. What characteristics are you trying to find before you start training? Try answering following questions:

a. Where are the “analysts”?

– Are they part of IT teams? Who requests reports from “IT analysts”. (they are your target audience!)

– Are they part of business units? (Great! Make them efficient by removing manual data copy-pasting from their to-do list)

– Do they report to CxO’s/presidents/VP’s? (Great! request examples of data driven decisions)

b. Are there examples of value generated using data driven decisions?

– understanding how business uses data to generate value is very important. you will create content using these examples!

c. How comfortable are users learning new technology?

– Have they shown resistance in learning new systems?

– Are they used to receiving ready-to-consume reports! (don’t expect them to change their behavior. But figure out the person creating reports for them. Train them! Make them better)

In summary, understand the culture of the organization, it would help you prepare before you start the training.

Conclusion:

In this post, we saw three pre training preparations (1. Data 2. Tool Selection 3. Culture) before you start training users.