Jessica Moss
SQL Down Under Show 43 - Guest: Jessica Moss - Published: 6 Feb 2009
In this show Jessica Moss discusses SQL Server Reporting Services in 2008 and lessons learned with all versions of Reporting Services.
Details About Our Guest
Jessica Moss is a SQL Server MVP and business intelligence mentor with Solid Quality Mentors. She’s certified as a Microsoft SQL Server MVP and business intelligence mentor with Solid Quality Mentors, is certified as an MCDBA, MCTS: SQL Server 2005 BI, and an MCITP: SQL Server 2005 BI. She has been working with SQL Server 2005 since its release and has participated in many warehousing and reporting solutions. Jessica enjoys working with the local community and is a regular speaker at user groups, code camps, and conferences. You can read about her work at
Show Notes And Links
Show Transcript
Greg Low: Introducing Show 43 with guest Jessica Moss. Jessica is a SQL Server MVP and business intelligence mentor with Solid Quality Mentors. She’s certified as a Microsoft SQL Server MVP and business intelligence mentor with Solid Quality Mentors, is certified as an MCDBA, MCTS: SQL Server 2005 BI, and an MCITP: SQL Server 2005 BI. She has been working with SQL Server 2005 since its release and has participated in many warehousing and reporting solutions. Jessica enjoys working with the local community and is a regular speaker at user groups, code camps, and conferences. You can read about her work at Welcome Jessica.
Jessica Moss: Thank you for having me.
Greg Low: First up, how did you come to work with SQL Server and acquire your current role?
Jessica Moss: I went to school to be a programmer, computer science major at UVA. Out of school I worked with a consulting firm, doing a bit of everything. Sounded great out of college. Programming, dotnet, java. My first client was setting up reporting solution. I started with Reporting Services 2000, when it first came out. Came out in 2003 or 2004.
Greg Low: Targeted for SQL Server 2003 release, but eventually became 2005 release. Had enough functionality and need to release early.
Jessica Moss: The client was really excited to get hands on it. I started with that. Stepped away from that, did some programming. I finally put together a full warehouse solution in 2005. Integration services through reporting. I fell in love with it.
Greg Low: Interesting to get that full perspective. Boot camp thing. I love that it wanders through building the whole solution. People can become specialized in one spot, but important to see whole flow.
Jessica Moss: Absolutely. Thing with most BI projects is it’s focused on end results, getting information to users, whether Performance Point or dashboard. That’s the goal.
Greg Low: First up, tempting to go feature by feature, but what do you see as biggest change in Reporting Services 2008?
Jessica Moss: Most people excited about the change that we no longer have to use IIS to run Reporting Services. Before, to set up administrator aspect, had to have whole web server set up. No longer need to. Biggest thing for people looking at 2008 to be excited about.
Greg Low: Need for that was lack of flexibility or implementation or installation or deployment blocker because of people not wanting IIS on same box?
Jessica Moss: How I understand it, they ran into restrictions, what they wanted to do with Report Manager, things they couldn’t do with IIS. They didn’t need all the functionality, just handling web components for Manager. Separated out for this version.
Greg Low: Interesting. IIS 5 or 6 broke stacking into two parts. Had http driver at terminal level. Separate from driver. In SQL Server 2005, native web services introduced, another client talking to same driver. Bad idea, but at least another application which talks to that native driver. Going through same path, just without IIS over the top.
Jessica Moss: Exactly. Using those drivers to run application.
Greg Low: Question I think of. With IIS configurable aspects to web services. That means that degree of flexibility needs to be in configuration of reporting services itself as it exposes. How configurable is it?
Jessica Moss: Still fairly configurable. I don’t know if it has everything IIS gave you. Can set up to run off different ports, what you want to do. No need to work through setting up virtual or anything like that.
Greg Low: One other issue, does that change how its implement in web farms or reporting services?
Jessica Moss: Great question. I don’t know. I haven’t seen anyone put 2008 in a web farm.
Greg Low: Limiting prior, licensing. Had to have enterprise on all servers. Expensive combination.
Jessica Moss: Absolutely.
Greg Low: In 2008 edition there’s been big move to put pressure back on enterprise edition. Is there anything Reporting Services-wise that’s only enterprise in this version?
Jessica Moss: Similar to 2005. They have enterprise features infinite fill throughs, data driven subscriptions, things that do same as in 2005.
Greg Low: Subscriptions have big impact. I put up blog about that. I did work enhancing what someone had done in standard. Felt nasty implementing, but wouldn’t have done reporting services at all if they had to get enterprise. Four times the price. Significant blocker.
Jessica Moss: There are some organizations that use beta driven subscriptions for practically all reports. Constantly fluctuating number of employees sending reports to. If you’re using for reports running, benefit to go up to next level.
Greg Low: Should mention, what is data drive subscription?
Jessica Moss: Allows you to dynamically get list of people to send report to. You can run query, get list of emails, send report on scheduled basis to that list. Data driven, gets information from table, so dynamic. Can change at any time.
Greg Low: Send out sales report to sales manager, but only for their region. That kind of thing. Apart from move from IIS, and deployment wise, corporate folk not keen on having IIS, what’s next main thing that changed?
Jessica Moss: Addition of tablix. Combine report items in 2005 of table, matrix, and list, into one report item. Increased functionality so you can put groups next to each other, have columns and rows grow dynamically based on data. Kind of what you could do with matrix before, but not aggregate subtotals, able to massage data to look as you want.
Greg Low: In toolbox, they still have table and matrix, but they put in tablix.
Jessica Moss: Yes, in a presentation I did on 2008 Reporting Services, I explained tablix and how wonderful they are. Then you open the toolbox and see table and matrix and everyone asked what happened to tablix? What they’re doing is set up template for you. When you start report, you know if you’re just doing a table, just wanting rows, or a matrix, by pulling one over, they’re going to set up for you, even if underneath a table.
Greg Low: Yes. Another thing that seemed to change underlying, something different on how engine works. 2005, scenarios where you could run out of memory. People processing 20,000 page pdfs. Now, copes with that well, spooling to disk?
Jessica Moss: Yes. They changed the rendering engine. I don’t know exact details. Set up so when you run report, will give you first page or two as data, then run report in background as you click through. Users see their data faster than before as it only renders one or two pages. As you click through, goes through and figures out data. By the time you get to those pages, they are there for you.
Greg Low: With IIS, limitation, timeouts difficult to change. If you had long running queries, challenge.
Jessica Moss: Makes sense. One thing you have to keep in mind with new rendering, it will render report items in different order. Possibility. In 2005, you could have had text box that referenced table elsewhere. Code function, creating water fall effect of rendering after another. If you try to move to 2008, may not work in same way as in 2005. If you’re upgrading.
Greg Low: Good point. What is compatibility level like from 2005 up?
Jessica Moss: Actually pretty good. Few reports I’ve upgraded have gone perfectly. No issues. I haven’t heard of upgrade issues.
Greg Low: If anyone comes across things that render differently, the team has gone out of way to keep compatible and wants to hear of issues. Word rendering.
Jessica Moss: Exciting. Added ability to export to Word. 2003 format. They didn’t get 2007 in there yet.
Greg Low: Doesn’t overly worry me. Discussion early on about .doc and .docx. But when I send documents to clients, I put in pdf so they print fine. I often send .doc’s as there are a lot of people that can’t deal with .docx yet. Rendering doesn’t worry me.
Jessica Moss: I agree. Most of the documents I send out in .doc so I don’t get email that saying can’t open.
Greg Low: There are document converters they could pull down.
Jessica Moss: Yes and the 2007 viewer, but better to go .doc route.
Greg Low: Changes to Excel and csv with rendering?
Jessica Moss: I haven’t been involved there.
Greg Low: Whenever I had Excel as target type, team had done great job making Excel spreadsheet look like the report, but people didn’t want that. They wanted the data just as plain data in Excel. Manipulate it. Amazing the work they did to make Excel look like report, to have people not want it. I found I’d have to build two reports for every report. One, normal rendering, then another a table with columns so it could be rendered to Excel. Options to do that?
Jessica Moss: I haven’t worked with that yet.
Greg Low: Charting areas. More things available?
Jessica Moss: Absolutely. Microsoft purchased ... just the control. Some of the controls. Charting and gauge control. Handy when building dashboards.
Greg Low: Which do you find more useful? I look at gauges. Looks cute, but I’m not compelled by gauges.
Jessica Moss: I prefer linear gauges. Straight line with pointer to one general vicinity, only for dashboard purposes. General concepts of issues or things going well. Like a red green yellow traffic light. That’s where I’ve use them.
Greg Low: Yes, that strikes me ok.
Jessica Moss: Some dashboards, you feel like you’re sitting in plane.
Greg Low: Feels to me like clutter around information in the gauges. Distracting.
Jessica Moss: You’ve been reading Steven Hughes book, information on old dashboard design. Big on dashboards and visuals, ensuring everything clean and straightforward, and only information actually needed presented.
Greg Low: I realize how much I dislike clutter. Even at home. I grew up in place where we hoarded things. I’m getting to point where I’ve gone other way. Will I use in two years? No. it goes. I have clutter. A lot of these screens strike me just like that. Clutter for no reasons. Sessions where people compared Google with other sites. Amazing how it focuses you on what to do. Empty. Others you could barely find search box, main reason for going there. Total lack of clutter and focus on message critical.
Jessica Moss: Absolutely. Clutter whether on home page, in life, on dashboard, distracting, preventing people from seeing information they need or fulfill purpose of going there.
Greg Low: Charting? Useful.
Jessica Moss: 3D ones added. I prefer traditional in most reports. Look amazing now.
Greg Low: Functionally similar, but amazing.
Jessica Moss: They look professional. Designed a lot different. More capabilities grouping, sorting, with what pops up. Before in property window and you had to dig to find.
Greg Low: I find onscreen manipulation before not that easy. Experience in doing that better? I haven’t played with a lot yet.
Jessica Moss: Yes. A full window that comes up with tabs. Looks like table in matrix of 2008 Designer. When I opened table in matrix in 2008, felt like ... chart. I dug in and found they had gone that route.
Greg Low: One thing I’ve struggled with is pagination. Any specific advice you have on that? Changes?
Jessica Moss: Unfortunately, they haven’t changed anything. One problem most people have, working with pages, linked pages. Biggest thing is to ensure your margins and report width set right to add up to final width and height you want.
Greg Low: Frustrating in Designer. I can do all that, but just accidently bump something and it changes page width. Wish there was way to say paper won’t change, so just run with me. They are thinking around html not printed paper?
Jessica Moss: Maybe. Two forms of rendering. Hard page and soft page. Hard page breaks are Word and pdf. Soft is html, jpg, etc. Focus and realize two types of rendering, but Designer tends to lean toward soft.
Greg Low: Break, then drill into lessons you’ve learned.
Greg Low: Welcome back. Jessica, is there a life outside SQL Server?
Jessica Moss: Not much of one, but there is. I’m involved in the development group in the area. Northern Virginia Girl Geek Dinner Group. Focuses on women in technology, providing support area for the few of us there are. Traveling to different user groups, doing speaking on weekends. I try to do traveling, visit family. My brother is having his first child in a month. I’m excited.
Greg Low: Next to drill in to, interested in your experience and lessons learned. Anything about authoring reports. Anything you tend to find people often don’t do right?
Jessica Moss: Number one, not only in report writing, but any development, making sure your requirements are set up and you and who you’re creating report for, end user, business side, all are clear on what report should be.
Greg Low: Do you mock up reports?
Jessica Moss: I do sort of a mock up. Two phases. Listing data points with full description of what they mean. Amazing how many different field numbers you can have in one company. Everyone needs to be on same page. Will be Visio version that show s general layout of where things should be.
Greg Low: Tool that a business analyst could use that could mock up how it should look. Hard that stuff in Reporting Services doesn’t let you see what it looks like until it’s built. Tool would be nice to show that. When you start to show mock ups of screens, different level of response from people.
Jessica Moss: I agree. Occasional reports, not much functionality. Sometimes easier to do mockup with Reporting Services. Screen shot it, send it off. Good portion of work completed by then.
Greg Low: Requirements down is key. Next lesson?
Jessica Moss: Probably, you started to mention pagination issues. Unfortunately, preview tab of Reporting Services doesn’t render using any applications you normally export to, not even html. If you’re going to create report, you want to ensure you render in actual format end user will see. Will change pages and text boxes, changing to next line. Even overlapping report items. Those can overlap or push down based on rendering format. Ensure you do full rendering in development.
Greg Low: I find criticism that print preview looks nothing like what rendering actually is. What’s point of print preview? In particular, things like multi-column reports. I don’t do a lot of them, but newspaper style, loses plot in rendering. If you go through to print rendering rather than print preview, totally different, closer to what it will look like.
Jessica Moss: I post most to Report Manager and do there. Final layout and design, I look at through Report Manager.
Greg Low: Other things in design area? I’d like your feedback on this. I’m not a fan on tsql code embedded in reports. I find hard to do maintenance on databases if code living in many places. No idea how to fix. My preference is to build so returns data that needs to be rendered, keeping report to rendering data rather than business logic. Advice?
Jessica Moss: I prefer to use sort procedures. Be wary of tendency to try and put as many columns as possible in there, bringing back to report, using columns you need.
Greg Low: I tend to create report schema, then drop one for one for every report I’m building. I find code in database, which makes life easier for maintenance. I see people embed tsql throughout reports and my concern is when you go to change, don’t know what you’ll break.
Jessica Moss: Great idea, especially if multiple data sets in report. Easier to see what data you’re bringing in.
Greg Low: Other reason I like, is allow those who write good procs, ... allows you to focus on what they need to do.
Jessica Moss: Yes. Separate the work effort.
Greg Low: Works well where we’ve started from scratch. I went to person who wrote procs. ... I found very useful. Other thing that’s issue. Reporting Services has own tsql parser, different from one in SQL Server. I often write code and having it complain about code. If I send to database engine, it’s fine. Do you run into that?
Jessica Moss: I probably don’t do such complicated things. But if you’re throwing in CTEs, throw for loop. Things like that it’s not happy with. Temporary tables. Doesn’t recognize them. Has to know what field are coming back so you can pull over and design report. If you put table there to pull out fields, should be ok.
Greg Low: Gets confusing. Trying to add things to language not normally there. Multi-select. Look at tsql in Reporting Services, you have variables you can’t do in tsql. Takes that code and turns into statements under covers. Trying to deal with their version of what they wish language looked like to support reporting elements. They parse themselves.
Jessica Moss: Trying to join the two worlds together, disconnect.
Greg Low: Those who try to parse. DLLs that are tsql parsing DLLs able to use with extended applications. Fascinates me.
Jessica Moss: Yes. Sounds interesting.
Greg Low: Anything else on authoring reports? How do you deal with pagination? I’m typically working with things that will end up on paper. I don’t want to fiddle with margins and things. Want something, even landscape and portrait.
Jessica Moss: Figuring out my margins and page width and height, I do just before deploying report. No point in worrying about before. If you add column, adds 1.5 inches to right of report. If you’re trying to shrink over, moves over. I wait until end, then render report in right format, and make sure actually there.
Greg Low: I wish I could set margin and say do not move. Would rather have chopped off. Other thing is when I’ve written applications, I pull back as pdf. I find what works well is most pdf viewers have automatic views. If they press print, deals with portrait landscape. Do you tend to have preference for using html report end user reporting interface? Report Manager, or would you prefer to call from your own application?
Jessica Moss: Most people prefer to have from their own application. Probably using report control in server model, bringing down latest version of report to their applications. Allows developer to put familiar wrapper around to pass information to report. If you want to go to Report Manager directly, extra page or interface to go through to do their jobs.
Greg Low: Always feels like treats reporting separate to rest of application. I don’t see that way. I want to run application and have reports part of application.
Jessica Moss: I agree. Half and half, people want in their application what you’re talking, an extension. Other half want set up as description. Emailed to them or let them know it’s on file share and they can grab when they need. Depends on what report written for and how employees using.
Greg Low: What are your thoughts on handling long running reports?
Jessica Moss: What is one for you?
Greg Low: Example. I taught an class in Brisbane. I had guy in class talking about timeout of two minutes. He asked if it could be increased. I asked how far, he said two hours. I thought he was kidding, but their organization needed to move whole reporting. He thought needed web page that took that amount of time. Interface schedule is important.
Jessica Moss: Ideally you get report time down from two hours, but if it still take several minutes, and I have reports that take ten to 15, depending on data source. We try to get report to run in middle of night or after data comes in and populates source. You can kick off and create snapshot and have that last for entire day. When user comes to view report, they see that snapshot.
Greg Low: Anything in terms of configuration of Reporting Services that people should look at?
Jessica Moss: When you’re configuring in general, there are two modes to work with. Reporting Services native mode or integrate with SharePoint, giving you functionality akin to your SharePoint site, report libraries, version control as in SharePoint.
Greg Low: I haven’t spent a lot of time here. I sometimes want to have reports exposed on SharePoint, but call web services. Can I do both? Integrated on and off, or what?
Jessica Moss: Good question. I don’t know.
Greg Low: Something to explore another day.
Jessica Moss: You can put reports in SharePoint, whether either mode. Store as regular file or include in Report Web Part that shows report when you don’t need to be in SharePoint.
Greg Low: Any performance related issues worth noting in terms of Reporting Services?
Jessica Moss: Big part of queries is keeping mind any time you do anything in report, grouping, sorting, filtering, all must happen in full set of data after brought into report rendering engine. If you’re just trying to show full total of all sales information, and your report shows just that one number, no reason to bring all that data in and have Reporting Services do. Do from query side and display that. SQL Server faster to do that.
Greg Low: I see people do filtering and sorting inside report itself. Preferences?
Jessica Moss: I always do in SQL Server.
Greg Low: Only one, sorting. Maybe done better in client-ish type thing? Filtering, yes.
Jessica Moss: You’re right. Sorting can do interactive sorting, allowing user to specify ascending descending. If you’re using that functionality, don’t need to do on query side as doing on rendering side. Depends on what you’re using for.
Greg Low: Do you cache reports?
Jessica Moss: Yes. I do. From more a long running report point. Similar to snapshots but smaller scale. I’ll set up to run to cache. If something not happening often, but runs a long time.
Greg Low: Pre emptive caching, yesterday’s sales report.
Jessica Moss: Possibility. Thing with caching is cache as per parameters set. Many reports have date range. If they can select any data range, can pick one not cached.
Greg Low: Internationalization issues?
Jessica Moss: Localization. A bit. If you’re dealing with different languages, best approach I’ve found is using custom component, using that to display on report the different language, titles. When currency or numbers, if you use formatting code of n is number, c currency, specifying decimals, will pick up user’s language.
Greg Low: I seen scenarios where they use c for currency. Suddenly $100,000 USD converts to $100,000 yen, that’s a problem. People need to think long and hard on this. Does it make sense? When you’re using for money, critical to ensure proper currency looks like that. Different story if general purpose. Need to be more careful formatting for currency. You don’t understand what can happen. Scott Hansen went to his website one day and half was in Chinese. He thought he’d been hacked. He had caching set up in, varied by parameter, not header. Again, what’s recommended. His website had restarted and first visitor Chinese. Everyone after got cached copy. With any caching by location, need to ensure those who end up with, are those that want it. He seriously thought he’d been hacked. Funny. Anything on ongoing management of Reporting Services that need to be worried about? Pretty much looks after itself, but anything you’ve come across? Need to validate reports still work. Logging, history? Anything useful?
Jessica Moss: They do have logs for Reporting Services that if there are issues or even when rendering, errors that occur will go in log file. Deep underneath Reporting Services folder structure. I’ve opened a couple times when I’ve had install or configuration issues. Will show issues with reports. Can look at those. Under the covers, Reporting Services stores data in SQL Server databases. Multiple tables. Will want to do regular database maintenance. Keep backed up so you don’t lose those.
Greg Low: Related note, most people don’t know about backing up certificates. Pain point when recovering.
Jessica Moss: Yeah. When you do install, crates certificate that allows Reporting Services to run reports, so manager talks to that database. 2008 pops up warning box. Make sure you backup and hold on to. If you do need to restore, pop in certificate and good to go.
Greg Low: Previous version, tick boxes on everything you’ve done in configuration. Did have one in there regarding backing up certificate. Private information stored encrypted. If you restore on another machine, lose the encrypted content. Won’t start. Not pretty. One key thing in terms of ongoing management. I’m surprised it doesn’t shift reports about Reporting Services. Which reports run, how popular, how long to run. Seems natural extension to me.
Jessica Moss: I want to say I’ve seen some reports like that. I don’t know where they are, if provided by Microsoft or what?
Greg Low: If a listener knows, maybe they can pop us a note and we can add that information. We’re close to time. What do you have coming up in your world?
Jessica Moss: As of now, I’ll be doing training courses. Our courses for Solid Quality can be found on our website. Public and private training. I’ll be at the Western Virginia Code Camp in March, speaking on integration services.
Greg Low: . Terrific. Thanks for your time, Jessica. We’ll talk soon.
Jessica Moss: Thanks for having me, Greg.
Phone: 1300 SQL SQL (1300 775 775) l International +61 1300 775 775 l Fax: +61 3 8676-4913
Copyright 2017 by SQL Down Under | Terms Of Use | Privacy Statement