Jet Reports vs SSRS for Dynamics NAV

For the past few months, I’ve been spending a lot of time creating reports with both Jet Reports and SSRS, mostly with Dynamics NAV and a little with Dynamics GP. If you’re a NAV customer, you have some version of Jet included with your purchase. Like all folks with SQL server databases, Dynamics folks use SQL Server and therefore have access to SSRS.

So which is better? Or rather, what works best in any given situation?

In considering this question, let’s focus neither on regular reporting (sales order, inventory, etc.) nor financial reporting. Jet is clearly better suited for financial reports than SSRS. And if Jet runs out of steam, you’re often better off finding another financially oriented tool rather than going to SSRS.

Instead, let’s focus on a few other factors that might nudge your decision one way or the other:

As I’ve said before, the biggest ERP salesperson lie is “your users can build their own reports.”

Yet getting data without having to call IT remains the Holy Grail for end users. And on that topic I’ll say this:

Based on the end users I know, and the folks we’ve trained, Jet is definitely easier for end users to learn. It’s really just a few additional Excel formulas and some key terms. So, your Excel jockeys will dive right in.

What’s more, Jet Reports lends itself really well to learning in chunks. You can get started quickly without having to know everything there is to know.

SSRS, isn’t nearly as easy. SSRS is designed by folks who like SQL and Visual Studio—intuitive it’s not. We’ve had some luck with SSRS when the end users are dedicated reporting folks within their departments, but Jet wins the easy-to-learn contest.

That said, even with Jet, a little IT support can go a long way. For example, we built a lot of views to combine multiple data for a Dynamics GP client. When the users realized they could use the views in Jet (because they had the right licenses), they were very, very happy.

Here, Jet Reports win again. Not surprisingly, as Jet is an Excel add-in. You can build reports in Jet and keep all your regular formatting. In contrast, while SSRS dumps things to Excel pretty well, your formulas won’t come over.

Hands down, SSRS is faster than Jet. It’s the nature of the beast. Jet, because it’s Excel based, issues commands to the database and then does its work on the PC.

Meanwhile, SSRS does its work on the server. When properly run, it’s faster. And this is especially true when reports get complex. SSRS allows you to use stored procedures to save time. But with Jet you’re relying on basic tables and views.

To be clear, Jet is plenty fast for most uses. It’s just that when you have lots and lots of users and lots of reports, SSRS can be faster.

Jet is an Excel tool. And it does a lot of work in Excel itself. This means it runs best when you have a solid PC working on the data and when the server is on premise. When your apps are hosted, you need to consider running Excel in the cloud as well. And in my experience, this doesn’t make end users happy. (Note: the newest version of Jet does work with Office 365. However, I’m still not sure how well everything will work together if things are hosted in different places.)

SSRS, as a server-only application, is great for the cloud. All you need on your desktop is a browser. (And with SQL server 2016, you can even use multiple browsers without much aggravation.)

Excel is both the strength and the weakness of Jet. With SSRS, if someone asks, “It’s 10:00 p.m. Do you know where your reports are?” You absolutely know. You can easily control who has access to what and where.

With Jet, you’re chasing spreadsheets. Yes, you can make this work with folder controls, proper assignment of licenses and good procedures. But years in this business have taught me that Excel spreadsheets tend to get out of control. That’s not Jet’s fault. That’s just how people use Excel.

I’m not going to create a scorecard. Any scoring will depend too much on context. But I will make a couple recommendations:

• If you don’t have a lot of IT resources, but you do have some super user Excel jockeys, then Jet’s a good solution.
• If your users don’t use many (or any) tools and come to IT for help, or you have complex requirements that you can only meet with the help of developers, stick with SSRS.

I’d welcome anyone else’s feedback and experience.