Microsoft Dynamics NAV

Team Blog

  • Microsoft Dynamics NAV Team Blog

    Send email with PDF attachment in NAV 2009

    • 13 Comments

    In this post I would like to explore the possibilities to create an email from the Role Tailored client and attach an invoice as a PDF file to the email, unfortunately we have do not have this functional build into our Demo application, but let me show you how this can be do with little effort.

    First I suggest you download the fob file which contains the 5 different options I will go through here.

    When downloaded the fob file you will see that I have added 5 new actions

    image

    1. SendAsPDF(Use of codeunit to rename) recommended solution
    2. SendAsPDF(Access to Server needed)
    3. SendAsPDF(With Temp file name)
    4. SendAsPDF(User prompted to save)
    5. SaveAsPDFrecommended solution(if you just want the PDF file)

    Let me go through the different options starting from the bottom, since I recommend option 1, but I would also like to share other options for doing this, since these might be valuable for you.

    Option 5: SaveAsPDF
    In this option you will get prompted if you want to open or save the PDF. The PDF file created will be based on the select Invoice in the Posted Sales Invoices List Place

    image 

    In this option all I do is to have the server create the PDF file for me and use the new download function in NAV 2009 to retrieve the PDF file created on the server.

    Option 4: SendAsPDF(User prompted to save)
    In this option, you will first be prompted to save the file.
    Here it is important to select to “”SAVE” the PDF file on the disk, to have the correct name of the PDF file. If you select to “OPEN” the PDF filename will be given a temp name.

    After you have saved the PDF we now create the email message you will get 3 messages similar to this when this happens:

    image

    You get these message because we connect to an external component(Outlook) to the Role Tailored client. It is of course up to you if you want to set this to “Always allow”, but this would remove these messages, the next time you open the Role Tailored client.

    When you have allowed these to run, email will be created with the PDF file attached.

    image

    In this option all I do is to download the PDF to the client and then use the Mail codeunit to create the email

    Option 3: SendAsPDF(With Temp file name)
    In this option, you will not be prompted to save the PDF file.
    And the email will be created immediately. This would probably be the preferred compared to downloading this to the user disk, but we will use the PDF file created on the server, and since this file get a TEMP name, like this: “__TEMP__570eb0279b9d4b1fa837caf3a14acbf7” this option is not really good.

    Let us look at the option 1 and 2 where this issue is solved.

    Option 2: SendAsPDF(Access to Server needed)
    In this option you will not be prompted to save the file either, but here the end user will need to have access the server folder where the PDF is stored on the server. In some situation you might want this, but for security reasons you might also not want to give this access to the user.

    Option 1: SendAsPDF(Use of codeunit to rename) recommended solution
    Again in this option you will not be prompted to save the PDF file, but the PDF file will be automatically added to the email. In this option we have build a codeunit to rename the TEMP file created on the server, and end user will not need to have access to any folders on the server.

    So all in all I recommend option 1 for attaching PDF file to an email. And once again I have made all the code available here, so feel to be explore how I build this. If you feel there is an option that I missed, feel free to leave a commit or use the contact form Email.

    Thanks,
    Claus Lundstrøm, Program Manager, Microsoft Dynamics NAV

  • Microsoft Dynamics NAV Team Blog

    “User ID and password are invalid.try again?”

    • 7 Comments

    If you get "User ID and password are invalid.try again?" when starting RTC you need to first check that you can login to database in the classic client. Note that the database should be the same as the service tier (the service you are trying to connect with RTC) is connected to.

    1. Start "classic client with SQL"
    2. Click File->Database->Open
    3. Pick the server the database exist one
    4. Select authentication "Windows Authentication"
    5. Pick the database
    6. Press OK

    If it works in the classic client make database login synchronization.

    1. Start "classic client with SQL" and connect to the database as above
    2. Click Tools->Security->Synchronize all logins
    3. Answer yes to the question if you would like to synchronize

    If it doesn't to connect to the database start SQL server manager and verify that the windows user have permission to login and use the NAV database.

  • Microsoft Dynamics NAV Team Blog

    Setting Up a Mail Template for Document Approval Notifications

    • 11 Comments

    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:

    default template

    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.

     

    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 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

  • Microsoft Dynamics NAV Team Blog

    Run As Administrator if you are doing an upgrade from the same machine where SQL is hosted

    • 0 Comments

    When doing an upgrade running Microsoft Dynamics NAV CSIDE client on the same machine as your SQL server, please run the CSIDE client with full administrator rights (Run as administrator).

    The reason why this is important is because, when connecting locally from a Vista/Windows 2008 server to SQL Server, it will strip down the Windows token from, among other things, honoring builtin\Administrator membership, unless connecting from an elevated command (run as administrator).

    This is especially important when doing upgrades for NAV 2009 SP1.

    This should not be needed if you connect from a remote workstation to SQL server.

    -- jtorres

  • Microsoft Dynamics NAV Team Blog

    Help Source Files Released for Microsoft Dynamics NAV 2009 SP1

    • 0 Comments

    We have released the source files for Dynamics NAV 2009 SP1 Help to PartnerSource. This release includes 8 different languages across 16 countries/regions, approximately 1 million files, and about 2.5 GB of content. If it were in book format, that would stack up to about 10 meters of books. That's a lot of help for a lot of users!

    With these source files and the previously released Help Toolkit for SP1 (also on PartnerSource), you can modify the Help files to match customizations that you create. Go ahead, add to the stack of content and customize the Help that you provide to your users!

    And as always, let us know what you think about the Help, the customization process, the tools, what's missing, and what would be more helpful.

  • Microsoft Dynamics NAV Team Blog

    More troubleshooting multi-machine installations of NAV 2009

    • 0 Comments

    Today I found a really good and interesting blog about general troubleshooting of multi-machine scenarios in NAV 2009. Most of the blog is checking SPN settings, delegations settings and different problems with those.

    Intro
    The NAV 2009 documentation walkthroughs provide step-by-step instructions for installing NAV 2009 on 2 or 3 machines. However, we have found that some of the same configuration issues come up time after time after installation.
    When on calls with partners and customers, it seemed to me that this information was spread out all over the place, so I wanted to organize it in a different way for troubleshooting purposes so that I would have most everything in one place. Hopefully this will be helpful to others as well.
    The intention of this post is to provide a checklist of sorts for troubleshooting some of the areas where we frequently find errors or omissions in configuration after NAV 2009 has been installed.

    Find the whole blog here: http://blogs.msdn.com/nav_developer/archive/2009/08/17/troubleshooting-multi-machine-installations-of-nav-2009.aspx

  • Microsoft Dynamics NAV Team Blog

    How using SQLIndex property can affect which index NAV uses

    • 1 Comments

    The usual rule for specifying a key in NAV is, that it will chose the first key that matches all the fields specified by SETCURRENTKEY, RunformLink, etc.


    Example:
    Table 21 "Cust. Ledger Entry" has the following keys (not all of them listed):
     
      - Entry No.
      - Customer No.,Posting Date,Currency Code
      - Customer No.,Open,Positive,Due Date,Currency Code
      - Customer No.,Applies-to ID,Open,Positive,Due Date
     
    You have code like this:
    CustLedgEntry.SETCURRENTKEY("Customer No.");
    CustLedgEntry.FINDFIRST;
      
    You will get a query with this ORDER BY:
    ORDER BY "Customer No_","Posting Date","Currency Code","Entry No_"
     
    So far, all works as expected: NAV finds the first key that matches the sorting, and uses that.
     
     
    This changes if you change the SQLIndex property on the key. If we design table 21 and set SQLIndex = "Customer No." on the key used above, then NAV will skip this key and use the next matching one, and the code above will now generate this ORDER BY:
    ORDER BY "Customer No_","Open","Positive","Due Date","Currency Code","Entry No_"
     
    So now, even if we want to sort by "Customer No.", and we have a SQL Index which is exactly that, NAV chooses a different index. And it no longer follows the rule of choosing the first key available which satisfies the requested sorting.
     
    So specifying SQLIndex on a key makes this key less likely to be chosen by NAV. Of course, this does not affect which index SQL Server actually decides on when it makes its query plan. It only afects the ORDER BY clause.
     

    The reason for this is:

    Above is mentioned that we want to sort on "Customer No.". This is not the whole truth. NAV always adds the primay key, so actually we want to sort on "Customer No.", "Entry No.". The primary key fields are used for specifying a sorting (ORDER BY) that is deterministic and they are also used to construct SQL for calls to Record.NEXT(), which might be needed to reposition itself in the database. This happens a lot from the UI but also from C/AL code.

    So the short story is: When SQLIndex is used, they key will by definition not match the ORDER BY which is based on the NAV key, and the index is likely to not be unique, so NAV will give the key a lower priority when there are other similar keys which do not have SQLIndex specified.

    Only when a SQL index is NOT specified, NAV automatically adds the primary key field(s) when it creates the index on SQL server. When we do add SQLIndex, this does not happen - the SQL Index will be exactly what you specify in this property.
     
    In this example where we set SQLIndex to "Customer No.", the SQL Index will just be just that - "Customer No_", so it will not be unique and it will not satisfy an ORDER BY on “Customer No.,Posting Date,Currency Code, Entry No.” and it will not satisfy calls to Record.Next(). So when SQLIndex is specified, NAV will continue down the list of keys, looking for the next key that matches the chosen sorting (and does not have SQLIndex specified) and use that instead.
     
     
    It’s recommended to use the SQLIndex property with great caution. Also see previous blog posts:
    Beware the SQL Index property on NAV 5.0 SP1 
    SQLIndex property
     
    If you do use it, then also be aware of the changes in behaviour described here
     
     

     

    Lars Lohndorf-Larsen

    Microsoft Dynamics UK

    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Manage multiple service tiers in NAV 2009

    • 2 Comments

    Did find an interesting tool developed by Kine yesterday about managing multiple service in NAV 2009. So what can this tool do?

    1. Start/Stop the NAV services
    2. Create new instances of the services
    3. Configure the services (change DB server, DB name, and enable/disable debugging on them)
    4. Remove the services (excluding the default one)
    5. Do it all on remote machine (remote registry access required)

    Kine's post about the tool: Here
    Zip: NAV2009_NST_Management_1_0_0_debug.zip
    Source code: http://github.com/kine/NAV_NST_Management

  • Microsoft Dynamics NAV Team Blog

    Microsoft Dynamics NAV 2009 SP1 Compatibility with Microsoft Windows 7 and Microsoft Windows Server 2008 R2

    • 3 Comments
    We are proud to announce that Microsoft Dynamics NAV 2009 SP1 is compatible with Microsoft Windows 7 and Microsoft Windows Server 2008 R2.

    We support the following versions of Microsoft Dynamics NAV with Microsoft Windows Server 2008 R2 and Microsoft Windows 7: Microsoft Dynamics NAV 2009 SP1. Please note though that tests are currently taking place on the compatibility of Microsoft Dynamics NAV 5.0 SP1 with Microsoft Windows 7 and Microsoft Windows Server 2008 R2. Furthermore, the compatibility for Microsoft Windows Server 2008 R2 does not cover Business Notification for Microsoft Dynamics NAV and Commerce Gateway.

    For more information about requirements, please go to http://msdn.microsoft.com/en-us/library/dd301054.aspx.

    -Christian Baek

  • Microsoft Dynamics NAV Team Blog

    Problems in transformation forms to pages using TransformationTools

    • 0 Comments

    After Microsoft Dynamics NAV 2009 SP1 released, more and more developers started using it and trying to adopt existing solutions for new 3tier environment.
    Most workload comes from trying to adopt current forms to new object - pages.
    Particularly transformation could be done by using TransformationTools (http://msdn.microsoft.com/en-us/library/dd338789.aspx), however it is not "best ever" and partners reporting problems and require to fix it...

    But it isn't so easy...

    This tool is delivered to us from dev team and it helps us to make transformation faster, but it is supplementary tool - we can use NAV without this tool and we can "convert " our solutions without it - do it manually.
    So "big thanks" to dev team for this tool, however we can't expect that dev team will fix all problems in tool with the same priority as base products (Microsoft Dynamics NAV).

    And we can't expect fixes for problems related to incorrect transformed statements - after transformation pages can't be compiled...
    Problem is in tool simplicity: it searches for text and convert it to another text. Converting rules are described in file CodeRules.txt (http://msdn.microsoft.com/en-us/library/dd338843.aspx).
    But simplicity is as strength as weakness of this tool - only text described in CodeRules.txt file will be converted, if there are any differences in text - transformation will be incorrect.
    For example:
    In form code we have statement:
    CurrForm.Number.UPDATEFONTBOLD(Number);
    Then after transformation on page will be created new variable
    NumberEmphasize
    And statement will be converted to:
    NumberEmphasize := Number;
    It is because UPDATEFONTBOLD is not used in pages and must be removed.

    So far so good.
    Tranformation will be done correct for statements:
    CurrForm.Number.UPDATEFONTBOLD(Number1=Number);
    CurrForm.Number.UPDATEFONTBOLD(Number1>Number);
    and etc. because transformation rules are described in CodeRules.txt

    But transformation tool is looking for direct text fit to rules and will not transform text which is not described in Coderules.txt. 
    If code becomes little more complicated (not described the same syntax as CodeRules) - transformation tool capitulates.
    For example next code will not be transformed:
    CurrForm.Number.UPDATEFONTBOLD(Number1<Number);
    CurrForm.Number.UPDATEFONTBOLD(Number1=xRec.Number);
    CurrForm.Number.UPDATEFONTBOLD(xRec.Number1=Number);

    CurrForm.Number.UPDATEFONTBOLD(Number1=Number2=Number3);
    CurrForm.Number.UPDATEFONTBOLD(Number1=(Number+100));

    ...

    Yes... We can make rules for these statements too (dev team delivered rules file), but we will never describe everything what could be written by happy-creative developpers around the world...
    Maybe some rules could be never used? Who knows..? 


    I can collect all requirements for CodeRules.txt and periodically release new one (fixed). Do you want to order me do it, let me know in comment to current post :) ...


    These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

    Gedas Busniauskas (gediminb)

    Microsoft Lithuania
    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Microsoft Dynamics NAV 2009 SP1 Ships!

    • 1 Comments

    If you're going to blog as infrequently as I have, it's a good policy to make sure that when you do, you focus on very important subjects. Since this is my first post since last fall's "Microsoft Dynamics NAV 2009 Ships!" entry, I'm happy to say I'm following my own policy. Today, we are releasing Microsoft Dynamics NAV 2009 SP1 to customers and partners, and my enthusiasm for what we've done for you, with you, is just as immense as it was when we shipped Microsoft Dynamics NAV 2009. Of course this time, I'm excited about different things.

    Let's rewind for a moment to set the stage: Last November, we released what I called a landmark release of Microsoft Dynamics NAV 2009. We transformed the user experience, revolutionizing and modernizing the rich client with a RoleTailored approach to design. We also significantly refactored the product's architecture introducing three tiers, Web services, a .NET runtime, and RDL reports for SQL. It was a mountain of a release, and we hit the summit.

    Microsoft Dynamics NAV 2009 was a release of courage. We made big bets. We wanted nothing short of transformation. And we accomplished our goal, shipping what is arguably the most significant release in Microsoft Dynamics NAV history.

    Microsoft Dynamics NAV 2009 SP1, by contrast, is a release of precision. We listened and collaborated with you, our partners and customers. We refined. We executed predictably. And, less than 12 months after the release of Microsoft Dynamics NAV 2009, we are giving you a service pack that extends the value of Microsoft Dynamics NAV 2009 and meets our joint goal of making Microsoft Dynamics NAV simply the most productive middle-market ERP product on the planet. In this way, it is as just as big as the release it is built upon.

    Here's how we did it and what we're giving you.

    When Microsoft Dynamics NAV 2009 shipped, we had a number of customers in our Technical Adoption Program (TAP) already live on the product. These customers and the partners providing them solutions together gave us a tremendous amount of feedback on the product. Much of this feedback influenced what we shipped in Microsoft Dynamics NAV 2009. But we couldn't respond to all of the feedback in our timeline for shipping. In addition, we wanted to add productivity improvements for both customers and partners, and features that took advantage of our new client architecture. So we decided to release this service pack and invest a fair bit in it.

    The features we have added to increase customer productivity follow the six dimensions of productivity I discussed originally in my Microsoft Dynamics NAV 2009 blog - usability, familiarity, flexibility, transactional efficiency, business insight, and collaboration. For example, the new Search box will be instantly familiar to users of Internet Explorer and will reduce the time it takes to find list places not already available in one's Role Center. In addition, users can now persist filtered views of list places, filter-as-you-type columns, and the default number of lines in a document page grid. We have provided transactional efficiency increases with some refactored matrix forms, a large increase in keyboard enablement, and the addition of many Classic client keyboard combinations. Reporting drill-down enhancements further improve business insight, and the Online Connect part gives users access to communities, knowledge base articles, and learning directly from the Role Center, and promotes collaboration with the Microsoft Dynamics NAV community.

    The features we have added to increase partner productivity are equally extensive. We improved the efficiency of page design by adding a page wizard and providing edit-and-run capabilities for pages directly from within the C/SIDE environment. We added data zoom (About This Page) to pages, which provides helpful page information and is similar to how partners have historically worked with forms. We added fixed IDs to page transformation, which should help ISVs manage their implementations better. Finally, we increased our documentation for partners, for example, adding walkthroughs for installation.

    But probably the most intriguing feature we have added for partners is client extensibility. We have exposed the client API and provided a very simple model for adding managed code controls to pages from C/SIDE, so that partners can now develop custom user experience parts that take advantage of powerful Microsoft technologies like Microsoft Silverlight, Windows Presentation Foundation (WPF), and Windows Forms. The opportunity for partners to differentiate their solutions from the competition through user experience innovation is limitless, and builds on the thought leadership we've provided in Microsoft Dynamics NAV 2009. We're looking forward to an explosion of creativity!

    None of this would have been possible without the unmatched support we've received from our partners and customers, especially through (but certainly not limited to) our TAP and ISV Beta Access Program (BAP).Whether through surveys, group meetings, informal e-mails, or short conversations at events, we've received a tremendous amount of feedback on Microsoft Dynamics NAV 2009.We've taken this feedback, incorporated it, worked with our TAP customers and partners to evaluate the results of this service pack, and now we're shipping it. Thanks many times over for your incredible support, devotion, and passion for Microsoft Dynamics NAV. Together, we've made a great product even greater.

    Before I finish this blog post, I want to mention something I ended my Microsoft Dynamics NAV 2009 Ships blog entry with: quality. At that time, I said, "Perhaps the thing I'm most proud about in this release is that we've substantially improved quality." That's true again with Microsoft Dynamics NAV 2009 SP1.We've continued to increase the bar on quality. In our customer satisfaction surveys, the reliability of Microsoft Dynamics NAV has always received high marks. And we're making it better and better. I simply can't emphasize enough how important quality is to us. And we know it's important to you.

    I hope you're as excited about Microsoft Dynamics NAV 2009 SP1 as I am. Download a copy from CustomerSource or PartnerSource, or try out the Microsoft Dynamics NAV 2009 SP1 VPC and let us know what you think!

    -Dan

    P.S. A final thought: For those of you who are intrigued by Microsoft codenames, Microsoft Dynamics NAV 2009 was once called Corsica. Indeed, the mountains of Corsica are tall, and the highest summit is Monte Cinto at 2700 meters, or just shy of 9000 feet. The view from this summit is unique because the mountain's island location in the Mediterranean gives it a wide panorama and views of other mountains that are quite distant. As we said when we released Microsoft Dynamics NAV 2009, "The view from the top rocks!"

  • Microsoft Dynamics NAV Team Blog

    What's New in Reporting in Microsoft Dynamics NAV 2009 SP1

    • 0 Comments

    We have now released Service Pack 1 for Microsoft Dynamics NAV. So you will now all be able to use the new Reporting features in this Service Pack. Previously I have described all new reporting features in this Service Pack. Please find all these features described here: What is new in Dynamics NAV 2009 SP1 for Reporting.

    Thanks,
    Claus Lundstrøm, Program Manager, Microsoft Dynamics NAV

  • Microsoft Dynamics NAV Team Blog

    What to do when the setup.exe doesn’t start in Dynamics NAV 2009

    • 8 Comments

    The reason that setup.exe doesn't start can be many. But I will try to give you ha checklist that you can work through if you end up in this situation.

    1. Ensure that the user account running setup.exe have administrator rights.
    2. Try the DVD/download one another computer. If this work you have eliminated that it's any wrong on the installation media.
    3. Start the task manager and check if any setup.exe is running. Sometimes under very special circumstances the setup.exe crash and will prevent any other attempt to start it again by staying running. If you find any setup.exe process kill them. And try to run the setup.exe again.
    4. Start the task manager and check if any msiexec.exe is running. This may happen when a windows update is running or that another installation process is running or have crashed. The best solution in this case is to restart the machine and check again and try to run the setup.exe again. In most cases the msiexec.exe process will be gone.
    5. If msiexe.exe are still there it probably mean that windows installer are running something. You can on your own risk try to kill all of those processes. The risk you take here is that you may end up with something half installed from windows update or other source. So it's recommended to run windows update after this to ensure its ok.
  • Microsoft Dynamics NAV Team Blog

    Building a Matrix Report in Microsoft Dynamics NAV 2009 SP1

    • 6 Comments

    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.

    Building Matrix Report in NAV 2009 SP1

    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

    image

    3. Select “Create a blank report“ and click “OK”

    4. Insert “Location” as the first DataItem

    image

    5. Insert “Item” as the second DataItem, make sure to indent under the “Location” DataItem.

    image

    6. Save Report and give an “ID” and a “Name”

    image

    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

    image

    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

    image

    11. With “Location” Section selected, open Field Menu:

    12. Open Field menu and select “Code” and “Name”

    image

    13. Add these 2 fields to the “Location, Body”

    image

    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”

    image

    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:

    image

    18. Lets us now design the layout.

    19. In the toolbox select the Matrix control and drag this to the Report Body

    image

    20. Drag “Items_by_LocationCaption” to the top left cell in the Matrix control

    image

    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”

    image 

    22. “Grouping and Sorting properties” dialog box will come up.

    Select to group on “=Fields!Item_Description.Value” and click “OK”

    image

    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:

    image

    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”

    image

    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.

    image

    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:

    image

    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

    image

    29. Now let us view the report, save, import, compile and run the report. We now get this output.

    image

    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)”

    image

    31. Now let’s run the report again, but now we set the following filter on the Request Page: Inventory is >0

    image

    We do this to avoid all entries with 0.

    32. Now select “Preview” to see the report.

    image

    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”

    image

    36. Navigate to the “Navigation” tab

    image

    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

    image

    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:

    image

    Set the EnableHyperlinks=TRUE, and compile again

    40. Run the report. It will now look like this:

    image

    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.

    image

    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.

    image

    Now let us look at some other Matrix Reports

    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.

    image

    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.

    image

    image 

    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

    image

    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

  • Microsoft Dynamics NAV Team Blog

    How to install more than one Dynamics NAV 2009 Demo Database alternative 2

    • 0 Comments

    Earlier I did describe how to duplicate an installed and existing database in post "How to install more than one Dynamics NAV 2009 Demo Database". This time I will attached the DB directly from DVD.
    The steps is as follow

    1. Create the directory where you would like to have the new database.
    2. Copy the database from \DVD\SQLDemoDatabase\PFiles\Microsoft Dynamics NAV\60\Database\ Demo Database NAV (6-0)_Data.mdf to the new directory.
    3. Run the following command in SQL manager studio and replace the @physname path with the new created on and set @dbname to the database name you would like to use.
      sp_attach_single_file_db @dbname= ‘new_Demo Database NAV (6-0)', @physname= ‘C:\Program Files (x86)\Microsoft Dynamics NAV\60\Database - Copy\Demo Database NAV (6-0)_Data.mdf'

    You need to ensure that the SQL server user have correct permissions to ‘C:\Program Files (x86)\Microsoft Dynamics NAV\60\Database - Copy\' or where you put the db files. I'm using ‘NETWORK SERVICE" as the SQL server user account, the NAV will use this account by default when doing demo installation.

    To set permission

    1. Open properties for the directory.
    2. Click security tab
    3. Click advance
    4. Click edit
    5. If the SQL server user is not present press add and pick the user else select the SQL server user and click edit
    6. Change the permission so that the following permission is checked
      1. Read attributes
      2. Read extended attributes
      3. Create files / write data
      4. Create folders / append data
      5. Write attributes
      6. Write extended attributes
      7. Delete subfolders and files
      8. Delete
    7. Click OK
    8. Click Ok
    9. Click OK
    10. Click OK
Page 36 of 49 (730 items) «3435363738»