A week ago, the Microsoft Dynamics NAV team celebrated the launch of version 2009, a product that involved over three years of work. We had a party on the MDCC campus (which subsequently ended up in an Irish bar somewhere in Copenhagen) and spent some time talking about our accomplishments. The energy in the room was infectious. We signed the ship poster, a tradition at Microsoft, previewed the Microsoft Dynamics NAV demo for Kirill's keynote at Convergence EMEA, and announced that we'd take a team day off – these all helped people savor the moment. Though we hit many bumps along the way during those three years, we finished with what it is nothing short of a landmark release. So, if you're an NAV fan and it's Friday night for you, toast to this release, because it's an event to remember!
In this blog post, I'd like to tell the story of Microsoft Dynamics NAV 2009. Instead of resorting to sanitized marketing sound bites, this will be a more personal account. It's my own particular point of view exploring this product and how it relates to our customers and partners.
There are a number of things that I simply love about this release, but I have to start with the UX (or user design). When I installed NAV 2009 when I first joined the team back in February, I knew the user experience was special. Then, in my last blog post, I admitted that I had a crush on it. Now, I must say that it's brilliant. To understand why, I have to make a brief detour.
At the heart of a good business solution is the resulting productivity of its users. How much time does the system save? How much higher is the quality of their work? Because we are engineers, we at MBS needed a business productivity framework to understand how to improve it with our products. Together with a research firm, we concluded that end user productivity had to take into consideration familiarity, ease-of-use, business insight, collaboration, and flexibility in addition to transactional efficiency (what we typically think about when we think about productivity). As I started to learn more and more about the Dynamics NAV 2009's user experience, I realized just how well it captured all these characteristics. It is familiar – Role Centers make users feel at home with the data and work they see upon entering the product; it is easy to use - Activities and the Action Pane make it very apparent how things are done and what needs to be done; it provides insight through integrated reports and data that are showcased in the context of actions to be taken; it makes collaboration easy through notifications and by working well with Office; it is flexible - personalizations permeate the experience and are very simple to make; and, finally, it is efficient - keyboard shortcuts and data positioned well with actions make performing tasks fast.
But this isn't brilliant. This isn't a work of art. This is just good engineering. What makes the user experience brilliant is that users fall in love with it. I recall an email thread I had with Jakob Nielsen, our Director of UX, sometime this past summer. In a larger forum, he was talking about getting our customers to have an emotional connection to our user experiences. I thought, "What's he talking about?" I joked, "What's he smoking?" But over time, it sunk in. And then last week, I saw a video from a customer that had just recently gone live with Microsoft Dynamics NAV 2009 as a part of our early technical adoption program (TAP). In the footage, a warehouse manager showed off his Role Center and asked the Director of Operations for the company, "Can I keep it?" Here was a person who was emotionally attached.
People who love the tools they work with end up loving their work more. People who love their work more end up being more successful. They make fewer mistakes, they treat each other better, and they get to work on time. If we build a Microsoft Dynamics NAV user experience that users love, it's a win-win-win situation. The customer wins. The customer's employee wins. And we at Microsoft win. That's why this user experience is brilliant.
Of course, Microsoft Dynamics NAV 2009 has a lot more to it under the hoods. If you're an engineer, you can sense when a software product is getting "brittle." Just like a bridge that has had too much stress over time and suffers metal fatigue, so too does software age if it isn't refactored over time. Entropy creeps in as code is added that doesn't adhere to the original design principles or doesn't meet the original quality bar (assuming this existed). For 2009 we've made a conscious effort to modernize the core architecture. For instance, in addition to giving the user experience a new look and feel, we've also built it from scratch on .NET. It has a modern, modular architecture that will help us introduce new innovations in the future quickly and without compromising quality, innovations such as a new "channel" like Sharepoint or new visualization controls that are based on the dynamic capabilities of Silverlight or WPF. We've also made the architecture 3-tier from a 2-tier fat client. In addition to improving security, such as running the business logic from one, trusted place, this change gives partners the ability to do application-to-application integration much more easily with web services. With a click of a button in C/SIDE, a new web service is exposed for consumption.
On the server tier, we've taken advantage of .NET once again. C/AL business logic written in C/SIDE now compiles down to MSIL via a C# transformation. Consequently, we leverage the innovations that the .NET runtime team has worked on for the past eight years. This frees our team up to focus on problems more closely related to ERP. As I've already mentioned for the client and the server, we've taken advantage of Microsoft's world class platform stack, particularly with .NET. But we've also done this with SQL reporting services, which are now our tool of choice for charts and table reports that can be viewed in context in the user experience. In summary, these architectural investments have a tremendous impact on this release and future releases. Good, modern architecture gets the best technology in your hands over time and maintains high quality. It's true that this release has largely been about technology. That was intentional. We made a commitment to modernize the product, and from our perspective, we're probably two-thirds done. We’ve finished the hard parts and the ones that impact partners and customers the most: the user experience, reporting, and the tiered architecture. We recognize that a fine balance must be made between innovation on the one hand and maintaining partners' and customers' investments on the other hand. There is no perfect formula here. Sometimes there are innovations that break changes that our customers and partners have made. In these cases, you have a couple of strategies to minimize the pain - you make the innovations "opt-in" and you create tools and provide support for completing the transformation. In Microsoft Dynamics NAV 2009, we've done both.
We allow our customers to run the "Classic" client and the RoleTailored client concurrently. This gives customers flexibility to move their users to the RTC over time. In addition, we've made sure that "Classic" reports, the most common customization that customers create, run in the RoleTailored client, so they do not have to be converted to SQL RS reports if the customer doesn't care to take advantage of RS's capabilities. As I mentioned before, the business logic in Microsoft Dynamics NAV 2009 is converted from C/AL to MSIL via C#. Though the runtime has changed, the actual code itself has not. This means that business logic changed or added by partners and customers is protected and migrates to the new version as-is. Finally, for partners, we've maintained the C/SIDE development environment, extended it with a page designer, and provided tools for converting "Classic" forms and reports to pages and RS reports, respectively.
Perhaps the thing I'm most proud about in this release is that we’ve substantially improved its quality. We're upping the bar considerably in a number of ways. The clearest indication is with the TAP and the Beta Access Program (BAP). As a result of these programs, we have 9 customers already running Microsoft Dynamics NAV 2009 in production, and over 80 different partners, including ISVs and implementers, trained and familiar with the new version. My favorite statistics are these - we have over 1 year of server uptime without a crash reported and over 135 user-months on the RoleTailored user experience. These should give you an indication of the stability and the attention to quality that we've paid on the release. There are a number of other quality initiatives and investments that we've made, including improving our stress test environment, improving our code coverage and automation, and testing on 100 configurations of the operating system, database, and other system elements. We think you're getting the highest quality NAV product yet.
As you can see, for me the story of Microsoft Dynamics NAV 2009 is one of tremendous success. The value proposition of end user productivity is core to our vision and very compelling to our customers. The investments in modernization of the architecture set the stage for innovation and product improvements in the future. The attention to partner and customer investments has made this release the best in terms of readiness. And the quality achievements have set the bar higher than they've ever been. While this is not a perfect release - it will require partners to learn new concepts like RoleTailored computing and new tools like the Reporting Services designer, it is one that I believe takes Microsoft Dynamics NAV, our partners, and our customers into a new, modern era of ERP! I hope that you're checking it out!
- Dan
As you may have heard, last week the MBS team announced that Darren Laybourn will be taking a new position as General Manager of the Outlook Mobile team at Microsoft. We also announced that I would become the new General Manager for Dynamics NAV and Mobility and continue to report to Hal Howard, who will now run all of MBS ERP R & D. As a part of my new role, my family and I will relocate from Seattle to Copenhagen, where I will work at the Microsoft Development Center Copenhagen (MDCC). In this blog post, I want to introduce myself and invite your questions or comments about these changes.
Before I do that, however, I want to thank Darren for his contribution to MBS, Dynamics NAV, and Dynamics Mobility. Darren is a true veteran of this business, having been with the combination of Great Plains Software and Microsoft Business Solutions for over 15 years. He's had an incredible, positive impact on our customers, and he's been a mentor for me through most of my career at Microsoft.
Let me start my introduction with a very brief bio. I've been with Microsoft for about six years, working on the Microsoft Business Framework, Project Fenway, and Dynamics AX. Before joining Microsoft in 2002, I ran the R & D group for a Silicon Valley start-up called Bistro that built workflow-based business applications (including financials management) for small-to-medium sized businesses. The rest of my career has been in IT consulting for large companies such as Hewlett-Packard, Charles Schwab, Ryder, Diners Club, and American Express. In short, I've worked on business applications my entire career and am no stranger to metadata, journal posting, and complex business value chains, on which our customers' success is predicated.
Nonetheless, I'm new to NAV and have a lot of learning to do. But one of the first things that I've learned is just how passionate all the stakeholders of this product are, whether they are customers, partners, or employees. It's people like you, the readers of this blog, that are making this product and the customers that use it, a success. Thank you for your support, and I hope I get a chance to meet you, work with you, and learn about the things you love, don't love, or wish you could love about NAV.
In the meantime, the NAV organization will continue moving forward according to our current roadmap, including NAV 5.0 SP1 this March and NAV 6.0 at the end of the calendar year. Our priorities haven't changed, and our commitment to the NAV product is as strong as ever. I’m excited by the future that lies ahead for NAV customers, NAV partners, and the NAV team itself!
I look forward to hearing your questions and comments about the changes.
Sincerely,
- Dan Brown
Earlier this year, Michelle, my colleague on the Microsoft Dynamics NAV documentation team, wrote a blog post about improvements that we have made to the content for Microsoft Dynamics NAV 2009. In addition to improving the product documentation, we are also working to improve how partners can customize and extend the Help.
In April, we published all of the Microsoft Dynamics NAV 5.0 platform documentation to the Microsoft.com Download Center. And now, we are releasing all of the source files for Microsoft Dynamics NAV 5.0 application Help on PartnerSource (log-in required). When Microsoft Dynamics NAV 2009 is released, we’ll also provide the new application Help source files on the same schedule as the localized releases.
Providing our partners with the documentation source files is only a first step toward improving Help customization. Feedback that we have heard is that the Online Help Guide (available on PartnerSource) is daunting. For Microsoft Dynamics NAV 2009, we will be replacing this guide with a streamlined version focused on what you need to do to customize and build documentation for your Microsoft Dynamics NAV solution or add-on.
We are also updating GATE for Microsoft Dynamics NAV 2009. While the functionality will be very similar to what you are using today, we’ll be making GATE more robust and easier to use by rewriting it as a .NET application.
Given the amount of time that we have left in this product cycle, these tactical changes are what we can provide in the Microsoft Dynamics NAV 2009 timeframe. You can continue to use RoboHelp (or your favorite HTML or Help authoring tool) to create Help. Over the coming months we will be developing our post-Microsoft Dynamics NAV 2009 strategy for improving Help customization. Based on what we heard at Convergence in March and in one-on-one discussions with several partners, we will be focusing on the following themes:
We’d like to continue to hear what your needs are, so we can ensure that we are improving the process for customizing and extending Help. You can add your comments below or e-mail us at navhelp@microsoft.com. Here are some questions to get you started:
It’s important for us to ensure that we are providing an efficient way for you to customize the documentation. We’re looking for volunteers to test our new Help processes, and provide us with feedback on how well these solutions meet your needs. In return, we’ll offer extra assistance in using these processes. If you would like to participate, please e-mail navhelp@microsoft.com.
- Paul Chapman
Recently I was asked about the encoding that it is done for record links in SQL.
If you are not familiar with Record Links, which have been available since NAV version 5.0, just open any Card or List and then click Edit -> Links (Ctrl + L).
“Record Links enable users to add links to documents to any record in Microsoft Dynamics NAV, such as a sales order or purchase order. The document or order can be stored in Microsoft SharePoint or on a file server and the user can access the document from Microsoft SharePoint or give access to others.”
In SQL, these Record Links are saved under the “Record Link” table, and contains (among others) the Record ID, URLs, Description, etc.
Basically, the Record ID should be able to uniquely identify to which record this link belongs, so…. Not surprisingly, this Record ID will contain the table number and corresponding key values (+ 2 null bytes).
In principle, what I have just written should be enough to understand the format, but I will further explain with the help of some examples.
Let’s start with Annette Hill, one of the employees that work at CRONUS International:
Annette’s Record ID on the Record Link table (in SQL) will look as “0x501400000089FF4148000000”. As I wrote before, this should correspond to the table number, the keys and 2 null bytes… That is:
The first 4 bytes correspond to the table number, in this case 0x50 14 00 00, which should correspond to table 5200.
Now, you must be wondering, how comes 5200 can be converted to such a strange binary representation. Well, we will have to remember two things: 1) The number needs to be in hexadecimal format, and 2) It needs to be encoded in little endian as we are working with Intel processors (just use your search engine, if you don’t know/remember).
So, we need 4 bytes for (table) 5200, that in hex is: 00 00 14 50, if we convert it to little endian, we will get: 50 14 00 00.
The next 2 bytes correspond to the data type of the key. The type is CODE, which is type 137, which is only converted to hex: 00 89. At this point you must be thinking that I am pulling your leg, as this is supposed to be further converted using little endian, but it turns out that the keys data types are encoded in their binary representation, in order to reuse code when searching for them.
The following 4 bytes correspond to the key itself. Interestingly enough, code data types will start with 1 byte that can tell you the length of the code (if numeric) or FF which would correspond to a character based code type. In this case, we have 0xFF, meaning that we have characters and those are 0x41 and 0x48 + null termination 0x00. Of course 0x41 corresponds to ‘A’ and 0x48 corresponds to ‘H’ (in “extended” ASCII), which is the key, as can be seen on the ‘No.’ field.
The last 2 bytes will always be null characters.
Let’s do something a little bit more interesting, now with Bill of Materials:
This link will look as: 0x5A0000000089FF313932342D57000087102700000000
The first 4 bytes correspond to the table number, in this case 0x5A 00 00 00, which should correspond to table 90.
The next 2 bytes correspond to the data type of the first key. The type is CODE, which is type 137, which is only converted to hex: 00 89.
The following 8 bytes correspond to the key itself. In this case, we have 0xFF, meaning that we have characters and those are ‘1924-W’ (+ null).
The next 2 bytes correspond to the data type of the next key. The type is Integer, which is 135, that is converted to hex: 00 87.
The following 4 bytes correspond to the key itself. In this case, we have 0x10270000, converting this to an integer, corresponds to 10000 (using the little endian rule).
If you are wondering where these values came from, Open Table 90, and verify that the Key is in fact ‘Parent Item No.,Line No.’ and that the values that we have decoded, correspond to the data we have added the links to.
Of course, you should always remember that this format is subject to change and there is no guarantee that it will be preserved on future versions, also remember the standard following claim.
-- jtorres
The email notifications that are sent between users according to the document approvals setup are based on email templates defining which fields and text to show in the notification.
The email template is an HTML file that you can export from Microsoft Dynamics NAV, edit in Word, for example, and then import back to the program where it then defines the content of approval notifications sent between users in the database.
The following default notification email template is shipped with the standard product:
The parameters represent the following variables when used for sales approval notifications:
The parameters represent the following variables when used for purchase approval notifications:
A sales approval notification based on the default template looks approximately as follows:
Note: The program automatically inserts variables in the notification according to the approval action and document type that it notifies about.
If the default mail template for approval notifications does not fit your needs, you can export, edit, and import it as described in the following procedure.
To Set Up a Mail Template for Document Approval Notifications
Begin by exporting the default mail template to a folder on your computer.
1. From the navigation pane, click Administration, and then click Application Setup
2. Click Document Approval, and then click Approval Setup.
3. In the Approval Setup window, click Mail Templates, point to Approval Mail Template, and then click Export.
4. Give the HTML file a name, such as “notification mail.htm”, and save it in any folder.
Proceed to edit the template to define which fields to include in approval notifications in your company.
1. Locate the exported template file.
2. Right-click on the file, point to Open with, and then click Word (or Note Pad).
3. Edit the template by adding, changing, or removing variables to define the notification content you want.
4. Save and close the HTML file.
Finish by importing the changed template.
3. In the Approval Setup window, click Mail Templates, point to Approval Mail Template, and then click Import.
4. Select the HTML file you edited in the previous steps, and then click OK.
5. Click Yes to overwrite the existing mail template in the database.
Overdue Mail Template
To define the content of reminder emails to users concerning overdue approval actions, follow the same procedure as for notification mails, but begin by clicking Mail Templates, and then point to Overdue Mail Template in the Approvals Setup window.
- Soren Groes-Petersen
This post describes the simplest possible way to transform a form into a page in NAV 2009. Both Pages and Transformation is documented in more details elsewhere. Here, I just want to make the simples possible example, just to get a simple form transformed into a simple page.
Prerequisites:
You need to have at least a NAV 2009 classic client and the Transformation tool. To test the page, of course you also need the Rolebased client. But to just transform a form, you don't need it.
Import the tool:
1) Open a classic client, go to Object Designer and import the file TIF.fob from the folder TransformationTool\TIF Editor\2) Create a new form in NAV, as simple as possible. For example a Card form based on the customer table, with just a few fields on it. This is the form you want to transform.
Setup and use the Transformation tool:
3) Run form 177000 "Transformation Forms"
4) Click Import -> "Import PageType - FormType mapping", and select the file TransformationTool\TIF Editor\FormToPagetypeMapping.txt.
5) Click Functions -> Get Forms. Filter on Type = Form, and ID = the new form, then click OK. This gets the form(s) that you want to transform.6) In the FormType field, select "Card" ("Form with TabControl only and a source table").
Run the transformation:
7) Export the settings from form 177000: Click on Export -> "Transform Pages". You must export it to the TransformationTool folder, and it must be called TransformPages.xml.8) From Object Designer, export the form (Tools -> Export) as xml.You must export it to the folder TransformationTool, and it must be called Forms.xml.
You now have the form you want to transform, and the meta-data that tells the TranformationTool how to transform it.
9) Run the file FormTransformation.exe from the TransformationTool folder. This generates a new file called Pages.xml. It also logs any progress or errors in the file Transformation.log.
Import the new page:
10) Back in NAV (classic client): Go to Object Designer, and import the file Pages.xml, and then compile it.
If all steps ran without any errors, you have now transformed your form into a page which can be displayed in the rolebased client.
Lars Lohndorf-Larsen (Lohndorf )
Microsoft Dynamics UK
Microsoft Dynamics NAV 2009 has a new graphical object type called Charts or KPIs (Key Performance indicators). It is a way to create simple graphical charts, to give a visual overview of key figures. This post describes what you can and cannot do with charts, and it contains some sample code to how you might create a "Chart Generator Tool" to make it easier to generate new charts.
A chart has a simple layout. You choose the table to base it on, then a field from that table to show along the X-Axis, and - if needed - a filter on this table. Then you select what data from the table to show on the Y-Axis (also called Measure). A measure can be based on Sum or Count. You can display multiple measures in the same chart.
This is what your charts could look like:
Charts can only be displayed in the new NAV 2009 client. To display a chart, follow these steps:
You can personalize the page again to remove a chart, or click "Restore Defaults" to remove any personalization. You can also remove any personalization (including any charts that a user has added) with the classic client, from table 2000000075 "User Metadata". This is the table that stores the personalization for each page for each user. Deleting a record from this table removes any personalization made to that page by that user.
Charts are stored in system table 2000000078 "Chart". You can view them with form 9182 Charts, or from Administration -> Application Setup -> Role Tailored Client -> Charts.
Charts are defined by xml documents which are stored as BLOB fields. You can export a chart to an xml document, then modify the xml document, and import it back as a new chart.
This is an example of a chart definition:
Modifying xml documents can be tedious. But it can be made easier with tools. Below, I have put some sample NAV code, how such a tool could be made.
Note !!!! === The code below is only supplied as an example of how such a tool could be made. It is completely un-supported and to be used at your own risk and responsibility.
====
The code below is provided in Text format. When importing text objects into NAV directly, you will not get any warnings whether to overwrite existing objects. So each object has been deliberately changed by adding [RemoveMe] in front of each Object ID. This would have to be removed, after reviewing each Object type and number, to check that it would not overwrite any existing objects!
This is how the tool would work:
the tool contains the following objects:
Type ID Name 1 72000 Chart Generator 1 72001 Chart Generator Filter 1 72003 Chart Generator YAxis 2 72000 Chart Generator List 2 72001 Chart Filters 2 72003 YAxis List 2 72004 Chart Generator Card 5 72000 Chart Generator Mgt
Charts can be generated from form 72004 "Chart Generator Card":
Hit F3 to create a new chart. Enter ID and a Name. As a minimum you must specify Title, "Table ID", "X-Axis Field Name", and one or more Y-Axis fields. Once you have specified the chart definition, then click on Chart -> "Generate Chart". This will automatically either update an existing chart, or generate a new chart in table 2000000078, ready to be used from the new client.
The following example shows how to create a chart to show Inventory for certain items:
Now the definition of the chart is done. To make it available in the new client, click chart -> Generate Chart (F11). If you want to change the chart definition, then just make the necessary changes from the "Chart Generator Card", then hit F11 again to update the existing chart.
Now the chart will be available for the new client as described above. If you update the chart, then the new client will use the new chart definition next time you restart it.
These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.
Lars Lohndorf-Larsen (Lohndorf)Microsoft Dynamics UKMicrosoft Customer Service and Support (CSS) EMEA
=== sample code only ===
===
Updated 19/10 2008
Updated version of the tool on this post:
3D charts. Chart Generator tool II
This version of the tool also has Z-axis for 3D charts
OBJECT Table [RemoveMe]72000 Chart Generator
{
OBJECT-PROPERTIES
Date=19/08/08;
Time=09:13:04;
Modified=Yes;
Version List=CGT;
}
PROPERTIES
OnInsert=BEGIN
IF Chart.GET(Company,ID) THEN
ERROR('Chart already exists in table 2000000078. Run form 9182 and delete it, or use a different ID.');
END;
LookupFormID=Form72000;
FIELDS
{ 1 ; ;Company ;Text30 }
{ 2 ; ;ID ;Code20 ;NotBlank=Yes }
{ 10 ; ;Name ;Text50 ;OnValidate=BEGIN
Title := Name;
{ 11 ; ;Title ;Text50 }
{ 15 ; ;Type ;Option ;OptionString=Column,Point }
{ 16 ; ;Table ID ;Integer ;OnValidate=BEGIN
MODIFY;
{ 17 ; ;Table Name ;Text30 ;FieldClass=FlowField;
CalcFormula=Lookup(Object.Name WHERE (Type=CONST(Table),
ID=FIELD(Table ID)));
Editable=No }
{ 20 ; ;XAxis Field ID ;Integer }
{ 21 ; ;XAxis Field Name ;Text80 ;OnValidate=BEGIN
IF "XAxis Field Name" <> '' THEN BEGIN
FieldRec.SETRANGE(TableNo,"Table ID");
FieldRec.SETRANGE(FieldName,"XAxis Field Name");
FieldRec.FINDFIRST;
"XAxis Field ID" := FieldRec."No.";
"XAxis Field Name" := FieldRec.FieldName;
"XAxis Field Caption" := FieldRec."Field Caption";
"XAxis title" := FieldRec."Field Caption";
END ELSE BEGIN
"XAxis Field ID" := 0;
"XAxis Field Name" := '';
"XAxis Field Caption" := '';
"XAxis title" := '';
{ 22 ; ;XAxis Field Caption ;Text80 }
{ 23 ; ;XAxis title ;Text80 }
{ 24 ; ;Show Title ;Boolean ;InitValue=Yes }
{ 30 ; ;YAxis fields ;Integer ;FieldClass=FlowField;
CalcFormula=Count("Chart Generator YAxis" WHERE (Company=FIELD(Company),
ID=FIELD(ID)));
KEYS
{ ;Company,ID ;Clustered=Yes }
FIELDGROUPS
CODE
VAR
Chart@1000 : Record 2000000078;
FieldRec@1102601000 : Record 2000000041;
BEGIN
END.
OBJECT Table [RemoveMe]72001 Chart Generator Filter
Time=16:44:37;
TESTFIELD("Filter Field ID");
{ 2 ; ;ID ;Code20 }
{ 3 ; ;Line No. ;Integer }
{ 10 ; ;Filter Field ID ;Integer ;OnValidate=BEGIN
IF "Filter Field ID" <> 0 THEN BEGIN
ChartGen.GET(Company,ID);
ChartGen.TESTFIELD("Table ID");
FieldRec.GET(ChartGen."Table ID","Filter Field ID");
"Filter Field Name" := FieldRec.FieldName;
END ELSE
"Filter Field Name" := '';
{ 11 ; ;Filter Field Name ;Text30 }
{ 15 ; ;Filter Value ;Text30 }
{ ;Company,ID,Line No. ;Clustered=Yes }
ChartGen@1102601001 : Record 72000;
OBJECT Table [RemoveMe]72003 Chart Generator YAxis
Time=07:26:24;
TESTFIELD("YAxis Measure Field ID");
LookupFormID=Form72003;
DrillDownFormID=Form72003;
{ 10 ; ;YAxis Measure Field ID;Integer }
{ 11 ; ;YAxis Measure Field Caption;Text30 }
{ 12 ; ;Mearure Operator ;Option ;OptionString=Sum,Count }
{ 20 ; ;Show Title ;Boolean }
OBJECT Form [RemoveMe]72000 Chart Generator List
Date=12/08/08;
Time=06:50:19;
Width=16500;
Height=6710;
TableBoxID=1;
SourceTable=Table72000;
CONTROLS
{ 1 ;TableBox ;220 ;220 ;16060;5500 ;HorzGlue=Both;
VertGlue=Both }
{ 2 ;TextBox ;0 ;0 ;4400 ;0 ;HorzGlue=Both;
Visible=No;
ParentControl=1;
InColumn=Yes;
SourceExpr=Company }
{ 3 ;Label ;0 ;0 ;0 ;0 ;ParentControl=2;
InColumnHeading=Yes }
{ 4 ;TextBox ;0 ;0 ;1700 ;0 ;ParentControl=1;
SourceExpr=ID }
{ 5 ;Label ;0 ;0 ;0 ;0 ;ParentControl=4;
{ 6 ;TextBox ;0 ;0 ;4400 ;0 ;ParentControl=1;
SourceExpr=Name }
{ 7 ;Label ;0 ;0 ;0 ;0 ;ParentControl=6;
{ 8 ;TextBox ;0 ;0 ;4400 ;0 ;ParentControl=1;
SourceExpr=Title }
{ 9 ;Label ;0 ;0 ;0 ;0 ;ParentControl=8;
{ 10 ;TextBox ;0 ;0 ;550 ;0 ;ParentControl=1;
SourceExpr=Type }
{ 11 ;Label ;0 ;0 ;0 ;0 ;ParentControl=10;
{ 12 ;TextBox ;0 ;0 ;1700 ;0 ;ParentControl=1;
SourceExpr="Table ID" }
{ 13 ;Label ;0 ;0 ;0 ;0 ;ParentControl=12;
{ 14 ;CommandButton;4400 ;5940 ;2200 ;550 ;HorzGlue=Right;
VertGlue=Bottom;
Default=Yes;
PushAction=LookupOK;
InvalidActionAppearance=Hide }
{ 15 ;CommandButton;6820 ;5940 ;2200 ;550 ;HorzGlue=Right;
Cancel=Yes;
PushAction=LookupCancel;
{ 16 ;CommandButton;14080;5940 ;2200 ;550 ;HorzGlue=Right;
PushAction=FormHelp }
{ 17 ;MenuButton ;9240 ;5940 ;2200 ;550 ;HorzGlue=Right;
CaptionML=ENU=Chart;
Menu=MENUITEMS
{ ID=18;
PushAction=RunObject;
CaptionML=ENU=Filters;
RunObject=Form 72001;
RunFormLink=Company=FIELD(Company),
ID=FIELD(ID) }
{ ID=20;
CaptionML=ENU=XAxis;
RunObject=Form 72002;
RunFormLink=Field1=FIELD(Company),
Field2=FIELD(ID) }
{ ID=21;
CaptionML=ENU=YAxis;
RunObject=Form 72003;
{ 19 ;MenuButton ;11660;5940 ;2200 ;550 ;HorzGlue=Right;
CaptionML=ENU=F&unctions;
{ ID=22;
ShortCutKey=F11;
CaptionML=ENU=Generate Chart;
RunObject=Codeunit 72000 }
OBJECT Form [RemoveMe]72001 Chart Filters
Time=09:21:22;
Width=12150;
SourceTable=Table72001;
AutoSplitKey=Yes;
DelayedInsert=Yes;
{ 1 ;TableBox ;220 ;220 ;11710;5500 ;HorzGlue=Both;
{ 2 ;TextBox ;0 ;0 ;1700 ;0 ;ParentControl=1;
SourceExpr="Filter Field ID";
OnLookup=BEGIN
FieldRec.SETRANGE(TableNo,ChartGen."Table ID");
IF FORM.RUNMODAL(FORM::"Field List",FieldRec) = ACTION::LookupOK THEN
VALIDATE("Filter Field ID",FieldRec."No.");
{ 4 ;TextBox ;0 ;0 ;4400 ;0 ;HorzGlue=Both;
SourceExpr="Filter Field Name" }
SourceExpr="Filter Value" }
{ 8 ;CommandButton;4890 ;5940 ;2200 ;550 ;HorzGlue=Right;
{ 9 ;CommandButton;7310 ;5940 ;2200 ;550 ;HorzGlue=Right;
{ 10 ;CommandButton;9730 ;5940 ;2200 ;550 ;HorzGlue=Right;
ChartGen@1000 : Record 72000;
FieldRec@1001 : Record 2000000041;
OBJECT Form [RemoveMe]72003 YAxis List
Time=07:31:05;
Width=10000;
SourceTable=Table72003;
{ 1 ;TableBox ;220 ;220 ;9560 ;5500 ;HorzGlue=Both;
SourceExpr="YAxis Measure Field ID";
IF FORM.RUNMODAL(FORM::"Field List",FieldRec) = ACTION::LookupOK THEN BEGIN
"YAxis Measure Field ID" := FieldRec."No.";
"YAxis Measure Field Caption" := FieldRec."Field Caption";
SourceExpr="YAxis Measure Field Caption" }
{ 6 ;TextBox ;0 ;0 ;550 ;0 ;ParentControl=1;
SourceExpr="Mearure Operator" }
{ 8 ;CheckBox ;0 ;0 ;1700 ;0 ;ParentControl=1;
ShowCaption=No;
SourceExpr="Show Title" }
{ 10 ;CommandButton;2740 ;5940 ;2200 ;550 ;HorzGlue=Right;
{ 11 ;CommandButton;5160 ;5940 ;2200 ;550 ;HorzGlue=Right;
{ 12 ;CommandButton;7580 ;5940 ;2200 ;550 ;HorzGlue=Right;
OBJECT Form [RemoveMe]72004 Chart Generator Card
Time=08:17:28;
Width=16170;
Height=6490;
{ 1 ;TabControl ;220 ;220 ;15730;5280 ;HorzGlue=Both;
VertGlue=Both;
PageNamesML=ENU=General }
{ 4 ;TextBox ;3850 ;990 ;2750 ;440 ;ParentControl=1;
InPage=0;
{ 5 ;Label ;440 ;990 ;3300 ;440 ;ParentControl=4 }
{ 6 ;TextBox ;3850 ;1650 ;5500 ;440 ;ParentControl=1;
NextControl=12;
{ 7 ;Label ;440 ;1650 ;3300 ;440 ;ParentControl=6 }
{ 8 ;TextBox ;3850 ;2200 ;5500 ;440 ;ParentControl=1;
{ 9 ;Label ;440 ;2200 ;3300 ;440 ;ParentControl=8 }
{ 10 ;TextBox ;3850 ;3520 ;2750 ;440 ;ParentControl=1;
{ 11 ;Label ;440 ;3520 ;3300 ;440 ;ParentControl=10 }
{ 12 ;TextBox ;3850 ;2860 ;1700 ;440 ;ParentControl=1;
SourceExpr="Table ID";
Object.SETRANGE(Type,Object.Type::Table);
IF FORM.RUNMODAL(FORM::Objects,Object) = ACTION::LookupOK THEN
"Table ID" := Object.ID;
OnAfterValidate=BEGIN
CALCFIELDS("Table Name");
{ 13 ;Label ;440 ;2860 ;3300 ;440 ;ParentControl=12 }
{ 21 ;TextBox ;12980;990 ;2750 ;440 ;ParentControl=1;
NextControl=1102601000;
SourceExpr="XAxis Field Name";
TESTFIELD("Table ID");
VALIDATE("XAxis Field Name",FieldRec.FieldName);
{ 22 ;Label ;9570 ;990 ;3300 ;440 ;ParentControl=21 }
{ 20 ;TextBox ;12980;1650 ;2750 ;440 ;ParentControl=1;
SourceExpr="XAxis Field Caption" }
{ 23 ;Label ;9570 ;1650 ;3300 ;440 ;ParentControl=20 }
{ 24 ;CheckBox ;12980;2750 ;440 ;440 ;ParentControl=1;
{ 25 ;Label ;9570 ;2750 ;3300 ;440 ;ParentControl=24 }
{ 27 ;TextBox ;12980;2200 ;2750 ;440 ;ParentControl=1;
SourceExpr="XAxis title" }
{ 28 ;Label ;9570 ;2200 ;3300 ;440 ;ParentControl=27 }
{ 1102601000;TextBox;12980;3410 ;1700 ;440 ;ParentControl=1;
SourceExpr="YAxis fields" }
{ 1102601001;Label ;9570 ;3410 ;3300 ;440 ;ParentControl=1102601000 }
{ 1102601002;TextBox;5720 ;2860 ;3630 ;440 ;ParentControl=1;
SourceExpr="Table Name" }
{ 14 ;CommandButton;13750;5720 ;2200 ;550 ;HorzGlue=Right;
{ 15 ;MenuButton ;11330;5720 ;2200 ;550 ;HorzGlue=Right;
CaptionML=ENU=&Chart;
{ ID=16;
PushAction=LookupTable;
ShortCutKey=F5;
CaptionML=ENU=L&ist }
{ ID=26;
{ ID=17;
MenuItemType=Separator }
{ ID=19;
Object@1002 : Record 2000000001;
XAxisField@1000 : Text[30];
OBJECT Codeunit [RemoveMe]72000 Chart Generator Mgt
Time=16:47:56;
TableNo=72000;
OnRun=BEGIN
CreateXML(Rec);
MESSAGE('Chart %1 was created / updated.',Chart.ID);
Chart@1015 : Record 2000000078;
ChartGen@1003 : Record 72000;
ChartFilters@1008 : Record 72001;
ChartYAxis@1012 : Record 72003;
"3TierMgt"@1013 : Codeunit 419;
XMLDoc@1000 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 4.0:{F6D90F11-9C73-11D3-B32E-00C04F990BB4}:'Microsoft XML, v4.0'.DOMDocument";
DomNode@1001 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 4.0:{2933BF80-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v4.0'.IXMLDOMNode";
DomNode2@1006 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 4.0:{2933BF80-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v4.0'.IXMLDOMNode";
DomTextNode@1002 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 4.0:{2933BF87-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v4.0'.IXMLDOMText";
DomAttribute@1004 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 4.0:{2933BF85-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v4.0'.IXMLDOMAttribute";
DomNodeList@1009 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 4.0:{2933BF82-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v4.0'.IXMLDOMNodeList";
DomProcessInstruction@1005 : Automation "{F5078F18-C551-11D3-89B9-0000F81FE221} 4.0:{2933BF89-7B36-11D2-B20E-00C04F983E60}:'Microsoft XML, v4.0'.IXMLDOMProcessingInstruction";
NameSpace@1007 : Text[80];
i@1010 : Integer;
TempFileName@1014 : Text[250];
Debug@1016 : Boolean;
DebugFileName@1102601000 : Text[250];
IStream@1102601001 : InStream;
PROCEDURE CreateXML@1(ChartGenerator@1000 : Record 72000);
//=== Enable debug and filename to save the xml document to disk
Debug := FALSE;
DebugFileName := '';
//===
CREATE(XMLDoc);
XMLDoc.async(FALSE);
// Initialize document and set namespaces
NameSpace := 'urn:schemas-microsoft-com:dynamics:NAV:MetaObjects';
DomNode := XMLDoc.createNode(1,'ChartDefinition',NameSpace);
DomAttribute := XMLDoc.createAttribute('xmlns:xsd');
DomAttribute.value := 'http://www.w3.org/2001/XMLSchema';
DomNode.attributes.setNamedItem(DomAttribute);
DomAttribute := XMLDoc.createAttribute('xmlns:xsi');
DomAttribute.value := 'http://www.w3.org/2001/XMLSchema-instance';
// add chart type
DomAttribute := XMLDoc.createAttribute('Type');
CASE ChartGenerator.Type OF
ChartGenerator.Type::Column:
DomAttribute.value := 'Column';
ChartGenerator.Type::Point:
DomAttribute.value := 'Point';
XMLDoc.appendChild(DomNode);
AddNode('ChartDefinition','Title','');
AddNode('ChartDefinition/Title','Text',ChartGenerator.Title);
AddAttribute('ChartDefinition/Title/Text','ID','ENU');
AddNode('ChartDefinition','Table','');
AddAttribute('ChartDefinition/Table','ID',FORMAT(ChartGenerator."Table ID"));
AddNode('ChartDefinition/Table','Filters','');
// Filters
ChartFilters.SETRANGE(Company,ChartGenerator.Company);
ChartFilters.SETRANGE(ID,ChartGenerator.ID);
IF ChartFilters.FINDSET THEN
REPEAT
AddNode('ChartDefinition/Table/Filters','Filter','');
AddNode('ChartDefinition/Table/Filters/Filter','Field','');
AddAttribute('ChartDefinition/Table/Filters/Filter/Field','Name',ChartFilters."Filter Field Name");
AddNode('ChartDefinition/Table/Filters/Filter','Value',ChartFilters."Filter Value");
UNTIL ChartFilters.NEXT = 0;
// XAxis
ChartGenerator.TESTFIELD("XAxis Field Name");
AddNode('ChartDefinition','XAxis','');
AddAttribute('ChartDefinition/XAxis','ShowTitle',BooleanFormat(ChartGenerator."Show Title")); ///
AddNode('ChartDefinition/XAxis','Title','');
AddNode('ChartDefinition/XAxis/Title','Text',ChartGenerator."XAxis title");
AddAttribute('ChartDefinition/XAxis/Title/Text','ID','ENU');
AddNode('ChartDefinition/XAxis','Field','');
AddAttribute('ChartDefinition/XAxis/Field','Name',ChartGenerator."XAxis Field Name");
// YAxis
ChartYAxis.SETRANGE(Company,ChartGenerator.Company);
ChartYAxis.SETRANGE(ID,ChartGenerator.ID);
IF ChartYAxis.FINDSET THEN BEGIN
AddNode('ChartDefinition','YAxis','');
AddAttribute('ChartDefinition/YAxis','ShowTitle',BooleanFormat(ChartYAxis."Show Title"));
AddNode('ChartDefinition/YAxis','Measures','');
AddNode('ChartDefinition/YAxis/Measures','Measure','');
AddAttribute('ChartDefinition/YAxis/Measures/Measure','Operator',FORMAT(ChartYAxis."Mearure Operator"));
AddNode('ChartDefinition/YAxis/Measures/Measure','Field','');
AddAttribute('ChartDefinition/YAxis/Measures/Measure/Field','Name',FORMAT(ChartYAxis."YAxis Measure Field Caption"));
UNTIL ChartYAxis.NEXT = 0;
TempFileName := TEMPORARYPATH + '\' + FORMAT(CREATEGUID) + '.xml';
XMLDoc.save(TempFileName);
IF NOT Chart.GET(ChartGenerator.Company,ChartGenerator.ID) THEN BEGIN
Chart.Company := ChartGenerator.Company;
Chart.ID := ChartGenerator.ID;
Chart.Name := ChartGenerator.Name;
Chart.INSERT;
Chart.BLOB.IMPORT(TempFileName);
Chart.MODIFY;
IF Debug THEN
XMLDoc.save(DebugFileName);
// Clean up
IF EXISTS(TempFileName) THEN
ERASE(TempFileName);
CLEAR(DomNode);
CLEAR(XMLDoc);
PROCEDURE AddNode@2(AddToNode@1000 : Text[120];NodeName@1001 : Text[120];NodeValue@1002 : Text[120]);
DomNodeList := XMLDoc.selectNodes(AddToNode);
DomNode := DomNodeList.item(DomNodeList.length - 1);
DomNode2 := XMLDoc.createNode(1,NodeName,NameSpace);
DomTextNode := XMLDoc.createTextNode(NodeValue);
DomNode2.appendChild(DomTextNode);
DomNode.appendChild(DomNode2);
PROCEDURE AddAttribute@3(AddToNode@1000 : Text[120];AttributeName@1001 : Text[120];AttributeValue@1002 : Text[120]);
DomAttribute := XMLDoc.createAttribute(AttributeName);
DomAttribute.value := AttributeValue;
PROCEDURE BooleanFormat@4(Boo@1000 : Boolean) : Text[30];
IF Boo THEN
EXIT('true');
EXIT('false');
EVENT XMLDoc@1000::ondataavailable@198();
EVENT XMLDoc@1000::onreadystatechange@-609();
You can create notifications from task pages such as customer cards or sales orders. You can use notifications as reminders, or as a way to send information to other NAV users in your company.
A notification is displayed on the recipient's Role Center. By clicking the notification, the recipeint opens the associated task page.
1. To create a notification, open the task page from which you want to send a notification. For example, open a customer card, sales order, or other task page.
2. In the FactBox area, scroll down to the Notes part.
3. In the Notes part, click to create a new note.
4. In the Enter a new note here text box, type a note.
5. Select a recipient.
6. Select Notify.
7. Click Save.
The notification is saved on the task page.
The notification is also relayed to the recipient and is displayed on his/her Role Center under My Notifications.
For more information about usability and the RoleTailored client, see the blog post Useful, Usable, and Desirable.
The Microsoft Premier - Dynamics team has created and compiled a set of scripts and tools for helping analyze and troubleshoot SQL Server performance issues on the Dynamics products. These are the same tools that we use on a daily basis for collecting SQL performance data and troubleshooting SQL performance issues on all our Dynamics products* and we want to make this available to our partners and customers. These tools rely heavily on the SQL Server DMVs so it is only available for SQL Server 2005, SQL Server 2008, and SQL Server 2008 R2.
This tool can aid in the troubleshooting of blocking issues, index utilization, long running queries, and SQL configuration issues. Instructions for installing and using the tool are included in the download package. One nice feature of this tool is that it creates a database called DynamicsPerf and imports all the performance data collected into the DynamicsPerf DB, which can be backed up and restored on any SQL Server (2005, 2008) for later analysis making it "portable." The collection of performance data can also be automated via a SQL job for which the scripts are provided.
Performance Analyzer 1.0 for Microsoft Dynamics can be downloaded via the following MSDN link. This tool is updated on a fairly consistent basis with bug fixes and new functionality so please check often for new versions.
http://code.msdn.microsoft.com/DynamicsPerf
This tool and associated scripts are released "AS IS" and are not supported.
*There is added functionality for Dynamics AX
-Michael De Voe
When you start a classic report from RTC (a report with no layout defined), it starts the report engine from the classic client. It can happen that after updating RTC, you end up with a version of RTC which is not compatible with the default classic client. In this case you will typically get this error message when trying to run the report:
Microsoft Dynamics NAV Classic client was opened from an untrustworthy component. Contact your system administrator.
Or if you have a test machine with multiple versions of RTC and classic you may want to start the classic client from another folder.
RTC finds the location of Finsql.exe in this place in registry:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\
So if you have any of the issues above, make sure that the classic client in this folder matches the version of RTC.
Lars Lohndorf-Larsen
Microsoft Customer Service and Support (CSS) EMEA
There have been many requests for some weeks if it is possible to synchronize contacts of Company type and contacts of Person type in Dynamics NAV to one single folder in Outlook. The answer is: Yes, this is possible. The question is: How should we do this? This blog posting will describe how this can be done. It is not difficult, but some steps needs to be done very careful. First of all, ensure the Outlook Synch User Setup form is empty. To do so, follow steps below:
Second, I would reset all demo entities to default values. To do so, follow steps below:
Now it is time to create a new entity. To do so, follow steps below:
Another step that needs to be done is to create the correct dependencies for the TASK entity. To do so, follow steps below:
The following will be shown:
Similar steps that need to be done are to create the correct dependencies for the APP entity. To do so, follow steps below:
Now, it is time to delete the original demo entities. To do so, follow steps below:
The entity list now looks like this:
Last but not least, the Outlook Synch. User Setup form will have to be filled in with correct date.
The following could be setup in Dynamics NAV:
The following could be setup in Microsoft Outlook:
Regards,
Marco MelsCSS EMEA
This posting is provided "AS IS" with no warranties, and confers no rights
I have collected few tips maybe could be used in NAV 2009 developing.Don't claim me too much if this looks to easy... :)
Thank you
When I tried to install the NAV 5.0 SP1 client I ran into an error 1935. This may happen on Vista and Windows 2008.
Error 1935; an error occurred during the installation ofassembly component {98CB24AD-52FB-DB5F-A01F-C8B3B9A1E18E}. HRESULT:0×800736CC.
The solution is simpler then it seems. You need to install Microsoft Visual C++ 20005 SP1 Redistributable pack. Remember if you are running on a 64 bit system you need to install both x86 and x64 package.
x86x64
The final Service Pack 1 for Dynamics NAV 2009 is now just around the corner, so with that I would like to share with you one of the new possibilities in the reporting area, Matrix reports. First I will go through how to build a Matrix Report in NAV 2009 SP1, then I will demo some Matrix reports done by two of our MVPs of Dynamics NAV and one of our partners. In the end of this blog I will share all the Matrix reports. So if you are not interested in how to build a Matrix Report in NAV 2009 SP1 or already know how to do this in NAV 2009 SP1, this is the time for you to scroll to the bottom of this blog, if you just want to download the objects. :-)
If you are still here and have not scrolled to the bottom, let us get started building a Matrix Report.
In this walkthrough we will be creating a Matrix Report sorted with Items by Location. When you have finished this Walkthrough you will understand how to build a Matrix Report in NAV 2009 SP1.
1. Select “New” in Object Designer with Report object selected
2. Add “Location” as Table
3. Select “Create a blank report“ and click “OK”
4. Insert “Location” as the first DataItem
5. Insert “Item” as the second DataItem, make sure to indent under the “Location” DataItem.
6. Save Report and give an “ID” and a “Name”
7. Now we have created the DataItems for this report, so now we need to create the Data Source for us to be able to create the Layout of the Report in Visual Studio. For this we need to go to the Section Designer. Open Section Designer
8. For now the Section Designer is Empty, so we need to add some Fields. We would need these fields:
· Items by Location – Title
· Code – Location
· Name – Location
· No. – Item
· Description – Item
· Inventory – Item
9. Make more space to have more fields in each of the Sections.
10. Add a Label with Caption=Items by Location
11. With “Location” Section selected, open Field Menu:
12. Open Field menu and select “Code” and “Name”
13. Add these 2 fields to the “Location, Body”
14. Navigate to the “Item , Body” and select “Field Menu” and select "No.", "Description" and "Inventory"
15. Add these 3 fields to the “Item, Body”
16. We have now created the Data Source for the report. Now we need to create the layout for this report.
Select “View / Layout”. Visual Studio will open.
17. In Visual Studio you will now see these elements in Data Source we can work with:
18. Lets us now design the layout.
19. In the toolbox select the Matrix control and drag this to the Report Body
20. Drag “Items_by_LocationCaption” to the top left cell in the Matrix control
21. In the “Rows” just below I want to add 2 rows directly under “Items by Location”, so we need to add a new group. Right click the “Rows” cell and select “Insert Group”
22. “Grouping and Sorting properties” dialog box will come up.
Select to group on “=Fields!Item_Description.Value” and click “OK”
23. In the new cell to the left of “=Fields!Item_Description.Value”, right click and select “Edit Group”
24. Select to group on “=Fields!Item__No__.Value” and select “OK”
Your report should now look like this:
25. It is now time to add the column above the data, and in this report we want to see in how many items are stored in each warehouse. Add “=Fields!Location_Name.Value”
26. And at last let us add the data cell in the bottom right corner of the matrix control. Add “=Sum(Fields!Item_Inventory.Value)” to the Data cell.
27. Now before viewing the report let us already do some resizing of the layout, for this to readable on one page. I choose to narrow the columns, so the report now looks like this:
28. Also to make this report more readable friendly, let us add a Solid Border style. Select all cells in the Matrix control, and select “Solid” for the “BorderStyle” property
29. Now let us view the report, save, import, compile and run the report. We now get this output.
Hmm, something is wrong. I.e. we have 32 bicycles on stock in each of the warehouses. Let us fix this issue.
30. Go to the DataItem “Item” and set the property “DataItemLink” to “Location Filter=FIELD(Code)”
31. Now let’s run the report again, but now we set the following filter on the Request Page: Inventory is >0
We do this to avoid all entries with 0.
32. Now select “Preview” to see the report.
33. We now have a working Matrix Report which is sorting Items by Location.
34. Now let us imagine you would like to know the Inventory Availability on the 52 “PARIS Guest Chair, black” in the “Blue Warehouse” or on the 55 “AMSTERDAM Lamp” in the “Red Warehouse”. So let use the Drill Through to Report feature in Dynamics NAV SP1 to drill through to our existing “Inventory Availability” report 705. Open Visual Studio again by selecting “View / Layout”
35. Right click the Data field in the Matrix control and select “Properties”
36. Navigate to the “Navigation” tab
37. Select “Jump to URL” and add the following expression:
="DynamicsNAV:////runreport?Report=705&Filter=Item.%22Location Filter%22:"+Fields!Location_Code.Value+"&Filter=Item.%22No.%22:"+Fields!Item__No__.Value
What we are doing here is that we open Report 705(Inventory Availability) filtered on Location and Item No., when we click data field in the Matrix report.
38. Before we save the report, let us make it visible in the report that we have a link to another report.
With the Data field selected set these properties:
Color=Blue
TextDecoration=Underline
39. Save, Import and Compile the report. Note you might this error:
Set the EnableHyperlinks=TRUE, and compile again
40. Run the report. It will now look like this:
41. Now when we click on the 52 “PARIS Guest Chair, black” in the “Blue Warehouse”, Inventory Availability report will now open based on the our Blue Warehouse and our Paris Guest chair.
And when we click on the 55 “AMSTERDAM Lamp” in the “Red Warehouse”, Inventory Availability report will now open based on the our Red Warehouse and our Amsterdam Lamp.
1. Items by Location done by Andrey Panko, MVP for Dynamics NAV. This report is very similar to the above walkthrough, it has extra logic on the data part and more advanced coloring and a Totaling to the very right. It does not contain any Drill Through to report 705, but this could easily be added following the above steps(35-37) on how to that.
2. Items by Location done by Rene Gayer, MVP for Dynamics NAV. This report is also similar to the above reports but here Rene is using the new Drill Through to report, so if click on the Item, left most column, you open a Item Dashboard, with Key Performance Indicators, Stock level illustrated and Sales History.
3. GL Entries per Account per Month, done by GAC Business Solutions. This report gives a overview of the entries in GL Account pr month. Notice when clicking the GL Account you will Drill Through to GL Account card, and when clicking the amount in the Matrix you will Drill Through to the Detail Trial Balance Report filtered on month and GL Account
Please find all the mentioned reports in the attached zip file.
Thanks, to Andrey Panko, Rene Gayer and GAC Business Solutions for sharing their Matrix Reports.
Thanks, Claus Lundstrøm, Program Manager, Microsoft Dynamics NAV