SQL Reporting question concerning G/L Total-Balance
gob_Behnke
Member Posts: 14
Hi,
I was asked to recreate the default Navision Report G/L Total-Balance (in NAV 2009 Report 11002) using SQL Reporting Services.
The first steps were quite easy, but somehow the totaling will not work. The problem is, that the information what accounts should be summed is in the table "G/L Account", whereas the corresponding amounts are in the table "G/L Entry".
Finally my query got quite expanded, and some of the Subselects are returning more than one Value, though i don't know why (see comments in code to see where the error occurs).
Now it looks like this:
I'm wondering if someone knows an easier way, to make this work,
or if someone knows why the error occurs, that the subselect returns more than one value (see comment in Query).
Thanks in advance.
With kind regards, Michael
I was asked to recreate the default Navision Report G/L Total-Balance (in NAV 2009 Report 11002) using SQL Reporting Services.
The first steps were quite easy, but somehow the totaling will not work. The problem is, that the information what accounts should be summed is in the table "G/L Account", whereas the corresponding amounts are in the table "G/L Entry".
Finally my query got quite expanded, and some of the Subselects are returning more than one Value, though i don't know why (see comments in code to see where the error occurs).
Now it looks like this:
-- The first block before the UNION-Statement selects the Accounts and their Amount-Fields without the Summation-Accounts
-- Everything works fine, but some of the accounts are summations of other accounts, and these summations are not shown.
SELECT No_ AS Nummer, Name,
(SELECT SUM(Amount) AS Saldo1
FROM [CRONUS AG$G_L Entry]
WHERE (GLAcc.No_ = [G_L Account No_]) AND ([Posting Date] <= DATEADD(Day, - 1, @begin))) AS Saldovortrag,
(SELECT SUM([Debit Amount]) AS Soll1
FROM [CRONUS AG$G_L Entry] AS [CRONUS AG$G_L Entry_1]
WHERE (GLAcc.No_ = [G_L Account No_]) AND ([Posting Date] BETWEEN @von AND @bis)) AS PeriodeSoll,
(SELECT SUM([Credit Amount]) AS Haben1
FROM [CRONUS AG$G_L Entry] AS [CRONUS AG$G_L Entry_1]
WHERE (GLAcc.No_ = [G_L Account No_]) AND ([Posting Date] BETWEEN @von AND @bis)) AS PeriodeHaben,
(SELECT SUM(Amount) AS PeriodEndSaldo
FROM [CRONUS AG$G_L Entry] AS [CRONUS AG$G_L Entry_1]
WHERE (GLAcc.No_ = [G_L Account No_]) AND ([Posting Date] <= @bis)) AS PeriodeEndsaldo,
(SELECT SUM([Debit Amount]) AS Soll2
FROM [CRONUS AG$G_L Entry] AS [CRONUS AG$G_L Entry_1]
WHERE (GLAcc.No_ = [G_L Account No_]) AND ([Posting Date] BETWEEN @begin AND @bis)) AS JahrSoll,
(SELECT SUM([Credit Amount]) AS Haben2
FROM [CRONUS AG$G_L Entry] AS [CRONUS AG$G_L Entry_1]
WHERE (GLAcc.No_ = [G_L Account No_]) AND ([Posting Date] BETWEEN @begin AND @bis)) AS JahrHaben,
(SELECT SUM(Amount) AS JahrEndSaldo
FROM [CRONUS AG$G_L Entry] AS [CRONUS AG$G_L Entry_1]
WHERE (GLAcc.No_ = [G_L Account No_]) AND ([Posting Date] <= DATEADD(year, 1, DATEADD(Day, - 1, @begin)))) AS JahrEndsaldo
FROM [CRONUS AG$G_L Account] AS GLAcc
UNION
-- The second block should take care about the Summation-Accounts:
-- The Account Numbers of the field "Totaling" in the table "G/L Account" are used as a filter for the corresponing amounts in the "G/L
-- Entry" - table. Therefore i need to split the "Totaling"-field (this cotains, e.g. 0030..0070) into two brackets and create a range-filter.
SELECT No_ AS Nummer, Name,
(SELECT SUM(Amount) AS Saldo1
FROM [CRONUS AG$G_L Entry] AS [CRONUS AG$G_L Entry_8]
WHERE ([G_L Account No_] BETWEEN LEFT
((SELECT Totaling
FROM [CRONUS AG$G_L Account]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_8].[G_L Account No_])), CHARINDEX('..',
(SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_27]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_8].[G_L Account No_])) - 1)) AND RIGHT
((SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_26]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_8].[G_L Account No_])), CHARINDEX('..',
(SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_25]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_8].[G_L Account No_])) - 1))) AND ([Posting Date] <= DATEADD(Day, - 1, @begin)))
AS Saldovortrag,
-- The following subselect (for example) causes an error: The Subselect has returned more than one value (message 512)
-- I expected that it would only return one value. Each Account as a maximum of one "Totaling"-expression and thus only one
-- corresponding amount. Somewhere i made a mistake, i just don't know where.
(SELECT SUM([Debit Amount]) AS Soll1
FROM [CRONUS AG$G_L Entry] AS [CRONUS AG$G_L Entry_7]
WHERE ([G_L Account No_] BETWEEN LEFT
((SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_24]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_7].[G_L Account No_])), CHARINDEX('..',
(SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_23]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_7].[G_L Account No_])) - 1)) AND RIGHT
((SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_22]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_7].[G_L Account No_])), CHARINDEX('..',
(SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_21]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_7].[G_L Account No_])) - 1))) AND ([Posting Date] BETWEEN @von AND @bis))
AS PeriodeSoll,
(SELECT SUM([Credit Amount]) AS Haben1
FROM [CRONUS AG$G_L Entry] AS [CRONUS AG$G_L Entry_6]
WHERE ([G_L Account No_] BETWEEN LEFT
((SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_20]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_6].[G_L Account No_])), CHARINDEX('..',
(SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_19]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_6].[G_L Account No_])) - 1)) AND RIGHT
((SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_18]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_6].[G_L Account No_])), CHARINDEX('..',
(SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_17]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_6].[G_L Account No_])) - 1))) AND ([Posting Date] BETWEEN @von AND @bis))
AS PeriodeHaben,
(SELECT SUM(Amount) AS PeriodEndSaldo
FROM [CRONUS AG$G_L Entry] AS [CRONUS AG$G_L Entry_5]
WHERE ([G_L Account No_] BETWEEN LEFT
((SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_16]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_5].[G_L Account No_])), CHARINDEX('..',
(SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_15]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_5].[G_L Account No_])) - 1)) AND RIGHT
((SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_14]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_5].[G_L Account No_])), CHARINDEX('..',
(SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_13]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_5].[G_L Account No_])) - 1))) AND ([Posting Date] <= @bis)) AS PeriodeEndsaldo,
(SELECT SUM([Debit Amount]) AS Soll2
FROM [CRONUS AG$G_L Entry] AS [CRONUS AG$G_L Entry_4]
WHERE ([G_L Account No_] BETWEEN LEFT
((SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_12]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_4].[G_L Account No_])), CHARINDEX('..',
(SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_11]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_4].[G_L Account No_])) - 1)) AND RIGHT
((SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_10]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_4].[G_L Account No_])), CHARINDEX('..',
(SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_9]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_4].[G_L Account No_])) - 1))) AND ([Posting Date] BETWEEN @begin AND @bis))
AS JahrSoll,
(SELECT SUM([Credit Amount]) AS Haben2
FROM [CRONUS AG$G_L Entry] AS [CRONUS AG$G_L Entry_3]
WHERE ([G_L Account No_] BETWEEN LEFT
((SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_8]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_3].[G_L Account No_])), CHARINDEX('..',
(SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_7]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_3].[G_L Account No_])) - 1)) AND RIGHT
((SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_6]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_3].[G_L Account No_])), CHARINDEX('..',
(SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_5]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_3].[G_L Account No_])) - 1))) AND ([Posting Date] BETWEEN @begin AND @bis))
AS JahrHaben,
(SELECT SUM(Amount) AS JahrEndSaldo
FROM [CRONUS AG$G_L Entry] AS [CRONUS AG$G_L Entry_2]
WHERE ([G_L Account No_] BETWEEN LEFT
((SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_4]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_2].[G_L Account No_])), CHARINDEX('..',
(SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_3]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_2].[G_L Account No_])) - 1)) AND RIGHT
((SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_2]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_2].[G_L Account No_])), CHARINDEX('..',
(SELECT Totaling
FROM [CRONUS AG$G_L Account] AS [CRONUS AG$G_L Account_1]
WHERE (GLAcc.No_ = [CRONUS AG$G_L Entry_2].[G_L Account No_])) - 1))) AND ([Posting Date] <= DATEADD(year, 1, DATEADD(Day,
- 1, @begin)))) AS JahrEndsaldo
FROM [CRONUS AG$G_L Account] AS GLAcc
I'm wondering if someone knows an easier way, to make this work,
or if someone knows why the error occurs, that the subselect returns more than one value (see comment in Query).
Thanks in advance.
With kind regards, Michael
0
Comments
-
Sorry, it was not meant to post again.
I've added a picture of the Navision-tables.
Here you can see how it should work. I just want to make the same for SQL-Reporting.0
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1.1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions