Hi All
I am using Microsoft Dynamics NAV 2013 , I also use Tableau to connect to my relational tables in SQL which is my data source for NAV, I am select data from the Value Entry , Item Ledger Entry and Item tables into a new table. The Value Entry Table and Item Ledger Entry Table joins to the Item table
The insert into query into the new table takes a while and then times out. then I have figured by grouping the date column will speed up the process which it does as I have tested the query by grouping the date , see script below
USE SPIERLIVE
GO
SELECT DATEPART(Year, d.[Posting Date]) Year, DATEPART(Month, d.[Posting Date]) Month,
d. [Source No_], c.[Company], a.[Inventory Posting Group], a.[Item Category Code], c.[Variant Code],d.[Global Dimension 1 Code] AS [Brand Class No],
d.[Sales Amount (Actual)], d.[Cost Amount (Actual)], d.[Expected Cost],d.[Sales Amount (Expected)], d.[Cost Amount (Expected)],
b.[Quantity], b.[Remaining Quantity], c.[Quantity] AS [WineMS Quantity],
c.[Cost Amount] AS [WineMS Cost Amount]
FROM [dbo].[Spier Live$Item Ledger Entry] b, [dbo].[Spier Live$Item] a, [dbo].[WineMS Inv_Transaction Tbl]
c, [dbo].[Spier Live$Value Entry] d
WHERE b.[Item No_] = a.[No_]
AND c.[Item No] = a.[No_]
GROUP BY b.[Quantity], b.[Remaining Quantity],
c.[Company],c.[Variant Code], c.[Global Dimension 1 Code], c.[Quantity],
c.[Cost Amount],
DATEPART(Year, d.[Posting Date]), DATEPART(Month, d.[Posting Date]),
I do not know if this is the correct way of doing the grouping for the dates. Our financial Year/Fiscal year
starts on the 1 July of every year and ending on 30 June of every year.
The DATEPART(Year, d.[Posting Date]),does group the year and it does display the years ,
however the DATEPART(Month, d.[Posting Date]), displays the month number e.g. month 6 for example and not the name of the month e.g. January
how can I get the month Name displayed. Should I also group the value columns and only the posting date
