November, 2008

  • Microsoft Dynamics NAV Team Blog

    Microsoft Dynamics NAV 2009 Ships!

    • 4 Comments

    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

  • Microsoft Dynamics NAV Team Blog

    Basic SQL - How to create a new NAV database for restoring a NAV backup

    • 2 Comments

    This post is part of "Overview of NAV-specific SQL features for application consultants". 

     

    This post describes the steps needed to create a new NAV database from a NAV backup (.fbk) file. All the steps are done from a NAV client:

    1)     Start a NAV client (finsql.exe), then go to File -> Database -> New. In Server Name, enter (local)  - assuming you have SQL Server on the same machine. Then click OK.


    2)    You only have to enter a database name, and click OK. But if the database is used for test purposes, I would also recommend you change “Recovery Model” from Full to Simple (Options tab), and change “Security Model” from Enhanced to Standard (Advanced tab). Then click OK.


    3)    Now you have a blank NAV database. All you have to do, is to restore your NAV backup (.fbk) file: Tools -> Restore.


    4)    If you want to change the NAV license, then go to Tools -> License Information, and click Upload, and select the NAV license. This uploads the license to the SQL Server, and it will be stored there for the next time you start NAV.

     

     

    Lars Lohndorf-Larsen (Lohndorf)

    Microsoft Dynamics UK


    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    TAP Customers and Partners Deploy Microsoft Dynamics NAV 2009

    • 1 Comments

    Most Microsoft product teams involve their top customers and partners the development process via Technology Adoption Programs (TAP).  These high touch engagements result in much better product quality and features as well as early (pre-RTM) deployments of new versions. 

    The Dynamics NAV 2009 TAP has succeeded with 10 live customers in Germany, Denmark, Canada and the US already deployed and using NAV 2009 CTP4!  Six of these are using upgraded ISV solutions for distribution, manufacturing, furniture and project management.  We also have previous Epicor and Infor customers who have made the switch to NAV 2009 as part of the TAP.  All deployments are using the new Role-tailored Client and several are running multiple NAV servers.

    The TAP has also made significant quality improvements to NAV 2009 with over 100 fixed bugs and several major design changes.  These great accomplishments are the result of over a year’s hard work between the TAP partners, NAV core teams and the TAP PM Team (including my European counterpart, Kipper York).  We are already receiving excellent feedback from the Go-Live customers, especially concerning the new client.

    For those of you attending Convergence 2008 in Copenhagen on November 18-20, we encourage you to attend the “Meet the Early Adopters” Sessions:
    •    TAP Partners: 11/19/2008 1:30PM-2:30PM, room B4.2
    •    TAP Customers: 11/20/2008 9:00AM-10:00AM, room B4.3

    We'll have lots of other NAV 2009 content at Convergence.  Hope to see you there and to share the excitement!

    - Brett Johnson

  • Microsoft Dynamics NAV Team Blog

    Basic SQL - Creating users in NAV on SQL

    • 1 Comments

    This post is part of "Overview of NAV-specific SQL features for application consultants".

    Before you can set up a user in Microsoft Dynamics NAV on SQL Server, you must create the user on SQL Server first. If you try to create a user which does not exist on SQL Server, you will get this error message:


    ===
    The Microsoft Dynamics NAV Classic and SQL Server security systems have not been successfully synchronized.  The SQL Server login MYUSER does not exist on the LARS-PC server.

    ===

    NoUserExists

    To create this user on SQL Server follow these steps:
    1)     Open SQL Server Management Studio and expand the group Security -> Logins.
    2)    Right click on “Logins” and select “New Login”. Type in the name of the user.
    3)    Change from “Windows Authentication” to “SQL Server authentication”, and then type in (and remember) a password.
    4)    If you leave the checkmark “Enforce Password Policy” enabled, then the password you select must be complex enough to live up to the policy. If you disable this checkmark you can choose any password you like, or even leave it blank.
    5)    Un-tick “User must change password at next login”. Otherwise the user will not be able to log into NAV at all.
    6)    Under “Default Database”, select the NAV database that you want the user to access.

    CreateUser

    7)     If you want this user to access more than one database, then click on “User Mapping”, and select the databases that you want to give the user access to. Otherwise just click on OK, and the user is ready to be used.

    UserMapping

    8)     You don’t need to assign any permissions on the SQL Server. As long as the user exists on SQL, then you can use it, and assign permissions from a NAV client.
    9)    So open the database in NAV, and go to “Database Logins” (Tools -> Security -> Database Logins), and then enter the User ID for the user you created, and then assign the appropriate roles.

    Synchronize permissions
    One more step is needed if you run NAV on SQL Server compared to Native. Depending on the security model in the database, you may need to also synchronize permissions. You can see and change the security model from NAV under File -> Database -> Alter:

    SecurityModel

    The security model defaults to Enhanced, but for a test system it is simpler to change it to Standard. To change it, you must also (temporarily) select “Single User” from the “Options”-tab. Changing the security model will automatically rebuild all the permissions. After this is done, don’t forget to go back and un-tick “Single User” again.

    If your database is set to use Enhanced security model, then every time you change or create users, roles or permissions, then you must also synchronize this by going to Tools -> Security -> “Synchronize all logins” before the changes take effect.

     

     

    Lars Lohndorf-Larsen (Lohndorf)

    Microsoft Dynamics UK

    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Basic SQL - Restoring a SQL Server backup

    • 0 Comments

    This post is part of "Overview of NAV-specific SQL features for application consultants".

    You can back up your Microsoft Dynamics NAV database either from a NAV client or from SQL Server Management Studio. To restore a backup made from SQL Server, follow these steps:

    1)     Open SQL Server Management Studio
    2)    You don’t need to create a database first, like you do from NAV. Just right click on Databases, then select “Restore Database...”.
    3)    In the box that opens, type in a name for your new database. You can name it anything. Then change the source from “From database” to “From device”. Device, in this case, just means file.
    4)    Click the Assist Edit button next to “From Device”, which opens a new dialogue box. Click Add, and then select the SQL backup you are restoring, and click OK.
    5)    SQL Server will list a few details about the backup you selected. Tick the “Restore” box, and then click OK:

    Restore

    The new database should now appear in SQL Server management studio, and you can access it from a NAV client.

     

    Lars Lohndorf-Larsen (Lohndorf)

    Microsoft Dynamics UK

    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Dynamics NAV 5.0 SP1 and export to Excel

    • 0 Comments

    A number of issues previously reported when running with Dynamics NAV 5.0 and using send-to Excel functionality, are now corrected.  These issues include (among others): decimals exported as text, Item/Customer numbers exported as decimals, dates exported as decimals.

    To implement correction, update with 5.0 SP1, update 1 (KB 956161, build 27191), use default style sheets included in SP1, and make the following change to default style sheet NavisionExportToExcel.xslt:

    Open the style sheet file in notepad, default file is NavisionFormToExcel, placed in Stylesheet folder of the Client folder.

    Browse to the following line :   

    <

    xsl:when test="@subtype != 'number'">

    and replace the line with

    <

    xsl:when test="@datatype != 'Decimal'">

     

    Then locate the following line :   

    <

    xsl:when test="@subtype = 'number'">

    and replace the line with

    <

    xsl:when test="@datatype = 'Decimal'">

     

    Jasminka Vukovic (jvukovic)

    Microsoft Dynamics NO


    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    NAV 2009 - Report Designer - Introduction to the new environment

    • 0 Comments

    Microsoft Dynamics NAV has always had its own report designer. In NAV 2009 it still does, but in addition to this you can also use the Visual Studio (VS) report designer.

    NAV 2009 – both the classic and the new client – will still run reports designed in NAV’s report designer. So in way nothing has changed. You can still use the existing report designer. VS report designer offers a lot of new options and features. The idea of this post is to describe what features out of 100s that you actually need. When it comes to a simple report, only a very few features are needed to get started. This post tells you which ones they are.

     

    Old versus New


    The classic client can still only run reports designed in the classic report designer. The new client (Role Tailored client – RTC) can run reports designed with either the classic or with VS report designer. When RTC launches a report, it checks if a layout has been defined in VS Report designer. If it has, then it will run that. If no layout has been defined, it will launch the report engine from the classic client and run the report exactly like it would have been run from a classic client. This does require that a classic client has been installed as well, even if the user will never have to run this client.
    I this way you can use the classic report designer for some reports, and VS Report designer for others.
    Report design is still done from Object Designer in the classic client.


    VS report designer is a different environment. It has features that are not available in the classic report designer, but also limitations compared to the classic report designer. For example, it does not support Trans-Headers and Footers. But as described above, the VS report designer is a choice you have – you can still use the classic report designer if there are things you can't achieve with the VS report designer.

    The new environment


    Sections is where you make your report layout in the classic report designer. This will run in the classic client as well as in RTC. For RTC you can create a report layout, which is done in VS report designer. So “Sections” refer to the classic report layout while “Layout” refers to the layout done in VS.


    The classic report designer introduces four new options:


    Tools -> Create Layout Suggestion:


    This makes a “best effort” to transform your classic report design and create a suggested layout in VS report designer. This can do a lot of the hard work for you so you don’t have to start from scratch, or design sections first in the classic report designer, and then create the layout in VS report designer. The tool can’t guarantee to transform every report, but it will always at least give you a good start, and for many reports it will do all the work needed.

    Tools -> Delete Layout:


    This deletes the report layout

    View -> Layout:


    This opens VS report designer and is where you will go to design your report for the RTC.

    View -> Request Page:


    The classic client runs forms while the new client runs pages. The same goes for request forms on a report. So if you want to add options to the report for the new client, then do that in the request page.

     

    VS Report Designer


    Going to View -> Layout opens VS Report Designer which looks like this:

    VSReportLayout

    The parts of this environment you need, are:


    Toolbox:


    Normally you can switch between the Toolbox and “Website Data Source” (fields) as shown in the red circle in the picture above. But if for some reason the toolbox is not visible, then go to View -> Toolbox. If you use the “Create Layout Suggestion”, then this will add the necessary elements to the report, and you won’t need the toolbox. If you create a layout from scratch, then all you need from the toolbox – at least for simple reports , is a table:

    ToolBox

     

    Website Data Source:


    This is where you select the fields to print on the report. It will automatically show you anything that you have added to sections in the classic report designer. So to add / remove fields from here, go to sections and add / remove them from there.
    To add the fields from the WebsiteData Source, just drag and drop them into a table or to where you want them displayed.

    VS report designer has 100s more options, features and elements, but the ones mentioned here are the only ones you need to get started.

    Workflow – designing reports


    So having explained which features you need - at least to create a simple report - this is how to use them:


    When you have done your layout in VS report designer, close and save it which will bring you back to the classic report designer. Moving one line up or down will prompt you if you want to load the report layout. When you do that, the VS report layout is saved in the report object itself.
    So exporting a report from Object Designer will export all of it, including the layout you have designed in VS report designer, whether you export the object as .txt, .xml or .fob. If you export the report as .txt or .xml, you can see the VS report layout added at the bottom of the report, in a section called RDLDATA.


    Finally, to run the report, either run it directly from Start -> Run, like this to run report 99800:
    dynamicsnav:////runreport?report=99800
    or of course you can add the report to a page to run it from the new client.

     

    Lars Lohndorf-Larsen (Lohndorf )

    Microsoft Dynamics UK

  • Microsoft Dynamics NAV Team Blog

    NAV 2009 - The structure of reports in VS report designer

    • 0 Comments

    One thing is to make a report look pretty. But before you can do that, you have to make it work first. This post is about how to get the data structure right when designing reports in VS report designer for the new client in NAV 2009.

    If you are completely new to the new environment, then take a look at this post first:

    NAV 2009 - Report Designer - Introduction to the new environment

    This post also assumes that you are already familiar with designing reports in the classic report designer.

    Start with a classic report


    The new environment in Visual Studio (VS) report designer relies completely on the data structure you define in the classic report designer. So you must first design data-items and link them in the right way. You must also use the classic report designer to decide what to display on the report – but not where to display it. Only data that you add to sections in the classic report designer is available in VS report designer. You can add fields and other data wherever you like in sections – as long as the data is there. Then you use VS report designer to place the data where you want it. 

    Sections in VS report designer - 1 Header, 1 Body and 1 Footer


    A report in Visual Studio always has exactly 1 Body section.  You cannot add more than 1. Optionally it may have 1 Header and 1 Footer. You cannot add further headers or footers.
    Enable Header and Footer sections by right clicking in a blank area, then enable / disable header and Footer section:

    Sections

    Warning: If you disable the Header Footer  the section with all its content will be deleted.
    When the report runs, it will run the Header section once, then the Body section once, and then the Footer section once. It will not – like in the NAV report designer – run the Body section for each iteration / record. Looping through records is done by creating a table in the body section. So understanding tables is really important to getting the right data in your report.

    Tables
    This is there we loop through the data in a report. To create a table, insert it from the toolbox. As default it will have a Header, Detail and Footer section.
    To enter something in a cell, either right click it, select Properties, and specify Value. Or drag and drop items from your Data set. Remember that your data set (“Website Data Sources”) consists of what you have added to the sections in the classic report designer.

    Table Sections:
    Header:
    This is a typical place to put captions. If you drag an item containing text from your data set into a header, the environment automatically adds “First” to it. “First” means that it will show the first value in the data set, as opposed to looping through each record in the data set.
    Detail:
    This is where you have the iteration of the data in the report.
    Footer:
    This is a typical place to put totals. If you drag an item containing a numerical value from your data set into a footer, the environment automatically adds “Sum” to it, and the report will print the total from the report.

    Example / exercise – simple report with one data item


    The following example shows how to use the basic sections in a table in a report with 1 data item.


    1)     Create a new report with one data item Customer. On Sections, add Customer No. Name and Balance (LCY):

    BasicReportSections

    Unless you plan to run this report from the classic client it doesn’t matter where or how you add fields to the sections, or if you add header / footer  or any other types of sections. You only add fields to the section because then they will then become part of the data source in VS report designer.


    2)     Click on View -> Layout to open VS report designer. Add a table from the toolbox. Look at “Website Data Sources” to see the fields available in the data set. This is the fields that you put on the sections. So if you want to add fields here, then go back to the sections, add them there, and next time you open the layout those fields will be there.

    3)     Drag and drop Caption fields into the Header, Normal fields into the Detail, and Balance_LCY_ into one of the Footer-cells:

    BasicReportLayout

    That’s all you have to do to create a simple report. So now let’s add some more structure to the report.

    Multiple data items:
    This is where it can easily go wrong if you forget a step or two. First, we need to go back to the “old” environment and see how this affects your data structure.
    The data structure and links are decided by the data items on the report and the fields you put on the sections (View -> Sections). About adding fields to sections, there are two things to be aware of:
    1)    To include something in the data set, it must be on the sections
    2)    If you add sections for another data item, then this affects the data set and the way the report runs
    If you created the report from Example 1 above, then add the following:
    Add a new data item “Cust. Ledger Entry”. Indent and link it to the Customer data item as you would normally. If you now run the report, it will run just as before. Then go to sections and add one or more fields from “Cust. Ledger Entry”. Run the report again, and this time you will see a difference. When you added fields to the sections, you also expanded the data set. The table in the layout will now print a row for each record, and because you have added data to the data set, it will print one row for each. You need to group this data in order for it to make sense. This is where it can get a bit tricky.

    Grouping


    The table in VS report designer will just print all the data in the data set. Unless you group the data correctly, the result will not make any sense. To insert a group, highlight a row in your table, right click it, then “Insert Group”:

    AddGroup

     

    For a group you must specify the Expression, which tells the table what to group by. When creating a group it’s for the indented table – in this case “Cust. Ledger Entry”. So we want to group it by Customer No. From the Customer table. So select that as the expression:

     

    Group

    Also, just to simplify the report, un-tick “include group footer”.
    This completes the structure of the table. But the report will print one group header and group fields, so leaving these empty will mean a lot of blank lines. To complete this report, drag fields into both the new group header and into details.

    This is where the report can easily get wrong if you miss a small detail, and where it may be necessary to try a few times before the report looks the way it should. The result of the report is a combination of data items, sections, and the table you created in VS report designer. So if something is not right, the problem may be any where in those areas. One tip: If the report prints a lot of blank lines, then check if you have any blank rows in the table. Here is a simple table layout that prints customer s and Cust. Ledger Entries:

    FinalTableElements

     

     

    Table elements


    As described in the previous sections, you have to use the right table elements in the right places. This is what the icons of the table elements mean, and how and where to use them:

    TableHeader   Table header
    Will print once at the top of the table.

    GroupHeader   Group header
    This is for when you have an indented data item. The header will show data from the top-level data item, for example Customer No.

    TableDetail   Table Detail
    This corresponds to a body-section, either from a main table or from an indented table.

    GroupFooter    Group Footer
    This is also for indented data items. How this will print, depends on how you have set up the group in the table.

    TableFooter   Table Footer
    Will print once, at the end of the table

     

     

    Lars Lohndorf-Larsen (Lohndorf)

    Microsoft Dynamics UK

    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Using variables and C/AL code in a report in NAV 2009

    • 0 Comments

    This previous blog "NAV 2009 - The structure of reports in VS report designer" describes how to get data from your data items into the layout of a report in Visual Studio (VS) report designer. This time we look at how to control the iterations of a report with code, and showing variables rather than fields on a report. So, for when you manually want to control what to print.


    The tricky part is really to get the structure of the report right. Once this is done, adding additional information from variables / controlling data items from code, is not much work - mainly for these reasons:

    • The data flow in the report is still done from data items, which means the old way.
    • To show variables on a report, you just add it to sections, and it automatically becomes available from the VS report designer.  


    This example shows how to:

    • Use code to control the number of iterations on a data item
    • Show variables on the report
    • Add options to the request page, so user can tick “show Details”

     

    Control data flow from code


    This is really the same as it has always been. Just use C/AL code on data item triggers to decide how many iterations / whether to print a record or not. For this example, we will just print integer numbers. So to begin with, make a new report with the following two data items:


    Integer -  DataItemTableView = SORTING(Number) WHERE(Number=FILTER(1..3))
      Integer (name it Int2), and indent it under the first one. Don’t specify further properties for this data item – we will control this from code. The report should look like this:

    DataItems

    As I am sure you realise, without filtering on the Integer table the report becomes very long! So, to restrict the second data item to print just 5 numbers, just do it as you would normally from C/AL code:

    Int2 - OnPreDataItem()
    SETRANGE(Number,1,5);


    This is really all you have to do to manually control what data is printed. As mentioned already, this is not different to how it has always been.
    So now we just need to complete the report to print these numbers. This is where it can get more tricky if you are new to tables in VS report designer. For further details about the next steps, refer to this post.

    First, to make data available in VS report designer, add it to the sections:
    Go to View -> Sections, and add Number for each of the data items (Integer.Number and Int2.Number) from the Field Menu. It doesn’t matter where on the sections you add it.
    Then, finally open VS report designer (View -> Layout) to put the numbers on the report. In VS report designer, add a table. Insert a group by right clicking on the icon for the Header:

    InsertGroup

    Set the group to have Expression = “=Fields!Integer_Number.Value”, and untick “Include group footer”. Then put Integer_NumberCaption into the header, Integer_Number.Value into the Group Header and Int2_Number.Value into the Group Details, like this:

    Table1

    The report should now print 3 times the numbers 1 – 5:

    ReportPrint

      

    Adding variables to the report


    Now, instead of printing records from the two data items, create a variable and assign it values dynamically. Create a new global variable called TextVar, type Text, then assign it some value, for example like this:

    Int2 - OnAfterGetRecord()
    TextVar := 'Num ' + FORMAT(Number);


    Again, just like you are used to. All you have to do from here, is to add it somewhere to sections to make it available in VS report designer.


    In many cases, a report is designed to be run from either the classic client or the Role Tailored Client (RTC), and you need to add something to sections just to get access to it from VS report designer, but you don’t want it to show when the classic report runs the report. In this case, just set Visible=False, and the classic report won’t show it. In places in the standard application where this is done, the property ForeColor has also been set to 65535 (Yellow), just to mark that this field is only there so that it can be used in the VS report designer. For example, look at sections for report 116 – Statement.

    Adding options to a request page


    In the classic report designer you have request forms. In reports for RTC, you have request pages. To add something to a request page, go to View -> Request Page, and you are in a normal page designer. For this report, add a variable called HideDetails (Boolean) to the request page like this:

    RequestPage

    Note that Type is “Field”, whether SourceExpr is an actual field, or as in this case a global variable. Again, to make it available in VS report designer, you must add the variable to the sections. Then go into the layout in VS report designer.
    To hide a section from the table, use the Visibility-tab for the section. Right click on the Details section in the table, then Edit Group, and on the Visibility tab set Visibility to Expression, and the expressions to =Fields!HideDetails.Value:

     

    HideDetails

    The end result is, that now when printing the report from RTC, you have the option to Hide Details.

     

     

     

    Lars Lohndorf-Larsen

    Microsoft Dynamics UK

    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Dynamics NAV and export to Excel: Export dates as true excel dates

    • 0 Comments

    When running Send-to Excel functionality and exporting data to MS Excel using style sheets, all data exported form NAV is formatted as either numbers or strings.

    That also includes dates, that are exported as strings and not formatted as true dates in excel. As of update 1 for NAV 5.0 SP1, data.xml generated when running export to excel contains data type attribute for each row with data printed. Using 'Date' attribute, one can add a fromatting rule in the style sheet, to specify formatting of data of type Date.

    Following example illustrates this. Please note, the style sheet below is created and tested on W1 NAV using US regional settings. Though tests have been run on few other combinations of regional settings and date format, you might need to adjust the rule to local date format.

    To represent strings as dates in excel, .xml file generated must contain dates in XML datetime format. XML format is defined as:

    yyyy-mm-ddT00.00.000Z.

    The scenario also assumes years in Dynamics NAV are represented with 2 digits,  and years in range 00..29 are in fact 2000..2029, while years in range  30.. will be formatted as 1930... Dynamcis NAV date format in this example is mm/dd/yy.

    In the coded below, substring(@value,1,2)=mm, substring(@value,4,2)=dd and substring(@value,7,2)=yy

    Modify the code in example below according to date format in local NAV client. 

    To modify the style sheet, open the default style sheet for export to Excel using notepad. The default style sheet is NavisionExportToExcel.xslt, and is placed in Stylesheet folder of the Client folder.

    Go to line 61 of the default style sheet, or locate the section below using Search/find, and add the lines marked in the code below.

    ....

    <

    Style ss:ID="CheckBox">   

    <Alignment ss:Horizontal="Left" ss:Vertical="Bottom"/>

    <Font ss:FontName="Verdana" x:Family="Swiss"/>

    </Style>

    <Style ss:ID="rowheading">

    <Font x:Family="Swiss" ss:Bold="1"/>

    <Interior ss:Color="#C0C0C0" ss:Pattern="Solid"/>

    </Style>

    <

    Style ss:ID="Dateformat">    <!-- THIS LINE IS ADDED !-->

    <!-- this is where we define the style for date type fields!-->

    <

    NumberFormat ss:Format="Short Date"/>    <!-- THIS LINE IS ADDED !-->

    </

    Style>   <!-- THIS LINE IS ADDED !-->

    .... 

    ****************************************************

    Go to line 235 of default style sheet, or locate the section below using Search/find, and add the lines marked in the code below.

     ....

    <

    xsl:template match="Control[@type='TextBox']">

    <

    Cell xmlns="urn:schemas-microsoft-com:office:spreadsheet">

    <xsl:choose>   

    <xsl:when test="(@datatype = 'Date')">      <!-- THIS LINE IS ADDED !--><!--  Dateformat style is to be used for data type Date !-->

    <xsl:attribute name="ss:StyleID">Dateformat</xsl:attribute>    <!-- THIS LINE IS ADDED !-->

    </xsl:when>   <!-- THIS LINE IS ADDED !-->

    <xsl:when test="(@datatype != 'Integer')and(@datatype != 'Decimal')and(@datatype != 'BigInteger')">

    <xsl:attribute name="ss:StyleID">TextBox</xsl:attribute>

    </xsl:when>

    </xsl:choose>

    <

    Data>

    <

    xsl:choose>     

    <!-- The actual date formatting, make sure data sent from navision are formatted according to XML datetime format  yyyy-mm-ddT00.00.000 and passed as dateTime type of variable. This makes sure Excel will consider this as date type value. Excel will from here take input data and format the output in the format selected in regional settings (or excel settings) !-->

    <xsl:when test="(@datatype = 'Date')and (substring(@value,7,2) &gt; 29)">  <!-- THIS LINE IS ADDED !-->

    <xsl:attribute name="ss:Type">DateTime</xsl:attribute>    <!-- THIS LINE IS ADDED !-->

    <xsl:value-of select="19"/><xsl:value-of select="substring(@value,7,2)"/>-<xsl:value-of select="substring(@value,1,2)"/>-<xsl:value-of select="substring(@value,4,2)"/>T00:00:00.000</xsl:when>  <!-- THIS LINE IS ADDED !-->

    <xsl:when test="(@datatype = 'Date')and (substring(@value,7,2) &lt;= 29)">  <!-- THIS LINE IS ADDED !-->

    <xsl:attribute name="ss:Type">DateTime</xsl:attribute>  <!-- THIS LINE IS ADDED !-->

    <xsl:value-of select="20"/><xsl:value-of select="substring(@value,7,2)"/>-<xsl:value-of select="substring(@value,1,2)"/>-<xsl:value-of select="substring(@value,4,2)"/>T00:00:00.000</xsl:when<!-- THIS LINE IS ADDED !-->

    <xsl:when test="(@datatype = 'Integer')or(@datatype = 'Decimal')or(@datatype = 'BigInteger')"

    <xsl:attribute name="ss:Type">Number</xsl:attribute>

    <xsl:value-of select="@value"/>

    </xsl:when>

    <xsl:otherwise>

    <xsl:attribute name="ss:Type">String</xsl:attribute>

    <xsl:value-of select="@value"/>

    </xsl:otherwise>

    </xsl:choose>

    .....

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

    All feedback providing scenarios where this change wouldn't work, or scenarios that would require any other formatting of date type fileds, is most welcome.

     

    Jasminka Vukovic (jvukovic)

    Microsoft Dynamics NO


    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Building a Role Center in 1 hour

    • 0 Comments

    First I want to say thanks to people who attended Michael Rosenørn and My session, about building a Role Center in 1 hour, at Convergence 2008 last week in Denmark. Our evaluation score for this session was second highest out of all sessions at Convergence, so we must have done something right :-) Thanks guys!!!

    Still I a have a feeling that there are so much more I would like to share with you on how we build up the Role Center, so I have decided to share our demo script for this session.

    To the readers which did not attend the session. Michael acted as the Customer and I as the Partner and Live implementer at stage.

    The story: Michael (The customers) is requesting Me(The Partner) to implement a Role Center for his Election Manager user profile. The Election Manager is responsible for following up on sponsor for the upcoming election. We are imaging that there is a big election coming up even though we all know this just finished in the US.
    So the Election Manager need to keep track of the sponsors in his portfolio. Which sponsor has delivered what they have promised? And which sponsor have not delivered? Election manager will need to get a fast overview of which Sponsor he need to follow up on.
    Also the Election Manager needs to setup events and would like to see who is responsible for each of these events. So with this background we start building the Role Center.
    Credit goes to Andy Blehm, our Developer Lead on the Client team, for coming up with this story. Thanks Andy.

    You can find the demo script attached to this post. In this zip file you will find the following files:

    1. Election - Demo Script.docx(This is the actual Demo Script, no proofreading so if there is any spelling mistake please just blame me)
    2. Election Demo Data.txt(Demo Data to import before demo)
    3. Election Objects START.fob(Prepared Objects. These are prepared to keep the demo in 1 hour, many of the prepared demo objects are explained during the demo)
    4. Election Objects START.txt(and in txt format as well if you prefer this)
    5. Election Objects END.fob(All objects included the objects build during the demo. If you just want to see what we build and don't care about the demo script, this is your file )
    6. Election Objects END.txt (and in txt format as well if you prefer this)

    Regards,
    Claus Lundstrøm

  • Microsoft Dynamics NAV Team Blog

    Tip 3: Using Exchange Public Folders and Outlook synchronization in Dynamics NAV 5.0, 5.0 SP1 and 2009

    • 0 Comments

    Many people ask if it is possible to use Public Folders for data storage in regards to the redesigned Outlook Synchronization in releases after Dynamics NAV 4.0 taking into account that there are no conditions or filters set for the contacts entities. The answer is simple: yes, you can! There is one big pitfall. In scenarios where there are thousands of contacts to be synchronized, Exchange Server tend to think that this is not OK. There are some KB published on that topic:
    http://support.microsoft.com/kb/830836
    http://support.microsoft.com/kb/830829/

    There are some more KB articles in regards to event ID 9646, but the two KB articles are more then enough in regards to the blog posting.

    The question I usually ask partners and customer is why they want all the Dynamic related contacts to be synchronized to each and every salesperson? Can you imagine how large these mailbox will become and what that means for the underlying network in Cached Exchange Mode and in non Cached Exchange mode. The answer is in most cases, we did not assign our contacts to a specific salesperson. If those partners or customers try to synchronize the whole Dynamics NAV contacts database to Outlook, then nothing synchronizes! Sometimes partners and customers answers that table 13 is filled with regional codes in stead of real salespersoncodes, like EU1 (Northern European Countries), EU2 (Southern European Countries), etc.  

    In this scenario, one may want to create three Public Folders for the three Contact entities without any condition and configure the Outlook Addin with the three Public Folders. In this scenario, one would still need a dedicated user account plus mailbox so that the Outlook Addin can be configured. Very easy, just create a mailbox enabled user on the Active Directory computer, configure that user in the Login table of Dynamics NAV with the necessary Roles, then configure the new user with at least the two Contact entities (Company contacts and Person contacts), registry that in the change log and you are done.

    In the scenario of separating Scandinavian contacts from Italian contacts and so on, one could create two mailbox enabled users and one could configure those two users with the two contacts entities with a condition defined for each part of Europe. To summarize, this scenario would end up with two user account that are only being used for the Outlook Synchronization and at least four Public Folders.

    Always remember that Tasks and Appointment must be somehow linked to a "salesperson". This is quite logical, because there must be a meeting Organizer and an Owner of the Tasks.

    -- mmels

  • Microsoft Dynamics NAV Team Blog

    Basic SQL - How to attach a database to SQL Server

    • 0 Comments

    This post is part of "Overview of NAV-specific SQL features for application consultants".

    If you receive a SQL Server database file (.MDF), then you must attach this to your SQL Server before you can access it. This is how to do that:

    1)     Copy the Database file (.MDF), for example “Demo Database NAV (6-0)_Data.MDF” into the folder where SQL Server keeps the database files. On SQL Server 2008 this defaults to “C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA”:

    CopyDBFile

    2)     Open SQL Server Management Studio. Note: On Windows Vista / Windows 2008, you must open it as Administrator to have the right permissions. Otherwise the next steps might fail, and maybe without giving you any error messages:

    OpenAsAdmin

    3)     In SQL Server Management Studio right click on Databases, select Attach... click Add, then select the file you copied in step 1, then click OK:

    AddDB

    4)     The database file may be pointing to a transaction log which you don’t have and don’t need. So, before clicking OK again, check if there is a reference to the transaction log under “Database Details”. Remove it if there is, and then click OK:

    RemoveTransactionLog

    5)     If you receive any warnings about adding catalogues, then ignore those. As long as you now see the new database in SQL Server Management Studio:

    DatabaseAttached

    You should now be able to open the database with a NAV client. Note, the SQL backup will contain any user IDs and permissions that were created in the database, so you would need to know what login to use. In the case of Windows logins, of course if the database came from another system / another domain, then any Windows logins will not be valid on your domain.

     

     

    Lars Lohndorf-Larsen (Lohndorf )

    Microsoft Dynamics UK

    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Tip 1: The related item for the synchronized Outlook item is not found in the specified folders. The following values were expected

    • 0 Comments

    When working with the Outlook Synchronization using Dynamics NAV 5.0, Dynamics NAV 5.0 SP1 and Dynamics NAV 2009 (present release: CTP4)
    in an Exchange Server environment, the following very annoying error may show up in the Outlook Addin.

    The related item for the synchronized Outlook item is not found in the specified folders. The following values were expected: /o=CRONUS/
    ou=First Administrative Group/cn=Recipients/cn=marcomels

    The Installation and Setup guide that was compiled for the Partnersource Update gives information about this error on page 41. Here is an extract of that paper:

    <snip>

    When working in Microsoft Exchange environment make sure that the e-mail for the salesperson is defined in Outlook, and not in Microsoft Dynamics NAV. In this
    case, the e-mail will be converted into Exchange format, for example c=us;a= ;p=Cronus;o=Exchange;s=Hill;g=Annette;i=ah.

    · When a contact being synchronized is registered in Microsoft Exchange environment, make sure that the e-mail for this contact is defined in Outlook, and
    not in Microsoft Dynamics NAV. In this case, the e-mail will be converted into Exchange format and the synchronization will work properly.

    · The name and e-mail of the to-do organizer must be filled in and be identical to the name and e-mail of the current Outlook user in order for the to-do
    to be synchronized correctly
    .

    · Choose related contacts and recipients for appointments or tasks exclusively from the folders defined in the synchronization setup

    When creating an appointment or task, choose related contacts and recipients from the folders defined in the synchronization setup. If contacts
    and recipients were selected from other folders, these appointments and tasks will not be synchronized.

    <snip>
    This issue can be easily reproduced in the following scenario:
    1. Windows 2003 or later hosting Active Directory and Exchange 200x Server or later hosting a mailbox enabled user called "Marco Mels"
    2. Windows 2003 or later hosting SQL 2005 or later
    3. Windows XP or Windows Vista hosting Outlook 2003 or later and hosting Dynamics NAV client 5.0 + Outlook Addin 25874 and later releases of
    Dynamics NAV and hosting the Dynamics NAV 5.0 NAS
    To exclude possible permissions error we may run against to, we assign the following roles / permissions to the Marco Mels user:
    1. AD: Domain Admin / Administrator Group
    2. Client: Local Administrator / SUPER user role in Dynamics NAV
    Steps to be taken in Dynamics NAV to finish the scenario:
    1. Synch permissions to SQL
    2. follow localization steps that are written on page 50 of the Installation and Setup guide (alligning system and license files)
    3. enable Change Log Activated via Administration / Application Setup / General / Change Log Setup
    4. if using a localized release of NAV, make sure the language matches the system text files and license files via Tools (menu) / Language, select localized language
    and press OK
    5. fix a small bug that exists in all releases of Dynamics NAV including the 25874 build and the Dynamics NAV 5.0 SP1 build.
    As per this writing, this bug also exists in all local releases of Dynamics NAV 2009 CTP4 release:
    - select Administration / Application Setup / Microsoft Office Outlook Integration / Outlook Synch. Entities
    - press F5 and select APP and press OK
    - under Outlook Collection, we see Recipients and a local value for Links (in Dutch release it is called Koppelingen)
    - select the local value for Links and press F6
    - manually select Links and press OK
    - press F5 and select TASK and press OK
    - under Outlook Collection, we see Recipients and a local value for Links (in Dutch release it is called Koppelingen)
    - select the local value for Links and press F6
    - manually select Links and press OK
    6. now enable the Marco Mels user with the 5 demo entities via Administration / Application Setup / Microsoft Office Outlook Integration / Outlook
    Synch. User Setup

    Outlook Synch User SetupJPG

    Setting conditions is not mandatory but should be done to prevent mass data transfer to a local mailbox or a Public Folder:

    NOTE: if large data is being send to either a mailbox or a public folder in an Exchange environment, an error can occur referring you to the log file which is
    or is not enabled by the adminisrator. It can well be that Exchange is being triggered by a limitation. To workaround that issue, please do see the following
    KB article:
    830829 Your Exchange Server 2003 computer may stop responding after a MAPI client opens more than the default value of certain server objects
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;830829
    7. start up Outlook, create the Outlook profile, create 5 new folders dedicated for the 5 entites and configure the Outlook Addin
    8. perform a Full Synchronization

    Result:
    A new contact item is created for the salesperson
    9. start up Dynamics NAV client and create a simple ToDo of type Meeting and a simple ToDo of type Phone Call for salesperson Marco Mels
    10. using Outlook, perform a synchronization
    Result:
    A new task is being created and a new meeting is being created
    An error occured in the Outlook Addin Troubleshooting window:

    The related item for the synchronized Outlook item is not found in the specified folders. The following values were expected: /o=CRONUS/ou=First
    Administrative Group/cn=Recipients/cn=marcomels

    The reason for this error is that in table 13 Salesperson/Purchaser, the field E-mail 2 is fileld with marcomels@cronus.com. The Outlook Addin however
    expected the following value: /o=CRONUS/ou=First Administrative Group/cn=Recipients/cn=marcomels

    To work around this issue, please do the following:
    From Outlook, open the contact entry and update the E-mail address with the correct values: marcomels@cronus.com. Synchronize back to Dynamics NAV.
    After you have configured the other entities and after you have done the same tests again with the two ToDo's, you sometimes see that the error still occurs.
    The reason for this is that you did not actually select the correct mailbox user out of the Exchange Global Address list.

    To correctly update the E-mail address in Outlook so that E-mail 2 can be filled with the correct values, you will have to do the following. After you have
    synchronized the salesperson to Outlook, open the contact entry in Outlook. Press the E-mail... button next to the E-mail address field and select the correct
    user from the Exchange Global Address List. Save the contact entry and perform a synchronization. Table 13 will now contain two different entries for the
    E-mail and E-mail 2 fields:

    table13

    Now, if you synchronize the two ToDo's, the results will be that the Task and Meetings are created without any further error messages.

    -- mmels

  • Microsoft Dynamics NAV Team Blog

    Tip 2: Fixing language issues when trying to setup the Outlook Synchronization in Dynamics NAV 5.0, Dynamics NAV 5.0 SP1 and Dynamics NAV 2009

    • 0 Comments

    If you are working in a localized release of Dynamics NAV with the redesigned Outlook Synchronization, I bet that you were dealing with a
    language issue. Perhaps you forgot to set the Regional Options for the NAS user account, perhaps you did not follow the Localization Tips
    of the Installation & Setup Whitepaper. Who does not know about the famous page 55 of that guide, please get familiar with this guide
    and download your copy of it today:
    Installation & Setup Whitepaper

    When you are running Dynamics NAV 5.0 SP1 or Dynamics NAV 2009 that will be released later this year, then this guide also applies as well.
    Everything that is written in this guide, applies to later releases of Dynamics NAV. Only NST (Navision Service Tier) and RTC (Role Tailored
    Client) is new and is not part of that Installation & Setup Whitepaper.

    If you start up a localized release of Dynamics NAV, it might well be that the language has been set to ENU. If you do not pay attention to
    that and click on Outlook Synch User Setup or Outlook Synch. Entities, then it might well be that the entities are configured for ENU
    language. Later on, after getting errors in the Outlook Addin, you would think about it and you change it. At that point, you will have
    mixed entities and the Outlook Synchronization will never occur without errors.

    Let me show you a screenshot of a small issue that can cause much troubles though the actual ToDo's are synchronized to Outlook:

    image 

    In another blog I already discussed how to fix this issue. Here is an extract of that blog:

    <snip>

    As per this writing, this bug also exists in all local releases of Dynamics NAV 2009 CTP4 release:
    - select Administration / Application Setup / Microsoft Office Outlook Integration / Outlook Synch. Entities
    - press F5 and select APP and press OK
    - under Outlook Collection, we see Recipients and a local value for Links (in Dutch release it is called Koppelingen)
    - select the local value for Links and press F6
    - manually select Links and press OK
    - press F5 and select TASK and press OK
    - under Outlook Collection, we see Recipients and a local value for Links (in Dutch release it is called Koppelingen)
    - select the local value for Links and press F6
    - manually select Links and press OK
    6. now enable the Marco Mels user with the 5 demo entities via Administration / Application Setup / Microsoft Office Outlook
    Integration / Outlook Synch. User Setup

    <snip>

    Now, let's assume you have started the wrong way and the you configured the Outlook Synch. User Setup with entities for a specific user
    "while the language of the CSIDE Client was set to English".

    image
    In ENU language this is Microsoft Dynamics NAV - Meetings, but in localized release, Meetings is translated to local value!

    There is an easy way to fix this, though it is a bit of work. You will have to clear everything out of the Outlook Synch. User Setup and you
    will have to reset all entities to default. E.g.:
     

    image

    After changing language and after clearing everything from the Outlook Synch. User Setup, the description will change to a local description.
    This will have to be done this in order of CONT_SP, CONT_COMP, CONT_PERS, TASK and finally APP. Of course, in the next screen you must
    select the correct choice:"

    image 

    After this has been done, you need to add the user and configure the required entities, the number of Elements, the conditions, etc. At the
    end, register the changes in the Change Log via:

    image

    Now the Outlook Synchronization has been correctly setup for any localized release of the CSIDE Client.

     

    --  mmels

Page 1 of 2 (19 items) 12