To make a difference in this competitive world, users of today’s ERP solutions want to easily perform analysis and produce reports based on their ERP data to make informed business decisions. They need an easy to use, intuitive environment to help them perform the necessary analysis on their ERP data. In some cases, they also need the ability to enhance the analysis with data from multiple sources or the ability to share the insight of the analysis.
The most popular tool for performing data analysis is Microsoft Excel. Excel is rich with built-in analysis capabilities, such as pivot tables, array formulas, charting, and filtering, all familiar mechanisms to the Excel user.
Microsoft Dynamics NAV 2013 offers simple and advanced ad hoc reporting options.
Simple Ad Hoc Reporting
Microsoft Dynamics NAV Excel Add-in The Microsoft Dynamics NAV Excel Add-in is the solution for those users who want a simple user-friendly ad hoc reporting experience. You simply choose the information you want to analyze and report from predefined lists in Microsoft Dynamics NAV. The add-in ties into and expands the existing integrated Send to Excel functionality. It enables you to work with data and format a report and then to keep the data updated. You can share the report, but it is important to note that a user can refresh the report only if the user has access rights to the given area of the application.
The following is a short introduction to the new Microsoft Dynamics NAV Excel Add-in.
1. You can begin analysis from a list in Microsoft Dynamics NAV, for example, Customer Ledger Entries. When you choose Send to Microsoft Excel, the export to Excel will reflect your filtering and choice of columns. In this case, all customer ledger entries are exported, but only the columns chosen by the user.
2. The exported data is formatted in a data region that you can easily use for pivot analysis. For example, you can use pivot formatting capabilities to see an aggregated view of the ledger entries by month. You can use filtering to view analysis only on open customer ledger entries. So, in this example, you can display expected receivables over time formatted nicely, as well as show the details per transaction.
It is very easy to create this kind of report – it only requires that you know where to find the data in Microsoft Dynamics NAV and have skills in formatting data in Microsoft Excel. To ensure better access to data, all relevant ledger entry pages have been added to navigation in the role centers.
Jet Reports Express for Microsoft Dynamics NAVIn some cases, you may need more than working from list pages provides. You may need to perform many business calculations to get to the correct data – this is typically true when you work on “By Period” data that needs to calculate balances.
Through the powerful Jet Reports Express for Microsoft Dynamics NAV reporting solution, you have a simple way to create these types of self-service reports. You can use the intuitive interface of Jet Reports Express and simple formulas in Excel to create high-impact reports.
Jet Reports Express comes with multiple report templates out-of-the-box and presents findings in a single, well-formatted report.
Advanced Ad Hoc Reporting
For more advanced analysis scenarios, you may need to access more data than what is shown on pages in Microsoft Dynamics NAV. In some cases, this is easily solved by simply adding fields to the list pages. In fact, this has been done by many of our partners.
But in some scenarios, this is not sufficient. Your report may require calculations and dimensions for your analysis, which are typically not relevant for the users of the ERP system. In other cases, adding a field can impact application performance, which you probably do not want to do if it’s only for reporting purposes. So consider using the new query object combined with PowerPivot for Microsoft Excel, which can solve this quite efficiently.
One of the strengths of the new query object is its capabilities in retrieving and aggregating data. In scenarios where data is stored in a variety of tables and databases, it is very important to be able to store, manage, and retrieve this data easily and quickly.
Building on the example from above, suppose that you wanted to add information about the customer name, dimensions, or territory. This is possible through query integration to PowerPivot:
1. Creation of a query requires access to the Microsoft Dynamics NAV development environment, but once the query has been defined and exposed through web services, it is very easy for an end user to manipulate the data in Excel. For those interested in the query definition and design, see other posts on using the Query object for reporting and BI on the Microsoft Dynamics NAV Team blog.
2. PowerPivot connects to the Microsoft Dynamics NAV database through OData web services. The link to the correct web service needs to be provided to the user, but after that, the user has access to the data quite easily. When you connect, it is possible to see all queries that are available, from which you can pick and choose.
Microsoft Dynamics NAV 2013 provides you with two approaches to data analysis, a simple and an advanced reporting scenario:
Microsoft Dynamics NAV 2013 provides a great set of opportunities for doing ad hoc reporting in Microsoft Excel.
Do you or your customers have peak hours when all the sales orders and invoices need to be posted at the same time? Or do you have large batches of postings that need to be run without blocking other users? I bet the answer is yes. If so, then you may also have experienced your screen “freezing up” for several seconds –minutes even -- until the order is processed. “Try again later” is also a well-known option.
To provide a better user experience and control of the posting, we have added the ability to post sales and purchase documents in the background. The user experience resembles that of printing of a document; the document to be printed is sent to the printer server and when the printer is ready, the document is processed. Typically, there is very little wait time – and the same goes for background posting.
It is important to note that when the posting is happening in the background, it uses a dedicated user session to ensure that the posted document is posted as being posted by the user. So it is still possible to track who did the posting.
The status of the posting can be identified in a few ways:
Background posting uses the job queue, which has been enhanced in many ways in this version. In Microsoft Dynamics NAV 2013 the job queue can run either directly in the user session or it can be run through a dedicated NAS Service. Additional flexibility in the way the job queue can be set up has also been the focus of the release. Now, the job queue has the following capabilities:
We hope that these enhancements to the job queue combined with the background posting will open up more flexible usage of Microsoft Dynamics NAV and will lead to more efficient users and a better experience in peak hours.
In order to set up background posting, there are several areas that are worth highlighting:
The first step is to set up the background posting, in this case, the sales side.
On the Sales & Receivables Setup page, a new FastTab has been added: Background Posting.
You activate background posting for sales documents by selecting the Post with Job Queue check box. To ensure that you have a dedicated job queue, you can provide a Job Queue Category Code. This routes the posting to the correct job queue.
The same applies for Post & Print – it is also set up if you select the check box.
The posting will create a job queue entry with the priority 1000. The priority field is used to determine the order of processing the job queue entries. By setting this to 1000, it is easier to allow other jobs to get in front of the queue.
Activate the Notify On Success check box if you want the user that posted the document to notified about the state of posting. There will be a notification attached to the posted document as well as a notification in the My Notifications part on the role center.
You can apply the same steps to the Purchases & Payables Setup page.
The next step is to set up a job queue to run the background posting.
In the job queue card, you create a new dedicated job queue. The Job Queue Category Filter resembles the filter set on the background posting setup. So this job queue will only run the job queue entries with the category Sales Post. The remaining fields in the General FastTab show the status of the job queue. In this case, the job has been started from the same session by the Start Job Queue action.
In the NAS Settings FastTab, it is possible to choose which NAS service the job queue must run on.
Note: The NAS Service needs to be restarted to pick up a new job queue.
Now, the application has been configured and it is possible to start posting.
Posting a sales order will create a job queue entry to be run on the dedicated job queue. This will execute the posting routine and if required, it will be sent to a printer. There is a visible difference in the posting routine, since the background posting will notify the user that the order has been sent for posting instead of showing the actual posting steps.
In the sales order list, it is possible to see the state of the posting. Showing the job queue status column on the list page will show the state of the posting.
If notification is turned on in the set up, it is very useful to add the Notes FactBox to the list page as well. This is really valuable in cases where the posting for some reason doesn’t finish.
In the case below, there is nothing to post which can be seen by the job queue status and the notification. Opening up the note shows the error message “There is nothing to post.”
It is also possible to see that there is something wrong in the posting on the role center. The My Notifications part shows any errors related to the posting. The same error can be found in the My Job Queue part.
The My Job Queue part also shows the progress of the jobs. Any job queue errors will be readable from the entry in the job queue. Failing job queue entries are displayed in red, while the job queue entries in line are displayed in black.
Recently our support service received support requests where out of the blue the E-mail logging stopped functioning. The reported error was:
This message is for C/AL programmers:
The call to member Count failed. Collaboration Data Objects returned the following message: [Collaboration Data Objects - [E_FAIL(80004005)]]
NOTE: sometimes the same error pops up E_FAIL with member Update
The failure appears to be caused by some or specific security patches for Office 2007 that were applied on the application server and / or patches that were applied to the Exchange Server.
Let’s assume the following scenario where we have Office 2007 RTM installed on the server hosting the application server. This is our starting point. The ultimate goal is to update Office 2007 with SP3 and later cumulative security patches. E-mail logging does work fine here. In his scenario, transport logging rules are running on the Exchange Server. That means, if you update the server hosting the application server with Office 2007 SP3 and cumulative updates, E-mails are still sent to the QUEUE folder. Now, after updating the Office 2007 with the latest patches, the scenario is as follows:
1. There are pre-SP3 messages in the queue; these ones will generate the E_FAIL error 2. There are post-SP3 messages in the queue; these ones will be processed by the E-mail dispatcher
The post-SP3 messages will be logged to the STORAGE folder. The pre-SP3 messages will generate an error. We have seen errors like The member Update or The member Count. They all end with E_FAIL which is something like “Access Denied”. Exchange Server is not able to handle them and we do not really know why. This is because security is more strict thus the Access Denied error message.
WORKAROUND: 1. Remove Office 2007 SP3 from the server hosting the application server 2. Install Office 2007 RTM / SP2 (whatever the original scenario was) on the server hosting the application server 3. Ensure QUEUE folder is empty before updating Office 2007 SP3 plus cumulative updates a. In this scenario, Transport Rules should be stopped which is not an ideal scenario since your end user may be a 24x7 company b. The update should be done after business hours which is not an ideal scenario as well; at least the number of pre-SP3 messages could be decreased in numbers c. Transport Rules should be reconfigured and point to a different E-mail address for a different Public Folder, then we have one pre-SP3 Public Folder and the original post-SP3 Public Folder (QUEUE) 4. Update Office 2007 SP3 plus cumulative updates 5. For any E-mail that does still come in to the QUEUE folder, these cannot be logged though you could use the suggestion below
For any pre-SP3 messages that are stuck in the QUEUE: For the ones that fail with the E_FAIL error message, move these messages from the QUEUE folder, open the message one by one, select Actions and select Resend this message. With Transport Rules enabled, the message will be sent to the QUEUE folder again where the message can be reprocessed by the E-mail dispatcher.
The above mentioned workaround should only be applied if there are hundreds of messages that are stuck in the QUEUE. If there are not that many messages stuck in the QUEUE, then resending the message may be a better idea to prevent the work to be done here.
Now we do have this blog in place, Administrators that are about to do some maintenance can be pre-warned this issue does exist when E-mail logging is configured and running in the company.
Marco Mels CSS EMEA
This posting is provided "AS IS" with no warranties, and confers no rights
Microsoft Dynamics NAV 2013 delivers compelling application functionality that helps businesses gain greater control of their business through increased visibility and insight into how the business is performing. Cost Accounting in Microsoft Dynamics NAV 2013 gives you an efficient way to control your company’s costs by providing visibility and insight into budgeted and actual costs of operations, departments, products and projects.
Today, more than ever, companies need to be able to identify budget deviations as soon as they occur and take the appropriate action. Synchronizing cost information with the general ledger and then allocating that information into different costs centers and cost objects, Cost Accounting in Microsoft Dynamics NAV 2013 provides management with an accurate and immediate overview of the company’s expenditures, including from where the costs are coming, which departments, projects and items and how much they are spending. Managers can see how the company is doing – whether the company is profitable or not – and have this analysis available at any time rather than at the end of the year. They are able to make informed decisions faster and more efficiently regarding performance, reducing and managing costs, adjusting sales prices for goods and services, or deciding to discontinue a product or project.
The Cost Accounting functionality has different possibilities to manage costs:
The Cost Accounting functionality retrieves data from the general ledger but works independently from the general ledger. As a result, transactions that are posted in Cost Accounting will not affect the data in the general ledger.
Cost Accounting in Microsoft Dynamics NAV 2013 is fully integrated with the rest of the standard application and includes other functionality such as a budget feature for cost accounting. This works like the budget feature for the general ledger. Also included is the ability to import and export cost accounting budget data to Microsoft Excel, create reports, aligning to multiple dimensions, and other enhancements.
Microsoft Dynamics NAV helps streamline routine accounting practices with automated financial processes. In Microsoft Dynamics NAV, you can set up multiple VAT rates using the VAT posting groups and general posting groups so that you can easily change VAT rates in order to maintain accurate VAT reporting. But companies often have thousands of open documents or journal lines where it would be cumbersome to manually update the VAT posting groups and general posting groups. Also, you would need to update master data such as items with the new default VAT posting groups and general posting groups due to the new VAT rate.
Microsoft Dynamics NAV 2013 delivers a new tool that makes it possible for you to define what master data needs to be updated, match old posting groups to new posting groups, and implement the changes on open documents and journal lines. This tool makes it possible for customers and partners to accommodate changes in VAT rates with a minimum amount of time and costs. The tool has previously been released as stand-alone, but it is now included in Microsoft Dynamics NAV 2013 with some improvements over the earlier version.
The VAT Rate Change Tool in Microsoft Dynamics NAV 2013 gives you a fast and efficient way to implement new and changing VAT rules yourself – without any updates or partner implementation. As a result, changes in VAT can be rolled out centrally and can be carried out quickly, so that your company can stay compliant with local VAT regulations. When the conversion is complete, VAT and general posting groups are converted, and changes are implemented in general ledger accounts, customers, vendors, open documents, journal lines, and so on.
If you are a Microsoft Certified Partner, you will no longer have to spend time rolling out changes in VAT rates, and you can use your time on higher value projects.
The VAT Rate Change Tool in Microsoft Dynamics NAV 2013 includes the following functionality: