Open discussion: Jet Reports vs SQL Server 2005 reporting

Dennis_Decoene

Hi guys,

I was having a discusion here at our company. We where talking about Jet reports and SQL Server Reporting Services. They both more or less do the same thing.

Some people are more affectionate towards one or the other product. I would like to know what you guys think about the differences and or similarities of both products and why one should choose one or the other.

Should make an interesting discussion...

AK

I don't know anything about jet reports apart from their website, but we had to decide between QlikView or a similar product (which may have been jet reports) and the SQL reporting service.
We'll take the reporting services because of those reasons:

-if it doesen't work with reporting services we can recycle the SQL server licences for future migrations of Navision to SQL, which is already planned.
-the know how we have to get will be usable with other products too.
-it's far more easy to find a trained SQL/reporting services person than one for another product.
-better integration into our software infrastructure (local Navision databases in 26 countries, talking via BizTalk to our central SAP system).
-overall it's much cheaper

Alex

Miklos_Hollender

JET supports all typical NAV stuff like FlowFields and so on. I don't know whether reporting services do it or not. I tried to install that, and I found all that stuff from the reporting server to the designer in visual studio is a bit too heavyweight for SMB's. In contrast, JET is just an Excel plugin. I think JET is ideal as small, smart and flexible, easy to install, easy to use reporting tool. If you want a big, "scalable reporting infrastructure" then go with reporting services. If you want something streamlined and smart, go with JET.

Also, there are three typical kinds of reports. One of them is when you want to know everything about something, like, about a customer, orders, invoices, whatever. I think both are good for that. The other type is analytics, when you query many kinds of data and make calculations. I think JET is better for that, because you can build sophisticated Excel calculations and pull the data from NAV with JET. The third type is multidimensional pivots like sales by store and item group. I think reporting services is better for that. I think JET does not support Pivoting queries.

DenSter

If your NAV database is already on SQL Server, then I would recommend SQL Server reporting services, and I don't even know JET. The next version of NAV will use reporting services, so that would take away some of your potential upgrade pain should you decide in the future to do that.

On the other hand, it probably depends on what you want to use it for. Research the ideal use of JET and see if that fits your requirement. Look for volume and the type of reports. Do you need a lot of ad hoc reports that users need to create themselves, or do you need standardized reporting that you have a development staff for. Things like that all play a role in deciding what you need.

Miklos_Hollender

Another thing: there are many add-ons about reporting. You could request an add-on catalog from MS. I have seen a very interesting add-on at a partner summit. It's the usual BI stuff, but with 2 important differences 1) they promise a lot of reports preconfigured 2) I saw a screenshot showing items vertically, months horizontally, and each cell showed how much their inventory changed in the given month, and they said it's possible without programming or SQL queries, which is a WOW I think. Unfortunately I don't remember the name, I think its 'BI something" like EasyBI or BI4Navi or something like that. I'm sure it's in the add-on catalog.

mbmartinwa

We use a combination of the two. We started out using Jet exclusively, but some of the more complex reports we needed would take over an hour to run. A similar report in SQL RS takes less than a minute. Our biggest problem is that we are using the Native Database, so we had to come up with a process to replicate the data out to a SQL database for reporting purposes.

Miklos_Hollender

Put you can configure JET to run the slower reports in batch mode during the night.

mr.mcgoo

I used to use Crystal Reports because I found Access reports to light weight. But SQL Reporting Services blew both of them out of the water for me.
Having said that, you will need a good understanding of SQL and good general VB knowledge to get the most out of RS. RS is a good centralised Enterprise Reporting System, it is not great (yet) for end user ad-hoc report generation although with the inclusion of Report Builder in SQL2005, Microsoft have taken a big step to fill this gap.
RS is great but you will have to put considerable effort into mastering it.
It strong area is in automatic report generation and for generating reports from different data sources. For instance, my previous employer used Great Plains, Siebel, Sharepoint, Ad-hoc Access DB's and an itranet database. My reports pulled data from all of these data sources to provide a better overview of what was going on in the company.

Alex_Chow

Jet Reports is great since it's a lot easier to pickup since it's basically Excel. You can also utilize all the graphics tools on Excel with your Navision data. Lastly, you can drill down into Navision FROM EXCEL!

However, I've noticed performance problems when trying to extract a large amount of data, i.e. print detail. Jet Reports also suffers from the limitation of Excel, which only accepts 65565 lines of data. So you're trying to print a Value Entries details report, it'll error out.

Joe_Little

I'm very fond of Jet Reports, but I may be less objective since I work at Jet and was involved in some of the early design work on the product a few years ago.

We created Jet because there were a great many things that were pretty difficult to do in Navision. Columnar reporting is difficult, reports without good specs that executives need by tomorrow, etc.

Our goal was to move some of the reporting requirements off the backs of the IT staff, consultants etc. and let the users that need the reports produce them themselves.

Also, as a Navision consultant and developer, I wanted my team to be able to solve the easy stuff quickly and spend our time on the highest value services that our clients couldn't do themselves.

SQL Reporting services wasn't an option at the time, and is now. It can be used to make some wonderful reports, but requires expertise that is beyond the depth of the average user and is really not suitable for ad hoc reporting.

The other thing that we saw quite a bit of over the years were certain reports, often exec. reports that would sort of grow and morph early in the cycle. It would start out as "I need x ASAP" and then would move into "If I could just get y added and group by z" and so on. None of the report designers available really worked well in this scenario. Mostly it just caused headaches for the consultants and staff.

Jet is an extension of Excel that works with multiple database connectors. The original connector was Navision, which uses CFront technology to offer the features of Navision (such as drill down & flow field support) from Excel.

Jet is really easy to learn (it has 4 commands) but also pretty powerful. It groups, sorts, sorts calculated fields (like sums), summarizes rows/columns automatically by whatever and so on, so you can do things like get sales totals by inventory posting group effortlessly and without setting up custom keys.

In the Navision report designer, you can do the same things, but it sometimes means going to temporary buffered tables, maintaining arrays for columns, etc. and it isn't completely trivial.

Jet isn't the answer for every report, but it is a nice bit of kit to have in your toolbox.

TallMikey

We have found that most of our customers use Jet because their non-it people want to write reports. SQL reporting services is good if you are in IT and have unlimited time to write reports. Jet is great because every accounting department I ever worked with uses Excel, and Jet enables them to bring their Navision data into Excel. Try teaching an accounting manager SQL queies and table joins :>