917.848.7284Data for Finance and Accounting

Top

FINANCIAL REPORTING AND ACCOUNTING BLOG

Several of my clients are New York-based social service agencies. As such, much of their income depends on Medicaid reimbursement. The problems they face are two fold: First, they need to make sure staff members document client work in a way it can be billed. That’s important, but it’s not something I deal with.

Second, they need to figure out how much they’re billing and collecting—and where the differences lie.… Read More

Many nonprofit agencies have reporting requirements that go beyond GAAP. For example, in New York State, social service agencies depend on state funding to serve their clients. To get this funding, they must submit various CFRs (Consolidated Fiscal Reports) to various NYS departments, including the OPWDD, OMH, OASAS, and OCFS (i.e. Office for Persons with Developmental Disabilities, Office of Mental Health, Office of Alcohol and Substance Abuse Service, and Office of Children and Family Services).… Read 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...

In an earlier couple of posts, I had described three critical elements of SQL Server security: pieces, people and permissions. For a non-technical person (such as, perhaps, an auditor) to understand SQL Server security, they need to have a grasp of all three elements. You can read more about the four pieces of SQL Server as well as the different types of people (i.e. logins). In this post, I’m going to cover the third essential element: Permissions. Part III: Permissions Now we have the pieces and the people. But how can they do anything? In other words, what can the people (the logins/users) do to the pieces (the databases/schemas and views)? For our purposes, we care about their abilities to add or change data in the database. We can grant that ability in two ways: Through a direct permission In a database, someone has a permission when they can something to something. For example, they can insert (i.e....

In an earlier post, I explained three essential elements of SQL Server security: pieces, people and permissions. Your auditor (or any other non-technical person) will need to have an understanding of all three parts to understand how security works in SQL Server. In this post, I’m going to explain the second element: People. Part II: The People While a database with no access would be secure, it wouldn’t be all that useful. So how do we tell SQL Server who gets access to the system? We start at the instance level and look at logins: Logins: A login is a way for a person or program to gain access to SQL Server. Let’s analyze a few of the logins I’ve set up here. Single Windows User Login In the above example, NH\ajacobson is a single windows user login. When you see a \ within a domain, you’re seeing a login that first existed in Windows.  Almost everyone reading this post...

I get calls from clients to answer auditor questions on a somewhat regular basis. These questions usually start with the accounting software and then drill down to the database level. Then, I often get this entirely reasonable question: Who can change data on SQL Server? This is, as I said, an entirely reasonable question. Unfortunately, sometimes the person asking the question knows next to nothing about SQL Server. Which makes it difficult to answer this question without launching into a lengthy explanation of SQL Server Security 101. (It’s disheartening when this happens. You would think that a person working for a large national firm sent to audit a system would have some knowledge of the database. It’s not like we’re using dbase or internally described files on the AS400. Yet, in talking to other folks, this is far from an uncommon problem.) So, to save you the trouble, I’m going to walk through...

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...