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

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 -->

 <add
key="ServicesCultureDefaultUserPersonalization"
value="false"/>

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 

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.

[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 (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

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.

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.

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

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

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. 

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

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 machine (3 tier).

However when accessing such network files in (2 and 3 tier) environment there might be some additional Delegation requirements.

As best practice i will summarize these requirements as following.

(Here NAV Server Service is runnign under Domain User account, Service Account for SQL can be same or separate dedicated Domain User account and NAV Server Service account is currently set to allow Kerberos only)

This msdn article can be followed with additional steps as described below

Managing network files in RTC (Setting Delegation)

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

1. Additional steps required:

    in Adsiedit.msc console Windows Server 2003 Service Pack 2 32-bit Support Tools, Open the User that is running in NAV Server Service in AD and go to the Delegation Tab

• Click on ‘Add’
• Select ‘Users or Computers’
• Enter in the name of the machine that is hosting the Shared Folder
• Click ‘Check Names’ and now it should show an underline below the servername
• Click ‘OK’
• Now you should see a list of Services for this machine that contains the shared folder
• Click on the Service called ‘cifs’
• Click ‘OK’

2. Please check the following Local Group policy on Middle tier machine and add this for NAV Service account domain user.

     Add these policies for this domain user under which NAV middle tier service runs.

• "Enable computer and user accounts to be trusteted for delegation” under computer Configuration -> Security Settings -> Local Policicies -> User Rights Assignment
• After this from Windows Start -> Run and type gpupdate /force in CMD prompt to refresh/update the policy.

 3. If the above steps does not work, additionally you can set the value of “UserAccountControl” flag value to 17367552 (To do this adsiedit.msc console can be taken)

     (TRUSTED_FOR_DELEGATION+TRUSTED_TO_AUTH_FOR_DELEGATION)
     According to following KB:

     http://support.microsoft.com/kb/305144/en-us

 4. After this please copy the “Kerbtray.exe” on the Middle tier and client machine and aftre running (double click on kerbtray.exe) right clickt -> purge the Tickets. So that on next RTC connection attempt it will automatically receive new Kerberos ticket.

     Kerbtray Tool you can download from the the „Windows Server 2003 Service Pack 2 32-bit Support Tools“.

5. With new RTC start this time now you are able to access the network files, (you may need to performance step 4 more than one time)

6. If it is still not working then probably Kerberos is failing back to NTLM, so you may also allow NTLM to the NAV Server service account "Use any authentication protocol" in the property of this Domain user or you are hitting the issue as described in below KB article.

   Delegation errors when working with file shares in the Microsoft Dynamics NAV RoleTailored client

   https://mbs.microsoft.com/knowledgebase/KBDisplay.aspx?scid=kb;EN-US;2621984

Hope with this way you can get access to network files from RTC client.

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.

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.  

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:

1. Table 50300 “Shortcut”
2. Page 50300 “Shortcut List”
3. 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

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

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

<add key="MaxNoOfXMLRecordsToSend" value="integerValue" />

(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