Displaying data from a SQL view in NAV

In this recipe we will see how to display data for a SQL view.

How to do it...

1. Open SQL Server Management Studio.

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

3. Copy the following code to the query window and execute it:

   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 International Ltd_$Detailed Cust_ Ledg_ Entry]
   GROUP BY "Customer No_", "Initial Entry Due Date",
   "Posting Date"

4. Create a new table in Object Designer.

5. Add the following fields to the table:

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

6. Add the following properties to the table:

   Property	Value
   DataPerCompany	No
   LinkedObject	Yes
   LinkedInTransaction	No

7. Save the table as Customer...