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: KB2558650Find 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 R2PartnerSource: 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:
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”
5. Create new function with Name=”OnReportRun”
6. Open Properties and change ID to 120
7. Now open “Locals” and create parameter= ReportId with Type=Integer
8. Select Variables tab and create ReportLog
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;
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:
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.
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:
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.
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 SetEnd Property
If all works out well you’ll end up with an Add-in control looking something like this. (See video)
-Johan Emilsson
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
2. Create DataItem ”Customer”
3. Go to Section Designer and add the following fields:
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)
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)
11. 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”).
12. 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)
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
13. 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
14. And then I set distinctive BackgroundColor and font Color just so this Transfooter and Transheader stand out in my report
15. 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))
16. 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:
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.
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:
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:
3. Click View >Properties, and then set the SourceExpr to Rec > Field Name > System To-do Type.
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):
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.
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:
You can download the objects needed for this report here, thanks to Anna Mihailava.
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.
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".
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.
So the charts we are concerned with here, are the system charts. Their characteristics are:
To make the most of them, use their simplicity as a strength and don't try to make them do too much.
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:
These areas are specifically designed to give a simple and personalized overview, so perfect for charts.
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:
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.
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 is attached just below as a txt file. It contains these objects:
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
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 :
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
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:
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:
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.