917.848.7284Data for Finance and Accounting

Top

SSRS

Many, if not most, of the SSRS reports I write end up being exported to Excel. But unfortunately, many reports don’t export cleanly. More often than not, you wind up with merged cells and blank spaces you don’t want.

In this blog post, I’ll show you how to build your report correctly for a cleaner export to Excel.

Exporting to ExcelRead More

Anyone who’s in software development, and works directly with business users, knows that no matter how much things change, one rule remains: You never get credit for what’s hard. You may, however, get credit for what’s pretty. In that spirit, I’m going to write a few posts on making things prettier in SSRS. And today, I’m going to discuss conditional formatting. By conditional formatting, I basically mean making things turn red when there’s a problem. I’m not talking about fancy charts. I’m talking about basic operational reports where you want users to know where there’s a problem. For example, one of my clients uses Dynamics NAV. The “joy” of NAV is that it lets you enter data incompletely—and then only warns you when you post. That’s sub-optimal. Instead, we’ve built messages in our reports to alert users before they post, such as when they miss a value or a transaction is not in balance. Using Conditional...

As I’ve said before, the cardinal rule of well-performing reports is to use SQL Server for your processing, and not SSRS. Instead, save SSRS for presenting and distributing data. (And here's why.) In addition, you should create and store these procedures as views first, and then as a stored procedure, if necessary. Continuing our discussion, let’s talk about datasets. First, what is a dataset? You can think of a dataset in two pieces: Some kind of SQL code (a SELECT statement or a stored procedure) Additional logic performed to the results of that SQL Code once the code is returned to SSRS. Now let’s look at specific types of datasets and some scenarios. Multiple SSRS Datasets for Parameters Many reports require more than one query. To illustrate, let’s take a simple example I’ve built over AdventureWorks. This simple report gives us sales and allows us to select those sales based on customer and/or item. Now, it would be theoretically...

I hope I’ve convinced you in my previous post to develop and store your SQL on the database and my rationale for that argument. The next question is how to create and keep this code on the server. You have lots of options, such as views, stored procedures, functions, simple data marts and even SSAS. In this analysis, I’m going to focus on views and stored procedures. That’s where I spend most of my time and is most relevant for “next level” super users. Generally, I like to start with views before going to stored procedures. I do this for two reasons: 1. Views get along with most report tools Almost every reporting tool I’ve ever used makes it easy to use views. After all, views look just like tables to a reporting tool. The same can’t be said for stored procedures. For example, if you connect to SQL Server from Excel, you’ll see views and tables, but...

As I started this series, I emphasized the importance of using SSRS for what it does best, not for everything it can do. I ended the post by noting the importance of having someone on your team with knowledge of T-SQL and access to the database. Whenever I make this point, whether in presentations or with colleagues, they ask me to back up my argument. So let me lay it out for you here: 1. You can’t assume that SSRS will be your only reporting tool Over the years, many IT shops have tried to control the number of ways folks see data. And this control makes sense, in theory. There’s value in having at least some of your staff up to speed on your preferred tool. But in reality, end users like the tools they like, regardless of anything you have to say about it. Because of this human proclivity to use whatever...

I recently presented on SSRS at the NAV Focus Conference in St. Louis. As often happens at these events, I wasn’t exactly sure who was going to show up and what they would already know. (Especially at an event like this, which had a development track but with no specified levels.) In any case, it became clear to me that many folks in the audience could benefit from a series of posts aimed at a “SSRS 102” level. They’ve mastered “SSRS 101” material—they can create a basic report and know what data source, datasets and reports are—but now, they’re ready to move up. The Cardinal Rule of SSRS Performance Happiness To move from a SSRS 101 level of understanding to a SSRS 102 level, you have to understand what SSRS does well—and what it doesn’t. Like most software tools, SSRS can do many things. Some of those things it does really well. Other things,...

Recently, I read a blog post by Andy Leonard on his SQLblog.com titled Long Poles and Critics. In this post, Andy discusses the importance of not being too quick to criticize other’s work when he’s called in to complete or extend software reporting projects. Almost by definition, consulting is an arrogant profession. SSRS consultants are paid considerable sums to solve other people’s problems. So being quick to criticize is endemic. Still, I agree with Andy. I’m careful to give those who’ve come before me the benefit of the doubt. But it’s not because I don’t know the full story, as Andy writes. It’s because I know that, while I’m proud of most of the work I’ve done, anyone looking at some of my projects would scratch their heads. If you were to ask me, “What were you thinking?” I could tell you exactly. Here are some examples: 1. The Simple, Quick Solution That Took Over...

Here’s another installment in my series of posts on SSRS consultant tips for super users and advanced beginners. (See my post Put Parameters in Your Query, Not Your Filter for the first installment.) Part of what I enjoy most about working as a SSRS consultant, and performing SQL Server consulting, is the variety. Some months, I spend a lot of time writing T-SQL. Others, I’m deep into Excel pivot tables. And then others I’m writing SSRS reports. Truth be told, if I could spend all my time doing solution architecture and T-SQL coding, I’d be happy. But that’s not my current life. Every once in a while, when I go back to SSRS, I find I’ve forgotten a few helpful but not crucial things. So, I empathize with folks who don’t use SSRS full time and need a moment to get their heads back into it. In that spirit, I’m going to use this...

I’ve been doing various forms of systems consulting since the mid-90s. From my very first job, it was clear that the amount of credit you receive is rarely related to the amount of work involved. So, in that spirit, I’m going to help you become a reporting hero by showing you how to set default values in your SSRS reports. As an SSRS consultant, I do the majority of my work with finance and accounting data. That means my reports generally run by fiscal year and period. Therefore, in this example, I’ll show you how to have fiscal year and period default on your report parameters. So, even if you have, say, 15 possible fiscal years, the current year comes up. If you want to follow along at home, I’m using the AdventureWorks2012 database with the addition of three custom tables: FiscalYears, FiscalPeriods and DateToFiscalYearPeriod. (When conducting actual client work, we have...

As a SSRS consultant, we’re often asked how to speed up reports. While there are many ways to do it, I would make this the top one: Put parameters in the query, not in the dataset filter, if at all possible. Filtering is not your friend. Repeat after me: Put your parameters in your query, not in your filter. To show you why, let’s look at two reports: one called Parameter in Filter, the other Parameter in Query. Both reports are identical except for one thing—in one report I use a filter on the dataset and in the other I use a filter in the query itself. Note: I’m using a dataset here based on Adam Machanic’s bigproduct and bigTransactionHistory tables. The view I’m querying has about 700,000 rows—so it’s not a huge set. When we run either report, we get the same output: However, there is a one striking difference in the two reports:...