Creating SQL Server VIEWs to show the Ledger Entry Table values including Flow fields

I am using two examples to explain the logical background in regard to retrieving values of the Flow Fields.

Example 1 – SQL View for Customer Table

In Customer (No 18 of Dynamics NAV DB) Table, ‘Balance’ and the ‘Balance LCY’ fields have been defined as Flow Field, therefore during the run time it will pull records from the Detail Customer Ledger Entry table (No 379 of Dynamic NAV DB ) to show the ‘Balance’ and ‘Balance LCY’ on the Customer Card. When you create a SQL view for customer table, you will also have to apply the same principle. In other words, you have to join ‘Detail Customer Ledger Entry’ with Customer table to retrieve the corresponding values.

USE [Demo Database NAV (6-0)]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[View_Customer]
AS
SELECT TOP (100) PERCENT dbo.[CRONUS Australia Pty_ Ltd_$Customer].No_, dbo.[CRONUS Australia Pty_ Ltd_$Customer].Name,
SUM(dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry].Amount) AS AMOUNT,
SUM(dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry].[Amount (LCY)]) AS AMOUNT_LCY
FROM dbo.[CRONUS Australia Pty_ Ltd_$Customer] LEFT OUTER JOIN
dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry] ON
dbo.[CRONUS Australia Pty_ Ltd_$Customer].No_ = dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry].[Customer No_]
GROUP BY dbo.[CRONUS Australia Pty_ Ltd_$Customer].No_, dbo.[CRONUS Australia Pty_ Ltd_$Customer].Name
ORDER BY dbo.[CRONUS Australia Pty_ Ltd_$Customer].No_

GO

Please note that I am using Dynamics NAV 2009 SP1 Asutralian & New Zealand version for my examples. Therefore when you test this example, you may be required to replace the compnay name ‘CRONUS Australia Pty_ Ltd’ with ‘CRONUS International Pvt Ltd.’ As appropriately. Further if you’re testing this scripts with a different database file, instead of ‘Demo Database NAV (6-0’, please replace the database name correctly.

 
Example 2 – SQL View for Customer Ledger Entry Table

In the second example, I am creating a SQL view for Customer Ledger Entry Table. Again, you know that ‘Amount’ and ‘Remaining Amount’ are defined as Flow Fields by placing a look up to the Detail Customer Ledger Entry Table. Therefore in the SQL View definition, you would notice that a JOIN command is used to establish the link between the Customer r Ledger Entry (18) & Detail Customer Ledger Entry Table (379). More importantly, JOIN command has been used for two times in order for calculating ‘Amount’ and ‘Remanning Amount’ respectively. In the first case, Detail Customer Leger Entry is joined only based on the Customer Ledger Entry No. field, whereas in the second case Detail Customer Leger Entry is joined based on both Entry No & Entry Type fields.

USE [Demo Database NAV (6-0)]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[View_Cust_LedgerEntry]
AS
SELECT dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Entry No_], dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Customer No_],
dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Posting Date], dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Document Type],
dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Document No_], dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].Description,
SUM(dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry].Amount) AS Amount, SUM([CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry_1].Amount)
AS Remaning_Amount


FROM dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry] LEFT OUTER JOIN
dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry] ON
dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Entry No_] = dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry].[Cust_ Ledger Entry No_] AND
dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry].[Entry Type] = 1 LEFT OUTER JOIN
dbo.[CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry] AS [CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry_1] ON
dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Entry No_] = [CRONUS Australia Pty_ Ltd_$Detailed Cust_ Ledg_ Entry_1].[Cust_ Ledger Entry No_]


GROUP BY dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Entry No_], dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Customer No_],
dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Posting Date], dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Document Type],
dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].[Document No_], dbo.[CRONUS Australia Pty_ Ltd_$Cust_ Ledger Entry].Description

GO

When you need to create SQL views for other ledger entry tables, you will have to follow the same principles as explained above. For example, in the case of creating a SQL view for Item or Item Ledger Entry Tables, you will have to link either ‘Item Leger Entry’ or ‘Value Entry’ or both as appropriately depending on the required fields available in the SQL view.

More Training Titles Released for Microsoft Dynamics NAV 2009

Brand new training titles have been released and are available on PartnerSource and CustomerSource (account required). The trainings are available as downloads and as instructor-led training (ILT).

Upgrading Microsoft Dynamics NAV 2009 Native Database to Microsoft SQL Server

This training is designed for individuals preparing to upgrade Microsoft Dynamics NAV 2009 Native database to Microsoft SQL Server. This training covers the upgrade process from preparing existing data, configuring Microsoft SQL Server, troubleshooting data issues, to validating data after upgrade.

PartnerSource: https://mbs.microsoft.com/partnersource/communities/training/trainingmaterials/student/course80298.htm?printpage=false

CustomerSource: https://mbs.microsoft.com/customersource/training/trainingmaterials/student/course80298.htm?printpage=false

Microsoft Learning: http://www.microsoft.com/learning/en/us/Course.aspx?ID=80298A&Locale=en-us

Microsoft SharePoint Technologies and Microsoft Dynamics NAV 2009

This training is designed for individuals who want to integrate Microsoft Dynamics NAV 2009 with Microsoft SharePoint technologies.

PartnerSource: https://mbs.microsoft.com/partnersource/communities/training/trainingmaterials/student/course80297.htm?printpage=false

CustomerSource: https://mbs.microsoft.com/customersource/training/trainingmaterials/student/course80297.htm?printpage=false

Microsoft Learning: http://www.microsoft.com/learning/en/us/Course.aspx?ID=80297A&Locale=en-us

Microsoft Dynamics NAV and SQL Server Database Compression

Microsoft SQL Server 2008 introduces two new compression features (available in Enterprise Edition) that are of interest to users with large databases. Data compression applies to individual tables and indexes within a database and comes in two options: ROW and PAGE. Backup compression is a new option when backing up a database to external storage. Both compression techniques can be used to reduce the amount of online storage required for databases. Data compression can be configured for an entire heap (table with no clustered index), clustered index, non-clustered index, or indexed view. For partitioned tables and indexes, the compression option can be configured separately (and differently) for each partition. Compression can also reduce disk utilization and sql memory utilization as dead is stores on disk in a compressed state and also reads in the SQL cache in a compressed state. Compression can add 10 - 30% percent increased CPU utilization depending on what tables and indexes are compressed and what level of compression is used.

For Dynamics NAV we recommend only compressing tables and indexes that have a read to write ration of 80%/20% (This is a conservative threshold) or higher as compressing tables with a higher write ratio can actually decrease performance. We also recommend using ROW compression if the space saving between ROW and PAGE level compression is less that 10%; if the difference is over 10% then we recommend PAGE compression. This is because if the space savings from PAGE compression is close to or similar to ROW compression then it is not recommended to incur the additional overhead associated with PAGE compression. An example of NAV tables that would benefit greatly from compression are the large "Entry" tables such as G/L Entry, Value Entry, and Item Ledger Entry. An example for NAV tables that would not benefit from compression and where performance may actually decrease due to compression are "Line" tables where the data is temporary in nature such as Sales Line, Purchase Line, and Warehouse Activity Line. SQL Server compression is completely transparent to the Dynamics NAV application.

Compression is done on Table or Index basis. On tables with clustered indexes the clustered index is the table so compressing the clustered index is equal to compressing the table. Running the ALTER TABLE.. WITH COMPRESSION is only necessary on HEAPS for all tables with Clustered Indexes you can use the ALTER INDEX.. WITH COMPRESSION.

How to determine the read/write ratio of an index? Luckily SQL keeps track of this for us and all we need to do is extract this data. SQL Server stores this information in the sys.dm_db_index_operational_stats DMV. Remember DMV's are "recycled" each time the SQL Server service is restarted so if SQL has only been up and running for a day or a week this information will be of minimal use. Ideally you would want the server to be up any running for several weeks and through a month end close to get a true idea of actual data access patterns of your database.

The following query will tell you how long the SQL Server instance has been up and running:

select 'Sql Server Service has been running for about '
+ cast((datediff(hh, create_date, getdate()))/24 as varchar(3)) + ' days and '
+ cast((datediff(hh, create_date, getdate())) % 24 as varchar(2)) + ' hours'
from sys.databases where name = 'tempdb'

The following query will give the approximate read write balance of all the used indexes in the database.

SELECT o.id,
o.name,
x.name,
i.index_id,
x.type_desc,
s.rowcnt,
i.leaf_update_count * 100.0 /
(i.range_scan_count + i.leaf_insert_count
+ i.leaf_delete_count + i.leaf_update_count
+ i.leaf_page_merge_count + i.singleton_lookup_count
) as Writes,
i.range_scan_count * 100.0 /
(i.range_scan_count + i.leaf_insert_count
+ i.leaf_delete_count + i.leaf_update_count
+ i.leaf_page_merge_count + i.singleton_lookup_count
) as Reads,
FROM sys.dm_db_index_operational_stats (db_id(), NULL, NULL, NULL) i
JOIN sys.sysobjects o ON o.id = i.object_id
JOIN sys.indexes x ON x.object_id = i.object_id AND x.index_id =
i.index_id
JOIN sys.sysindexes s ON s.id = x.object_id and s.indid = x.index_id
WHERE (i.range_scan_count + i.leaf_insert_count
+ i.leaf_delete_count + leaf_update_count
+ i.leaf_page_merge_count + i.singleton_lookup_count) <> 0
AND objectproperty(i.object_id,'IsUserTable') = 1


You can also run a simulation with the sp_estimate_data_compression_savings stored procedure in SQL with will run a sample of the data through the chose compression level and give you the estimate of the space saved by enabling compression.

I strongly recommend reading the following MSDN article before engaging in database compression activities.

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

Note: You can use the following query to check to see if there are currently any compressed indexes in the database.

SELECT o.name Table_Name, p.index_id as Index_ID, i.name as Index_Name,
CASE
WHEN p.data_compression = 1 THEN 'ROW Compression'
WHEN p.data_compression = 2 THEN 'PAGE Compression'
ELSE 'ERROR'
END Compression_Type
FROM sys.partitions p
JOIN sys.objects o ON p.object_id = o.object_id
JOIN sys.sysindexes i ON o.object_id = i.id AND p.index_id = i.indid
AND p.data_compression in (1,2)
ORDER BY o.name, p.data_compression, p.index_id


Source:
http://blogs.msdn.com/b/nav/archive/2011/02/11/microsoft-dynamics-nav-and-sql-server-database-compression.aspx

Microsoft Announces the Availability of Management Reporter for Microsoft Dynamics ERP

Microsoft has now announced the general availability of Management Reporter for Microsoft Dynamics ERP. Management Reporter, formerly codenamed Management Reporter 2.0, will act as the replacement for Microsoft FRx,

www.newscom.com/cgi-bin/prnh/20000822/MSFTLOGO

Management Reporter extends the proven functionality of Microsoft FRx and enables people to quickly and easily create, generate, help secure and publish professional-looking financial statements, such as profit and loss statements, balance sheets, and cash flow reports, and will also allow them to take advantage of the following:

• Custom reports without IT assistance. These reports reduce the burden on the IT department by empowering the finance team to create its own financial statements.

• Deep cross-functional analysis. Management Reporter provides easy and accurate financial reporting, intercompany reconciliations and eliminations.

• Reduced training time and increased productivity. A familiar user interface and reporting tools minimize training time since users will easily be able to transition from Microsoft Office applications to the similar navigation pane and toolbars in Management Reporter.

• Strong data integrity and report accuracy. Management Reporter supports regulatory requirements, such as generally accepted accounting practices and international financial reporting standards.

"Management Reporter is like Microsoft FRx on steroids — it takes the familiar building blocks of Microsoft FRx and revolutionizes the way reporting and business intelligence can be delivered," said Les Wright, senior consultant, BDO Canada LLP, a Microsoft Dynamics partner. "This is something customers have been asking for and provides even more value to their Microsoft Dynamics ERP solutions."
Microsoft FRx customers making the transition to Management Reporter will experience a variety of new features including the following:

• Direct integration to the Microsoft Dynamics ERP general ledger. This integration aids in simplified, real-time reporting with greater accuracy.

• Reusable building blocks. Blocks of Report, Row, Column and Tree definitions can be mixed and matched to produce a wide variety of boardroom-quality reports.

• A Report Library. A Report Library is where reports can be secured, stored, and viewed

Management Reporter is available for Microsoft Dynamics GP 2010 customers at NO ADDITIONAL COST and will be available with future releases of other Microsoft Dynamics ERP solutions.

To assist customers currently using Microsoft FRx make the transition to Management Reporter, a migration tool, detailed guidance and information is available at

www.microsoft.com/dynamics/en/us/products/management-reporter.asp

Direction of Microsoft Dynamics NAV

Based on Microsoft Dynamics NAV Statement of Direction – April 2010

Microsoft has now announced that NAV is going to support for Unicode with the release of Dynamics NAV "7", which will be released next year - 2011. This will also mean that we are finally going to say goodbye to Navision's native database, which otherwise have served very well basically since the release of the first DOS version of Navision in 1987. It is a goodbye to this REALLY GREAT & SUPER STABLE database.

Unicode has been too much of a worry to the average Navision customer. But for international customers, who have been working with many different countries in the same database, then it's going to be a great help.

NAV "7" will also be another goodbye. With the release of NAV “7”, "Forms" are also going to be disappeared. "Forms" have been the data presentation object until NAV 2009; however with the introduction of the “Role Tailored Client” - “RTC”, it is now the "Pages" which is providing the data presentation to the user. The "Forms" are still used in the Classic Client, but now with the removal of the "Forms" then the typical NAV user will no longer be able to use the Classic Client. (Fortunately) Developers will still have to use the Classic Client, until new development tools will be available in the “Role Tailored Client” with NAV "8" in 2013.

Future of NAV Development Environment

The C/SIDE development environment is going to be replaced from Classic Client to RTC. As you know, still we have to depend on the NAV Classic Client for making any customization, even though we do the implementation in RTC, but this structure is going to replace with the upcoming versions of Dynamics NAV. Some of the highlights would be as follows;

1. No Object Designer! Simple. This will be replaced with Designer Home Center; it will be another role center which allows developers/consultants to make any changes to the standard objects of the application.

2. Page Designer will be replaced with another page where it will allow you to proceed with your table customizations. This feature will be brought into the other object types as well.

3. In the Object List, developer will find the all objects including data about where they used and information about the usage.



4. No Visual Studio! Own simple editor for making all changes in Code Units.



5. The structure of the Editor would be more towards to the look & feel of the Visual Studio. For example the Symbol menu is to be replaced; any parameter that you define will be available in the top of the code lines.

To be continued …

Source:
www.dynamicsuser.net/blogs/kine

Navision Employee Portal

Employee Portal provides a Web-based interface that gives employees quick access to mission-critical business information such as invoices, customer data, and some selected reports. Moreover, it real time integrates with the standard Microsoft Dynamics NAV database while collaborating with Microsoft® Windows® SharePoint® Services 3.0 (WSS 3.0) or Microsoft Office SharePoint Server 2007 (MOSS 2007) to provide you a browser based interface.

Depending on the volume of transactions and the number of concurrent users, you can setup Employee Portal to run with either WSS or MOSS. However with MOSS, you would get additional support for making further developments.

Employee Portal can also be identified as a Web Portal for Sales Persons who travels all over the country. In such instances, salesperson can logging to NAV and start creating Sales Orders or Sales Quotes as required by them.

Let’s look at some typical screens available in the default Web pages.

Item List /Product List Page
This page can be configured as a combination of List Web Part and Card Web Part page in such a way that Item List is available in the top side of the page. Once user clicks on the ‘Card’ hyperlink on the list then user is directed to the Item Card of the particular Item in the bottom of the page.


What is Employee Portal

There are number of options available in the Item Card where user can modify or even delete the existing items from the system. It also gives some reporting options such as ‘Quantity Explosion of BOM’, ‘Rolled-up Cost Shares’ and ‘Detailed Calculation’



Sales Order Page
As in Item Page, existing Sales Orders can be displayed in the top of the page. As and when user clicks on the particular Sales Order Number then user can be directed to Sales Order Card screen where both the header and the line information are displayed.



However there is no static principle in designing the web pages in Navision Employee Portal. With the given Web Parts, you always have the flexibility to for designing each web page according to the user requirement. For example, in the sales Order screen, instead of showing all existing sales orders in the system, you can display all existing customers in the system. From the Customer List you can filter respective sales orders list belongs to particular customer and then you display the card view of a selected sales order.

Since the installation and setup process takes a very sensitive path, it is recommended that you must strictly follow the standard Microsoft Installation and Configuration Guidelines without any change.

To be continued…