We have Navision 5.1 and SQL - 2005. I heard you can write reports right out of SQL-2005. How do you do this? Under Visual 2005??
Thanks in advance,
Greg
Hi Greg,
You have a number of options. The stock answer is to us MS reporting services for future proofing. Most report tool will link into a SQL database and you can manually develop report in visual studio. The main things to consider is the skill set you/the company has and also the impact on the SQL server. You might want to create a mirrored SQL database for reporting or a separate data warehouse
Thanks Dave.
I’m looking at the SQL book under Enterprise Reports.
Greg
You can also pull data into Office products.
What do you wnat to report that is not achieved by the NAV application?
I heard in Version 6 SQL reports will be used. I also heard it’s a lot easier to write them in SQL.
In NAV2009 you would still create a NAV report object to create the dataset, and then you’d create the report layout in any RDL tool you want to use. SQL Server just comes with one for free (with the actual editor in Visual Studio), so it would make sense to use that one. This is only for NAV reports that run within the context of a NVA client. If you want to run reporting off of SQL Server Reporting Services, you will have to create everything yourself, and those reports will not run within the context of a NAV client.
Thanks Daniel.
You can also consider the hundreds of BI tools available instead of writing reports from scratch.
So is there a document that tells me what are the fields and links(joins) should be used , or what their meaning is ?
Gábor
That question has nothing to do with this discussion, but that information would be in F1 help inside the NAV client.
For complicated reports my favorite way is to create a SQL Stored Procedure first, which collects & processes data - including grouping, summarizing, substituting [No]s with [Name]s or [Description]`s etc, whatewer is needed - and then Crystal comes in as front end to format the result set nicely and in color, which is definitely impossible in NAV native report…
Such approach has 2 advantages:
first is SPEED - all data processing is done by SQL Server, you don`t need to overload network just for filtering out 5 records from 10 million on the client side. Example - there was a native report running ~9 hours (!!!) to print out a half of A4 page, SQL SP gathered the same data in 6 seconds + little more for Crystal to format it & print…
and second - unlimited formatting posibilities, maybe a not always necesssary extra, but sometimes one needs them.
… was editing to long my previous post, here comes Part 2 [:$]
And shortcomings:
Besides Crystal & Transact SQL, which many people are familiar with, you must know all the intricacies of NAV table structures. As DenSter mentioned, field lists one can find in F1, but just that wouldnt be enough - there is no info, how tables are linked etc. A special case are FlowFields - you wouldnt find them in SQL table directly [8-)] and so on.
I heard that the SQL name of a field is different from the Navision table field name.
Of course you can do this, and there are cases for running reports direct in SQL, but know why you are doing this first. If you need business information that is not in NAV right now, then think furst about Alex’'s suggestion of a BI solution. If its because the reports in NAV don’t give you exactly what you want, then consider modifying and enhancing existing reports before starting a new.
If in fact you just want to try it out and see what can be done (which personally I think is a great reason to try it) then I recommend you buy Rene’s book http://dynamicsuser.net/forums/t/24010.aspx which is a great starter guide to using Reporting Services in NAV.
Thanks David.