A few times we have seen case where changes to NAV objects are not seen in RTC until the NAV Server is restarted. This post explains a likely solution to such problems. And as we are on the topic anyway, also explains a bit about the process that turns C/AL code into Metadata (c#).
When you save an object in Object Designer, then table 2000000071 "Object Metadata" is updated as well. An easy way to see this part of the process in action is:Run table 2000000071 and delete Page 1 (or any object). Then compile Page 1 from Object Designer. Then check that it has been re-created in table 2000000071.
If you look at this table in SQL Server Management Studio, you can see that it has SQL triggers which update the table "Object Tracking". It is the "Object Tracking" table that NAV Server uses to see when an object has changed, so that it knows to send updated Metadata to RTC.
There are two ways that the NAV Server can get updated on activity in the "Object Tracking" table: SQL Server Broker Polling
If SQL Server Broker is enabled, then NAV Server will rely on that to notify it when an object has changed. You can see whether the broker is enabled from SQL Server Management Studio under Database Properties, then under Options look for "Broker Enabled".
If the broker is enabled, then when NAV Server starts up and on first activity (When first RTC connects), it will create a queue and a service under SQL Service broker. You can see this in SQL Server Management Studio under the NAV Database if you expand Service Broker. Here, under Queues and under Services you will see new objects with names like SqlQueryNotificationService-27b7fb21-74a7-4a63-876a-c96b8eecd583. These are created by NAV Server and removed again when NAV Server stops. Their job is to listen to the "Object Tracking" table and notify NAV Server when there are any changes there.
If the broker is not enabled, then NAV Server will use polling, i.e. check for changes every now and then. You can see this in two ways. First in the Application Log the NAV Server will log this event shortly after startup:
Service: MicrosoftDynamicsNavServerSQL Query Notifications are unavailable on SQL Server '.' in Database 'NAVDatabase'. The Object Change Listener has switched to polling.
Secondly, when NAV Server is in polling mode and you start SQL Profiler you will see this query being run regularly by .New sqlClient Data Provider:
exec sp_execute 1,@lastKnownTimeStamp=463223
This is a pre-prepared query which looks like this:
SELECT [Object Timestamp], [Object Type], [Object ID], [Object Key] FROM [dbo].[Object Tracking] WHERE [Object Timestamp] > @lastKnownTimeStamp',@lastKnownTimeStamp=463225
In polling mode, this is how NAV Server checks for object changes since last time it checked, so it knows whether to send updated object definitions (metadata) to RTC.
So that's the background. How do we handle the case where object changes are not seen in RTC until we restart NAV Server? If this problem happens, then switch method from SQL Broker to Polling or visa versa. You switch by enabling / disabling the broker like this:
ALTER DATABASE [MyNAVDatabase]--SET ENABLE_BROKER SET DISABLE_BROKERWITH ROLLBACK IMMEDIATE
The "WITH ROLLBACK IMMEDIATE"-part of this query is to avoid what happened for me that the query just hang, or would only run if the database was put in single user mode.
In the cases we have seen, the broker has been enabled but had some kind of problem. A slightly cryptic message was recorded in the SQL Server Log every time an object was changed in Object Designer. This message was not logged in the Application log. So make sure to check in SQL Server Management Studio under Management -> SQL Server Logs -> Current. If this gives enough information to solve the problem then good. If not, then there is the option of switching to polling (disable the broker) until the root of the problem can be resolved.
Enabling the broker is the preferred option since it saves NAV Server for checking ever so often for object changes. But if the broker doesn't work then at least there is the option to disable it until any problems can be resolved.
Dynamics NAV Support EMEA
In "old good classic" NAV we have feature (and property) named AutoFormatType. Together with property AutoFormatExpr we can set decimal to any format we need.This could be done in the way: we describe new AutoFormatType in the codeunit 1 in function "AutoFormatTranslate(AutoFormatType : Integer;AutoFormatExpr : Text) : Text)"For example I can describe that i want to have decimal in the way 1 234 567,89 and doesn't matter what regional settings i have, or i want to format decimal depending on "customer on sales invoice" language but without changes regional settings. So this works in Classic Client reports but it doesn't in RDL, because RDL format decimals according regional settings or if say more precisely: according .NET culture settings.
But what to do if we want to format decimal on the same report regarding some national rules?We want to have:1 234 567,89or1.234.567,89or1,234,567.89
in ReportLayout design we can find that every Textbox has property named Language. Value could be: Default, Afrikaans,Afrikaans (South Africa) and etc. So if we for example set Language = French, then decimal will be 1 234 567,89; if Language=German (Germany) - number will be 1.234.567,89, if Language=English (United States) - number 1,234,567.89 and etc.
But this is "hardcode" language set. What if we want to set language dynamically? Lets say i want to set report language in request page and choose between: Default; English (US), German, French formats.
Then I open report in report designer and created variable "OptLang" type option and OptionString is Default,English (US),German (Germany),French.
Add this variable to Request page:
And in the sections I add textbox which SourceExp=OptLang and DataSetFieldName = Lang. Dont forget to set this textbox Visible=No as we need this textbox only to transfer value to RDL report.
Now in the RDL reportlayout we see datafield "Lang" in DataSet. It value could be the same as we set in option string, but ATTENTION: we can't directly fill this value to property Language. This property accepts only values as it are described in http://msdn.microsoft.com/en-us/goglobal/bb896001.aspx. So it is French we need to assign "fr-FR"; is English (US) then "en-US"; if German (Germany) then "de-DE"Easest way to do that is to create new public Function in report properties code. This function returns correct value depending on value we send to it.
Now return to RDL sections, select whole row (or all textboxes) and in language parameter write in expression: =code.SetLang(Fields!Lang.Value)
Now save and compile everything.Run report from RTC.In open request page chose language you want and preview report.
You see that now decimals are displayed with separators we want.
That is all what i want to show.BTW: how number will be displayed you can set in Format property by describing string like it is described at http://msdn.microsoft.com/en-us/library/dwhawy9k(v=VS.95).aspx
Special thanks to Claus and Roman :)
These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.
Microsoft LithuaniaMicrosoft Customer Service and Support (CSS) EMEA
With introduction of RDLC reports, we've seen number of questions being raised recurringly around specific reports' features, or lack of them.
We've tried to collect the most recurring ones in one place and hope you might find this useful. if you have questions and/or answers you think should be added to this list, please share them with us.
The document and the post will be updated regularily as new questions pop up, so if you find this useful, make sure to check the blog from time to time for an udpated version.
Questions can be posted here, or directed to our OTC (Online Technical Community) forum on Dynamics NAV MPN site:
Lars & Jasminka
Q: Why is my report header / footer blank? And why is it blank only in preview but not when I print, or visa versa?
A: If Visibility property is conditionally set, the header / footer might not show or show differently in preview compared to print. Or it may even show differently in preview if you zoom in or out. For more details about rendering behaviour check this link:http://msdn.microsoft.com/en-us/library/bb677573(v=SQL.100).aspx
Q: Can I change the decimal formatting of the report?
A: You can overwrite the formatting of each decimal field in the layout using the format property, or format the decimal like this: =replace(Replace(FormatNumber(Fields!MyNumber.Value),","," "),".",",")
Q: Can I send parameters to classic report run from RTC?A: NO. Once classic engine is invoked, there is no communication between the two, parameters can’t be passed to classic report.
Q: Is there a limit to number of records printed by report?A: No. But if selecting to print many records, you may end up consuming all available client memory, and RTC may hang/crash. Alternatively, redesign or filter the report, or increase the page file.
Q: Is it possible to run a report modally from RTC?
A: Yes, but only if the report has a layout defined. If the report runs as classic, then RUNMODAL does the same as RUN, i.e. RUNMODAL will not wait until the report is complete before running the next line(s) of C/AL code. Depending on the exact scenario, consider:a) Define layout for the reportb) Split the process into two. So the user first runs the report, and then when the report is complete they launch the next process to do whatever needs to be done with the report.c) Add IF NOT CONFIRM('Please press Yes to continue when the report has finished.');
Also refer to this post:http://blogs.msdn.com/b/nav/archive/2010/10/20/running-classic-reports-in-rtc-runmodal-and-a-few-other-things.aspx
Q: Does a classic report consume a license?
A: No. If you look carefully under sessions (or run sp_who2) while a classic report is running from RTC, you can see that it logs in using "Application Name" = '111'. Such sessions do not count against licensed sessions.
Q: How can I find a specific control / anything in my report layout?
A: The layout has a feature called "Document Outline" which lists all controls, letting you find the control you are looking for in a list instead of trying to find it somewhere in the layout where it might be hidden in or under a table or some other control. Open the layout in Visual Studio from View -> Other Windows -> Document Outline.
Q: I still cannot find a control in the layout. I'm certain that it is on the sections but I cannot see it in the dataset in the layout.
A: To minimize the dataset, if two or more controls in sections have the same SourceExpression, only one of them will get included in the dataset.
Sometimes, you want to make sure that your personalizations and customizations are not changed by a user. In Microsoft Dynamics NAV R2, and builds of Microsoft Dynamics NAV SP1 31671 and higher, you can specify a new startup parameter setting that allows you to control this:
You can do this from a command prompt and from shortcuts that you create for profiles. For more information about how to start the RoleTailored Client from a command prompt, see the following help topic: Starting the RoleTailored Client from a Command Prompt.
The DisablePersonalization startup parameter has the following effects on the user experience:
A user's personalizations are not applied when you start the RoleTailored client from the command line using the DisablePersonalization parameter.
An often requested feature is automatic download of currency exchange rates, and this short article illustrates how you can build your own using RSS feeds and MS XMLDOM 6.0 which should work with all NAV versions 2.0 to 2009R2. Going forward, you should consider using DotNet types instead.
There are a lot of RSS feeds out there, some free and some not. The basis of all of them is that you can simply load them into an xml document and parse the data from there. The good news is that the RSS format is standardized. The bad news is that every RSS feed provider has a lot of freedom to specify the data as they choose. Therefore we will just look at one example using Yahoo, and you may build your own from there.
If you go to e.g. http://pipes.yahoo.com/pipes/pipe.run?_id=_kHVIdZ13hGXgt1VwmH_9A&_render=rss you will see a list of currencies and their exchange rates relative to USD as shown here:
If you download the rss feed and save it as an xml file, you get a layout similar to this:
<rss version="2.0" xmlns:geo="http://www.w3.org/2003/01/geo/wgs84_pos#" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:media="http://search.yahoo.com/mrss/" xmlns:yt="http://gdata.youtube.com/schemas/2007">
<title>USD Exchange Rates</title>
<description>Grabs USD exchange rates from Yahoo Finance and outputs as RSS feed.</description>
<pubDate>Mon, 18 Apr 2011 07:55:57 +0000</pubDate>
<pubDate>Mon, 18 Apr 2011 09:00:00 +0000</pubDate>
<pubDate>Mon, 18 Apr 2011 10:53:00 +0000</pubDate>
And so on...
The task is then just to parse the information.
I have attached two objects in text format, table 50000 and codeunit 50000 that illustrate how you might implement some code that parses the input and updates the currency exchange rate table.
NOTE!! The code is sample code and serves only the purpose of illustration and cannot be guaranteed to work or to be correct, and Microsoft does not take any responsibility in how the code is utilized. Furthermore, there may be legal or financial responsibilities in utilizing a particular RSS feed, and the use of Yahoo's RSS feed is only one example.
We have released the Help source files for Microsoft Dynamics NAV 2009 R2 to PartnerSource.
This release supports the following countries:
We have also released the Help Toolkit for Microsoft Dynamics NAV 2009 R2 to PartnerSource.
With these source files and the Help Toolkit, you can modify the Help files for Microsoft Dynamics NAV 2009 R2 to match customizations that you create.