Microsoft Dynamics NAV Team Blog

Table Information including Index information (Usage, Blocks and Reads) - Again

Lars Lohndorf-Larsen — Wed, 08 Feb 2012 11:01:00 GMT

This is only the same query which is already here:

Table Information including Index information (Usage, Blocks and Reads)

But I noticed that the query somehow got mal-formed to a point where it could not run. So here is a cleaned-up version.

In summary: This query shows a list of all tables and indexes in a SQL database to help identifying in which tables the most blocks happen, and to give a starting point for index tuning. For further details, refer to the original blog post.

--use NAVDatabase

IF OBJECT_ID ('z_IUQ2_Temp_Index_Keys', 'U') IS NOT NULL
DROP TABLE z_IUQ2_Temp_Index_Keys;

-- Generate list of indexes with key list
create table z_IUQ2_Temp_Index_Keys
([l1] [bigint] NOT NULL,
[F_Obj_ID] [bigint] NOT NULL,
[F_Schema_Name] [nvarchar] (128) NULL,
[F_Table_Name] [nvarchar] (128) NOT NULL,
[F_Row_Count] [bigint] NULL,
[F_Reserved] [bigint] NULL,
[F_Data] [bigint] NULL,
[F_Index_Size] [bigint] NULL,
[F_UnUsed] [bigint] NULL,
[F_Index_Name] [nvarchar] (128) NULL,
[F_Index_ID] [bigint] NOT NULL,
[F_Column_Name] [nvarchar] (128) NULL,
[F_User_Updates] [bigint] NULL,
[F_User_Reads] [bigint] NULL,
[F_Locks] [bigint] NULL,
[F_Blocks] [bigint] NULL,
[F_Block_Wait_Time] [bigint] NULL,
[F_Last_Used] [datetime] NULL,
[F_Index_Type] [nvarchar] (128) NOT NULL,
[F_Index_Column_ID] [bigint] NOT NULL,
[F_Last_Seek] [datetime] NULL,
[F_Last_Scan] [datetime] NULL,
[F_Last_Lookup] [datetime] NULL,
[Index_Key_List] [nvarchar] (MAX) NULL
)
GO

CREATE NONCLUSTERED INDEX [Object_ID_Index] ON [dbo].
[z_IUQ2_Temp_Index_Keys]
(
[F_Obj_ID]
ASC
)
GO

CREATE NONCLUSTERED INDEX [Index_ID_Index] ON [dbo].
[z_IUQ2_Temp_Index_Keys]
(
[F_Index_ID]
ASC
)
GO

CREATE NONCLUSTERED INDEX [RowCount_ID_Index] ON [dbo].
[z_IUQ2_Temp_Index_Keys]
(
[F_Row_Count]
ASC
)
GO

INSERT INTO z_IUQ2_Temp_Index_Keys
SELECT
(
row_number() over(order by a3.name, a2.name))%2 as l1,
a1.object_id,
a3.name AS [schemaname],
a2.name AS [tablename],
a1.rows as row_count,
(
a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved,
a1.data * 8 AS data,
(
CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size,
(
CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused,

-- Index Description
SI.name,
SI.Index_ID,
index_col(object_name(SIC.object_id),SIC.index_id,SIC.Index_Column_ID),

-- Index Stats
US.user_updates,
US.user_seeks + US.user_scans + US.user_lookups User_Reads,

-- Index blocks
IStats.row_lock_count + IStats.page_lock_count,
IStats.row_lock_wait_count + IStats.page_lock_wait_count,
IStats.row_lock_wait_in_ms + IStats.page_lock_wait_in_ms,

-- Dates
CASE
WHEN
(ISNULL(US.last_user_seek,'00:00:00.000') >= ISNULL(US.last_user_scan,'00:00:00.000')) and
(ISNULL(US.last_user_seek,'00:00:00.000') >= ISNULL(US.last_user_lookup,'00:00:00.000'))
THEN
US.last_user_seek
WHEN
(ISNULL(US.last_user_scan,'00:00:00.000') >= ISNULL(US.last_user_seek,'00:00:00.000')) and
(ISNULL(US.last_user_scan,'00:00:00.000') >= ISNULL(US.last_user_lookup,'00:00:00.000'))
THEN
US.last_user_scan
ELSE
US.last_user_lookup
END AS Last_Used_For_Reads,
SI.type_desc,
SIC.index_column_id,
US.last_user_seek,
US.last_user_scan,
US.last_user_lookup,
''

FROM
(
SELECT
ps.object_id,
SUM
(
CASE
WHEN
(ps.index_id < 2)
THEN
row_count
ELSE
0
END
)
AS [rows],
SUM(ps.reserved_page_count) AS reserved,
SUM
(
CASE
WHEN
(ps.index_id < 2)
THEN
(ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE
(ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
)

AS data,
SUM (ps.used_page_count) AS used
FROM
sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1

LEFT OUTER JOIN
(
SELECT
it.parent_id,
SUM (ps.reserved_page_count) AS reserved,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps

INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)

INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id )
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
INNER JOIN sys.indexes SI ON (SI.object_id = a1."object_id")
INNER JOIN sys.index_columns SIC ON (SIC.object_id = SI.object_id and SIC.index_id = SI.index_id)
LEFT OUTER JOIN sys.dm_db_index_usage_stats US ON (US.object_id = SI.object_id and US.index_id = SI.index_id and US.database_id = db_id())
LEFT OUTER JOIN sys.dm_db_index_operational_stats(NULL,NULL,NULL,NULL) IStats ON (IStats.object_id = SI.object_id and IStats.index_id = SI.index_id and IStats.database_id = db_id())

WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY row_count DESC
GO

-- Populate key string
DECLARE IndexCursor CURSOR FOR SELECT
F_Obj_ID,
F_Index_ID
FROM
z_IUQ2_Temp_Index_Keys
FOR UPDATE OF
Index_Key_List

DECLARE @objID int
DECLARE @IndID int
DECLARE @KeyString VARCHAR(MAX)

SET @KeyString = NULL
OPEN IndexCursor
SET NOCOUNT ON
FETCH NEXT FROM IndexCursor INTO @ObjID, @IndID

WHILE @@fetch_status = 0 BEGIN
SET @KeyString = ''
SELECT @KeyString = COALESCE(@KeyString,'') + F_Column_Name + ', '
FROM z_IUQ2_Temp_Index_Keys
WHERE F_Obj_ID = @ObjID and F_Index_ID = @IndID
ORDER BY F_Index_ID, F_Index_Column_ID
SET @KeyString = LEFT(@KeyString,LEN(@KeyString) -2)
UPDATE z_IUQ2_Temp_Index_Keys
SET Index_Key_List = @KeyString
WHERE CURRENT OF IndexCursor
FETCH NEXT FROM IndexCursor INTO @ObjID, @IndID
END;
CLOSE IndexCursor
DEALLOCATE IndexCursor
GO

-- clean up table to one line per index
DELETE FROM z_IUQ2_Temp_Index_Keys
WHERE [F_Index_Column_ID] > 1
GO

-- Select results
SELECT
[F_Table_Name] TableName,
[F_Row_Count] No_Of_Records,
[F_Data] Data_Size,
[F_Index_Size] Index_Size,
[F_Index_Name] Index_Name,
[F_User_Updates] Index_Updates,
[F_User_Reads] Index_Reads,
CASE WHEN
F_User_Reads = 0 THEN F_User_Updates
ELSE
F_User_Updates / F_User_Reads
END AS Updates_Per_Read,
[F_Locks] Locks,
[F_Blocks] Blocks,
[F_Block_Wait_Time] Block_Wait_Time,
[F_Last_Used] Index_Last_Used,
[F_Index_Type] Index_Type,
[Index_Key_List] Index_Fields
FROM z_IUQ2_Temp_Index_Keys

--order by F_Row_Count desc, F_Table_Name, [F_Index_ID]
--order by F_User_Updates desc
--order by Blocks desc
--order by Block_Wait_Time desc
--order by Updates_Per_Read desc
ORDER BY F_Table_Name

WS on a multilanguage environment

Diego Garcia Alvarez — Mon, 06 Feb 2012 09:05:34 GMT

After collecting Partner's and Customer's feedback about the changes introduced on build 32558 regarding Web Services (remember), DEV team has decided to introduce a way of avoiding them if desired.

In order to do so a new feature has been introduced on build 300759 (KB Article 2667345), this new feature allows you to add a new key to CustomSettings.Config file that will revert changes performed on build 32558 and therefore language used or WS will be the one that is logged for the calling user identity in the User Personalization Table, if any.

This is the key that has to be manually added (Meaning is not present on the Config File)to the NAV Server configuration file:

<!--

The default Culture in which SOAP Web Service calls are run.

Supported values

"false" (the default)
ensuring Web Services are running on a fixed culture,

"true" use the Culture
that is logged for the calling user identity in the User Personalization Table,
if any -->

If we select false then everything keeps the same and what was applied on build 32558 remains, thus you will see all error messages in English and culture (Decimals, dates, etc.) will work with EN-US values.

However if we set the key to true then the Culture that is logged for the calling user identity in the User Personalization Table (if any) will apply.

KB article for this Hotfix is placed on the next link:

https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?scid=kb;EN-US;2667345

Please be aware that this change (32558 build one) was introduced to break the dependency of WS language to RTC one so applying this HF and setting this key to true will enable that dependency back again which could be buggy on mltilanguage environments where different languages are present for different NAV clients (RTC, WS, Classic Client).

What was introduced on build 32558 guaranteed that WS will always take EN-US as default language building a more predictable scenario.

Anyhow and following your feedback we have asked DEV for a way to let NAV administrators to make this decision and this is the answer to this.

I hope you find this helpful.

Best regards

Diego García Álvarez

Dynamics NAV Senior
Support

How calendars are considered when calculating lead times

Sigfredo Beerman — Wed, 01 Feb 2012 09:25:34 GMT

Lead time calculation is the calculation that takes place to calculate "Expected Receipt Date" from the "Order Date" or the other way around. Similarly, sales have the calculation to calculate "Planned Delivery Date" from "Shipment Date" or the other way around as well. Here, we will be focusing on the Purchase side but this will have same explanation for the Sales.

In any case, the interesting piece I would like to cover is how determines what calendar to consider when calculating lead times. As you know, there are many different calendars in NAV:

Company calendar
Location calendar
Vendor calendar
Shipping Agent calendar
...

But, how all those relate when this calculation takes place? First of all, we need to have the following NAV premise in mind: if we don't have vendor calendar defined, NAV considers location calendar. Thus, if for whatever reason you have a location calendar while vendor does not have any (7 days per week), NAV will calculate the expected receipt date based on the location calendar. Here, most of the times, we wouldn't like this since we don't want the vendor lead time to be impacted by our location/warehouse calendar. If you are aware of this premise, you will understand better the design and you will have to setup a workaround: to create a vendor calendar with all days as working days (7 days per week). Having this in mind, NAV will find the vendor calendar (all working days) and will use this together with the vendor lead time.

Now, what are the different calculations that NAV is doing with lead times and calendars? a backward calculation and a forward calculation. Lets try to explain this a bit:

Forward. Here, we are calculating the expected receipt date (when do we have this in our company's premises?). This calculation is being done from the "Order Date". In other words, we now the purchase "Order Date" and we need to calculate the purchase "Expected Receipt Date" together with lead times and calendars. As mentioned above, if we don't have vendor calendar, it will use the location calendar. If we have vendor calendar (workaround: 7 days/week working days), it will use this vendor calendar together with the lead time and, once this final date is found, it will round to the next working day as per the location calendar (so, we won't set the "Expected Receipt Date" to a non-working date based on our location calendar but to the next working).
Backward. Here, we know the "Expected Receipt Date" (for instance, this is a requisite from our planning) and we need to calculate the "Order Date". The logic is similar to the above while we are calculation this backward (we know when do we want to have this available "Expected ..." and we would like to determine the date when we need to place this purchase "Order Date").

Here, the main thing is the fact about location calendar being used when vendor calendar is not set. As long as you are aware of this, you will anticipate issues by setting a 7 d/week calendar for the vendor to ensure this will be considered.

New Report Design Guidelines

clausl — Tue, 31 Jan 2012 09:34:00 GMT

[This post has been updated for accuracy since the first publication.]

For NAV RDLC reports, we have drafted Report Design Guidelines.

I see report developers as artists because they start with a blank piece of paper, just like an artist. As artists, only your imagination stops you from what you draw and add to this piece of paper. If you have worked with our new RDLC reports in NAV 2009 you have probably realized the many possibilities and options in Visual Studio. So not to limit the creativity of report developers but to help you, we have decided to leverage new Report Design Guidelines for many of our reports in the standard application in our next version. The intention with these guidelines is not to create extra work for the report developers but actually to help. Also this gives consistency so that when designing several reports, it is very easy for report developers to know how the reports should look and you do not need to spend time inventing a new look and feel for each report. In essence, the Report Design Guidelines should save you time when designing reports.

Although the Report Design Guidelines are not final and published yet, there is nothing stopping you from leveraging the new guidelines for your RDLC reports in NAV 2009.

Attached you will find the draft of the Report Design Guidelines.

At the moment, the draft of the Report Design Guidelines comprises a checklist, which you can go through one by one. For example, it is a good practice to define the Page Width, Page Height, and margins in the Report Properties before starting to design your report. Please note that all checks do not necessarily apply to all report types and that the guidelines are currently in a draft state. We would very much like to hear your feedback about the guidelines!

To help you understand how to use the new Report Design Guidelines in NAV 2009 we have done several “How to videos”. You can find the videos here at YouTube: http://www.youtube.com/playlist?list=PL5C8332C783CEA7A0

In the new Report Design Guidelines you will also find several examples on how reports will look when the guidelines have been followed. Also I have applied the new Report Design Guidelines to couple of attached standard reports. Please find these in attached zip file.

Thanks,
Claus Lundstrøm

QR Codes for Microsoft Dynamics NAV

navteam — Thu, 19 Jan 2012 19:34:00 GMT

QR codes (abbreviated from Quick Response code) are appearing in many different places today, and they are found to be quick and efficient when it comes to working with mobile phones and other devices which can read them. QR Code is a multipurpose instrument and it can hold all sorts of different types of valuable information like your company’s or your salesperson’s contact details, sales invoice information, promotional codes, location information, checksums, amounts, web links etc., which you can read using a QR code reader to automate some of the routine manual processes, like typing in things manually.

The QR code is a two-dimensional data-matrix which can be decoded very fast. The format is clearly defined and published as an ISO standard.

As the Windows Phone 7.5 update, code name “Mango”, rolls out to customers, it makes it even more relevant to use the QR codes, as you can now use your Windows phone camera to scan QR codes by bringing them into camera view. Bing will recognize QR codes and will help you save and use the information encoded in it.

In some countries, popularity of QR codes has grown so much, that their usage is now considered a national standard. Our team has recently released an update for the Mexican market, where we added QR codes to several major Microsoft Dynamics NAV documents. And we thought – why don’t we let everyone else enjoy this new cool feature?

The update is available for NAV 5.0 SP1, NAV 2009 SP1 and NAV 2009 R2 versions of the product.

URLs for Microsoft Dynamics NAV 2009 SP1 and R2:

https://mbs.microsoft.com/customersource/downloads/taxupdates/MSDNAV2009SP1ElectronicInvoice_Mexico

URLs for Microsoft Dynamics NAV 5.0 SP1 :

https://mbs.microsoft.com/customersource/downloads/taxupdates/MSDNAV5SP1ElectronicInvoice_Mexico

However, the only part you need from it is the MXElectronicInvoice.msi file included in the package. Note that the .msi file is exactly the same for both versions of NAV.

Here is what you have to do to get your data encoded into a QR code:

1. Run the installer to deploy the dll we shipped for this update. Among other things, the dll includes QRCodeProvider and IBarCodeProvider classes which we can use.

2. Add a BLOB field which will be storing the QR Code image into the Sales Invoice Header table for example:

3. Remember to set the SubType property to Bitmap if you would like to use the QR code on pages:

4. You can now use the following code to generate a QR code image, which for demo purposes will be saved into a first found posted sales invoice (needless to say, you should be doing it on a test database ;) ) In this example we will encode a contact card with some predefined details.

OBJECT Codeunit 50001 QR Code Mgt.
{
OBJECT-PROPERTIES
{
    Date=;
    Time=;
    Modified=Yes;
    Version List=QR Code;
}
PROPERTIES
{
    OnRun=VAR
            CompanyInfo@1170000004 : Record 2000000006;
            SalesInvoiceHeader@1170000003 : Record 112;
            TempBlob@1170000002 : Record 99008535;
            QRCodeInput@1170000000 : Text[1024];
            QRCodeFileName@1170000001 : Text[1024];
          BEGIN
            // Save a QR code image into a file in a temporary folder
            QRCodeInput := CreateQRCodeInput('John,Doe','+555 1231231','john@doe.zzz','www.johndoe.zzz');
            QRCodeFileName := GetQRCode(QRCodeInput);
            QRCodeFileName := MoveToMagicPath(QRCodeFileName); // To avoid confirmation dialogue on RTC

           // Load the image from file into the BLOB field
            CLEAR(TempBlob);
            ThreeTierMgt.BLOBImport(TempBlob,QRCodeFileName,FALSE);
            IF SalesInvoiceHeader.FINDFIRST THEN BEGIN
              SalesInvoiceHeader."QR Code" := TempBlob.Blob;
              SalesInvoiceHeader.MODIFY;
            END;

           // Erase the temporary file
            IF NOT ISSERVICETIER THEN
              IF EXISTS(QRCodeFileName) THEN
                ERASE(QRCodeFileName);

            MESSAGE('Done!');
          END;
}
CODE
{
    VAR
      ThreeTierMgt@1170000001 : Codeunit 419;

    LOCAL PROCEDURE CreateQRCodeInput@1020046(Name@1020000 : Text[80];PhoneNo@1020002 : Text[80];EMail@1020003 : Text[80];URL@1170000000 : Text[80]) QRCodeInput : Text[1024];
    BEGIN
      QRCodeInput :=
        'MECARD:' +
        'N:' + Name + ';' +
        'TEL:' + PhoneNo + ';' +
        'EMAIL:' + EMail + ';' +
        'URL:' + URL + ';';
    END;

    LOCAL PROCEDURE GetQRCode@1020038(QRCodeInput@1020001 : Text[1024]) QRCodeFileName : Text[1024];
    VAR
      IBarCodeProvider@1020000 : Automation "{89F54BC4-E6C9-44BA-8574-86568625BFF8} 1.0:{9FE38730-1A3C-4B84-A8C2-AFAC6A90E641}:'Microsoft Dynamics Nav MX Services'.IBarCodeProvider";
    BEGIN
      GetBarCodeProvider(IBarCodeProvider);
      QRCodeFileName := IBarCodeProvider.GetBarCode(QRCodeInput);
    END;

    PROCEDURE GetBarCodeProvider@1020001(VAR IBarCodeProvider@1020000 : Automation "{89F54BC4-E6C9-44BA-8574-86568625BFF8} 1.0:{9FE38730-1A3C-4B84-A8C2-AFAC6A90E641}:'Microsoft Dynamics Nav MX Services'.IBarCodeProvider");
    VAR
      QRCodeProvider@1020002 : Automation "{89F54BC4-E6C9-44BA-8574-86568625BFF8} 1.0:{69FEA5E6-0A76-4555-B74B-F170956B0098}:'Microsoft Dynamics Nav MX Services'.QRCodeProvider";
    BEGIN
      IF ISCLEAR(QRCodeProvider) THEN
        CREATE(QRCodeProvider,TRUE,TRUE);
      IBarCodeProvider := QRCodeProvider;
    END;

    PROCEDURE MoveToMagicPath@1170000000(SourceFileName@1170000000 : Text[1024]) DestinationFileName : Text[1024];
    VAR
      FileSystemObject@1170000001 : Automation "{F935DC20-1CF0-11D0-ADB9-00C04FD58A0B} 1.0:{0D43FE01-F093-11CF-8940-00A0C9054228}:'Windows Script Host Object Model'.FileSystemObject";
    BEGIN
      DestinationFileName := ThreeTierMgt.ClientTempFileName('','');
      IF ISCLEAR(FileSystemObject) THEN
        CREATE(FileSystemObject,TRUE,TRUE);
      FileSystemObject.MoveFile(SourceFileName,DestinationFileName);
    END;

BEGIN
END.
}
}

5. With the image saved in the BLOB field, it is now “business as usual” to add it to a report. You can see, for example, how company logo is added to the standard NAV document reports. NB. Don’t forget to run CALCFIELDS on the "QR Code" field before you display its content. :-)

6. And finally – run the report to see the QR code which you or your customers can scan, for example, with your favorite Windows 7.5 mobile phone:

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

Best regards,

Microsoft Dynamics NAV ERM Team

Dmitry Chadayev, Program Manager

Simulation of Average Cost Calculation

navteam — Fri, 13 Jan 2012 12:54:00 GMT

When using Average Costing method it’s sometimes difficult to interpret the assigned costs of an outbound entry.

In the scenario below there is process description for simulating the average cost calculation. This is often used when investigating costing issues related to the average costing method. It has been helpful in verifying the recognized COGS, to describe the average cost calculation or using it as identification that somewhere in time the average cost is unexpected. It helps to identify the area for deeper research of the records demonstrating unexpected values.

This blog post describes the process for how the average cost calculation can be simulated only and does not describe possible causes or possible correction processes.

The following scenario is carried out to create the basic data set. The data set is then used as base for processing and analyzing the average cost calculation when having the setup Average Cost Period as Day and Month respectively.

The scenario is carried out in a W1 Cronus database.

The first two steps create the basic data set which later on is used in respective simulation of average cost calculation.

1. Create Item: TEST

Average Costing method

Unit Cost: 10

2. Create and post the following documents.

If you are aiming for creating the full scenario and working through the respective simulations of Average cost calculation for Day and Month, it’s a good thing to create a backup now.

Inventory setup, Average Cost Calc Period = Day

3. Run the Adjust Cost - Item Entries batch job.

4. Filter the Item Ledger Entry table on Item TEST and review the fields specified below.

5. Open Revaluation Journal

6. Run Function Calculate Inventory Value:

Filter Item: TEST

Posting Date: September 15, 2011

Per Item

7. Change Unit Cost (Revalued) to 12 as above.

8. Post Line.

9. Run Adjust Cost - Item Entries batch job.

10. Filtering the Value Entries table on Item TEST, the following records are available:

Simulation of Average Cost Calculation with Average Cost period = Day

Now we are moving into the process of simulating the Average cost Calculation when Average cost period is Day, using the data for item TEST created in the scenario above.

When you have identified the Item that you need to further analyze the following process can be used. Below the described process, there is a screenshot showing the results of the simulation of the Average Cost calculation of item TEST.
In addition there is an Excel sheet attached where the full data set is available where used formulas, etc. can be reviewed more closely.

1. In the Value Entries table, filter on the particular Item that is to be analyzed.
If Average Cost Calc. Type is per Item&Location&Variant the filter has to cover also these fields with particular values in scope for the analysis.

2. Paster filtered Value entries into Excel.

3. Do a Custom Sorting using the fields as below:

Comments to respective field being a part of the sorting:

Valuation Date: is the date for when the entry shall be part of the Average cost calculation.

Partial Revaluation: a field that states Yes on Value entries with Type Revaluation. Revaluations affect the valuation of the following period’s outbound entries, not the outbound entries of the same period.

Valued quantity: is populated on every Value entry, corresponds to Item ledger entry quantity, Invoiced Quantity or Revalued quantity. Largest to smallest brings the inbound entries to come before the outbound entries of the period and thereby create the base for calculating the average cost of the period.

Item Ledger entry No.: Is to group the value entries attached to same Item ledger entry no.

4. Insert Summary lines where you want to establish the periods Average Cost (grey lines below). A summary line shall be inserted above the first outbound entry of a period. To identify the breakpoint for inserting the summary line follow these steps:

a. Establish the Valuation Date to be in scope for the investigation and locate these entries in the sorted Value entry list.

b. Then follow the stated quantities in field Valued Quantity for the chosen Valuation date. Identify the first line with negative quantity and you have the first outbound entry of the period.

c. Insert a line for calculation, above the first outbound entry of the period (example in the screenshot below and in attached spreadsheet; column M row 3 and 5, row 3 positive Valued Quantity, row 5 negative Valued Quantity, Summary line is inserted, row 4.

5. Make a Sum of the columns; Cost Amount (Actual), Cost Amount (Expected) and Item Ledger Entry Quantity. Calculate the Average Unit Cost of the period (column R) with the following formula:

If you have several Summary lines inserted, make sure to include the previous summary line into the calculation of respective column for the next period.

6. Choose an outbound entry, usually the first outbound entry of the period and then a couple of others, randomly selected in the period or those that for some reasons is of particular interest, and calculate the average cost per unit with the formula above (green, purple and blue sections in screenshot below).

- Does it correspond to the average unit cost of the period?

If not, ensure it is not fixed applied to an inbound entry: If field Valued By Average Cost is False, it is fixed applied to an inbound entry.
To which entry?; Follow up on the parent Item Ledger entry, field Applies-to Entry shall carry the entry no. of the supplying Item Ledger Entry.
If not fixed applied; establish the Amount Rounding precision and investigate if that has an effect on the Calculated Average cost.

These are the Value entries for item TEST when they have been sorted as described in step 3, where Summary lines has been inserted to establish Average cost for a certain period (step 4, 5) and where the first outbound entry of the period is calculated (step 6).

In attached spreadsheet used formulas can be checked by clicking in respective field.

Inventory setup, Average Cost Calc Period = Month

Another Average Cost Calc Period to use is Month, so let’s work with the basic scenario, create some additional data and see the effects having Month as Average Cost Calc Period and finally look into the simulation of the Average Cost calculation and its specifics.

The scenario continues using the basic data set created until step 2. If you did a backup after step 2 and have been working with the Average Cost Calc Period of Day you now have the opportunity to restore the backup and you will be able to start with step 3 below.

3. Change Inventory setup; Average Cost Calc Period to Month.

4. Run Adjust Cost - Item entries batch job.

5. Filter the Item Ledger Entry table, Item TEST, and review the fields specified below.

6. Open Revaluation Journal

7. Run Function Calculate Inventory Value:

Filter Item: TEST

Posting Date: September 30, 2011

Per Item

8. Change Unit Cost (Revalued) to 12 as above.

9. Post Line.

10. Run Adjust Cost - Item Entries batch job.

11. Filtering the Value Entries table, Item TEST, the following records are available:

Simulation of Average Cost Calculation with Average Cost period = Month

Now we are moving into the process of simulating the Average cost Calculation when Average Cost period is Month, using the data for item TEST created in the scenario.

When you have identified the Item that you need to further analyze the following process can be used. Below the described process, there is a screenshot showing the result of the simulation of the Average Cost calculation of item TEST using the Value entries created in the scenario. In addition there is an Excel sheet attached where the full data set is available where used formulas etc can be reviewed more closely.

1. In the Value Entries table filter on the particular Item that is to be analyzed.
If Average Cost Calc. Type is per Item&Location&Variant the filter has to cover also these fields with particular values in scope for the analysis.

2. Paste filtered Value entries into Excel.

3. Conversion of Valuation Date into Period:
Having another Average Cost Calc Period than Day requires the Valuation date to be translated into the chosen Average Cost Calc Period. In this case it’s Month.
In the screenshot below and in the attached Excel sheet the mentioned columns can be found.

a. Column F is added: The Valuation Date column is copied into column F. Thereafter column F is selected and the Format is changed to Number, no decimals. The Valuation Date is now converted to a number in column F.

b. Column G is added and is intended to carry the Year of the Valuation Date:
Select column G and change Format to Number, no decimals.
Add formula: =YEAR(F2) in cell G2, then double click on the plus sign in the right corner of the cell and the Year is generated for the rest of the lines.

c. Column H is added and is intended to carry the Period No. of the Valuation Date:
Select column H and change Format to Number, no decimals.
Add formula: =MONTH(F2) in cell H2, then double click on the plus in the right corner of the cell and the Month is generated for the rest of the lines.

4. Do a Custom Sorting using the fields as below:

Comments to respective field being a part of the sorting:

Year and Period No.: is the time for when the entry shall be part of the Average cost calculation.

Item Ledger entry No.: Is to group the value entries attached to same Item ledger entry no.

5. Insert Summary lines where you want to establish the periods Average Cost (grey lines below). A summary line shall be inserted above the first outbound entry of a period. To identify the breakpoint for inserting the summary line follow these steps:

a. Establish the time period to be in scope for the investigation and locate these entries in the sorted Value entry list.

b. Then follow the stated quantities in field Valued Quantity for the chosen time period.
Identify the first line with negative quantity and you have the first outbound entry of the period.

c. Insert a line for calculations. (Column P, row 4 and 6, row 4 positive Valued Quantity, row 6 negative Valued Quantity, Summary line is inserted as row 4).

6. Make a Sum of the columns; Cost Amount (Actual), Cost Amount (Expected) and Item Ledger Entry Quantity. Calculate the Average Unit Cost of the period (column U) with the following formula:

If you have several Summary lines inserted, make sure to include the previous summary line into the calculation of respective column for the next period.

7. Choose an outbound entry, usually the first outbound entry of the period and then a couple of others, randomly selected in the period or those that for some reasons is of particular interest, and calculate the average cost per unit with the formula above (green, purple and blue sections in screenshot below).

- Does it correspond to the average unit cost of the period?

If not, ensure it is not fixed applied to an inbound entry: If field Valued By Average Cost is False, it is fixed applied to an inbound entry.
To which entry?; Follow up on the parent Item Ledger entry, field Applies-to Entry shall carry the entry no. of the supplying Item Ledger Entry.
If not fixed applied; establish the Amount Rounding precision and if that has an effect on the Calculated Average cost.

These are the Value entries for item TEST when they have been sorted as described in step 4, where Summary lines has been inserted to establish Average cost for a certain period (step 5,6) and where the first outbound entry of the period (+ the 2^nd in period 10) is calculated (step 7).

Note that all inbound entries in September (Period No. 9) is sorted at the top and demonstrate the effect on all outbound entries in September regardless of the specific valuation date.

To follow the process and be able to review used formulas etc., an Excel sheet is attached and contains the following tabs:

Basic Data

---------------

Contains the scenario and what data it creates. Thereafter the basic scenario moves into two paths, one for using Day as Average Cost period and the other for using Month as Average Cost Period. The respective set of Value entries are thereafter pasted into the next tabs.

Average Cost simulation - Day

----------------------------------------

At the top the Value entries are pasted from the Basic Data scenario addressing the Average cost period of Day.

The Value entries are processed; sorted and calculated as described beneath the section of value entries.

Average Cost simulation - Month

-------------------------------------------

At the top the Value entries are pasted from the Basic Data scenario addressing the Average cost period of Month.

The Value entries are processed; sorted and calculated as described beneath the section of value entries.

Any feedback to how this process and documentation can be further developed to provide more insight in the average cost calculation is very welcome.

Helene Holmin

Escalation Engineer NAV Costing EMEA

hholmin@microsoft.com

Costing Error Detection and Data Correction White Paper for Microsoft Dynamics NAV

Susanne Priess — Thu, 12 Jan 2012 20:20:00 GMT

A new Costing Error and Data Correction white paper and a Costing Error Detection report have been released on PartnerSource and CustomerSource.

The Costing Error Detection and Data Correction white paper discusses common inventory costing issues and how you can correct erroneous data after inventory costing issues have been identified. The white paper focuses on the data and the fields that typically cause problems in the cost adjustment process.

The Costing Error Detection report can help you find common costing data problems. If the report shows that there are errors in your database, you can use the suggestions in the white paper to correct the data. The report can also be used to validate inventory data after an upgrade.

To review the white paper and download the report, go to:

CustomerSource (logon required): Costing Error Detection and Data Correction White Paper for Microsoft Dynamics NAV
PartnerSource (logon required): Costing Error Detection and Data Correction White Paper for Microsoft Dynamics NAV

Jet Reports Express for Microsoft Dynamics NAV Now Available for Swedish and Norwegian Languages

navteam — Wed, 04 Jan 2012 23:43:16 GMT

As of December 28, 2011, Jet Reports Express for Microsoft Dynamics NAV has been updated to include Swedish and Norwegian languages. This easy to use and very popular Microsoft Excel add-in for ad-hoc reporting is available as a download on Customer Source and Partner Source for no additional costs for customers on an active Business Ready Enhancement Plan.

Post – Apply – From Item Entry Application Across Locations

navteam — Wed, 04 Jan 2012 16:59:00 GMT

The following blog post is to provide guidance for users of the Inventory costing functionality within Microsoft Dynamics NAV. The specific article is designed for providing guidance that you are able to use Appl. – from Item Entry to create an Item Entry Application across locations. This feature is not permitted using the Appl. – To Item Entry when you try to make an Quantity Item Entry Application across Locations.

Appl. – from Item Entry is intended when you want to make an Item Fixed Cost Application overwriting the default Costing Method. It means that the inventory increase, for example a sales credit memo line is linked to the inventory decrease in the item ledger entry that you indicate in this field.

Initial scenario to illustrate

This scenario is carried out in W1 Cronus.

1. Create new item 70061, Unit of Measure PCS, any posting groups and select costing method FIFO.

2. Create Purchase Order against vendor 60000, Item 70061 Location BLUE, Quantity 1, Direct Unit Cost Excl. VAT 10. Specify vendor invoice number and posted as Received + Invoiced.

3. Sell item 70061 using Sales Order against customer 60000, Location BLUE, Quantity 1. Post as Shipped + Invoiced.

4. Create Sales Credit Memo against customer 60000, using Get Posted Document Lines to Reverse to retrieve the Posted Sales Order processed at step 3.

Notice: Appl. – From Item Entry is populated.

5. Change location from default BLUE to RED.

Notice: Appl. – From Item Entry has not been changed.

Notice: Return Reason Code field on the Sales Credit Memo line will enable to set a Default Location Code.

6. Post the Sales Credit Memo as Invoiced.

7. Purchase Order for Item Charge Freight against vendor 61000, Location BLUE, Quantity 1, Direct Unit cost Excl. VAT 2.50.

8. Assign the Item Charge against the Purchase Order that been processed at step 2. Specify vendor invoice number and posted as Invoiced.

9. Run Adjust Cost Item Entries batch job. Lookup Item Ledger Entries and notice 2.50 been forwarded from Sales Order to the Sales Credit Memo we processed at step 4.

By applying a positive entry in this case Sales Credit Memo to a negative through Appl. -From Item Entry, we make a cost application between them and hence turn this positive into a “Return / Correction”, that has to receive its cost from the applied outbound. Since the positive entry is now effectively a “return”, the entry application is allowed across locations. This is essentially a feature of the Returns Order Management that was introduced since 3.01. In recognition of the fact that
customers may want to return products to a location different from the one where they originally sold.

Additional scenario

1. Create new item 70062, Unit of Measure PCS, any posting groups and select costing method FIFO.

2. Open Item Journal, Entry type Negative Adjustment, item 70062, Location BLUE, Unit Cost 10, Quantity 1. Post.

3. Open Item Journal, Entry type Positive Adjustment, item 70062, Location RED, specify Appl.-From Item Entry against Negative Adjustment.

Notice: When you drill down into Appl. – From Entry, there are no Item Ledger Entries. You need to remove the (location) filter and then you can select the Negative Adjustment.

Notice: Lookup item ledger entries of item 70062 and both item ledger entries are left open. This is intended design.

4. Post the Positive Adjustment created at step 3.

5. Open Item Journal, Entry type Positive Adjustment, item 70062, Location BLUE, Unit Cost 13, Quantity 1, Post.

6. Run Adjust Cost Item Entries batch job.

Notice: Outbound (step 2) and Inbound (step 4) are now closed on the Item ledger entries.

7. Adjustment of LCY 3 is now forwarded according to the chain: Positive Adjmt., step 5 -> Negative Adjmt., step 2 -> Positive Adjmt., step 3.

So, the Item Entry Application is possible between locations when and only when we have a Cost Application between the outbound and inbound entries.

The same logic cannot be applied to Transfer Orders as they generate a quantity application between entries. One reason you cannot create Transfer Order when you do not have the Quantity on Hand.

-Christiaan Osborne

Product Support Escalation Engineer

APGC Customer Support & Services SMS&P

Managing network files in RTC

Arvind kum — Thu, 22 Dec 2011 10:18:00 GMT

In Dynamics NAV 2009 in RTC client if you are accessing files located on network machines, these files can be accessed when each service is running on single machine, when SQL and NST are on same machine (2 tier) or each service is running on separate...(read more)

Mainstream Support for Microsoft Dynamics NAV 5.0 Ends April 2012

Susanne Priess — Mon, 19 Dec 2011 01:00:00 GMT

Mainstream support for Microsoft Dynamics NAV 5.0, including 5.0 SP1, ends April 2012 per the Microsoft Support Lifecycle Policy. Customers who are current on a service plan can continue to access the following benefits through CustomerSource:

Previously released Upgrades, Updates, Service Packs, Fixes and Regulatory/Tax Updates
Self-Help Support through Knowledge Base articles and online content
Unlimited Online Training
CustomerSource Community and Tools

For more information related to Microsoft Support Lifecycle policies and product dates for all Microsoft Dynamics NAV products, see the Support Lifecycle Page. For support lifecycle information for Microsoft Dynamics NAV in particular, see Microsoft Dynamics NAV Support Lifecycle Information.

Microsoft Dynamics Diagnostics

Susanne Priess — Wed, 14 Dec 2011 02:28:00 GMT

The Microsoft Dynamics business is using Diagnostics to help automate Data Collection and Data Analysis when troubleshooting support incidents. Diagnostics allow our support teams to capture certain pieces of data about an environment to more efficiently resolve a customer case. Diagnostics can do basic data collection to gather information about product version, system configurations and settings, as well as do a deeper analysis of this and other data collected to determine the root cause of an issue.

The Microsoft Dynamics business has been working with Diagnostics for the past 2-3 years, but just recently made changes to the online incident submission process. Steps on how to run a Diagnostic will now be presented after submitting most cases on CustomerSource/PartnerSource. If you have recently logged a support incident via CustomerSource/PartnerSource you may have noticed this new information after submitting the support incident.

When presented with this information, please follow the steps provided to run the Diagnostic and upload the results to the Microsoft Dynamics Support team. Diagnostics allow us to capture necessary product versions, system configuration, log files and other information to allow our support teams to more effectively and efficiently troubleshoot and solve support cases. Running Diagnostics and uploading these results will reduce the number of questions support engineers will need to ask you, reduce the amount of information you need to manually gather and send us, and ultimately allow us to resolve cases quicker. The Diagnostics will automatically capture and upload the information to our support teams. You do have the ability to view the information that is being collected prior to uploading it to Microsoft.

How to Add Shortcuts Menu in the RoleTailored Client

navteam — Tue, 06 Dec 2011 16:43:58 GMT

In the Microsoft Dynamics NAV 2009 R2 RoleTailored client environment the Shortcuts Menu feature, which was present in earlier versions of Dynamics NAV for Classic client, has not been added.

In Classic client it is actually possible to Create and Open Shortcut from this Menu.

I have then tried, in this blog, to mimic what Classic client does and developed a couple of pages and one table to let users achieve a similar functionality for RoleTailored client. The text object in this blog contains:

Table 50300 “Shortcut”
Page 50300 “Shortcut List”
Page 50301 “Create Shortcut Worksheet”

The first task is to add Page 50300 “Shortcut List” to a Department MenuSuite like in the below example:

Secondarily it is needed to customize Navigation Pane using RoleTailored client in order to add a new Menu called, for example, “Shortcuts” with Page 50300 “Shortcut List” added to it.

This could be done manually

http://blogs.msdn.com/b/nav/archive/2011/03/30/nav-2009-tips-and-tricks-personalize-the-departments-menu.aspx

or configuring that for a profile

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

Once you have fulfilled those 2 steps then your users may be good to go to Create and Open shortcuts in a similar way as it was and as it is actually for Classic client.

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

Few tips about filters usage in latest Dynamics NAV RTC builds

Gedas B — Fri, 25 Nov 2011 12:27:00 GMT

I want to describe few tips about filters usage in Dynamics NAV RTC. This can be useful for users who worked with classic client and came to RTC.

In Dynamics NAV Classic Client we can construct filters in filters window using following operators:

.. Range

& And

| Or

< Less than

<= Less than or equal to

> Greater than

>= Greater than or equal to

<> Different from

* Forms a part of value

@ Case-insensitive

Let’s say for example we want to find item in item list ending with “Front” then can construct filter on description: *front

And we have results

Now try the same in RTC.
In RTC we have “Filter Pane” and we don’t need to call filter window. So here we just change filter field to description and ad the same filter value:*front

But wow, result is different:

It also includes items which starts with ‘front’ and even has ‘front’ in the middle of description.

What is wrong? Nothing, this is feature of RTC filters: whatever we add to this filter, it will be translated to string by adding ‘@’ in front and ‘*’ in end. So our filter string ‘*front’ becomes ‘@*front*’ and RTC filters for any description includes ‘front’ in upper or lower case.

We can see this transformation if open “About this page” filter section:

If we know this feature, then we can use it very smart: if we know how description starts, we just type begin of it without any filter operators and we have all description, which begins with this string, filtered. Fast work, is it?

But what to do if we really want to filter only descriptions which end with ‘front’. Then we have 2 options:
1. Use apostrophes for filter string for example enter “ ‘*front’ ” instead of “ *front ”, then RTC filter exactly on this string:

or
1. Expand Filter Pane and filter on exactly what we want:

That’s all what I want to say.

Ps. If you have question “why I filter on ‘front’, but results always includes ‘Front’”, then answer is: my db SQL collation is not cases sensitive, so SQL doesn’t see difference here and shows upper and lower case values.

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

Gedas Busniauskas

Microsoft Lithuania
Microsoft Customer Service and Support (CSS) EMEA

Manage Max No. of XML Records to Send Option from RoleTailored Client (with .NET Interop)

navteam — Tue, 22 Nov 2011 00:59:00 GMT

With NAV 2009 R2 it is now possible to set how many XML records to send to RoleTailored Client (RTC), bypassing the previous hardcoded limitation of 5000 records.

This could be done by installing platform hotfix KB 2492490 Build no. 32146 or higher.

https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?scid=kb$EN-US$2492490&wa=wsignin1.0

I just would like to explain how this Hotfix works in short.

In order to bypass the previous hardcoded limit of 5000 Max no. of xml records to send, after installing the aforementioned platform hotfix or higher, you have to set a key like that on every RTC Client machine in the ClientUsersSettings.config file

(where integerValue is an integer value that represents the max number of xml records to send when using the Export to Microsoft Office feature)

Since the Classic Client handles this value directly from the IDE changing “Max. no. of XML records to send” property from Tools > Options, I have been requested if there is a more flexible way to manage this key in the ClientUsersSettings.config file.

I have then tried, in this blog, to mimic what Classic Client does and developed a simple page and codeunit to let the user change dynamically the value for this key and if the key is not present, by adding it directly to the ClientUsersSettings.config file with the desired value (this would also make the Hotfix deployment faster).

The code in the attached .txt file is quite simple and is based on .NET interoperability using the following:

System.Environment

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

System.IO

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

System.XML

http://msdn.microsoft.com/en-us/library/y3y47afh(v=VS.90).aspx

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

A special thanks to Jorge Alberto Torres - DK-MBS NAV Development

Directions EMEA Registration Now Open – Follow News from Microsoft on the Event Blog!

navteam — Fri, 18 Nov 2011 16:41:00 GMT

Visit the new Microsoft Blog on the Directions EMEA web site. Hear the latest Microsoft Dynamics NAV news, comments, and opinions from Dan Brown and Paul White – together “The Beige Blog”; ww.directionsemea.com/Blogs/The_Beige_Blog.htm.

The registration for Directions EMEA is open now at www.directionsemea.com. The conference takes place April 25-27 in Rome and is the largest Microsoft Dynamics NAV conference for partners organized by partners! Early Bird discounts are available. Read more about the event in Paul White’s introduction on “The Beige Blog” here: http://www.directionsemea.com/Blogs/The_Beige_Blog/2011/11/Microsoft_Dynamics_NAV__7__pre-release.htm.

Introducing…. Metadata version in NAV 2009 SP1/R2 hotfixes

Jorge Alberto Torres NAV — Thu, 17 Nov 2011 11:17:13 GMT

As a follow up to my previous post, we are happy to announce that, from build 32942, the hotfix releases will detect if objects need to be recompiled and prompt you to recompile accordingly. This will happen when importing incompatible fobs files or when applying new platform hotfixes.

If you try to import fob files from previous (or possibly also newer versions), or if you export them from newer builds (32942 and newer) and back to NAV 2009 R2 or newer versions (up to build 32942), the system will detect that the metadata is incompatible and force a recompilation when imported. The same detection will happen if you apply a hotfix to your system, and your objects need to be recompiled.

In the future, the metadata version (which is now in the Object Metadata table in field 27) will be updated if a compilation will be needed after applying a hotfix, not making it necessary always to re-compile the objects every time you apply a new hotfix (and preventing the RTC client from losing the connection to the server). The current metadata version is 60300, whereas the R2 runtime metadata version is 60200.

Since we are changing a system table (table 2000000071), we need to do a database conversion. Moreover, since the objects are not recompiled with a database upgrade, you also need to recompile the objects that you need. The nice feature now, is that the RTC will prompt you on demand to recompile the objects that need recompilation (basically, the ones you use).

Order modifiers are not being considered with planning exceptions or emergencies

Sigfredo Beerman — Tue, 15 Nov 2011 14:12:00 GMT

Here, we have an scenario where NAV does not respect order modifiers (maximum/multiple/minimum order quantity) when there are exceptions. As we mentioned in earlier post, exceptions are ... exceptions!!! Yeah, I know you might have an issue but, isn't this a situation that should occur exceptionally in your customer? If for whatever reason, these are not exceptions, you might have an issue with your planning parameters. When NAV flags the Warning Icon, it means there are exceptional situations like negative inventory, stock below safety stock, ...

The issue here is that customers treat all these scenarios differently. If we have the inventory level below safety stock, one customer might wait for the next planned replenishment to increase the ordered quantity. Other customers might create an urgent replenishment for the required quantity regardless of order modifiers since safety stock should not be rounded with order modifiers (it increases safety level in fact) ... Or ... whatever. Exceptions are treated differently. We mentioned this on that previous blog. What we also mentioned is that NAV design does not activate the "Accept Action Message" flag when this is an exception. What this means is that NAV is not suggesting a replenishment. It just warns the user of this exceptional situation for the user to take decission on how to address this. Thus, it should only be understood as NAV warning you that an exceptional situation exists.

And, that is why NAV does not respect order modifiers. It only warns the user about an exception situation (ie. inventory level is 20 pcs below safety stock). It does not suggest the user to replenish (ie. it does not suggest to replenish 20 pcs). Thus, user need to decide what is the best to address this exception ... and to consider order modifiers if required.

As stated, different customers treat exceptions differently. From a standard code, it is true we are not taking into consideration the order modifiers ... but this is an exception and should not occur but in very rare times. Thus, we need to manage customer in the fact that this should not happen to them frequently and that we are not suggesting anything ... we are only flaging the exceptional scenario for the user to decide action to take.

Hotfix released for "Create PDF" functionality in the Role Tailored Client

Robert Miller NAV — Wed, 09 Nov 2011 18:46:00 GMT

The new Create PDF functionality is available as an option on the Print button on any report request page that prints through the Role Tailored Client once the associated hotfix (KB 2628466) has been installed. Since this feature was added at the executable level, there is no code that needs to be added to each report to get this new functionality.

This functionality automatically enables the ability to have the report be printed as an Adobe Portable Document Format (PDF) document on the Microsoft Dynamics NAV 2009 server and then be downloaded to the RoleTailored client (RTC). The first time you execute this functionality you may be prompted with a dialog box asking you to Open/Save/Cancel the document with the option to "Always ask before opening this type of file.". If you uncheck the option then you will no longer be prompted to Open/Save/Cancel the PDF file.

Once you open the PDF file and assuming that Adobe Acrobat has previously been installed, then the PDF document will be automaticaly rendered. From this point, you can either save or print the document or print to the desired printer.

NAS is consuming user sessions

Robert Miller NAV — Wed, 09 Nov 2011 17:37:58 GMT

When the Microsoft Dynamics NAV 2009 Application Server (NAS) runs under the Network Service account, the NAS may to try to repeatedly restart. In this situation, the restarts of the NAS consume user sessions. Eventually, this will cause the users to receive the following error message when they log on to the system ...

"Your program license does not permit more users to work simultaneously. Wait until another user has stopped using the program. Contact your system administrator if you want to allow more simultaneous users on your system."

This problem occurs in the following products:

Microsoft Dynamics NAV 2009 R2
Microsoft Dynamics NAV 2009 Service Pack 1 (SP1)

The issue is caused by a change in the operating system between Microsoft Windows Server 2003 and Microsoft Windows Server 2008 with regards to how security accounts are viewed. The easiest way to correct this issue is to change the NAS service login from NT Authority\NetworkService to a Domain Account. Once the service login account is changed and the NAS is restarted the NAS will only consume one user session as expected.

Objects need recompilation after applying certain hotfixes

Jorge Alberto Torres NAV — Tue, 01 Nov 2011 13:11:00 GMT

If you have recently applied a new hotfix and start running the RTC right away, your RTC client might eventually get disconnected, and a new log on the service tier host machine can appear stating that a new Exception of type “System.ArgumentNullException” has been raised. This can usually happens if you were trying to run a report that was last compiled with the pre hotfix version, and you have not recompiled it after applying the new hotfix bits.

This issue appears because the hotfix runtime has a different signature from what is expected from the previously compiled code and therefore, the .Net runtime will complain that it is not possible to execute the code (which is indeed true).

To resolve this issue, simply recompile the objects in question. It is strongly recommended to re-compile all objects after applying a new hotfix to prevent this from happening (this is especially true if you are applying a hotfix with a build number bigger than 32657).

The general issue will be resolved in an upcoming hotfix, so that the runtime itself will detect if a recompilation is needed, and a message will appear on the RTC stating which object type and number needs the recompilation (and obviously the client will not get disconnected), but until then, please recompile the objects.

Document Management - Zetadocs Express for Microsoft Dynamics NAV is Now Available for Download!

navteam — Mon, 31 Oct 2011 05:00:00 GMT

Download now through PartnerSource: Zetadocs Express for Microsoft Dynamics NAV
Download now through CustomerSource: Zetadocs Express for Microsoft Dynamics NAV

We are very happy to announce that now it is here - Zetadocs Express for Microsoft Dynamics NAV.

Now customers on a Business Ready Enhancement Plan can get a document management solution integrated with SharePoint Online. With an subscription to Microsoft Office 365 customers are now able to drag & drop documents into Microsoft Dynamics NAV which then are stored on SharePoint Online. Additionally customers will also be able to send documents as PDF and store them automatically when sending.

Partners can download Zetadocs Express now and include document management in their demonstrations of Microsoft Dynamics NAV. Customers on a Business Readiness Enhancement Plan should talk to their partners to get this installed in order to take advantage of document management capabilities integrated into Microsoft Dynamics NAV.

Useful Dialog Windows with .NET Interop and NAV 2009 R2

navteam — Sun, 30 Oct 2011 11:49:39 GMT

We have been asked recently how to display a Dialog window for the RoleTailored client that would collect a Directory path.

The old (good) Codeunit 412 “Common Dialog Management” was not suitable for that purpose (and honestly I would love to go for something more RTC oriented).

I thought myself, then, there may be a lot of useful Dialog windows based on System.Windows.Forms namespace.

How to select a DIRECTORY

http://msdn.microsoft.com/en-us/library/system.windows.forms.folderbrowserdialog(v=VS.90).aspx

How to select a FILE

http://msdn.microsoft.com/en-us/library/system.windows.forms.openfiledialog(v=VS.90).aspx

How to select a COLOR

http://msdn.microsoft.com/en-us/library/system.windows.forms.colordialog(v=VS.90).aspx

How to select a PRINTER

http://msdn.microsoft.com/en-us/library/system.windows.forms.printdialog(v=VS.90).aspx

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

The code is fairly simple.

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

- Thanks to Carsten Scholling from Microsoft Dynamics CSS Germany -

Installing NAV 2009 R2 without Active Directory

peterwib — Thu, 27 Oct 2011 13:55:00 GMT

Normally you need to have an Active directory server running to use multi-tier of NAV 2009 R2. But in some cases this is not possible or existing. In NAV 2009 R2 we got an new configuration option called ClientCredentialType that is meant to be used over WAN. But I will in this blog post show one simple way to get this up and running for development and test purpose, in a LAN environment

In a developer or test environment
1. Install Service, Database and Classic client on the server machine.
2. Create the needed windows users on the server machine
3. Add the newly created window users to NAV in classic client, under Tools->Security->Windows Logins
4. Now sync all new logins in classic client, under Tools->Security->Synchronize All Logins
5. Install RTC on client machine
6. Update ClientCredentialType in C:\ProgramData\Microsoft\Microsoft Dynamics NAV\ClientUserSettings.config (path is for Windows 7 and Windows 2008) to "UserName" on client machine. Also update the server key to server machine name. It’s important that you have never started RTC on this machine before to make this working.
7. If you have started RTC earlier on the client machine you also need to update ClientCredentialType in C:\Users\[user name]\AppData\Local\Microsoft\Microsoft Dynamics NAV\ClientUserSettings.config to "UserName" on client machine. Replace [user name] with your user name. Also update the server key to server machine name.
8. Start RTC and you will get a popup box asking for credential. For user name type "[server machine name]\[user name on server machine]".

For an production environment I would recommend to use certificates to authorize the connection between RTC and server. Guides to setup certificates can be found under subject "Procedural Overview" on http://msdn.microsoft.com/en-us/library/gg502476.aspx

Embedding fonts into PDF

clausl — Tue, 25 Oct 2011 09:41:00 GMT

Update: I stand corrected, embedding fonts into a PDF is not an option with the SAVEASPDF function in NAV 2009.

But now you at least know that this is solved in our next version.

/Clausl

******

I just want to share a small tip today. If you need to include a custom font into a RDLC report you need to first install the font, of course.

The Font must be a True Type font. If your font is an Open Type font you will need to convert this first for it to visible in Visual Studio.

Then you will need to restart the computer for the font to be embedded into the report when saving as PDF.

The restart issue took me some time to figure out, so with this small tip, you hopefully will not loose as much hair as I did, when you include a custom font into a RDLC report.

I have attached a sample RDLC report with a custom barcode font.

/Clausl