Displaying items by tag: Reports
When developing Dynamics GP customizations in our WebSan environment, our development phase is contained within a local server configuration to ensure that all modifications to the forms and reports dictionaries are local and will not affect our hosted users. However, there are a few more items to note when separating a development from a production environment.
When enabling and granting access to modified forms and reports in GP, administrative users would navigate to the Microsoft Dynamics GP menu >> Administration >> Alternative/Modified Forms and Reports and grant access to the modified item by selecting the ID, Product, and Type (Windows: Forms or Reports), then checking the box beside the version they would like accessible:
The ID field at the top of this window is used to specify which group of users has access to the forms and reports chosen in the list underneath. Users are assigned an “Alternate Modified Forms and Reports ID” in the ‘User Security’ window:
Therefore, if you are customizing the ‘Payables Transaction Entry’ window and want to ensure that current GP users cannot view these changes until they are completed, you can add your own GP login to a new “DEV” group that has the customized form selected such that further work and testing can be performed without effecting production users. While the ID field handles what version of the form or report the user can view, any VBA code that is applied to the customization is made accessible through the “EventMode” property in the ‘Visual Basic Editor’ (Alt+F11), if the form or report has been added to VBA.
Within the VBA code, you can select the form or report that is using VBA and set the EventMode property to either “0-emOriginalOnly”, “1-emModifiedOnly”, or “2-emNever”. The first applies the VBA code to the original (un-modified) object only, while the second applies it to the modified version. The third ensures that VBA code is not run when that object is accessed:
By utilizing these two properties, it is possible to contain a customization to only a particular group of users until testing is completed, and is ready to be deployed to a production system. If development is occurring within a production environment, configuring these two settings will ensure that other users are unaffected.
It's a Friday afternoon, 3pm, going into a long weekend. You are already going to hit traffic heading up to the cottage if you leave now, however it won't be as bad as in a couple of hours. You just finished a modified SOP Blank Invoice Form and would like to import it into your client's production environment. As you click the import button in Customization Maintenance, your heart starts to race as you cross your fingers and pray that the REPORTS.DIC file is not locked by any users....and you get the "Unable to open customizations dictionary" message. The afternoon sun will have to wait until tomorrow.
When importing customized reports through Customization Maintenance, users accessing the reporting dictionary will need to log out of Dynamics GP to have the report import successfully. This can be a pain and a scheduling nightmare for users and yourself alike. "There has to be a better way!" Well, luckily, there is. If you have access to the REPORTS.DIC file found in the "Microsoft Dynamics/GP/Data" directory, then you can import the modified report straight from this dictionary file using Report Writer (this is assuming that you developed the report in a test environment and would now like to push it through to production). To do this, follow the steps below:
Step 1: Copy the REPORTS.DIC file from your test environment to a local or network shared drive that is accessible from the production server.
Step 2: Log into the production environment with 'POWERUSER' access and launch Report Writer (Microsoft Dynamics GP menu >> Tools> Customize >> Report Writer).
Step 3: Click 'Report', then click the 'Import' button:
Step 4: It will ask you to browse to the path of the REPORTS.DIC file that contains the modified report you would like to upload. Click the ellipse (...) and select the reporting dictionary that you saved from the test environment.
Step 5: All modified reports from this dictionary file will be displayed in the left menu. Select the one you would like to import and click 'Insert'. If a modified version of that report already exists in the production environment, it will ask you if you would like to overwrite the existing report. Click the 'Import' button.
Step 7: Close Report Writer and if prompted to save, click 'Yes'. If this is a newly modified report, don't forget to set the ID in Alternate/Modified Forms and Reports in the Administration module.
That's it! No more waiting for all users to log out of the system. The next challenge is sneaking by your boss's office without him noticing.
If you have ever created any customized reports from within Microsoft Dynamics GP, then you are quite aware of how reporting can be a useful tool in extracting the exact information needed to increase your productivity and make effective business decisions. While there are a number of different reporting methods that can be utilized (such as SQL Server Reporting Services (SSRS), SmartLists, and Word Templates), the most popular and basis for reporting within GP is Report Writer.
While exceptionally powerful, Report Writer is not the easiest application to operate without prior knowledge or experience. However, the following is a trick that one can use to have Report Writer work for you.
A few weeks ago, a client had requested a Purchase Order form for a new company that they had just recently added to GP. The formatting of the PO form was to resemble that of an existing company, the only difference being a change in logo in the top-left corner of the report. Should be easy. However, Report Writer will only allow two versions of the same type of report: either the original GP standard or the modified version. When a report is 'modified', it will print in place of the standard version of the report. Although there are both 'Copy' and 'Duplicate' options when choosing a report to customize in Report Writer, these secondary versions of the report will only be accessible through (Reports → Customized) within GP and cannot be printed or displayed, for example, from the 'Purchase Order Entry' window.
Luckily, the Purchase Order form has two documents types: the Blank form or Other form. This way, both companies can utilize their own type of purchase order form. However, if the client requests the POP Purchase Order Other Form to be a copy of the POP Purchase Order Blank Form, the latter having numerous formatting changes and functions created; the replication of this report can be a lengthy process. There must be a better way, right? Well, there is.
Through the (Microsoft Dynamics GP → Tools → Customize → Customization Maintenance) menu, you can export a package file of the report you wish to copy. Package files are XML formatted and contain all the modifications and fields used on the report. Due to their text-based nature, these files can be manipulated within a simple text editing program, such as Notepad, without ever having to access Report Writer itself. After exporting the POP Purchase Order Blank Form as a package file (the form we would like to replicate) we can open the file and copy all text between the Report “POP Purchase Order Blank Form” line and the closing </Component>line.
This can then be pasted into an exported POP Purchase Order Other Form package file, in the exact same location of the text document (between Report “POP Purchase Order Other Form” and the closing </Component>).
Once saved, the POP Purchase Order Other Form package file can be imported back into GP through the Customization Maintenance window. The only step remaining is to replace the logo in Report Writer with that of the new company. Simple.
When importing packages into GP, it is important that all users, other than yourself, are logged out as the package will not import correctly and problems can occur. Backing up existing packages files is always a good idea in the case that something goes awry. Access will also need to be granted to the users who wish to view or print the modified report.
Tricks like this one can save you a lot of time and stress when dealing with reporting in GP, and if a particular task appears as though it will take more work than warranted, then it is likely that there is an easier way of accomplishing it.
Happy Canada Day!
For those enjoying a little R&R today, imagine this was your every day! To get achieve that, you would need to start building some automation into your business processes. The first step you can take is by ensuring your reporting is easily & readily available.
Homepage Metrics offer out-of-the-box reporting that covers all GP modules. There exists an extensive list of potential reports one can use. These are automatically integrated to ones Homepage for easy & quick viewing.
Try adding some Metrics to your Homepage today by selecting the pencil icon / edit button on the Metrics heading.
Adam MacIntosh is a Senior Project Manager at WebSan Solutions Inc, a Microsoft Dynamics GP Partner offering implementation & support services for large & small firms in a variety of industries.
Management Reporter 2012 for Microsoft Dynamics has been released & is now available for use. Management Reporter is a robust financial reporting tool that provides companies visibility into organizational health. Management Reporter is available for the following Microsoft Dynamics products:
- AX 2009 & 2012
- GP 2010
- NAV 2009
- SL 2011
Upgrades include greater design flexibility, the ability to collaborates with co-workers, enhanced drill-down capabilities & a more interactive viewing experience.
In 1983, Quaker Oats purchased Gatorade, transforming it from a multi-million dollar business into a multi-billion dollar business. Fast forward a decade & Quaker decided that another drink manufacturer was primed for the same growth. This time, Quaker eyed a takeover of Snapple, a popular fruit beverage. After paying a large ransom for the Snapple business, Quaker did not see the returns it had forecast. The Snapple brand fizzled as Quaker resources were washed down the drain. Eventually, the Snapple brand was sold at a $1.4 billion loss to Quaker.
What went right with Gatorade that went so wrong with Snapple?
The measurement tools that Quaker used to evaluate Gatorade were both accurate & appropriate. However, in the decade that past, these same evaluation tools were not updated. Quaker applied the same methods & metrics to the Snapple brand that they did to the Gatorade brand. However, in the decade that had passed, these tools were no longer the most appropriate.
Evaluation mechanisms are constantly being updated. What works today might leave you behind tomorrow. Quaker found this out first hand. If companies want to continue to thrive, they must be constantly tweaking & evaluating the tools they use to evaluate their business.
Microsoft Dynamics GP leverages ever evolving Microsoft technologies in order to provide the best reporting options possible. Users can access reports in many different formats. Business Alerts can send automated emails to any staf member when exception criteria are met or at specific intervals. Emails can be configured to include attachments, including lists of applicable information. Users can have similar automated Reminders open on their screens when specified criteria have been met. This allows for fast & automated management by exception.
To Do lists & Check List force users to complete tasks periodically in a structured way. Meaning, no longer are workflows undocumented or dependent on a single employee. If an employee leaves the organization, those same tasks must still be completed.
Standard reporting is available in paper format as would be expected. Users can further use inquiries to research linked transactions if auditing information. The same information can be exported to Excel or Word at the click of a button through a Smartlist. These can all have filters applied to them to restrict the information that is displayed. However, where Dynamics GP takes things a step further is SRS Reporting. Smartlists can live on the server in the form of an Excel Report. These can contain, charts, graphs, lists or whatever one's heart desires. These reports are linked to the server & the Dynamics GP database through Excel, meaning information is constantly changing on them automatically as data is entered into Dynamics. And the best feature is that those who wish to view these reports do no require a Dynamics GP user license as they are not actually logging into GP. The same premise applies to SRS (or SQL Reporting Services) Reports. These are built through regular SQL statements & can include filters, etc, just like any SQL query would have. Data can be displayed in many different formats & again, do not require a license to view.
The evaluation tools offered by Microsoft are beyond comparison & constantly evolving. those utilizing Dynamics GP are always in the know & won't be left behind.
If you're a business owner and frequent user of Dynamics GP, you might think, "I have a lot of data in my Dynamics GP system. I need to analyze my data, extract a new knowledge from it and make decisions based on this analysis. I want to see the KPI of my business in real time mode to drive my business better. I want to have a tool which will help me see hidden trends in my business and industry...". If you already have these thoughts, there's good news - the Business Intelligence technologies (BI) will help you.
Basically, you need to have three things:
- The data warehouse. It is a special data base which has a star-schema data model (info cubes). Also, this database is in read-only mode for users and it is optimized for reporting and multidimensional analysis.
- Extraction, loading and transformation process (ELT) which loads your data from OLTP system like Dynamics GP to your data warehouse. Generally speaking this ELT process "cleans", transforms and loads your daily operation data from Dynamics GP into your data warehouse.
- Tools to create analytical reports and KPIs based on data from your data warehouse.
There are alot of BI tools which can help you to create a whole process of these 1-2-3 steps. However, if you have ERP based on MS SQL Server (i.e. Dynamics GP - you have a simple way of studying the system and pretty powerful BI tool right out from the box! It is SQL Server Reporting Service (SSRS) which actually is the part of MS SQL Server installation.
So, let's see what SSRS can provide you:
- Simple access to your Dynamics GP database and use it as a data warehouse, you just need to write a bunch of SQL-statements and SSRS will transform on the fly the results of these queries to BI reports
- Loading and analyzing data from MS Analysis Services which is more powerful solution to build data warehouse from Microsoft
- Special tool for building in drag-n-drop mode you BI reports - Report Builder 2.0 It is free and can be downloaded from Microsoft site
- Creating beautiful graphic dash-boards with KPI's in Report Builder 2.0
- Different filters for your BI reports
- Access to your reports, dash-boards and KPIs via Web, you can see all your BI stuff just in your internet browser
- Generating your BI reports in off-line mode and sending them via e-mail on a schedule. (Do you want to see some daily reports every morning in your inbox? No problem, SSRS will do it for you. Just create your reports in Report Builder, then publish and schedule them)
- Integration with your MS Share Point portal, yes you will be able to build reports and analyze data right in your portal
- Built in export of reports to Word, Excel, PDF and TIFF-format
MS SQL Server Reporting is very simple for installation and studying. You could get BI tool and start analyze your data in Dynamics GP in two or three days. Simple, powerful and ready for use right out from the box.
Do you often find yourself canceling reports that automatically print after you have posted a document? Or even changing the default destination of these reports? Well, one can set the default destination of these reports or even stop them from printing all together.
Try entering the Posting Setup screen (Microsfot Dynamics GP > Tools > Setup > Posting > Posting). In this screen, you can select whether or not a specific report will print by selecting or de-selecting the checkbox in the Print column. Also, one can specify destination for a report by making the appropriate selection in the Send To column.
See the attached screenshot below & save yourself all that extra clicking when you next post a transaction.
FRx is the standard financial reporting tool utilized by most Microsoft Dynamics GP users for financial reporting. FRx links directly into the general ledger to allow the creation of endless types of reports. These report are then viewed in the FRx DrillDown Viewer. Users I have spoken to in the past have expressed that they are more comfortable viewing reports in Excel.
To take your FRx financial report & export to excel:
1 - Generate the report
2 - In the DrillDown Viewer, select File > Export > Worksheet File > Formatted Excel
3 - Save the report to its desired location
This quick & easy trick should save you time & energy in attempting to convert your reports.
Home sweet home! It’s a beautiful place. Its where we feel most comfortable & spend most of our time. The same can (& should!) be said about your ERP. The homepage in Dynamics GP has access to everything you need in the click of a button.
Managers are always interested in quick easy access to reports. The Dynamics GP homepage offers various metrics to the user the second they log in. Click on the pencil icon to the right of the word Metrics on your desktop to modify what metrics will display. Although there are dozens to choose from, my fav five are:
- Top 5 Customers by YTD Sales
- Top 10 Items by Sales Amount for the Past 30 Days
- Receivables Aging
- Payables Aging
- Gross Profit for the Past 12 Months
Scroll through your metrics using the < & > keys for instant access to the reports you desire.
Try these other tips to help make your homepage a one-stop shop for all your GP needs:
Right click on the navy blue banner at the top of the screen to create graphical shortcuts
- Click on the pencil icon to the right of the Quick Links heading for one-click access to your favourite windows
- Click the ‘Customize this page …’ button to change layout of your homepage
- Select the following icon to add or remove windows from your desktop & even configure shortcuts:
Dynamics GP Homepage