June, 2011

  • Microsoft Dynamics NAV Team Blog

    Updated: How to log report usage

    • 27 Comments

    Updated: Now with links to both NAV 5.0 and NAV 2009 hotfixes

     /Claus Lundstrøm

    ******

    In several partners meetings I have heard this requests over and over again.

    Claus, when we are planning an upgrade of Classic reports to RDLC reports, we do not always know which reports are actually being used at the customer site. It would be great to be able to log which reports are used so we know exactly which reports we need to upgrade to RDLC.

    Well, if you download below hotfix you will now have the capability to log report usage at a customer site.

    Dynamics NAV 5.0: KB2575296

    Dynamics NAV 2009: KB2558650
    Find links to all NAV 2009 Platform Hotfixes here:
    CustomerSource: Overview of Released Platform Hotfixes for Microsoft Dynamics NAV 2009 SP1 and Microsoft Dynamics NAV 2009 R2
    PartnerSource: Platform Hotfixes for Microsoft Dynamics NAV 2009 SP1 and Microsoft Dynamics NAV 2009 R2

     

    In the following steps I have outlined which steps you need to do to get this log file. Remember above hotfix is required to perform the following steps.

    1. Create new table to be used for Log report usage:

    clip_image002

    OBJECT Table 50000 Report Log
    {
    OBJECT-PROPERTIES
    {
    Date=22-06-11;
    Time=14:19:58;
    Modified=Yes;
    Version List=CLAUSL;
    }
    PROPERTIES
    {
    }
    FIELDS
    {
    { 1 ; ;No. ;Integer ;AutoIncrement=Yes;
    MinValue=1 }
    { 2 ; ;User ID ;Code50 ;TableRelation="User Role"."Role ID";
    CaptionML=ENU=User ID }
    { 3 ; ;Report ID ;Integer ;CaptionML=ENU=Report ID }
    { 4 ; ;Report Name ;Text249 ;FieldClass=FlowField;
    CalcFormula=Lookup(AllObjWithCaption."Object Caption" WHERE (Object Type=CONST(Report),
    Object ID=FIELD(Report ID)));
    CaptionML=ENU=Report Name }
    { 5 ; ;Date Time ;DateTime }
    }
    KEYS
    {
    { ;No. ;Clustered=Yes }
    }
    FIELDGROUPS
    {
    }
    CODE
    {
    BEGIN
    END.
    }
    }

    2. Now with the table created for our Report Usage log please open Codeunit 1

    3. Open “C/AL Globals”

    4. Navigate to ”Functions”

    clip_image004

    5. Create new function with Name=”OnReportRun”

    clip_image006

    6. Open Properties and change ID to 120

    clip_image008

    7. Now open “Locals” and create parameter= ReportId with Type=Integer

    clip_image010

    8. Select Variables tab and create ReportLog

    clip_image012

    9. Now all we need is to write the code for this new trigger. Open C/AL Editor and navigate to the end.

    10. In OnReportRun write the following code:

    ReportLog."User ID" := USERID;

    ReportLog."Report ID" := ReportId;

    ReportLog."Date Time" := CURRENTDATETIME;

    ReportLog.INSERT;

    clip_image014

    11. Now Restart Classic Client

    12. Run a couple of reports

    13. And then at last run the Report Log table to see the result:

    clip_image016

    Yes I’m aware that this solution only works for Classic Reports, and yes I also would like a feature so it’s possible for you to log all objects being used at a customer site. For now we do not get this, but let’s see what the future brings.

    /Claus Lundstrøm

  • Microsoft Dynamics NAV Team Blog

    Transfooter and Transheader functionality in RDLC(SSRS) reports - revisited

    • 8 Comments

    In one of our previous blog post we discussed the possibility to do Transfooter and Transheader functionality in RDLC(SSRS) reports and describes a viable solution for this in RDLC.

    In this blog post we would like to suggest an alternative, a bit more economical and easier to implement solution for the same problem.

    For the demo we use the same table and the same report and will strive to achieve the same results as in the mentioned in our previous blog post.

    1. Create new report blank report with table 18

    clip_image002

    2. Create DataItem ”Customer”

    3. Go to Section Designer and add the following fields:

    • No.
    • Name
    • Debit Amount

    4. Save the report as ID 50000 – Transfooter / Transheader

    5. Now go to Visual Studio (View / Layout)

    6. Create table and add the fields No, Name and Debit Amount

    7. Give this table the name "MainTable"

    8. Now we have added the basic for this report. But I would also like to have a Grand total of the Debit Amount so I add this as well. I add this in the Footer of the table

    ="GrandTotal: " & sum(Fields!Customer__Debit_Amount_.Value)

    image

    9. Now if my report is printed I get a list of my all my customer with Debit Amount displayed and with GrandTotal in the end of the report:

    10. Now I create a small block of VBS code in order to perform some calculations and store intermediate data

    Open “Report->Report Properties” dialog and select “Code” tab, enter the following VBS code:

    11. Define a hashtable for storing running accumulated sums for each page of the report

    Shared RunningTotals As New System.Collections.Hashtable

    12. Define two public functions, which populate and query the hashtable from above

    Public Function GetRunningTotal(ByVal CurrentPageNumber)

    Return IIF(CurrentPageNumber > 0, RunningTotals(CurrentPageNumber), 0)

    End Function

     

    Public Function SetRunningTotal(ByVal CurrentPageTotal, ByVal CurrentPageNumber)

    RunningTotals(CurrentPageNumber) = CurrentPageTotal + GetRunningTotal(CurrentPageNumber - 1)

    Return RunningTotals(CurrentPageNumber)

    End Function 

    image

    13. Ok, it’s now time to add a Transfooter and Transheader.

    Enable Page Header and Page Footer in the report (click “Report->Page Header” and “Report->Page Footer”).

    14. In the Page Footer I place a text box with the following expression:

    ="Transfooter subtotal = " & Code.SetRunningTotal( Sum(ReportItems!Customer__Debit_Amount_.Value), Globals!PageNumber)

    image

    This code actually performs the following actions:

    - calculate the sum of all “Debit Amount” values on the current page (sic)

    - adds this value to the running total, which has been already calculated for the previous page

    - returns this value as the actual running total for the current page

    15. In the Page header I place a text box with the following expression:

    ="Transheader subtotal = " & Code.GetRunningTotal(Globals!PageNumber-1)

    This code fetches the running total, calculated up to the previous page

    image

    16. And then I set distinctive BackgroundColor and font Color just so this Transfooter and Transheader stand out in my report

    image

    17. Now I’m almost done but I would like to not see the Transheader on the first page and not to see the Transfooter on the last page.

    So I set the following expressions for the “Visibilty->Hidden” properties of the page header:

    =IIF(Globals!PageNumber > 1, False, True)

    And for the page footer:

    =IIF(Globals!PageNumber < Globals!TotalPages, False, True)

    18. Now I’m done, I save, import into NAV and compile. After some fit and finish on the report it now looks like this when I print

    Now I’m done, I save, import into NAV and compile. After some fit and finish on the report it now looks like this when I print:

    clip_image027

    clip_image029

    Question: Would this also work in the example of having a list of sales order lines per sales header and the sales order lines goes to multiple pages?

    Answer: The report above is a bit simplified in order to illustrate the point. It can be easily extended to support your scenario. I.e. the key for the hash should include page number AND header no to accomplish this.

    You can download the report object here, thanks to Nickolay Belofastow.

    /Claus Lundstrøm

  • Microsoft Dynamics NAV Team Blog

    New Excel-Based Reporting Tool for Microsoft Dynamics NAV

    • 8 Comments

    It’s no secret that Microsoft Dynamics NAV customers and partners have needed an ad hoc reporting solution that’s easy to use. Well, now we have one!

    We’ve been working in cooperation with Jet Reports, Inc. on the development of an Excel-based reporting solution. The result is Jet Reports Express for Microsoft Dynamics NAV – a simple but effective business reporting tool that gives customers an easy and simple way to create high impact reports and helps us to enhance our BI & Reporting value proposition.

    Within a familiar Microsoft Excel environment, users will be able to access Microsoft Dynamics NAV data and utilize all of the Excel capabilities, such as Power Pivot, formatting, charting and Pivot Tables, to create powerful, insightful and well-formatted reports. There are multiple report templates available out of the box.

    Plus, users will be able to answer and analyze ad hoc business queries with real time data from Microsoft Dynamics NAV. It’s possible to access and combine data from NAV – including tables, fields, flow fields and dimensions, and you can slice and dice data and do consolidation. You can also drill down into any value in a report to see the underlying data with just one click.

    Jet Reports Express for Microsoft Dynamics NAV really is simple to work with and very easy to demo. It’s the perfect solution for the majority of the Microsoft Dynamics NAV customers who need a basic ad hoc reporting solution.

    Jet Reports Express for Microsoft Dynamics NAV will be available to Microsoft Dynamics NAV customers as new functionality at no additional costs, provided they are on an active Business Ready Enhancement Plan.

    The product can be downloaded via a link from PartnerSource and CustomerSource that will be available sometime in Q3 CY2011. Look out for more information on final release date.

    Stay tuned for more information about all the cool things customers can do and partners can demo with this smart reporting tool.

  • Microsoft Dynamics NAV Team Blog

    Currency Exchange Rates from RSS (using .NET Interop)

    • 3 Comments

    This great blog post from Bardur Knudsen (Currency Exchange Rates from RSS) sounded to me like an invitation to extend it using .NET interop variables with Microsoft Dynamics NAV 2009 R2 based on System.XML namespace.

    http://msdn.microsoft.com/en-us/library/system.xml(v=VS.80).aspx

    Attached to this blog you will find 3 objects in TXT format (1 table, 1 codeunit, 1 page).

    Inside the codeunit there is a useful code that may be worth looking at.

    This code has been written to perform the same action and achieve the same result BUT using 3 different approaches in order to understand how .NET interop works compared with Automation Server usage.

    Those 3 different approaches are:

    1. Use System.XML dotNet variables running at Service side (property RunOnClient = No)
    2. Use System.XML dotNet variables running at Role Tailored Client side (Property RunOnClient = Yes)
    3. Use MSXMLDOM automation (as it was in the previous blog post) at Client side.

     

     

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

    Duilio Tacconi (dtacconi)

    Microsoft Dynamics Italy

    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Let NAV BEEP! (with .NET Interop and Microsoft Dynamics NAV 2009 R2)

    • 3 Comments

    It is known that in Role Tailored Based environment BEEP C/AL function is not supported.

    http://msdn.microsoft.com/en-us/library/dd301405.aspx

    This simple blog is based on SystemSounds Class from System.Media namespace

    http://msdn.microsoft.com/en-us/library/ms143809.aspx 

    My Ingredients :

    • NAV 2009 R2 Role Tailored Client
    • Windows 7 Enterprise

    Attached you will find 1 unbounded page object in TXT format.

    The code is fairly simple: there are just 5 lines of code related to 5 page actions.

    dnBeep.Asterisk.Play();

    dnBeep.Beep.Play();

    dnBeep.Exclamation.Play;

    dnBeep.Hand.Play();

    dnBeep.Question.Play();

    In order to have more fun with this object, I invite you to edit Control Panel:

    Control Panel\Appearance and Personalization\Personalization

    And push the sounds Action button (NOTE: normally “Question” is not present in system Themes) while switching between themes.

    REMEMBER: Raise high the volume of your earphones / speakers.

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

    Duilio Tacconi (dtacconi)

    Microsoft Dynamics Italy

    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Chart Generator Tool for RTC (CGTRTC)

    • 3 Comments

     

    Based on feedback from these previous posts:

    3D charts. Chart Generator tool II

    NAV 2009 - How to generate charts / KPIs

    Here is a version of the tool for RTC (with pages). In fact, this time I removed the forms. There is nothing special added to the tool compared to last time, except for the pages, so if you do want to still run it in the Classic Client, then you can get the forms from the previous version.

     

    But first a few general things about Charts.

    What Charts?

    This post concerns the charts that live in table 2000000078 "Chart" (Departments/Administration/Application Setup/RoleTailored Client/Charts" (page 9182)). These charts can be added as a system part to a role center or via "Customize this page".

     

    What other charts exist?

    Other charts are not the topic for this particular post. But just to explore the alternatives, more advanced charts can be created using Client Add-ins, examples of which you can find here:

    More Charts  
    Add-ins

     
    I'm sure there are more ways to add charts in NAV. Please feel free to add comments about other ways and also your experience with this and other types of charts.

     

    Which charts?

    So the charts we are concerned with here, are the system charts. Their characteristics are:

    • Inbuilt - no external components needed, everything is built into RTC.
    • Simple - good at showing a simple overview, but advanced BI they are not. 
    • Limited -  They can be based on only one table and have limited scope. 

    To make the most of them, use their simplicity as a strength and don't try to make them do too much.

     

    Ideas for charts

    My experience with charts is that they are easy to overcrowd and best kept simple. Trying to come up with ideas for simple charts, one only has to look at the Role Center - designed to give a role based, quick overview. Some of the features of the default Role Center are:

    • Activities - Numbers of orders, quotes, etc
    • My-Xyz - My Customers, My Items, etc

    These areas are specifically designed to give a simple and personalized overview, so perfect for charts.

     

    Activity Charts (Charts based on Cue tables)

    This example shows how to make a chart to show Activities in a chart like this:

     

    To get charts based on Cue tables:

    After importing the objects attached below, start with Page 72000 "Chart Generator List". In my case I added it to Home Items under Page Actions in my default Role Center 9006 as shown here:

     


    Then click "New" to create a new chart:

     

    Enter ID and Name. For table, select table 9053 "Sales Cue", and for X-Axis, just select "Primary Key". The chart needs to have an X-Axis, but in this case the X-Axis information is not really important. So it should look like this:

     

    Then we add a column (Y-Axis) for each activity type we want in the chart - in this example we have three columns:

    • Ready to Ship
    • Partially Shipped
    • Delayed

    Do this by clicking the Y-Axis action, then select those three fields.

    This would be enough, but if you created the chart now, then it would give you a different picture than the Activity Center. That's because the activities apply a Date Filter. If you want to apply the same filter in the chart, then just click on Filters, and set "Date Filter" = 01011900..W. This will show you all orders from 1900 until Workdate.

    Once you have completed the chart, click "Generate Chart". This will create a new system chart, using the Name you typed in as ID. Or if this chart already exists, then "Generate Chart" will overwrite the existing one.

    The last thing you need to do, is to add the chart to your Role Center, either via "Customize this page" from your Role Center (Charts can only be added to the Role Center, not to any other page type), or by designing your Role Center and adding a part of type Chart.

     

    My-Charts - Chart based on My Customers

    Here is the next example: Show the customers under "My Customers" plus a little additional information, for example Balance (LCY):

     

    This chart requires a little bit of preparation in the form of table design to begin with. First: The field you want to see in the Y-Axis (in this example Balance (LCY)) has to be added to table 9150 "My Customer". In this table you can basically copy any flowfield from the Customer table. I created a new field 50.000 called "Balance (LCY)", FieldClass = FlowField, and CalcFormula = Sum("Detailed Cust. Ledg. Entry"."Amount (LCY)" WHERE (Customer No.=FIELD(Customer No.))).

    Secondly we need to filter the table by User ID. In this case we cannot set the filter in the chart itself because there is no way in the charts to filter on USERID. But, this is what you can do instead: In Codeunit 1, apply a global filter. This will take effect also for charts. This is the code you would need:

    Codeunit 1:


    CompanyOpen()
    IF GUIALLOWED THEN
      LogInStart;

    // == begin ===
    MyCustomer.FILTERGROUP(1);
    MyCustomer.SETRANGE("User ID",USERID);
    MyCustomer.FILTERGROUP(0);
    // == end ===

     

    FILTERGROUP 1 is used for applying a global filter. When setting this filter in the CompanyOpen function, it will be applied to the whole session whether you run NAV from Classic or RTC.

    Now, even charts will only see customers belonging to the current user.

    So, finally, this is how you could create the chart in the tool:

     

     

    The tool

    The tool is attached just below as a txt file. It contains these objects:

    • Table 72000 Chart Generator
    • Table 72001 Chart Generator Filter
    • Table 72003 Chart Generator YAxis
    • Codeunit 72000 Chart Generator Mgt
    • Page 72000 Chart Generator List
    • Page 72001 Chart Filters
    • Page 72003 YAxis List
    • Page 72004 Chart Generator Card

    I have added "DeleteMe" in front of every object ID in the txt file. So before importing it you must open the file in NotePad, then find all "DeleteMe", check that the Object ID and name is OK, and then delete "DeleteMe". Once you are OK that all the object IDs in the txt file, then import it.

    Note!! When importing a txt file into NAV, it will replace existing objects without any warning.

     

    And finally: This tool is unsupported. These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

     

    Lars Lohndorf-Larsen

    CSS EMEA

     

  • Microsoft Dynamics NAV Team Blog

    Using Standard Costs on an SKU (Stockkeeping Unit)

    • 1 Comments

    Currently, the standard cost is only SKU specific for purchased Items. In order to calculate manufacturing variances it is necessary to have the following fields, which are only present on the Item Card: Single-Level Material Cost, Single-Level Capacity Cost, Single-Level Subcontrd. Cost, Single-Level Cap. Ovhd Cost, Single-Level Mfg. Ovhd Cost.

    In order to handle a situation where there are different production costs per SKU, an option to define BOM and routing details by SKU would be required and this is not currently available.

    Please see the following potential workarounds for this situation:

    1. An Item in combination with a Variant and/or Location, which carries a specific Standard Cost could be created as a unique item on an Item Card.
    2. If the user only consumes materials in the manufacturing process, a customization could be created to use the Standard Cost on the Stockkeeping unit record. Please note: a possible drawback to this approach, is that only one Variance G/L account is available per Item and Location combination. Variance per Item Variant could not be posted and traced to a separate G/L account without further customizations.
    3. If additional cost elements are used in the calculation of the final cost of a produced item, the fields that are currently only present on the Item Card (Single-Level Material Cost, Single-Level Capacity Cost, Single-Level Subcontrd. Cost, Single-Level Cap. Ovhd Cost, Single-Level Mfg. Ovhd Cost.), should be implemented on the SKU card and maintained in the same way as on the Item Card.
  • Microsoft Dynamics NAV Team Blog

    Presence Control Add-In in Microsoft Dynamics NAV with Lync

    • 1 Comments

    Some may have seen this Add-In somewhere already as it has been around for some time. This is a high level explanation to how it was actually done.

    First thing to mention here is that the base for this Add-In is a publicly available sample of Presence Controls intended for Office Communicator 2007. You can find the Sample Code, prerequisites and updates as well as related stuff here: Microsoft Office Communicator 2007 Presence Controls. You can use these Presence Controls with either Microsoft Office Communicator 2007 or with Microsoft Lync.

    So in order to build this Sample Presence Add-In Control for Dynamics NAV, you can simply use the output from the above sample, which is a Set of Presence Controls contained in – PresenceControls.dll

    Next, create a new Project and include a reference to the Presence Control Set – this is the Dynamics NAV Add-In Project.

    For more details on the basics structure and references for a Dynamics NAV Add-In: How to: Create a RoleTailored Client Control Add-in.

    After that you have your CreateControl() function which is returning the Add-In control to NAV. The two main things you want to return here are the Objects to be shown in your add-in on screen together The Presence Control and a Textbox: 

    Dim persona As New persona ‘the presence control, a persona object from the Presence Controls Set

    Dim txtbox As New System.Windows.Forms.TextBox ‘a regular text box

    In order to display these two controls nicely “as one” you may want to place them in some kind of parent container/containers to have them Size and move properly together. I used System.Windows.Forms.TableLayoutPanel but there are probably better ways to do this.

    Finally you will have to need to ensure the control is updated properly:

    The Persona Control has a SipUri property, which is basically what you need to bind your Add-In Value to, so you need to ensure that changes to the value are passed both to the Textbox and the Persona Object.

    As usual there are a number of ways some nicer than other to achieve this, you could either bind the Persona property SipUri to the Textbox Value itself or handle the updates of the value to both controls manually in your add-in, but the basics are to simply ensure the SipUri of the Persona always needs to be kept as the same as the value of the TextBox, for example:

    ' Copyright © Microsoft Corporation. All Rights Reserved.
    ' This code released under the terms of the
    ' Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)
    Public Property Value() As String Implements Microsoft.Dynamics.Framework.UI.Extensibility.IValueControlAddInDefinition(Of String).Value
            Get
                Return txtbox.Text
            End Get
            Set(ByVal value As String)
               persona.SipUri = value
               txtbox.Text = value
           End Set
    End Property

    If all works out well you’ll end up with an Add-in control looking something like this. (See video)

    -Johan Emilsson

  • Microsoft Dynamics NAV Team Blog

    Sales Dashboard report updated

    • 0 Comments

    In one of our previous blog posts Rene Gayer shared his report Sales Dashboard. The report provides insight into company’s top customers and items, balance per country region, and item purchases/sales:

    The report also shows the list of opportunities, and you can drill into to-dos for each of the opportunities:

    image

    In this blog post we offer several improvements for the Sales Dashboard report. The first thing is adding an extra column to the list of to-dos. Note, that there are two occurrences of each to-do in the list, they have different numbers, but the description is same (compare to-dos TD000005 and TD100005 on the screenshot above). It is not clear why until we make the column “Type of to-do” visible. The reason is that each to-do has several participants, for example Organizer and Contact Attendee. To make the column “Type of to-do” visible, you need to do the following:

    1. In the Object Designer, select page 70002 Sales Dashboard To-do Part, and then click Design.

    2. Add a new field to the page:

    image

    3. Click View >Properties, and then set the SourceExpr to Rec > Field Name > System To-do Type.

    clip_image006

    4. Click OK, and compile the page.

    Furthermore the to-dos appear sorted by their number. We offer to sort them by starting date, which is helpful when you want to get a quick overview of the latest to-dos. That is how you can add sorting to the list:

    1. In Object Designer, select table 5080 To-do, and then click Design.

    2. Click View > Keys.

    3. Add new key, which is a field Date (Starting Date):

    image

    4. Compile the table.

    5. Select the page 70002 Sales Dashboard To-do Type, click Design.

    6. Click in the first empty row, and then click View > Properties

    7. In the SourceTableView field, click the Assist button.

    8. Set the Key to Date, and the Order to Descending. Click OK.

    clip_image009

    9. Close the Properties window and compile the page.

    The final result looks like this – the to-dos are sorted by date and To-do Type is visible:

    image

    You can download the objects needed for this report here, thanks to Anna Mihailava.

    /Claus Lundstrøm

  • Microsoft Dynamics NAV Team Blog

    New White Paper Available: Prepayments

    • 0 Comments

    For those of you considering implementing the prepayments functionality in Microsoft Dynamics NAV, a whitepaper has been released to help you sort through the scenarios the feature is designed to address. Prepayments enable you to create and post invoices for advance payments (prepayments) that your company may require before it opens a sales or purchase order. This white paper provides an overview of the current implementation, and explores areas that may be enhanced in future releases. In addition, the white paper notes some of the known limitations.

    To review the white paper, go to:

  • Microsoft Dynamics NAV Team Blog

    Timeline Business Data Visualization in Inventory Projection for DynamicsNAV 2009 R2

    • 0 Comments

    GoldenCaller

     

    Just have released a new improved release of the Timeline visualization for DynamicsNAV 2009 R2
    on Partner Source and Customer Source!

     

     

    In this update release: New UI features, new interactions, much more flexible API for application developers.

    image

    Get all the details here!

     

     

Page 1 of 1 (11 items)