I am trying to recreate some of the reports that are setup in Navision for external reporting via web browser. How do I access the underlying code for the reports currently setup in Navision. Is there SQL Code associated with it?
Which report do you need to do this for?
There isn’t SQL code associated with the report in the way SRS uses SQL, but there are SQL calls that you can capture via Client Monitor (not the recommended way to go).
Instead, you should go to the Report Designer (click Ctrl+F2 while on the Report) if it is in your license. If it is not in your license, you will have to ask your partner for help.
Clicking on View, Sections will give you an idea of how the report is laid out. Clicking on each dataitem and hitting F9 for C/AL Code will let you see what code gets executed. Frankly, I don’t think that’s going to be enough – you’re probably going to need your partners helps.
if i connect via odbc, i can use normal sql code to query the db and then use the data to create a report outside of navision?
Yes, you sure can. Most people do this through Reporting Services.
You’ll start having to learn the data model a bit. What report are you trying to publish?
thanks alot for your help.
i am not sure if there are standard reports in the system or were setup special, but they are:
50080 - Outstanding Sales orders Status - New
50083 - Cust/Item by Rep for Rep
10045 - Customer Listing by Rep
50099 - Sales Rep Sales Totals
5000 - Prospect Profile
10062 - Top 20 Customers
Reports in the 50000 range are customized.
For Report 10045, the standard NAV report is “Customer Listing” not Customer Listing By Rep – so I’m not sure what other modifications have been made to the report. Assuming just the name has changed, you will have no trouble getting this report out of SRS. The tables you will need are Company Information (for the report header info), Customer (for the customer information), and Customer Ledger Entries (for the balance information). To calculate the balance, as of the date you are running the report, simply sum the Customer Ledger Entries where Open = True. If you need to run this report by Rep, you probably want to add a filter to the Salesperson/Purchaser field.
For Report 10062 Top 20 Customers you will first have to calculate a balance or (sales) for each Customer (summing the Customer Ledger Entries as above) and then sort them by Balance.
that makes life easier. i was actually thinking of using a reporting software called DBxtra to run the reports instead of SQL Server Reporting Services. Any thoughts about this.
I’m not familiary with that tool, but with an ODBC connection, every tool should be equally good.
What are the advantages of DBExtra over SRS?
Glad I could help. Welcome to the forum, jaredl!
well, it is one of the cheaper reporting tools and has a web viewer with authentication for users already built and allows for creation of pdf and excel versions of reports with the click of the mouse, which means i can deploy reports alot faster and not need to build an entire framework.
Sounds like pretty good reasons. Doesn’t SQL Reporting Services do all of that out-of-the-box too?