917.848.7284Data for Finance and Accounting

Top

FINANCIAL REPORTING AND ACCOUNTING BLOG

I attended NY Nonprofit Media’s Tech Conference #NYNTechCon yesterday.  A good use of a day because it let me catch up and learn about things that aren’t part of my regular work.  A few things that stood out: Ransomware may finally force folks to take backup and recovery seriously. The vast majority of mid-size companies (and certainly mid-size non-profits) don’t ever test their backups. Backups are something that IT does.  They generally don’t restore those backups. And no user is ever forced to test that restored data.  We won’t even talk about a detailed disaster recovery plan. Now, with ransomware, people are scared.   And the only real answer is making sure that you have backups of your data in places that are not immediately connected to your network. And that regularly test the validity and accessibility of that data.. If you can’t protect it, don’t collect it. HT to @HaddassahDamien for the great...

An article in Politico caught my attention recently. It was about the Health Homes program and some of its problems.

For those of you not familiar with this program, here’s a brief explanation from the article:

Health homes are not brick-and-mortar buildings. They are a concept based on the idea that if several providers work together to coordinate care for the most expensive Medicaid patients, they can provide better care at a lower cost.

Read More

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