917.848.7284 Data for Finance and Accounting

Financial Reporting and Accounting Blog

SSRS Consultant Tip: Conditional Formatting

Adam Jacobson July 27,2017

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.

Read More

Tags: SSRS

SSRS Consultant Tip: Multiple and Shared Datasets

Adam Jacobson June 29,2017

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.

Read More

Tags: SSRS

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

Adam Jacobson June 27,2017

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.

Read More

Tags: SSRS

SSRS Consultant Tip: Keep Your SQL in Your Database

Adam Jacobson June 22,2017

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:

Read More

Tags: SSRS

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

Adam Jacobson June 20,2017

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.

Read More

Tags: SSRS

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

Adam Jacobson March 9,2017

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.

Read More

Tags: SSRS

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

Adam Jacobson March 2,2017

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.

Read More

Tags: SSRS

SSRS Consultant Tip: Defaulting Your Parameters

Adam Jacobson February 28,2017

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.

Read More

Tags: SSRS

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

Adam Jacobson February 14,2017

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.

Read More

Tags: SSRS

Understanding Row Level Security (Before SQL Server 2016)

Adam Jacobson March 16,2016

As I’ve posted before, writing this blog is much easier when I focus on things that interest me, which aren't purely technical generally. That said, some technical questions get asked so many times I feel moved to write the answer.

And that's the case with today's post.

Row Level Security and SSRS

Say you have a company with multiple territories – Northwest, Northeast, Central, Southwest, and Southeast. (For this example, I'm using MS AdventureWorks 2012 sample database.)

Read More

Tags: SSRS