I completed my first report in nav, and now I need to add to the report. The report is http://dynamicsuser.net/forums/t/42231.aspx I need to add some fields that are in the Item table….
(description and description 2 fields) need to be added and I need to sum the quantity field and NOT return any results with 0 in the quantity field only negative numbers .
This is how I would do the report in SSRS , can someone string me along on how to produce this report in nav. I am an extreme noob with Dynamics NAV
with rs as (
select
a.[Item No_]
,a.[Entry Type]
,a.[Posting Date]
,a.[Global Dimension 1 Code]
,a.[Global Dimension 2 Code]
,b.[Description]
,b.[Description 2]
,SUM(a.[Invoiced Quantity])over(partition by [Item No_]) as InvoicedQuantity
,SUM(a.[Invoiced Quantity])over(partition by [Item No_]) as Quantity,
RANK() OVER (partition by [Item No_] order by [Posting Date] DESC) as rn
from [Ulterra Drilling Technologies$Item Ledger Entry] a
join [Ulterra Drilling Technologies$Item] b on b.No_=a.[Item No_]
where
[Item No_]
between COALESCE(@startNo,[Item No_]) and COALESCE(@endNo,[Item No_])
and [Posting Date]
between COALESCE(@startDate,[Posting Date]) and COALESCE(@endDate,[Posting Date])
)
select distinct
[Item No_]
,[Global Dimension 1 Code]
,[Global Dimension 2 Code]
,[Entry Type]
,[Posting Date]
,entType
,[Description]
,[Description 2]
,[Quantity]
,[InvoicedQuantity]
from rs r
join @english e on e.entNo=r.[Entry Type]
where
[Quantity]<> ‘0’
and
rn = 1;