SQL Server reporting services hasn’t changed in what seems like 10 years.  The rest of the reporting environment at Microsoft has seen some MEGA changes and it seems like we could use a few updates to the environment. Our guest this episode is Jessica Moss, a data architect and Microsoft MVP.  She brings us up to speed on what we can expect in the new version of SSRS and how this ties into the other changes Microsoft is making.

Episode 55 Quote

“So those four different groups that we have Microsoft has aligned the four reporting tools with each of those. So your paginated reports will be your Reporting Services. Your interactive reports will be PowerBI and that new product that we have that you were mentioning. We have our mobile reports which will be our SSRS Mobile or also known as DataZen in the past. And then analytical reports and charts will be Excel.” – Jessica Moss

Steve and I also answer a question we got from Episode 49 about when a table variable might actually be a good choice to use over a temp table.

About Jessica Moss

Jessica M Moss is an architect with New Market Corporation in Richmond, Virginia.  She is a Microsoft SQL Server MVP and well-known practitioner, author, and speaker in Microsoft SQL Server business intelligence. Jessica has created numerous data warehousing solutions for companies in the retail, internet, health services, finance, and energy industries. She’s also written technical content for multiple magazines, websites, and technical books. Jessica enjoys working with the Central Virginia community and speaks regularly at user groups, code camps, and conferences.

Resources

Follow Jessica on Twitter
Jessica’s blog
MSDN: What’s new in SSRS
SQL Server Reporting Services Team Blog

*Untranscribed introductory portion*

Carlos: So Jessica, welcome to the show!

Jessica: Thank you for having me.

Carlos: So Compañeros, for those of you who don’t know Jessica is a Richmond native and lives around here, so we see each other from time to time and so Jessica, I h ave to admit that I still keep checking my mailbox looking for that wedding invitation. I’m sure it just got lost in the mail.

Jessica: Oh yes, lost in the mail for about four months, that’s when the wedding was!

Carlos: Oh well, it’s slow. Well congratulations on that. Four months, it doesn’t seem like it’s gone by that fast.

Jessica: That depends on if you were the one that just got married four months ago.

Carlos: Yeah, that’s true.

Jessica: But thank you. Very, very exciting time in my life.

SSRS 2016 Changes

Carlos: No doub.t Well thanks for making a little time for us to come on the show. Ultimately our conversation today is on reporting with a focus on SQL Server Reporting Services. So some of those new features that are coming in 2016 and kind of how that landscape has changed quite a bit in the reporting and analytics space. So I guess, maybe let’s kind of jump off in there first. Why don’t we take a peek and you tell us what’s new when it comes to SQL Server Reporting Services 2016 and some of these new features?

Jessica: So, Reporting Services has actually made some great leaps and bounds within 2016. Microsoft really didn’t make any changes to reporting services for the past five, six years really since the 2018 R2 release, so 2016 they’ve actually done a lot of great work incorporating mobile reports and enhancing the reporting experience that we have today to really pull all of the different tools together and provide a really great and dynamic reporting platform.

Carlos: Right, you had mentioned that things haven’t changed there. We are getting some differences now. The other thing we’re also getting is all these other tools. So we’re also getting PowerBI, you know I don’t know, it seems like every day they’re coming out with something different: things in the cloud, stream analytics, all these different things. Why would we now, why still reinvent the wheel? What is SQL Server Reporting Services going to do for us that these other things can’t?

The Four Report Types

Jessica: That’s a great question and the way that Microsoft is proposing this is that they’re really focused on what the business needs are for reporting. So they’ve broken up those report groups into four different categories. Where the first one is your paginated reports. And when we say paginated, we mean kind of the reports that we think of today. So your Pixel Perfect reports, the ones you might have to send outside of your organization in a particular format, those are going to be paginated.

Carlos: And I also think of it like data reviewed and verified? Is that fair as well? Like my accounting group is going to want to look at that before anybody sends it.

Jessica: Absolutely. Financial reports are a huge way of doing the paginated reports. They want the numbers to be absolutely static so that they can look at it and prove it before it goes out anywhere. Your second group is going to be your interactive reports. Or, the analytics where you’re trying to dig into answer some sort of question where you may not really know what the details or what it is you’re trying to figure out but you have an inkling and you are going to drill into it to try to figure out an answer. Really that interactivity. The third group is going to be your mobile reports, where you have really more of a quick glance at something. You want to get a high level KPI or key performance indicator, where we’re looking at where we want to go from sales comparing this month to last month. I have a quick answer and decision. And then our final fourth group is going to be our analytic reports and charts. And that’s where we’re going to do some analysis and calculations on that information. So those four different groups that we have Microsoft has aligned the four reporting tools with each of those.

So your paginated reports will be your Reporting Services. Your interactive reports will be PowerBI and that new product that we have that you were mentioning. We have our mobile reports which will be our SSRS Mobile or also known as DataZen in the past. And then analytical reports and charts will be Excel.

Carlos: So let’s dive into that for a second, because one of the nice things about SQL Server Reporting Services in the past is that I didn’t need IIS. I could whip my SQL Server install, get that up and running, you know get a service account and have access through a web interface. Now when I hear mobile reports, that makes me think that things have changed a little bit there.

Jessica: You would think so, but actually they have made it even simpler than it was before to setup your entire reporting services configuration and all you have to do is tie it to, if you want, the PowerBI integration. You would point it to the PowerBI service that you have and then from a mobile perspective to be able to connect to that server, you’re actually going to download the PowerBI mobile app. It’s just an application on your phone. You specify the server that you’re going to connect to and that wil allow you to view those reports on your phone.

Carlos: I guess that makes senses, it seems like there’s a delineation there. I was thinking, oh, I’m going to see a web browser, go to a web browser and connect maybe through a VPN or something, and see my reports. What you’re saying is that there’s an app for it that I download and connect up.

Jessica: Absolutely. And that app all you’re gonna need is that encryption layer and your username and password if you’ve got it exposed through the internet or you might have to use VPN if you’ve got that on your intranet instead.

Steve: Okay, just to clarify one thing there then, if we’re using the PowerBI mobile application is that for that second category of interactive reports that you mentioned?

Jessica: That’s one of the great aspects of this application and the way that Microsoft is going is that they’re trying to pull all of these different tools together. So the application is called the PowerBI mobile app and you download and it allows you to connect to both the interactive PowerBI service that you have and the reporting services web portal that you have today.

Steve: Okay. Great.

Carlos: That’s going to be pretty cool. So one of the interesting things there is now I have multiple components. Now I have a report, I have a database server, and I have an application. Right now in the past those were kind of three distinct functions or groups of people that were doing that. Howa re organizations are deploying this? So now if I’m the data guy and I’ve done a little bit of reporting services, just hand out some reports, but the analytics has been kind of kept at bay or that’s another group. Maybe the Analytics group. Maybe I have one or there’s somebody who’s doing that. How are you seeing that mesh? Am I going to have to get cozy with my infrastructure guys so I can make sure the app can deploy and all that stuff?

Jessica: [laughs] It’s actually interesting journey that organizations are taking when we talk about these different reporting aspects and a lot of the analytics that you’re talking about, and data scientists is really where we’re going with that, they want to be able to do that interactivity, dig into that data, ideally as easily as possible which is where some of our data developers can help provide that information. But there is going to be some level of technical expertise that they need to be able to grab all of that together. When you actually start to turn that into an enterprise or a corporate feature of the organization, it tends to go back out of that analytics group back into your developers and your data team, so it can be exposed, maintained because you have all the fun support items that go into that like disaster recovery and making sure that we have a decent uptime.

Carlos: Sure.

Jessica: And all of that, so there is a little bit of a passing back and forth for that, but really supporting and maintaining this is still going to live in the job description of the DBA.

Carlos: Okay companeros, whoever you happen to be sitting next to, grab their hands, kumbaya…sing it with me!

Jessica: [laughs]

Carlos: So one of the things I was always interested in is you mentioned these KPIs that are available. How does somebody normally go about choosing a KPI? Because I kind of feel like that’s a business decision, but they’re putting this into, “Hey, you can do KPIs!” So now it’s like, “You write the report and you choose the KPIs.” How does that work and what are the proper ways of going about getting what those are?

Jessica: So KPIs, or key performance indicators, have been around forever. Especially when we’ve been talking about score carding and dash boarding. What reporting services gives you is the ability to create those separately from your reports and have them exposed right at the first page of the web portal. So ti’s right upfront where you’d want to see it. So even though it is available through the web portal, our data folks are going to be creating those, we still just like before want to work with the business and come up with what those KPIs should be and what they are most interested in seeing from where we are today and our target goal of where we want to be in the future. To define it with the business, then you can incorporate that right into the web portal so that they can see that immediately.

Carlos: So that web porta, right, I’m thinking previous versions when I go in I have severs/reports, server/report server. What’s modifying on that interface? Is it changing?

Jessica: It has not changed at all. You still have your reports layer and that is what used to be report manager portal and has that full availability for you to interact with the reports, view, update, manage them, all of that and the report server behind the scenes is going to be where your reports and all your items and resource are stored.

Carlos: Okay. Now the creation of those reports, so they introduced the…I can’t remember the old name for it, but now it’s the data tools right?

Jessica? To do the development of the report?

Steve: Oh, it was the Business Intelligence Development Studio for a while.

Carlos: Oh there we go. Which then became some kind of data tools, and now that integration with Visual Studio was almost complete, right?

Jessica: Yes, as of SQL Server 2016, SQL Server Data Tools has actually been incorporated into the Business Intelligence version of that. So you can download one set of tools and you get the templates for reporting services, integration services, analysis services, and your database development. So all in one neat package and not only do you get it for 2016, you’re also able to develop in previous versions of each of those. So very exciting that we don’t have to have a whole barrage of these tools on our boxes anymore

Carlos: Because that was confusing, right? You had SQL Server tools, Visual Studio tools, and it was…yeah. It was kind of a mess.

Jessica: Absolutely.

Steve: So wait a second, just to confirm there. So if I wanted to deploy from or create reports and deploy from SQL Server, and I had an old SSRS server running 2008 R2, you’re saying you could deploy from those tools to that older version a well?

Jessica: Absolutely. The only thing you need to do is set a property within the project itself so that it points, so for reporting services it’s either 2008 or earlier, 2008R2 or earlier, 2014 to 2016 and up. So you’ve got three different levels there. You select one of those, it magically changes the code behind the report, and then you can upload it to the appropriate environments.

Steve: Oh, very interesting.

Jessica: Very helpful.

Carlos: But that’s the install. Am I going to get those tools on SQL Server or is that still a Visual Studio component?

Jessica: It is still a Visual Studio component, so it’ll be a separate download so that they can make updates to it and keep it as close to up-to-date as possible where you don’t even have to re-install anything in your server or your 2016 world. So they’ve really separated those two sides of it.

Carlos: So with that new functionality, you already mentioned KPIs, we’re getting some new goodies and all the Power-things with the way that we can display data. It’s more robust, is that the right word to show that data? But I also kind of feel like, anytime we get anything fancy that’s more ways that things can break. So as we get users, and again I’m thinking of the PowerBI space as users can create their own reports and upload them, right, which we’ve kind of had on the SQL Server. We’ve had that SQL Server Designer or that data, whatever that designer tool was?

Jessica: Report Builder.

Carlos: Report Builder, yes, that they put out there. I was …eh…. Let’s take a poll. Have you had any of your non-IT people use Report Builder? I have not.

Jessica: [laughs] I actually had one client that did that.

Steve: I know I haven’t seen it.

Carlos: Yeah, okay, I was just curious to see if maybe I was just doing it wrong.

Jessica: It is something that you have to work into whatever the project or department is. So in the one scenario I had it, it was a group of analysts that were working with Excel to try to create those sorts of reports before. For them, report builder was a great step up and we had an analysis server cube underneath so it was very much of a drag-and-drop environment so it…

Carlos: So everything was sort of set, sort of pre-staged in a sense?

Jessica: Exactly.  But at least the different clients I’ve worked with, it has been few and far between. But in 2016 they have actually made Report Builder a little bit prettier. They’ve refinished it and they’ve added some additional charts for people to use. So maybe we’ll start seeing more companies use it.

Carlos: Interesting. So you mentioned new charts, right. So one of the ones you mentioned was tree map, starburst, customer parameter panes… and again, so now these are scatterplots right? All these things. So I kind of think there’s this, maybe not divide, but now there’s “data” and then displaying that data and then who supports that? So the numbers, okay I get that maybe if I’m the data person I’m responsible for those numbers. But then if you can’t understand what that scatterplot means, how are people getting over that?

Jessica: Yeah, what they’re, and this is that divide from the data folks, from the business versus those of us who have to actually support it and develop it. What some organizations have is a middle layer of a data architect, a data warehouse type team, that is that middle ground between understanding the business and what they’re trying to do. Knowing where that data comes from but not really doing all of the maintenance and support that all of our DBAs do. They really can make that connection between the two people or the two groups and make sure that what the business wants to be displayed is how it’s displayed and what should be there without having to understand all of the maintenance side of things.

Carlos: So it kind of just depends, it sounds like it’s a cultural or environmental thing. You almost have to have a person with that role. But if you are the report person, then…

Then that becomes you! [laughs]

Carlos: Okay, interesting. I guess, other things we should be looking forward to in SQL Server 2016?

Jessica: You mentioned some of the new report features that we have, such as the new charts and the other big thing that they did was re-did the rendering engine for reports. So before we were using HTML4 and Silverlight, they have revamped that to now be in the HTML5 rendering engine. Which means that we can now use reporting services web portal on a variety of different web browsers which is great. You can now use Chrome or Firefox and not just Internet Explorer or Edge.

Carlos: There you go, that’ll be nice.

Jessica: Very helpful for all of us. And the custom parameter pane actually is something that we’ve been asking for forever, whereas before it was static and you could only have so many on the pane that you had there. And it was actually to the point that I had many web applications where we would actually create our own parameter pane within an .ASP web app so we customize it. So it would lay out where we wanted it to and we could change the colors of that and then embed the reporting services report within an iframe within the application.

Carlos: Interesting.

Jessica: So, they’ve really taken us a step away from that, hopefully, give us the ability to at least put those parameters in different places within the report so that’s exciting as well.

Carlos: So I see, so if you had sections for example, you could actually move that parameter lower down in the report?

Jessica: Exactly.

Carlos: Instead of being up there at the top?

Jessica: Oh sorry, not in the report itself. Still in the pain in the top, but in a different order up there.

Carlos: Oh I see. So instead of the way you had to specify it in the report to show up there, you now have some manipulation options there?

Jessica: Right, so you could them all in one column or in one row, or 5×10… whatever is easier for you

Carlos: I see, okay. That is good.

Steve: Wow.

Carlos: I guess one last thought that I had, and Steve and I were talking about this in preparation for this episode and that is why, obviously if you’re an analyst Reporting Services is something that you’ve at least experienced. There’s Tableau, and PowerBI, and all those other features. Again on the data side, I still feel like there’s lots of folks out there who aren’t at least taking advantage of SQL Server Reporting Services. So if someone says, “Hey can you run this query and send me the results?” I still see a lot of people doing that. Now I know that maybe in the beginning, because you had to get into the designer and create some of those reports, if somebody hasn’t’ taken the time to get into SQL Server and into Reporting Services, is it going to be easy enough for them to say, “Okay, you know what? I have a query; I want to make a report out of this.” That it’s going to be pretty straightforward?

Jessica: I hope that the really ramp up to getting people into reporting services will be easier. From a 2016 perspective, we haven’t really made any jumps in that regard.

Carlos: Okay, interesting.

Jessica: You’re still going to need to create your own queries and there are the tools within Reporting Services to be able to do that within Table Designer or if you’re doing Analysis Services, kind of through that drag-and-drop interface. But in general you’re still going to be following the same pattern, especially when we’re talking about paginated reports. Now the mobile reports, they have made that easier at least to reduce that learning curve. One of the interesting things, at least within the mobile report publisher, is that you have the ability to design your report without having any data within it. So they actually provide sample data sets so that you can see how it would look and know what data you would want before having to pull in your data. So that is

Carlos: Why does that sound scary to me?

Jessica: [laughs]

Carlos: That sounds like, “Here’s the data that I want, go make my report do this.” That’s kind of what that sounds like.

Steve: Is that the bait and switch model?

Jessica: It can be! Hopefully before you actually publish it you do get the data the way that you want it to and expose it, but it’s kind of a different way of thinking about creating reports. It’s like, “Hey, I want to see it in a trend, but how should the data look to be able to show this trend?” So it’s a little bit of a different take at least on the mobile side and we’ll see what Microsoft does in the next version or so to bring those two models together. If they can really decrease the time to get up to running with reports.

Carlos: Now one thing you did say, that it didn’t dawn on me, so my mobile report interface, or my mobile report creation and my regular SQL Server report development will be in different tools?

Jessica: Uh, yes, actually.

Carlos: Ahh.

Jessica: We do have multiple tools that we have to work with.

Carlos: Of course! We’re talking about Microsoft reporting; you can’t just have one. Where’s the fun in that?

Jessicia: It is all exposed through the web portal. You can download each of the tools from there. But yes when it comes to the tools, we have the SQL Server data tools that we mentioned which is within Visual Studio, we have our Report Builder 3.0 and those two do our paginated reports. And then we have the Mobile Publisher and this is actually the tool that used to be in DataZen, which is the mobile company that Microsoft purchased and which has been incorporated into our reporting services mobile infrastructure. So that is a separate download as well.

Steve: So a question there on the editors there. You talk about the report builder 3.0 and the SSDT interface into the paginated reports. Have there been any major improvements to the editing and layout of those paginated reports or is that still kind of a bit challenging?

Jessica: What do you mean by challenging, Steve?

Steve: Well, when you’re trying to get a report to laid out to that pixel-perfect lok and you’re trying to get it to format for the page and for the screen and all of the different places in that repot where you can edit the layout and positioning and all those things, it always seems like it wasn’t quite as smooth as regular Visual Studio type tools.

Jessica: And unfortunately they have not added any functionally for managing that pixel perfect aspects. They’ve always had that snap to grid where we could start to get it to line up with other things, and that’s still there. What they really focused on this time was the modern look of it. So all of the charts all of that has been updated. So maybe that will help the users overlook some of the lack of having everything lined up the way you want it. But there’s always ways to get around it but it’s always going to take a little bit of time to get it the way that you want it.

Steve: Okay. I can only hope for improvements there.

Carlos: Yeah, pixel perfect is going to come at a cost there. Awesome, so Jessica, let’s do SQL Family.

Jessica: Great.

SQL Family with Jessica Moss

Steve: Alright, so the first SQL Family question is around keeping your skills sharp. With SQL Server features changing so rapidly with each new release, what kind of things do you do to keep your SQL Server skills sharp?

Jessica: Probably the most helpful that I’ve found with 2016 is a lot of the webcasts that are out there right now. So the PASS organization has virtual chapters for the engine, business intelligence, business analytics. They have literally a whole slide full of the different virtual chapters that are out there and they have just amazing presentations and sessions out there all the way from introductory to a deep dive. So I sign in and watch those as often as I can.

Steve: Okay, I know those re usually really, really good.

Carlos: And it’s interesting that you bring that up. I saw it yesterday, and I don’t know if it happened yesterday or last week, but Microsoft press has actually released all of their employees. And so they are not, I think they’re still going to be putting out books, but hat business has changed so much that the time it takes to get all those chapters and things put together that people are going to webinars and what-not because, how could you create a book on PowerBI for example? It changes every week.

Jessica: That’s incredible.

Carlos: That idea of kind of constant iteration, we’re seeing that in the way people are learning. So we’ve talked a little bit about SQL Server today and kind of the nuances of it, but if you could change on thing about SQL Server, what would it be?

Jessica: I would have to say, some of the licensing aspects.

Carlos: [laughs]

Jessica: Have you heard that one before?

Carlos: No, but I chuckle because I’m surprised I haven’t to be honest. Because it’s like a nightmare! No one wants to deal with that.

Jessica: It actually is. And you know, Carlos, when we give presentations we often get questions of, “Well, how is it like this?” Or, “how many people could I do for this?” and “what edition?” And really my answer every time, “I can give you my best guess but you have to talk to a licensing specialist.” And the fact that we actually have Microsoft licensing specialists, I think we’re doing something wrong. I just wish that was simpler to understand and that everybody would just know what we could do to get it to more people.

Carlos: Right.

Steve: Yeah, that’s a great point. So, the next question is on career advice. What is the best piece of career advice that you’ve received?

Jessica: Another great question. If I had to give or repeat a piece of career advice I’ve been given, it wasn’t by any one person, more of a culmination of a lot of people saying it in different ways. But the most important thing is to do what you love. To enjoy your work if you’re not enjoying what you’re doing, then change what you’re doing. If you focus on what you enjoy and what you love, then you will advance and move forward. You will advance and move forward and be a lot happier.

Steve: That is a very good piece of advice. I know I’ve experienced both sides of that myself.

Carlos: And I think we’re fortunate enough, and I won’t say that there are jobs on every corner, because I know that some sectors are struggling more than others, but I think we are fortunate that we have a lot more opportunities than most.

Jessica: Absolutely.

Carlos: So Jessica, our last question for you today. If you could have one superhero power, what would it be and why would you want it?

Jessica: So you had actually sent this one over to me before this episode and I was looking at it. I know what first came to my mind which points out one of my faults, but I’m going to go ahead and say it anyway. If I did have one superhero power, it would be teleportation. Because I tend to run late a lot. And there are plenty of times where I’ve thought, “Wow, if I could only just be there right now.”

Carlos: Yes. Voosh!

Jessica: Even though that points out one of my faults, that would definitely be the superhero power that I would like.

Carlos: Well Jessica, thanks so much for being with us today.

Jessica: Thank you so much for having me, this was a great time.

Carlos: So if people wante dot follow up with you, how could they get a hold of you?

Jessica: Absolutely. My website is www.jessicammoss.com . It has contact info and any of my upcoming presentations.

Carlos: Well awesome, thank you Jessica.

Jessica: Thanks Steve! Thanks Carlos!

Steve: Thanks Jessica. Have a great day.