The reason that setup.exe doesn't start can be many. But I will try to give you ha checklist that you can work through if you end up in this situation.
The final Service Pack 1 for Dynamics NAV 2009 is now just around the corner, so with that I would like to share with you one of the new possibilities in the reporting area, Matrix reports. First I will go through how to build a Matrix Report in NAV 2009 SP1, then I will demo some Matrix reports done by two of our MVPs of Dynamics NAV and one of our partners. In the end of this blog I will share all the Matrix reports. So if you are not interested in how to build a Matrix Report in NAV 2009 SP1 or already know how to do this in NAV 2009 SP1, this is the time for you to scroll to the bottom of this blog, if you just want to download the objects. :-)
If you are still here and have not scrolled to the bottom, let us get started building a Matrix Report.
In this walkthrough we will be creating a Matrix Report sorted with Items by Location. When you have finished this Walkthrough you will understand how to build a Matrix Report in NAV 2009 SP1.
1. Select “New” in Object Designer with Report object selected
2. Add “Location” as Table
3. Select “Create a blank report“ and click “OK”
4. Insert “Location” as the first DataItem
5. Insert “Item” as the second DataItem, make sure to indent under the “Location” DataItem.
6. Save Report and give an “ID” and a “Name”
7. Now we have created the DataItems for this report, so now we need to create the Data Source for us to be able to create the Layout of the Report in Visual Studio. For this we need to go to the Section Designer. Open Section Designer
8. For now the Section Designer is Empty, so we need to add some Fields. We would need these fields:
· Items by Location – Title
· Code – Location
· Name – Location
· No. – Item
· Description – Item
· Inventory – Item
9. Make more space to have more fields in each of the Sections.
10. Add a Label with Caption=Items by Location
11. With “Location” Section selected, open Field Menu:
12. Open Field menu and select “Code” and “Name”
13. Add these 2 fields to the “Location, Body”
14. Navigate to the “Item , Body” and select “Field Menu” and select "No.", "Description" and "Inventory"
15. Add these 3 fields to the “Item, Body”
16. We have now created the Data Source for the report. Now we need to create the layout for this report.
Select “View / Layout”. Visual Studio will open.
17. In Visual Studio you will now see these elements in Data Source we can work with:
18. Lets us now design the layout.
19. In the toolbox select the Matrix control and drag this to the Report Body
20. Drag “Items_by_LocationCaption” to the top left cell in the Matrix control
21. In the “Rows” just below I want to add 2 rows directly under “Items by Location”, so we need to add a new group. Right click the “Rows” cell and select “Insert Group”
22. “Grouping and Sorting properties” dialog box will come up.
Select to group on “=Fields!Item_Description.Value” and click “OK”
23. In the new cell to the left of “=Fields!Item_Description.Value”, right click and select “Edit Group”
24. Select to group on “=Fields!Item__No__.Value” and select “OK”
Your report should now look like this:
25. It is now time to add the column above the data, and in this report we want to see in how many items are stored in each warehouse. Add “=Fields!Location_Name.Value”
26. And at last let us add the data cell in the bottom right corner of the matrix control. Add “=Sum(Fields!Item_Inventory.Value)” to the Data cell.
27. Now before viewing the report let us already do some resizing of the layout, for this to readable on one page. I choose to narrow the columns, so the report now looks like this:
28. Also to make this report more readable friendly, let us add a Solid Border style. Select all cells in the Matrix control, and select “Solid” for the “BorderStyle” property
29. Now let us view the report, save, import, compile and run the report. We now get this output.
Hmm, something is wrong. I.e. we have 32 bicycles on stock in each of the warehouses. Let us fix this issue.
30. Go to the DataItem “Item” and set the property “DataItemLink” to “Location Filter=FIELD(Code)”
31. Now let’s run the report again, but now we set the following filter on the Request Page: Inventory is >0
We do this to avoid all entries with 0.
32. Now select “Preview” to see the report.
33. We now have a working Matrix Report which is sorting Items by Location.
34. Now let us imagine you would like to know the Inventory Availability on the 52 “PARIS Guest Chair, black” in the “Blue Warehouse” or on the 55 “AMSTERDAM Lamp” in the “Red Warehouse”. So let use the Drill Through to Report feature in Dynamics NAV SP1 to drill through to our existing “Inventory Availability” report 705. Open Visual Studio again by selecting “View / Layout”
35. Right click the Data field in the Matrix control and select “Properties”
36. Navigate to the “Navigation” tab
37. Select “Jump to URL” and add the following expression:
="DynamicsNAV:////runreport?Report=705&Filter=Item.%22Location Filter%22:"+Fields!Location_Code.Value+"&Filter=Item.%22No.%22:"+Fields!Item__No__.Value
What we are doing here is that we open Report 705(Inventory Availability) filtered on Location and Item No., when we click data field in the Matrix report.
38. Before we save the report, let us make it visible in the report that we have a link to another report.
With the Data field selected set these properties:
Color=Blue
TextDecoration=Underline
39. Save, Import and Compile the report. Note you might this error:
Set the EnableHyperlinks=TRUE, and compile again
40. Run the report. It will now look like this:
41. Now when we click on the 52 “PARIS Guest Chair, black” in the “Blue Warehouse”, Inventory Availability report will now open based on the our Blue Warehouse and our Paris Guest chair.
And when we click on the 55 “AMSTERDAM Lamp” in the “Red Warehouse”, Inventory Availability report will now open based on the our Red Warehouse and our Amsterdam Lamp.
1. Items by Location done by Andrey Panko, MVP for Dynamics NAV. This report is very similar to the above walkthrough, it has extra logic on the data part and more advanced coloring and a Totaling to the very right. It does not contain any Drill Through to report 705, but this could easily be added following the above steps(35-37) on how to that.
2. Items by Location done by Rene Gayer, MVP for Dynamics NAV. This report is also similar to the above reports but here Rene is using the new Drill Through to report, so if click on the Item, left most column, you open a Item Dashboard, with Key Performance Indicators, Stock level illustrated and Sales History.
3. GL Entries per Account per Month, done by GAC Business Solutions. This report gives a overview of the entries in GL Account pr month. Notice when clicking the GL Account you will Drill Through to GL Account card, and when clicking the amount in the Matrix you will Drill Through to the Detail Trial Balance Report filtered on month and GL Account
Please find all the mentioned reports in the attached zip file.
Thanks, to Andrey Panko, Rene Gayer and GAC Business Solutions for sharing their Matrix Reports.
Thanks, Claus Lundstrøm, Program Manager, Microsoft Dynamics NAV
Earlier I did describe how to duplicate an installed and existing database in post "How to install more than one Dynamics NAV 2009 Demo Database". This time I will attached the DB directly from DVD.The steps is as follow
You need to ensure that the SQL server user have correct permissions to ‘C:\Program Files (x86)\Microsoft Dynamics NAV\60\Database - Copy\' or where you put the db files. I'm using ‘NETWORK SERVICE" as the SQL server user account, the NAV will use this account by default when doing demo installation.
To set permission
== Nov 9 update ==
In some cases we got integer overflow when running this query. So the table definitions in the query have now changed from using int to bigint to avoid this.
== end of update ==
The query below combines these three queries into one:Index Usage QueryRecent Bocking HistoryTable Information Query
It can be used to just see the number of records in each table. But also by just changing "ORDER BY", it can be used to see which index cause most blocking / wait time / updates or locks. Or to compare Index Updates with Index Reads to get an idea of cost versus benefit for each index for the purpose of index tuning.
So in short, one query gives you: - Index / Table Information - Index usage (benefits and costs information for each index) - Index locks, blocks, wait time and updates per read (cost/benefit).
The query must be run in your NAV database. It will create a table called z_IUQ2_Temp_Index_Keys and use various Dynamic Management Views to collect information for each index into this table. First time you run it, or if you want to refresh data, you must run the whole query which may take up to a minute of two for each company in the database. After that if you just want to change sorting / get the results again, then you only need to run the last part of the query, beginning with:
-- Select results
The last lines suggest various "ORDER BY"s that might be useful to enable instead of the default one, which is by Table Name.
Lars Lohndorf-Larsen (Lohndorf )
Microsoft Dynamics UK
Microsoft Customer Service and Support (CSS) EMEA
These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.
--use NavisionDatabase
IF
NULL
DROP
;
-- Generate list of indexes with key list
create
(
[l1] [bigint]
NULL,
[F_Obj_ID] [bigint]
[F_Schema_Name] [nvarchar]
[F_Table_Name] [nvarchar]
[F_Row_Count] [bigint]
[F_Reserved] [bigint]
[F_Data] [bigint]
[F_Index_Size] [bigint]
[F_UnUsed] [bigint]
[F_Index_Name] [nvarchar]
[F_Index_ID] [bigint]
[F_Column_Name] [nvarchar]
[F_User_Updates] [bigint]
[F_User_Reads] [bigint]
[F_Locks] [bigint]
[F_Blocks] [bigint]
[F_Block_Wait_Time] [bigint]
[F_Last_Used] [datetime]
[F_Index_Type] [nvarchar]
[F_Index_Column_ID] [bigint]
[F_Last_Seek] [datetime]
[F_Last_Scan] [datetime]
[F_Last_Lookup] [datetime]
[Index_Key_List] [nvarchar]
NULL )
)
go
CREATE
[z_IUQ2_Temp_Index_Keys]
ASC
insert
z_IUQ2_Temp_Index_Keys
SELECT
,
a1
a3
a2
, (
-- Index Description
SI
index_col
),
-- Index Stats
US
-- Index blocks
IStats
-- Dates
case
when
last_user_seek
last_user_scan
else
last_user_lookup
end
SIC
''
FROM
ps
SUM
CASE
WHEN
row_count
ELSE
0
END
used
GROUP
LEFT
it
INNER
WHERE
) INNER
) inner
inner
) left
left
()) left
())
'IT'
order
desc
-- Populate key string
declare
for select
select
for
Index_Key_List
int declare
set
open
IndexCursor
on fetch
fetch
@IndID
while
begin set
', '
from
where
ORDER
F_Index_Column_ID
update
@KeyString
close
deallocate
IndexCursor go
-- clean up table to one line per index
delete
1 go
[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
F_User_Updates
F_User_Reads
[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
--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
F_Table_Name
The NAV 2009 documentation walkthroughs provide step-by-step instructions for installing NAV 2009 on 2 or 3 machines. However, we have found that some of the same configuration issues come up time after time after installation.
When on calls with partners and customers, it seemed to me that this information was spread out all over the place, so I wanted to organize it in a different way for troubleshooting purposes so that I would have most everything in one place. Hopefully this will be helpful to others as well.
The intention of this post is to provide a checklist of sorts for troubleshooting some of the areas where we frequently find errors or omissions in configuration after NAV 2009 has been installed.
Errors on the RTC resulting from configuration problems include but are not limited to...
Login failed for user...
A Server was not found at...
The User ID and password are invalid
*************************************
Before anything else, check to see what accounts are running the NAV and SQL services. Make note of domain account names, machine names, and FQDN (Fully qualified domain name)
Check for Incorrect SPN’s
When the NAV Server and the database are on separate machines AND the NAV Service is running under a Domain Acct:
2 SPN’s have to be set up for the NAV Service. SERVER will be different depending on your server name.
i.e. Server_DynamicsNAV/Server.FQDN:7046
Examples…
SERVER_DynamicsNAV/SERVER.NAV2009DC.LAB:7046
SERVER_DynamicsNAV/SERVER:7046
SPN for SQL Service is not needed IF SQL is running under NetworkService.
SPN for SQL IS needed if SQL is running under a Domain account – see "SQL SPN" at the end of this post
When the NAV Server and the database are on separate machines, AND the NAV Service is running under NetworkService account:
No SPN is needed for the NAV Service.
Tools
Tools are normally installed on Win 2008 by default (depending on features selected). For 2003, install the Windows Support Tools
- Windows 2003 Support Tools
o ADSIEdit.msc
o SETSPN.exe
Setting the SPN :
o Run the ADSI Edit tool on any server computer in the domain. To do this, click Start, click Run, type Adsiedit.msc, and then click OK. In the ADSI Edit window, expand Domain, expand DC, expand CN=Users, right-click CN= AccountName, and then click Properties. Note: The AccountName placeholder represents the domain account you are using to start the NAV Server (and/or SQL) service.In the Properties dialog box, double-click the servicePrincipalName attribute to open the Multi-valued String Editor dialog box. (There are a few shortcuts to find the servicePrincipalName, you can check the ‘Show only attributes that have values’ to shorten the list or click in the Attributes box and type ‘ser’ to jump close to the attribute.)In the Value to add box, add a SPN for the NAV Server (or SQL Server), and then click Add, keeping in mind that the SERVER will be different depending on your server name. For SQL SPN see Appendix A.SERVER_DynamicsNAV/FQDN:7046Note: Replace “SERVER” with the name of your server, and “FQDN” with the fully qualified domain name, such as “SERVER.MICROSOFT.COM”.In the Value to add box, add a SPN for the NAV Server, and then click Add. Keeping in mind that the SERVER will be different depending on your server name. SERVER_DynamicsNAV/SERVER:7046Note: Here for the “SERVER” value, only specify the name of the server.Click OK two times. Close the ADSI Edit window.
Check Delegation
When running the NAV Service under a Domain Account:
Delegation has to be set up for the account running the NAV service.
Note: The Delegation tab will only be present after adding the SPN to the domain user account.
o Click Start, then click Run.
o Type in dsa.msc and click OK.
o Expand the Domain and then click on Users.
o Locate the domain user account you are using , right click and select Properties.
o Under that Delegation tab, select the ‘Trust this user for delegation to any service (Kerberos only)’, then click OK. (This is not constrained delegation as mentioned in the Walkthrough, but this makes it a little easier to setup delegation. You can always come back after it is setup and working to implement constrained delegation.)
o Close the Active Directory Users and Computers window.
o Note: for Constrained delegation, select Trust this user for delegation to specified services only and then select MSSQLSvc.
When running the NAV Service under NetworkService Account:
Delegation has to be set up for the machine running the NAV service.
o Click Start, and then click Run.
o Expand the Domain and then click on Computers.
o Locate the computer name, right click and select Properties.
o Under that Delegation tab, select the ‘Trust this user for delegation to any service (Kerberos only)’, then click OK.
o Close the Active Directory Users and Computers window
Check SQL Logins and OCL
Adding the login(s) to SQL and setting up the Object Change Listener (OCL):
OCL is NOT required if the NAV Server and SQL Server are on the same machine AND the NAV service is running under Network Service.
If using a Domain User to run services, make sure the login has been added to both SQL and NAV. Also check that user has Full Control to the server folder.
o The account may already exist in SQL but the permissions must be manually set correctly):Open Microsoft SQL Server Management Studio.Click Security to expand the tree-view, right-click Logins, and then select New Login.This opens the Login - New dialog box.Add the domain user account in the Login name field, using the following format:domain\domainUserClick OK to exit the Login - New dialog box. Click Databases, Demo Database NAV (6-0) or other database name, and then click Security to expand the tree view.Under Security, right-click Users, and then select New User.This opens the Database User - New dialog box.Add the domain user account in the User name and Login name field, using the following format:domain\domainUser
o Add $ndo$navlistener in the Default schema field.Click the Securables page. Click Add, click OK, click Object Types, check Tables and then click OK. Click Browse, check the [dbo].[Object Tracking], click OK, click OK again.In the Explicit permissions check Grant on the Select permission. Click OK to exit the Database User - New dialog box.Close Microsoft SQL Server Management Studio.
If the NAV server and SQL Server are on different machines AND the Network Service Account is running the NAV Service, then the Login and OCL must be set up using the same steps but for the machine account rather than the domain user…Use the above steps, but replace the domain account with the machine account, i.e. <domain>\<computername>$
Check the configuration of Delegation for the RTC
Change the ClientUserSettings.config on the computer running the RTC, under the current user's profile, to define that a domain user account is to be used when connecting to the NAV Service tier.On Windows Vista or Windows Server 2008, the default location is:X:\Users\\AppData\Local\Microsoft\Microsoft Dynamics NAV On Windows XP or Windows Server 2003, the default location is:X:\Documents and Settings\\Local Settings\Application Data\Microsoft\Microsoft Dynamics NAV Add the following key to the file:<add key="DelegationInfo" value="DomainUser"></add>
There are two possible values: NetworkService and DomainUser. To enable delegation, set the parameter to DomainUser.
This will need to be repeated for all workstations that will be using the RTC.After confirming all items above and making any changes, be sure to stop and start the NAV Server service before you attempt to re-connect using the RTC. If you still encounter the error message, remember that Kerberos tickets last for 10 hours, so if you add/change the SPN, you may either have to wait for any existing tickets to expire or download KerbTray and attempt to expire any existing tickets. This is found in the Windows Server 2003 Resource Kit, which can be downloaded from:http://www.microsoft.com/Downloads/details.aspx?FamilyID=9d467a69-57ff-4ae7-96ee-b18c4790cffd&displaylang=en
Check the SQL SPN (if applicable)
SPN must be added for SQL Server when running under a domain account.
There are errors in the documentation walkthrough regarding how to create the SPN for SQL. The easiest way to check for the correct SPN syntax is to look at how it automatically generates the SPN when running with network service (switch the account running the SQL service to NetworkService, check the SPN and use that to set up the SPN for a Domain Acct.
An example might be...
MSSQLSvc/LALA1719334.NAV2009DC.LAB:1433
SQL Service = MSSQLSvc
Server = LALA1719334
Domain = NAV2009DC.LAB
Port = 1433
Laura K. Lake ( lalake)
Microsoft Dynamics NA
Microsoft Customer Service and Support (CSS) North America
Recently I was asked about the encoding that it is done for record links in SQL.
If you are not familiar with Record Links, which have been available since NAV version 5.0, just open any Card or List and then click Edit -> Links (Ctrl + L).
“Record Links enable users to add links to documents to any record in Microsoft Dynamics NAV, such as a sales order or purchase order. The document or order can be stored in Microsoft SharePoint or on a file server and the user can access the document from Microsoft SharePoint or give access to others.”
In SQL, these Record Links are saved under the “Record Link” table, and contains (among others) the Record ID, URLs, Description, etc.
Basically, the Record ID should be able to uniquely identify to which record this link belongs, so…. Not surprisingly, this Record ID will contain the table number and corresponding key values (+ 2 null bytes).
In principle, what I have just written should be enough to understand the format, but I will further explain with the help of some examples.
Let’s start with Annette Hill, one of the employees that work at CRONUS International:
Annette’s Record ID on the Record Link table (in SQL) will look as “0x501400000089FF4148000000”. As I wrote before, this should correspond to the table number, the keys and 2 null bytes… That is:
The first 4 bytes correspond to the table number, in this case 0x50 14 00 00, which should correspond to table 5200.
Now, you must be wondering, how comes 5200 can be converted to such a strange binary representation. Well, we will have to remember two things: 1) The number needs to be in hexadecimal format, and 2) It needs to be encoded in little endian as we are working with Intel processors (just use your search engine, if you don’t know/remember).
So, we need 4 bytes for (table) 5200, that in hex is: 00 00 14 50, if we convert it to little endian, we will get: 50 14 00 00.
The next 2 bytes correspond to the data type of the key. The type is CODE, which is type 137, which is only converted to hex: 00 89. At this point you must be thinking that I am pulling your leg, as this is supposed to be further converted using little endian, but it turns out that the keys data types are encoded in their binary representation, in order to reuse code when searching for them.
The following 4 bytes correspond to the key itself. Interestingly enough, code data types will start with 1 byte that can tell you the length of the code (if numeric) or FF which would correspond to a character based code type. In this case, we have 0xFF, meaning that we have characters and those are 0x41 and 0x48 + null termination 0x00. Of course 0x41 corresponds to ‘A’ and 0x48 corresponds to ‘H’ (in “extended” ASCII), which is the key, as can be seen on the ‘No.’ field.
The last 2 bytes will always be null characters.
Let’s do something a little bit more interesting, now with Bill of Materials:
This link will look as: 0x5A0000000089FF313932342D57000087102700000000
The first 4 bytes correspond to the table number, in this case 0x5A 00 00 00, which should correspond to table 90.
The next 2 bytes correspond to the data type of the first key. The type is CODE, which is type 137, which is only converted to hex: 00 89.
The following 8 bytes correspond to the key itself. In this case, we have 0xFF, meaning that we have characters and those are ‘1924-W’ (+ null).
The next 2 bytes correspond to the data type of the next key. The type is Integer, which is 135, that is converted to hex: 00 87.
The following 4 bytes correspond to the key itself. In this case, we have 0x10270000, converting this to an integer, corresponds to 10000 (using the little endian rule).
If you are wondering where these values came from, Open Table 90, and verify that the Key is in fact ‘Parent Item No.,Line No.’ and that the values that we have decoded, correspond to the data we have added the links to.
Of course, you should always remember that this format is subject to change and there is no guarantee that it will be preserved on future versions, also remember the standard following claim.
-- jtorres
The Supply Planning white paper for Microsoft Dynamics NAV 2009 SP1 has been updated on both PartnerSource and CustomerSource. The Supply Planning white paper is intended for planning experts who are involved in the implementation or support of supply planning functionality in Microsoft Dynamics NAV installations and for those who need to make modifications within the area. It gives an overview of the concepts and principles that are used within the supply planning area of Microsoft Dynamics NAV 2009 SP1.
In this version, the following issue is addressed:
When using reorder policies Maximum Qty and Fixed Reorder Qty, the planning system focuses on the projected inventory in the given time-bucket only. This means that the planning system may suggest superfluous supply when negative demand or positive supply changes occur outside of the given time bucket.
The issue is fixed as follows:
A warning is displayed when superfluous supply causes the projected inventory to exceed the highest projected inventory in an ideal supply plan, defined as the "overflow level". The warning message displays the overflow level that the planner must adjust to if he wants to avoid the superfluous supply.
To download the white paper, see the following links.
PartnerSource:
https://mbs.microsoft.com/partnersource/deployment/documentation/whitepapers/supplyplanninginmicrosoftdynamicsnav2009.htm
CustomerSource
https://mbs.microsoft.com/customersource/documentation/whitepapers/supplyplanninginmicrosoftdynamicsnav2009.htm