How to Export to Excel Without Making a Mess

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 Excel

Read more…

SSRS Consultant Tip: Conditional Formatting

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 Formatting to Highlight Errors

Let me illustrate with an example. Here’s a snip of a report in Visual Studio. It shows a simple text box with an expression:

Now, let’s look at the expression:

In this case, Invoice_Amount is the amount entered at the header level (which was a modification we needed in NAV). Sum is the sum of the expense distributions for the invoice.   When they don’t equal, the user gets an error message (in red font).

Which was acceptable for the first couple of months. But users wanted more. They wanted it to be OBVIOUS. So, we decided to format the box itself.

We started with Text Box Properties:

From Text Box Properties, we chose Fill:

On the Fill page, we choose expression by clicking the function symbol:

If we look at the function, we see pretty much the same logic. If the amounts differ we want the box to be red. (Instead of changing the background color, we could have chosen font and created a similar expression.) But the client wanted the error message to be super obvious.

As for the report itself, we can see that—depending on whether the report balances or not—we get an error message on a red background.

Here’s the data, one invoice is in balance and one is out of balance.

And here’s the output:

As you can see, the first invoice is balanced (with no error message) and the second invoice is NOT balanced (with an error message).

A final word: Like many things, a little formatting goes a long way. Use this kind of formatting sparingly. If the report starts to look like a Christmas tree, the colors lose their impact.

Those of you guilty of this know who you are.

 

SSRS Consultant Tip: Multiple and Shared Datasets

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 possible to just have the user key in the appropriate item number. But that’s not going to make anyone happy (especially if they’ll eventually to want to select multiple items or customers). So, this report has two additional data sets: one to provide values for the customer and one to provide values for the item.

Let’s take a look at the report:

We have five datasets, but they’re not all the same.

The first dataset (called “DataSet1”), is used in the table and provides the bulk of the report output.

The other four datasets—“Territories,” “Categories,” “FiscalYears,” “FiscalPeriods,” and “Quarters”—are only used for parameter selection.

Shared Datasets vs. Embedded Datasets

Continuing with this example, only DataSet1 is unique to this report.

You can see this by right clicking on the dataset and choosing properties:

Now, we can see that the DataSet1 is “embedded in the report.” This means that this particular set of “query plus extras” can only be used by this report.

The other four datasets use the shared dataset option. That means that the query was defined BEFORE we created the report.

If we look on the right side of Visual Studio, we can see all the shared datasets that I’ve created for this particular project. I’m using most, but not all of them in this report.

When to Share Datasets

When I work in development, I almost only use shared datasets for parameters. If I have complicated logic that might power multiple reports, I’ll create a view, stored procedure or even simple datamart rather than create a complex dataset (as discussed in my post “SSRS Consultant Tip: Stored Procedures vs. Views for SSRS Reports“).

To keep things clear, I generally name datasets as I have here: “DataSet1” for the dataset unique the report and then real names (e.g. “Products,” “Territories,” “FiscalYears”) for shared datasets.

Multiple Datasets in One Report Beyond Parameters

In the example above, I only have one main dataset. But sometimes I’ll use other datasets in one report.

For example, for a NAV customer, I created a report to show all invoices that have been selected for payment. The user then asked to see all vendors with a credit balance who were not receiving checks at that time. In other words, every time the user asks for step one they also want to see step two.

Therefore, I created another dataset and another table in the report. But essentially, they’re two reports in one because they’re always requested together.

A No-No: Combining Data from Multiple Datasets With the Look Up Function

SSRS has the ability to combine data from multiple data sets. Going back to my cardinal rule about using SSRS only for presenting data and reporting, I don’t think this is a good idea.

Yes, it’s possible to link each dataset in the report to a different data source and therefore a different database (which I once did, combining Unix data and AS400 data in one report. Oh, the horror!). But the performance suffers.

I intend to write more posts on this topic as this is a fairly common scenario for users using multiple software packages.

 

SSRS Consultant Tip: Stored Procedures vs. Views for SSRS Reports

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 no stored procedures:

So save yourself some trouble and go with views wherever you can.

2. You can combine a view with other views

At Red Three, we often create a basic view that we can then combine with many other views.

For example, we often work with Dynamics GP and NAV. Both systems have situations where the data is stored in an entity-attribute-value format, which is less then ideal for reporting. In Dynamics GP, for example, additional customer data may be stored in Extender while in GP additional customer data may be stored in Dimensions.

To deal with this problem, we’ll create a master customer view of this data and then “normalize” it so we can then combine that view with many others.

(I acknowledge that this can become too much of a good thing. Combining too many views can produce a mess of unsupportable code.)

All that said, I do still use stored procedures regularly. Sometimes, a report will require multiple steps or a calculation that can’t be handled in a view.

Processing Speed

You may have heard that stored procedures are faster than views, and therefore preferable. (A consultant once told me that stored procedures are ALWAYS faster than views.) It’s true that a stored procedures doesn’t need to be compiled (as views do). And I suppose that this extra time might be meaningful if a report is going to run thousands of times.

However, I live and breath mid-market companies, and this difference in processing speed is NOT A FACTOR in performance in my universe. (At some point in the future, I am going to spend some time talking about important performance differences. But this isn’t one of them. For an example of something that does matter, see “SSRS Tip: Put Parameters in Your Query, Not Your Filter.”)

 

SSRS Consultant Tip: Keep Your SQL in Your Database

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 damn tools we want, a more realistic approach is to say, “If you’ll let me (or someone on your staff who knows what they’re doing) develop your queries, you can then use whatever tool you want to make your presentations pretty.”

Those tools could be Excel, or Excel with an add-in like Jet Reports, Power BI or Tableau. Which tools they choose doesn’t really matter. If you build the logic once—and know it’s good—you’ll have a greater sense of comfort (if not complete ease) when that logic gets used with other tools.

At the least, you’ll be reasonably confident that the data is accurate.

2. Debugging in SSMS is much, much easier than debugging in SSRS

Microsoft makes some great development tools. I work with both SSMS and Visual Studio on a regular basis. However, even though SSDT (SQL Server Data Tools) is an add on to Visual Studio, it isn’t that nice to work with if you’re developing queries while building reports.

Indeed, just this week I found that the query designer in SSDT isn’t just hard, it’s sometimes wrong. I had copied code from SSMS into the Query Designer. I wanted to quickly see some results. But when I did, I realized that SSDT had rewritten my query and actually changed my logic. But I digress.

Further, I find it’s crucial to fully verify data before formatting. Therefore, I’ll often write my queries, dump output to Excel and verify—all before I get around to creating reports (or passing the data to someone else to create reports). This process is much, much easier in SSMS, especially if you, like me, buy inexpensive tools like SQL Prompt to make development easier.

3. Six months, one year or two years later—you can still understand what happened

My point isn’t to just put some code in SQL server. The idea is to do most of your work in SQL server. I’ve seen situations where the report developer started with a view (or stored procedure) on the database, and then created all kinds of additional calculations in the dataset.

Then, six months or two years later, something needs to be changed. At which point, the developer (or even worse, a new developer), will spend days, weeks or months clicking on formulas, trying to figure out where the calculation came from.

In this respect, SSRS isn’t any worse than many other tools. (I’m thinking of the many years I spent with Crystal Reports). But still, if you don’t want to create “write only code,” it’s better to get your stuff in T-SQL and store it as a view, stored procedure or even datamart in your database. This way, it’s much easier to figure out what happened when you open up your report years later.

In my next post, I’ll delve into further into this topic of how to store code on the server.

 

SSRS Consultant Tip: What SSRS Does Well (And What It Doesn’t)

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, not so much. When you get your knowledge from books (and I read and scan through lots of books), you tend to learn about what you CAN do with a program, rather than what you SHOULD do. And when it comes to SSRS, this is an important distinction.

In my next few blog posts, I’m going to try and take you to the next level of understanding. Everything I describe will stem from my cardinal rule of SSRS performance happiness:

SSRS is an excellent tool for presenting and distributing data. It’s NOT a good tool for querying and processing data. That’s what SQL Server is for.

Let’s dig a little further into the implications of this rule:

  • You want queries to return ONLY the data you need for SSRS. (For more on this, see my post “SSRS Tip: Put Parameters in Your Query, Not Your Filter.”)
  • You should mostly perform aggregation and sorting in SQL server and not in SSRS. (Which we’ll cover when we discuss detail reports.)
  • If you need to combine data from multiple sources, it’s better to do that in SQL Server rather than have multiple data sources in one SSRS report.

In other words, use SQL Server for data crunching. Save SSRS for reporting.

Someone on Your Team Needs T-SQL

Following from the above, there’s one more key point I want to make: you cannot build efficient, easy to maintain SSRS reports without someone on your team knowing T-SQL and having access to the database.

Yes, there’s certainly a role for folks with less raw database skills on reporting projects.

Certainly, at Red Three we’ve successfully trained many super users on SSRS. But even skilled SSRS super users aren’t going to write the SQL themselves. Instead there going to take those queries and create many different kinds of pretty presentations.

In my next post, I’m going to elaborate on my reasons for using SSRS only for what it does best.

 

What to Criticize (and Not Criticize) as an SSRS Consultant

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 the Universe

This scenario has come up more than once, most recently a little over two years ago. We had done a bunch of data integration and reporting work for a client. Then they asked us to build a simple tablet app that could replace a spreadsheet they were printing and filling out by hand.

Our software development services were focused on data work. So, building tablet apps for the client’s warehouse was a little out of our ballpark. But the spec was simple. So we said okay.

Eight months and around 1500 billable hours later, the “simple spreadsheet” app had become what we (only half jokingly) referred to as their new warehouse management system. Oy!

2. The “We Have to Go Live” Work Around

Another fun situation. I had a role in a Lawson implementation project, focused mostly on converting data and some customization. Other consultants were in charge of writing a major interface between the company’s AS400 system and Lawson on Unix.

Two weeks before going live, the client asked me to test the interface. It took me five minutes to break it in two. It wasn’t going to work.

So, yes, I did criticize the other guys. But then my own solution, which I had to put together with whatever was available, was not my prettiest work. That’s what happens when Perl and COBOL are the only languages you have, the setup is beyond complicated, you have to work with a second rate integration tool, and time is up before you start. But we went live on schedule. And my workaround worked.

A few months later, I was able to clean it up. But anyone who’d seen the code in the meantime would think I was on drugs.

3. The Only Guy to Get Anything Done

This story isn’t about a program we wrote, but a program we had to upgrade. Essentially, we upgraded the existing “poor man’s workflow.” The workflow started as a RPG program. Which called a Java program. Which sent emails to users in Lotus. Who clicked on the email to approve changes. Which sent data back to Java. And then back to RPG. Which called an API in the ERP system.

It wasn’t pretty.

But the final result was highly useful to the business, and the program ran pretty well for many years. And the client’s IT department had the tendency to design beautiful solutions that worked in PowerPoint and nowhere else—so the client had no real alternative.

With these experiences, I find it pretty easy to limit my criticism of other’s work.

If people claim things that are absolutely false, then I’ll call them out on it and won’t be shy. But I won’t critique things because they aren’t elegant or don’t show best practices. Because I remember much of what I’ve written. And it isn’t always pretty.

 

SSRS Consultant Tip—Round Your Numbers, Don’t Just Format

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 post to point out one of those “helpful but not crucial” things: the importance of rounding your data instead of just changing the format.

Rounding Numbers Before Exporting to Excel

To illustrate, let’s use a simply example from the AdventureWorks Database.

Writing a report, we see that the total line amount is set to a numeric field with a total length of 38 with 6 decimal digits.

If we create a simple report (as below), we see all six decimal digits show up:

Let’s start by changing the formatting.

We change the properties for my Total Sales column to show only two decimals:

And when we run the report again, we think we’ve fixed the problem:

But have we?

Exporting a Formatted Number From SSRS to Excel

Let’s see what happens when we export from SSRS to Excel. (I work mostly with back office types, so I fully expect almost all of my reports to wind up in Excel at some point.)

In this case, the numbers look fine—but there’s a lot more detail behind the scenes?

And any finance person who looks at that number is going to ask questions.

(In this case, everything is formatted correctly in Excel. But once I did this in Dynamics NAV (which defaults to 18 decimal precision), and the field showed up with all 18 digits in the cell itself. Not a good thing.)

So what should you do instead?

Make sure that what’s in the cell matches the formatting.

You can do this in many ways, but the easiest is to create an expression that rounds the number.

In this case, let’s start by going to the reports dataset. On the fields page of the dataset properties, let’s create a new calculated field called TotalSalesRounded. The expression is simple:

=Round(Fields!TotalSales.Value,2)

Where “2” is the accuracy I want:

Then we replace TotalSales with TotalSalesRounded in Tablix:

The report looks the same in SSRS. But when we export it to Excel, the extra decimals are gone:

Rounding Your Numbers to Make Sure Everything Adds Up

I want to make one more point about how important rounding in the formula can be.
Let’s modify the report to show both the “rounded” and “just formatted” columns.

When we run the report, the columns look correct all the way down:

But if we go to the grand total row, we see something strange:

As you can see, the grand totals are different.

Why? Because the “just formatted” column adds the actual values and then formats the result.

In contrast, the rounded column works with the rounded value from the start.

As you can imagine, this “small” difference can become a big one, especially when rounding to whole dollars, thousands or millions.

 

SSRS Consultant Tip: Defaulting Your Parameters

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 more complex date tables—but that’s a discussion for another time.) I’ll include all the code at the end of this post for your use.

Step 1: Create Datasets for the Parameters

In order to select a parameter, we need to have a set of available values to work with.

So, I’ve created two simple datasets, one for FiscalYears:

And one for FiscalPeriods:

Now that I have my datasets ready to go, I can use them with my parameters.

I’ve create a very simple report that allows users to show order for a given year and period:

If we look at parameter properties, we can see how we’ve set the available values to use our FiscalYear and FiscalPeriod datasets.

For example, here’s my FiscalYear parameter:

And here are the available values which are set to come from the FiscalYears dataset:

(The same setup also applies to FiscalPeriod.)

When run, the report looks like this:

Now, if we use the dropdown box for either Fiscal Year or Fiscal Period, we see all possible values for our dataset:

Now, it’s NOT a big deal for the user to select from a list. But again, the idea is to make things look good. And if you have lots of fiscal years, it’s a little annoying to have to scroll down to choose the current year 99 percent of the time.

Now that we have our report, let’s see how we can set defaults.

Let’s start by setting the default value in the report itself.

Step 2, Option A: Set the Default with a Hardcoded Value

In general, I don’t like to set hardcoded values in the report itself. I don’t want to have to go to the report to change the value every time I run it. Still, if users generally run a report one way rather than another, it can be useful.

To set the default value, we go back to our parameter, in this case FiscalYear. We choose properties, and then choose default values.

In this case I’ve chosen to specify a value:

You’ll see that the default value is set to “Expr.” If we look at the expression, we see it’s set to 2007:

And, as soon as I open the report, I see 2007:

Step 2, Option B: Set the Default With an Expression

Of course, you can use a fancier formula. For example, it might be sufficient to default to years based on the current date of the system. The formula would look like this:

And when you opened the report, you’d see the 2017 default (which, I acknowledge, won’t return any data from AdventureWorks).

But with many reports deriving the default from current data, this might not be the best thing. We generally don’t issue financial or other statements until the time period has passed. So we need more control.

Step 2, Option C: Set the Default From Another Dataset

We can’t use generic logic to know that folks have moved from period 1 to period 2. (Indeed, sometimes I even create applications that set defaults by user name to get even more specific, but that’s beyond the scope of this post.) To do this, our basic FiscalYears Table not only has a column for FiscalYear, but also has a column for CurrentYear.

So, we can build another dataset called “FiscalYearDefault.” Through other means, we make sure we have only one year marked as current. Then we build a simple query to bring back that one value:

If we go Query Designer, we see that this is indeed the case. The one value of 2008 is returned:

Now, we go back to Parameter Properties and use this dataset as our source for the default value:

When we run the report, we see that 2008 defaults into the report:

Step 2, Option D: Set the Default Through Report Manager

There is one more way to set a default: in Report Manager. Generally, I only use this method when I’m deploying the same report to different report folders, and I want to specify certain parameters based on the users who work with the folder.

We find the report in Report Manager:

We right click to the right of the report and select Manage.

For my sample report, I created a default by FiscalYear, which SSRS shows me:

We haven’t set a default for fiscal period. But if we wanted to, we would check the default box and enter a value:

Now when we run the report, we see that Fiscal Period defaults to “1”:

One thing you should also note: If you provide defaults for all your parameters, the report will run as soon as it opens—which may or may not be what you want.

(Usually, I have at least one other parameter beyond my defaults that users must choose, but it’s something to consider.)

The code to set up the tables can be found below. If you’d like the RDL and RDS files, please let me know.

–INSERT CODE HERE–

 

SSRS Tip: Put Parameters in Your Query, Not Your Filter

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: performance. Parameter in Query results come back instantaneously. Parameter in Filter results take about 10 seconds.

Why is that?

To find out, let’s take a look in Report Builder.

Parameter in Filter

In the first report (Parameter in Filter), I have a very simple layout and one parameter:

And if we look at the query in DataSet1, we can see that the parameter is not in the query:

Figure 1 – No Parameter in Query

It is, instead, referenced in the filters:

Figure 2 – Parameter Used in Filters

Parameter in Query

In contrast, in my other report (Parameter in Query), you can see that the parameter is in the query:

Figure 3- Parameter Used in Query

SSRS Execution Log

SSRS has a view in the database called ExecutionLog3. It’s useful for seeing the differences between the two reports. (You may have seen it in the initial report manager screen shot.)

Let’s take a look:

You can see the path name of the reports on the left. The columns we care about are Time Processing and Row Count.

When the parameter was placed in the filter, SQL Server returned 756,000 rows to SSRS. SSRS then had to plow through them all, which took almost seven seconds. (The processing times given are in milliseconds.) This is even though the report only printed 25 rows.

In contrast, when the parameter is placed in the query, only 25 rows were returned. Which were the rows we wanted.

So, here’s the first rule for fast SSRS reports. Let SQL Server narrow your data as much as possible to avoid bogging down your processing times. If you’re not sure whether you have a problem, compare the row count from the execution log to the row count on the actual report output. If the numbers are way out of balance, you probably do.

 

Get tips and insights delivered to your inbox

Start a conversation with us

Call: 917-848-7284
Email: inquiries@redthree.com

Request a Consult