Navision Report Generator Vs Access

Hi all, I have a Client who would like to impliment SQL Option and use Access to design reports for himself (ofcourse he can use Crystal Reports but Access is his idea). He thinks the Report Generator in Navision is not that good and not easy, which I think is just an un-informed statement. I need to offer a comparison for this guy before he goes in for the option he like as I seem to think it would not be a good decision since it is for only reports; of course other benefits would come in later. can someone out there help me on some key comparisons that I can make, especially from a technical point of view? I have looked at cost of training, and stuff like that, but am also not experienced with Sql, so am not confident enough of certain things. Waiting Robert

Hm, my question would be WHY Access and not OLAP/Analysis Services when using SQL? However, my suggestions for all our customers is: stay as long as you can on the native database. There are more (and better) report generators than Crystal Reports (soooooo sloooow) or Access (dam, why I can not access the database, (uh, now I know where the name came from)). As you have to pay for all these products anyway, why not using JetRepors (look at the sponsors). If your customer does not understand the report generator in Navision, he will not be able to create reports with other products, because he don’t know where the data (information) is stored in the database. Kind regards Walter

Thanks guys Now I can see you question and the sense there is. Indeed I also seek to justfy the advise to stay as long as possible on Navision Database. Only Customer’s excuse is that tehy would like to develop own reports easily and without reference to NSC or developers. What are the tools available to work with in SQL OLAP? What kind of skill would one need to work with OLAP/Analysis servcies of SQL? Where can I look for possible material on this kind of stuff? Robert

I can speak from experience as an end user of Navision and the native reporting tool. I too disagreed with my Consultant about using the native reporting tool built into Navision. I had the same reservation that you client has. It’s a big unknown considering it’s seems to be it’s own language, AND it didn’t seem all that powerful. Well I can tell you that yes, it has been a pain in the ass, and sure I don’t all the ins and outs yet, BUT I can assure you that it has opened my eyes up to how Navision works, where things are stored, why and when, and it is much more powerful than I originally thought. Sure there are limitations, but there are limitations to everything. The only thing that I don’t like about it is the limitations of the page layout, you can’t get to crazy and flexible w/o some serious programming. Hope that gives you some insight.

IF he wants to get graphical, Navision can not compete with for example Crystal Reports (but you can use C/ODBC to get data in Crystal no ?). Golden rule, stick to the native database unless the customer forces you to move to SQL. But reporting is certainly not a good reason to move to SQL Server, … and then use Access on top of it ??? I think C/ODBC is a better alternative in that case (although it costs you a session). SQL does not realy bring added value to Navision (because of the way Navision uses SQL, not because SQL is not good), it only introduces extra costs and complexity (locking is nt so easy to understand).

Without getting into an argument over which is better, I have hit a few limitations with Access reports which I guess for now are a result of ODBC limitations and the basic assumption by Access that it is talking to a Relational Data Model which of course it is not. I use Access for a few quick and simple reports just because it was easy and I am no expert in Navision coding. From my experience I suggest your customer will very quickly find things that they simply cannot do in Access because of the limitation above. The sort of problems I quickly hit were - - folw fields and flow filters don’t translate and commonly don’t return any data to an SQL select. There is no such concept in SQL Server. - date and date range selections have issues that I don’t really understand. - occasionally you have to ‘adjust’ the Access generated SQL Query code by hand in some cases to get the data you want. Things like ‘Left Inner Joins’. - Access is a very ‘Cool’ object drag and drop interface but unfortunatley the results are often very slow beacuse Access allows joins on anything Navision needs Keys / Indexes to perform. - what appears to be a by product of the SQL Statements assumptions in Access results in extremely slow returns (like hours) if the result set has select criteria on columns one or two layers down the join structure. For example in access - join Invoice Header to Invoice lines then set criteria in the Header, not the result, then set criteria on the Lines and note the result. Now join another table to lines and set criteria on this third table. My belief is that a Navision programmer has two factors woorking for (or against depending on you point of view) them when writing Navision reports - 1. They MUST understand the Navision data structure as it is, NOT as it appears to be when roughly translated to an ‘SQL looking’ representation. 2. They are working in the native environemnt with the availability of Navision C/AL code which can solve a lot of the problems. I still continue to do some reports in Access and some in Navision.

OLAP/Analysis server is a very underated tool. And if you have SQL server - it’s FREE! For serious reporting capabilities, anyone will need a combination of tools - Navision reports for hard data, Access/Crystal etc via ODBC for graphical needs and so on - but if you exclude OLAP from your tool bag then you really are missing out. Skills? There are technical elements no doubt, but it is an open MS technoloy and one that anyone with some technical capabilities could teach themselves.