Displaying data from a SQL view in NAV

Most of the data in NAV is stored in tables, but you can also display data from other sources. This recipe will explain how to show data from a SQL View in NAV.

How to do it...

1. Open SQL Server Management Studio.

2. Select your database and open a new query window.

3. Execute the following code:

   CREATE VIEW [Customer Ledger View] AS
   SELECT "Customer No_","Initial Entry Due Date","Posting Date", COUNT_BIG(*) "$Cnt", SUM("Amount") "SUM$Amount", SUM("Amount (LCY)") "SUM$Amount (LCY)"
   FROM "CRONUS USA, Inc_$Detailed Cust_ Ledg_ Entry"
   GROUP BY "Customer No_", "Initial Entry Due Date", "Posting Date"
   

4. Create a new table from Object Designer.

5. Add the following fields to the table:

   Field Name	Data Type	Length
   Customer No_	Code	20
   Initial Entry Due Date	Date
   Posting Date	Date
   $Cnt	BigInteger
   SUM$Amount	Decimal
   SUM$Amount (LCY)

6. Set the following properties on the table.

   Property	Value
   DataPerCompany	No
   LinkedObject	Yes
   LinkedInTransaction...