What Can Interactive SSRS Reports Do?
The Interactive SSRS report types you will see in this post have been a complete game changer for my consulting career. They are wildcard I get to play to keep my customers getting what they need without exploding the budget. I can work around so many functional requirements that typically cannot be met without a complex customization in Dynamics GP. They are like my Ace in the Hole, a little trick up my sleeve that gets the job done without new software to maintain or change orders to sign. The best part of it for me is that they are challenging and unique to the customer most of the time. These reports I will discuss can be so specialized to meet a customers exact needs and they are very fun to program, both in SSRS and SQL.
This post will contain a summary of the interactive SSRS report types I have used to solve these some unique customer requirements. To bring these theoretical categories to a more concrete view, I will follow this post up in the following weeks with detailed examples of each type so you can really get a feel for the power and flexibility of these interactive SSRS reports.
How Microsoft Defines Interactive…
Microsoft has a specific page for interactive reports in SSRS, I am setting out to completely redefine this very limited view of what it means to have interactive reports in SSRS.
Here is the post by Microsoft on its interactive SSRS types. It lists five different areas of “interactive” options. You’ll see after my idea of interactive they are really quite basic when we can do so much more with this tool at our disposal.
- Dynamically Show/Hide Report Objects
- Sort Columns of Data
- Link to Other Reports/Websites
- Add a Document Map
- Add Drill Through Reports
Of these, really only two are huge things that really provide great benefits to the end users. Dynamically hiding objects and drilling through to other reports (passing current report data along with it) are extremely helpful interactive items that I use quite a bit. The other three are pretty much standard expectations of any reporting tool in my opinion. They don’t provide much extra interactive benefits and certainly don’t come close to solving the customer needs like the following “Next-Level” Interactive SSRS Reports
The Next Level Interactive SSRS Reports
Now for the fun stuff – this is where I get really excited because these things can add so much value at so little cost. These solutions do not apply to every client scenario, but they very often can provide a bridge to get clients that extra value that they need out of their accounting package. Sometimes a new 3rd party software solution is needed for these types of problems, every situation is different. These solutions have worked many times for me with great success and they are not very often even considered as a possibility.
My passion is to take software that people own but don’t fully utilize (“shelf-ware”) and adjust it to reach it’s highest potential for serving your business needs. With that in mind, lets look at how SQL Reporting Services can bring out a whole new type of interactive reporting.
Interactive SSRS Type 1: Check Custom Business Logic
This is probably my most common custom report that I build. Every single accounting department has processes and controls in place that can be clearly identified by things that are tracked in the database. With SSRS, we can completely customize what those controls look like and raise a flag immediately when any of them are not met, or even if it is just a potential issue that needs to be investigated.
Here is a list of some common things this type of report can check for.
- Check for Proper Inter-Company Relationships on Transactions
- Checking for Illogical Balances in Accounts
- Checking for monthly accrual postings
- Monitor GP Workflow and Approval Paths
- Check for Missed Recurring Batch Postings
Follow Up Posts:
- Detailed look at reporting in SSRS using custom business logic
- Live Video Example of my Inter-company Audit Solution
Interactive SSRS Type 2: Perform Recurring Repetitive Tasks
There are always some things in accounting systems that are repetitive, automating them can free up your time for actually analyzing and thinking about your business/accounting needs. Some of these reporting types do need to update tables in GP – whenever that is the case I am extremely diligent in my testing and in making sure that my customers test out the process thoroughly to make sure the process is safe and secure.
Here are a couple of examples of repetitive tasks that can be automated
- Remove dormant GP Users on a schedule
- Remove Empty Batches
- Restore Production Database over Test Database (Dynamics GP)
Interactive SSRS Type 3: Change Level of Reporting
Often how you want to view a report is not how your system is setup. If this is the case and you are manually grouping or splitting information based on some other rules, it can be done automatically in SSRS reports.
The functional layout of the system or the categories you want to report on are not always at the level of detail you would prefer. One very common example of this is that my customers want to budget at something other than the GL Account level. In this case I have setup interactive reports to import excel based budgets (usually customer or vendor based) and compare that data to actuals coming out of Dynamics GP. We can even roll-up the Customer/Vendor level budgets for import at the GL level into GP so you can use the budget at that level too.
There are also instances where my customers have been using the system so long and certain things like item categories, vendor/customer classes, and account categories are more detailed for some things but they want to report at a higher level. In those cases we just create a mapping together (that the end user maintains) that rolls up those detailed records into the higher level for reporting.
Interactive SSRS Type 4: Execute Complex Forecasting Calculations & Reporting
Forecasting is a difficult thing to do, and often just ignored for that reason. Another reason companies tend to ignore it is that the systems out there that do it are either very expensive or not very user friendly. With some careful thought and planning, many of the outputs that those other tools use can be built into logic in an SSRS report. Once again, in this scenario we define the calculations up front then hand it off to the customer to decide which GL Accounts (or segment ranges) should use what forecasting type. Handing this off to a customer means we create a report interface where they have 100% control over what calculations should happen to the accounts. We can then use an interactive SSRS report to read that table and project out based on whatever parameters the end user desires (Years, Calculation Types, Filter Data, etc..).
Interactive SSRS Type 5: Track Changes from Report to Report
In my experience watching how accounting teams function, there are many repetitions of same reports again and again (especially during month end close). Many times these reports are being re-run just to check if one or two changes hit the books.
In other cases, end users want to see changes from one report to another for approval purposes. With all the new workflow features in Dynamics GP, more and more teams are getting this extra control into making sure accounting entries are perfect from the start. This comes with some administrative pains up front though where again reports are run and re-run, having to pick through the data to make sure things are right. These managers love to see a report that shows them the exact differences from the last time they ran it.
In a future post, I will show you what this looks like when approving AP Transactions, using Interactive SSRS Reports in place of the standard GP Edit Lists.
Interactive SSRS Type 6: Use Setup Table to Adjust Report Functionality
I recently completed a private sector implementation that used Interfund Management, which can keep funds in balance but does not give much control on what funds should and should not interact. We ended up creating a setup table in SQL (managed by the end users through a SSRS report) that shows which funds should or should not be on the same transaction as other funds. We then use the settings in that setup table to run reports identifying immediately what transactions violate their expected fund relationships. Errors can be corrected within a day or two instead of becoming an audit issue down the road.
Having end user maintained setup tables is an excellent way to set expectations on what should be happening in your accounting system. The possibilities are endless; so many business rules can be added to setup tables like this and used in reports to not let anything fall through the cracks. I have also used setup tables to determine report subscription email settings, forecast calculations per GL account, and tracking sign off on month-end processes.
Interactive SSRS Type 6: Enter Reporting Only Balance Adjustments or Allocations
Sometimes Managerial Accounting needs are different then what your Financial Accounting requirements dictate. There are often things you may want to do to make business decisions that are just not tracked very well in your accounting system. This is because accounting systems are targeted toward the financial accounting world. Managerial accounting however is trying to solve a completely different set of problems. Through this type of reporting we can put rules in place to use in reports or track that little bit of extra information you need to get Managerial Accounting Reports. This can help determine things like what costs are direct/indirect, fixed/variable, or to show the cost drivers of your business.
This type of report allows you to display adjustments to classifications without ever having to touch your actual financial data. It does take a lot of time and consideration up front to get the exact rules in place, but once it is done, is a very useful tool for decision making.
Unbounded Creativity with SSRS Reports
To close this out, I just want to encourage you to get creative with these different reporting types in SSRS. Accounting teams often jump through too many hoops and are trying to follow strict rules at month end that are totally unnecessary and prone to error. If you can identify rules around when things should or should not be happening, and tie those rules to your ERP data, I guarantee these reports can be a game changer for you.
I love to break down business routines and processes to determine the objective rules behind them, and then use technology to make it so much easier. If you are curious if these reports could help you out, send me an email and I would be glad to think through the options with you.