October 2, 2008 at 1:42 pm
I tried that on the datasource and it did not help.
October 2, 2008 at 1:43 pm
Glen (10/2/2008)
cardgunner,I did not answer your problem. Original post is published by gardenlady.
According to a very brief description of a problem that you are having - it is exactly in the fact that you do not have a stored procedure. Make a separate post with the problem description, I am sure you will be helped in notime!
Sorry.
I was trying to find a common connection for a larger good.
I did post my problem. I'm almost certain it has nothing to do with stored procedures cause I have never used them or called on them in the 40+ reports I have. where as Friday they all worked. Monday they did not.
I gave my RDL to another consultant who has done the sames patches and updates as me. The only difference is he is operating Vista and I am not. He has no problem running my report.
When I trouble shoot I like to find other instances of the same problem.
October 2, 2008 at 1:54 pm
gardenlady,
can you please post your stored procedure here?
October 2, 2008 at 2:12 pm
Sure. It's a long one--it branches off into one of two options--I think I'll just paste the top part to remove some bulk. Also my IT guy is installing virtual machine and I should be able to test in Vista tomorrow sometime if I don't get sidetracked.
Forgive the messy comments and commented out code I've left for myself.
USE [trn]
GO
/****** Object: StoredProcedure [dbo].[Trans_History3_pr] Script Date: 10/02/2008 14:23:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Trans_History3_pr]
@Company AS INT,
@AccountingUnit as VARCHAR(100)
,@StartDate AS DATETIME
,@EndDate AS DATETIME
,@Accounts AS VARCHAR(100)
,@LRange as Bit
,@Group as nvarchar(16)
,@NoBalAct as bit
,@BegSubAcct as INT
,@EndSubAcct as INT
--1st test to get zero records ddr 9/24/08
--exec Trans_History3_pr 3000,'ALL', '2007-01-01','2007-01-01','10010,10010',1,'Account',1,0,999
--exec Trans_History3_pr 9330,'ALL', '2008-08-01','2008-08-09','10000,99999',1,'Account',1,0,999
--exec Trans_History3_pr 9330,'ALL', '2008-08-01','2008-09-30','10000,99999',1,'Account',0,0,999
--exec Trans_History3_pr 9330,'ALL', '2008-01-01','2008-01-08','10010,99999',1,'Account',0,0,999
--exec Trans_History3_pr 9330,'ALL', '2008-08-01','2008-08-31','10010,99999',1,'Acct_unit',1,0,999
--exec Trans_History3_pr 9330,'ALL', '2008-01-01','2008-01-08','10010,99999',1,'Acct_unit',0,0,999
--NOTE: This code is separated by sorting acct_unit, accounts and then by accounts,acct_unit
--Accordingly, you need to change both sets of code for any enhancements/bugs ddr 8/15/08
AS
----***********NOTES
----used Running Balances code found on the Internet ddr 9/10/08
----This procedure is used to get beginning balance (based on parameters)
----transactions with date range and calculate and ending balance
----It's a basic general ledger for the clients
----************
--
--ERROR CHECKING
--print @Group
if @StartDate > getdate() or @StartDate (getdate() + 10000) or @EndDate < '1970-1-01'
begin
--print 'Please check your date range. It appears to have an error.'
-- declare @PrintMsg nvarchar(50)
-- set @PrintMsg = 'Please check your date range. It appears to have an error.'
--it won't print but it at least stops processing.
return
end
--
--***LEAVE DECLARED
DECLARE
@FiscalYearStart as Int,
@FiscalYearEnd as Int,
@FiscalMonthStart as Int,
@FiscalMonthEnd as Int,
@FirstOfStartMonth as datetime,
@StartDateFirstDay as datetime,
@i AS INT,
@AcctUnit1 as nvarchar(50),
@IDs as int,
@Delim varchar(1),
@FirstLen int,
@LastLen int,
@NumCommasAccounts int,
@StartAccount AS INT,
@EndAccount AS INT,
@Counter AS INT,
@rowcount as int,
@rowcount2 as int,
@rowcounttmpGL as int,
@rowcountGLBegBal as int,
--@msg as nvarchar(5),
--@msg2 as nvarchar(5),
@rowcount3 as int
--setting up variables to check Accounts parameter
set @Delim = ','
set @FirstLen = len(@Accounts)
set @NumCommasAccounts = 0
set @StartAccount = 0
set @EndAccount = 0
set @rowcount = 0
set @rowcount2 = 0
set @rowcount3 = 0
set @rowcounttmpGL = 0
set @rowcountGLBegBal = 0
--SET @StartDate = @StartDate -1 -- to get 24 hours before midnight
----for testing*****
----SET @Company = 9330
----SET @StartDate = '2007-02-01'
----SET @EndDate = '2007-03-31'
----SET @StartAccount = 10042
----SET @EndAccount = 10042
----SET @AccountingUnit = 7030300
----SET @BegSubAcct = 020
----SET @EndSubAcct = 050
----*************
--
--print @StartDate
SET @FiscalYearStart = YEAR(@StartDate+1)
set @FiscalYearEnd = YEAR(@EndDate)
set @FiscalMonthStart = MONTH(dateadd(Day,1,@StartDate))
SET @FiscalMonthEnd = MONTH(dateadd(Day,1,@EndDate))
SET @StartAccount = 0
set @EndAccount = 0
--
----IF MONTH(@EndDate) = 1
---- BEGIN
---- SET @FiscalMonthPriortoEndDateMonth = 12
---- END
----ELSE
---- BEGIN
---- SET @FiscalMonthPriortoEndDateMonth = MONTH(@EndDate) -1
---- END
--TO get first of month of beginning date set to a variable
IF DAY (@StartDate) <> 1
BEGIN
SET @FirstOfStartMonth = dateadd(month, -1,DateAdd(Month,DateDiff(Month,0,@StartDate),0))
END
else
BEGIN
SET @FirstOfStartMonth = @StartDate
END
set @StartDateFirstDay = dateadd(month,0,DateAdd(Month,DateDiff(Month,0,@StartDate),0))
SET @Delim = ','
set @NumCommasAccounts = @FirstLen - LEN(Replace(@Accounts,@Delim,''))
--give default range based on no range, no commas and no account numbers input
--no account input - give them whole range
if @Accounts = '0' OR len(@Accounts) = 0
BEGIN
set @StartAccount = 10000
set @EndAccount = 99999
set @LRange = 1
END
--one account only, whether or not they clicked the Range checkbox
ELSE
BEGIN
--code based on user input
if @NumCommasAccounts >= 1 and len(@Accounts) > 0 and @Accounts <> '0'
begin
DECLARE @VALUES TABLE(Account nvarchar(4000))
declare @tmpAccountsRowNum table
(Acct int
,Range bit)
INSERT INTO @VALUES
select cast(param as int) from dbo.fn_MVParam(@Accounts,',')
SET @Counter = @@rowcount
CREATE TABLE #tmpAccounts
(RowNumber int IDENTITY(1,1)
,Account int
)
INSERT #tmpAccounts(Account)
Select DISTINCT Account
From @VALUES
IF @@ROWCOUNT = 2
SELECT @EndAccount = Account FROM #tmpAccounts WHERE RowNumber = 2
ELSE
SELECT @EndAccount = Account FROM #tmpAccounts WHERE RowNumber = 1
SELECT @StartAccount = Account FROM #tmpAccounts WHERE RowNumber = 1
-- print convert(nvarchar(5),@StartAccount)
--print convert(nvarchar(5),@EndAccount)
--range of 2 numbers
IF @NumCommasAccounts >= 1 and @LRange = 1 and @Counter = 2 -- gets rid of compile error-this should be empty, values already set
set @StartAccount = @StartAccount
--USE VALUES 'IN @tmpAccountsRowNum'
ELSE
BEGIN
set @StartAccount = 0
set @EndAccount = 0
SET @LRange = 0
END
END
-- else -- one account
-- IF ((@NumCommasAccounts -1)
-- and len(@Accounts) > 0) and @Accounts <> '0'
-- BEGIN
-- set @StartAccount = cast(@Accounts as int)
-- set @EndAccount = cast(@Accounts as int)
-- set @LRange = 1
-- END
end
--PRINT @StartAccount
--PRINT @EndAccount
--PRINT @LRange
--
--SELECT * FROM #tmpAccounts
----------------********************
------BEGINNING GL BALANCES FOR EACH MONTH FOR EACH FISCAL YEAR REQUIRED BY USER
------gets cumulative balances, debit and credit, by month and year
------based on time period, join this with transactions
------appears to me that you'll just need balances for month prior to ending month
------since it is cumulative and you'll need debittrans and credittrans
------selected below
------the view was built with Doug's help--vwLawsonGLAmounts, currently on dev-prod to speed
------up this query since GLAmounts is not well normalized.
------had to use the DB_AMOUNT_13 field to get an integer field to use for month
------get regular accounts first; memo accounts 2nd
----
DECLARE @tmpGLAmts TABLE(Company INT
,fiscal_year INT
,account INT
,sub_account INT
,acct_unit NVARCHAR(100)
,TMONTH INT
,DMTD DECIMAL(18,2)
,CMTD DECIMAL(18,2))
DECLARE @tmpViewGL TABLE
(Company INT
,fiscal_year INT
,account INT
,sub_account INT
,acct_unit NVARCHAR(100)
,TMONTH INT
,DMTD DECIMAL(18,2)
,CMTD DECIMAL(18,2))
DECLARE @tmpGLMemos TABLE(COMPANY INT
,fiscal_year INT
,account INT
,sub_account INT
,acct_unit NVARCHAR(100)
,TMONTH INT
,DMTD DECIMAL(18,2)
,CMTD DECIMAL(18,2))
--
DECLARE @tmpGLMemoAmounts TABLE(COMPANY INT
,fiscal_year INT
,account INT
,sub_account INT
,acct_unit NVARCHAR(100)
,TMONTH INT
,DMTD DECIMAL(18,2)
,CMTD DECIMAL(18,2))
DECLARE @tmp_GLAccounts table(Company int
,var_levels nvarchar(30)
,account int
,sub_account int
,acct_unit nvarchar(15)
,chart_name nvarchar(12)
,description nvarchar(30)
,account_desc nvarchar(60))
DECLARE @tmp_GLBegBalTrans TABLE
(company int
,fiscal_year int
,acct_unit nvarchar(100)
,account int
,sub_account int
,debittrans decimal(24,2)
,credittrans decimal(24,2)
,description nvarchar(30)
)
DECLARE @tmp_GLBegBal TABLE
(company int
,fiscal_year int
,acct_unit nvarchar(100)
,account int
,sub_account int
,DebitsBeginning decimal(24,2)
,CreditsBeginning decimal(24,2)
,TMONTH int
,account_desc nvarchar(150)
,debittrans decimal(24,2)
,credittrans decimal(24,2)
,description nvarchar(30)
)
DECLARE @tmp_CurrPeriodTrans table
(company int
,fiscal_year int
,acct_unit nvarchar(100)
,account int
,sub_account int
,posting_date datetime
,tranamount decimal(24,2)
,reference nvarchar(150)
,description nvarchar(150)
,update_date datetime
,control_group int
,system nvarchar(2)
)
DECLARE @tmp_CurrPeriodTrans2 table
(company int
,fiscal_year int
,acct_unit nvarchar(100)
,account int
,sub_account int
,posting_date datetime
,tranamount decimal(24,2)
,reference nvarchar(150)
,description nvarchar(150)
,update_date datetime
,control_group int
,system nvarchar(2)
)
DECLARE @tmp_Totals TABLE
(company int
,fiscal_year int
,TMONTH INT
,acct_unit nvarchar(100)
,account int
,sub_account int
,TotBegDebits decimal(24,2)
,TotBegCredits decimal(24,2)
,begbal decimal(24,2)
,totaldebits decimal(24,2)
,totalcredits decimal(24,2)
)
--do not turn into # tmp table as I have to DELETE with it and it has an identity column
DECLARE @tmpZeros Table
(RowNum int, BegBal decimal (24,2), totdebits decimal(18,2)
,totcredits decimal(18,2), fiscal_year int
, account int, acct_unit nvarchar(15)
, sub_account int)
--print (@Group)
--
--*************************sort by acct_unit or by account****************************
if rtrim(@Group) = 'Account' and right(rtrim(@Group),4) <> 'Unit'
BEGIN
--get ending balances at a month from the view built with Doug
--had to build views for both tables because they are not normalized and months are columns
--which doesn't work
--dbo.vwLawsonGLAmounts only has accounts less than 80000
--dbo.vwLawsonGLAmounts_Memo only has accounts 80000 and higher
--Print @AccountingUnit
--print convert(nvarchar(10),LEN(@AccountingUnit))
--print @StartAccount
--print @EndAccount
--print @LRange
--print @FiscalYearStart
--print @FiscalYearEnd
IF UPPER(@AccountingUnit) <> 'ALL' AND LEN(@AccountingUnit) > 0
IF @StartAccount <> 0 AND @StartAccount <> 0 and @LRange = 1
BEGIN
insert into @tmpViewGL
SELECT company, fiscal_year, account, sub_account, acct_unit
,DB_AMOUNT_13 AS TMONTH, SUM(dbo.vwLawsonGLAmounts.DMTD) AS DMTD
,SUM(dbo.vwLawsonGLAmounts.CMTD) AS CMTD
FROM dbo.vwLawsonGLAmounts
WHERE company = @Company
and fiscal_year between @FiscalYearStart and @FiscalYearEnd
and acct_unit in (@AccountingUnit)
and account between @StartAccount and @EndAccount
and sub_account between @BegSubAcct and @EndSubAcct
GROUP BY company,fiscal_year,account,acct_unit,sub_account,DB_AMOUNT_13
--ORDER BY company,fiscal_year,account,sub_account,acct_unit,DB_AMOUNT_13
INSERT INTO @tmpGLMemos
SELECT company, fiscal_year, account, sub_account, acct_unit
,DB_UNITS_13 as TMONTH, SUM(dbo.vwLawsonGLAmounts_Memo.DMTD) AS DMTD
, SUM(dbo.vwLawsonGLAmounts_Memo.CMTD) AS CMTD
FROM dbo.vwLawsonGLAmounts_Memo
WHERE company = @Company
and fiscal_year between @FiscalYearStart and @FiscalYearEnd
and acct_unit in (@AccountingUnit)
and account between @StartAccount and @EndAccount
and sub_account between @BegSubAcct and @EndSubAcct
GROUP BY company,fiscal_year,account,acct_unit,sub_account,DB_UNITS_13
--ORDER BY company,fiscal_year,acct_unit,account,sub_account,DB_UNITS_13
END
else
BEGIN
insert into @tmpViewGL
SELECT company, fiscal_year, account, sub_account, acct_unit
,DB_AMOUNT_13 AS TMONTH, SUM(dbo.vwLawsonGLAmounts.DMTD) AS DMTD
,SUM(dbo.vwLawsonGLAmounts.CMTD) AS CMTD
FROM dbo.vwLawsonGLAmounts
WHERE company = @Company
and fiscal_year between @FiscalYearStart and @FiscalYearEnd
and acct_unit in (@AccountingUnit)
and account in (select Account from #tmpAccounts)
and sub_account between @BegSubAcct and @EndSubAcct
GROUP BY company,fiscal_year,account,acct_unit,sub_account,DB_AMOUNT_13
-- ORDER BY company,fiscal_year,account,sub_account,acct_unit,DB_AMOUNT_13
INSERT INTO @tmpGLMemos
SELECT company, fiscal_year, account, sub_account, acct_unit
,DB_UNITS_13 as TMONTH, SUM(dbo.vwLawsonGLAmounts_Memo.DMTD) AS DMTD
, SUM(dbo.vwLawsonGLAmounts_Memo.CMTD) AS CMTD
FROM dbo.vwLawsonGLAmounts_Memo
WHERE company = @Company
and fiscal_year between @FiscalYearStart and @FiscalYearEnd
and acct_unit in (@AccountingUnit)
and account in (select Account from #tmpAccounts)
and sub_account between @BegSubAcct and @EndSubAcct
GROUP BY company,fiscal_year,account,acct_unit,sub_account,DB_UNITS_13
-- ORDER BY company,fiscal_year,acct_unit,account,sub_account,DB_UNITS_13
END
else -- ALL ACCOUNTING UNITS
--range of accounts
IF @StartAccount <> 0 AND @EndAccount <> 0 and @LRange = 1
BEGIN
insert into @tmpViewGL
SELECT company, fiscal_year, account, sub_account, acct_unit
,DB_AMOUNT_13 AS TMONTH, SUM(dbo.vwLawsonGLAmounts.DMTD) AS DMTD
,SUM(dbo.vwLawsonGLAmounts.CMTD) AS CMTD
FROM dbo.vwLawsonGLAmounts
WHERE company = @Company
and fiscal_year between @FiscalYearStart and @FiscalYearEnd
and account between @StartAccount and @EndAccount
and sub_account between @BegSubAcct and @EndSubAcct
GROUP BY company,fiscal_year,account,acct_unit,sub_account,DB_AMOUNT_13
--ORDER BY company,fiscal_year,account,sub_account,acct_unit,DB_AMOUNT_13
INSERT INTO @tmpGLMemos
SELECT company, fiscal_year, account, sub_account, acct_unit
,DB_UNITS_13 as TMONTH, SUM(dbo.vwLawsonGLAmounts_Memo.DMTD) AS DMTD
, SUM(dbo.vwLawsonGLAmounts_Memo.CMTD) AS CMTD
FROM dbo.vwLawsonGLAmounts_Memo
WHERE company = @Company
and fiscal_year between @FiscalYearStart and @FiscalYearEnd
and account between @StartAccount and @EndAccount
and sub_account between @BegSubAcct and @EndSubAcct
GROUP BY company,fiscal_year,account,acct_unit,sub_account,DB_UNITS_13
--ORDER BY company,fiscal_year,acct_unit,account,sub_account,DB_UNITS_13
END
else -- get all accounting units, not a range
BEGIN
insert into @tmpViewGL
SELECT company, fiscal_year, account, sub_account, acct_unit
,DB_AMOUNT_13 AS TMONTH, SUM(dbo.vwLawsonGLAmounts.DMTD) AS DMTD
, SUM(dbo.vwLawsonGLAmounts.CMTD) AS CMTD
FROM dbo.vwLawsonGLAmounts
WHERE company = @Company
and fiscal_year between @FiscalYearStart and @FiscalYearEnd
and account in (select Account from #tmpAccounts)
and sub_account between @BegSubAcct and @EndSubAcct
GROUP BY company,fiscal_year,account,acct_unit,sub_account,DB_AMOUNT_13
--ORDER BY company,fiscal_year,account,sub_account,acct_unit,DB_AMOUNT_13
INSERT INTO @tmpGLMemos
SELECT company, fiscal_year, account, sub_account, acct_unit
,DB_UNITS_13 as TMONTH, SUM(dbo.vwLawsonGLAmounts_Memo.DMTD) AS DMTD,
SUM(dbo.vwLawsonGLAmounts_Memo.CMTD) AS CMTD
FROM dbo.vwLawsonGLAmounts_Memo
WHERE company = @Company
and fiscal_year between @FiscalYearStart and @FiscalYearEnd
and account in (select Account from #tmpAccounts)
and sub_account between @BegSubAcct and @EndSubAcct
GROUP BY company,fiscal_year,account,acct_unit,sub_account,DB_UNITS_13
--ORDER BY company,fiscal_year,acct_unit,account,sub_account,DB_UNITS_13
END
INSERT INTO @tmpGLAmts
SELECT company, fiscal_year, account, sub_account, acct_unit
,TMONTH, SUM(DMTD) AS DMTD, SUM(CMTD) AS CMTD
FROM @tmpViewGL
WHERE TMONTH = @FiscalMonthStart
GROUP BY company,fiscal_year,account,acct_unit,sub_account,TMONTH
INSERT INTO @tmpGLMemoAmounts
SELECT company, fiscal_year, account, sub_account, acct_unit
,TMONTH, SUM(DMTD) AS DMTD, SUM(CMTD) AS CMTD
FROM @tmpGLMemos
WHERE TMONTH = @FiscalMonthStart
GROUP BY company,fiscal_year,TMONTH,account,acct_unit,sub_account
SELECT @rowcounttmpGL = count(*) from @tmpGLAmts
--select * from @tmpGLAmts
--select * from @tmpGLMemoAmounts
if LEN(@AccountingUnit) = 0
begin
print 'Must have accounting unit--can be "ALL"--for report to run'
-- IF OBJECT_ID('tempdb..#tmpAccounts') IS NOT NULL
-- DROP TABLE #tmpAccount
return(0)
end
----have to get all accounts for left outer join below even though they may not
----have beginning balances, they may have transactions and won't be available
----for the left side of the join without this
IF UPPER(@AccountingUnit) <> 'ALL' --AND LEN(@AccountingUnit) > 0
IF @StartAccount <> 0 AND @EndAccount <> 0 and @LRange = 1
BEGIN
insert INTO @tmp_GLAccounts
select distinct m.company
,m.var_levels
,m.account
,m.sub_account
,m.acct_unit
,m.chart_name
,n.description
,chdtl.account_desc
from dbo.glmaster m
inner join dbo.glnames n on m.company = n.company
and m.acct_unit = n.acct_unit
inner join dbo.glchartdtl chdtl
on m.chart_name = chdtl.chart_name
and m.account = chdtl.account
and m.sub_account = chdtl.sub_account
where m.company = @Company
AND m.acct_unit in (@AccountingUnit)
AND m.account between @StartAccount and @EndAccount
and m.sub_account >= @BegSubAcct and m.sub_account <= @EndSubAcct
END
ELSE
--if @StartAccount = 0 AND @EndAccount = 0 or @LRange = 0
BEGIN
insert INTO @tmp_GLAccounts
select distinct m.company
,m.var_levels
,m.account
,m.sub_account
,m.acct_unit
,m.chart_name
,n.description
,chdtl.account_desc
from dbo.glmaster m
inner join dbo.glnames n on m.company = n.company
and m.acct_unit = n.acct_unit
inner join dbo.glchartdtl chdtl on m.chart_name = chdtl.chart_name
and m.account = chdtl.account
and m.sub_account = chdtl.sub_account
where m.company = @Company
AND m.acct_unit in (@AccountingUnit)
AND m.account in (select Account from #tmpAccounts)
and m.sub_account >= @BegSubAcct and m.sub_account <= @EndSubAcct
END
else
-- UPPER(@AccountingUnit) = 'ALL'
--Acct_unit code --for index
IF @StartAccount <> 0 AND @EndAccount <> 0
BEGIN
INSERT INTO @tmp_GLAccounts
select DISTINCT m.company
,m.var_levels
,m.account
,m.sub_account
,m.acct_unit
,m.chart_name
,n.description
,chdtl.account_desc
from dbo.glmaster m
inner join dbo.glnames n on m.company = n.company
and m.acct_unit = n.acct_unit
inner join dbo.glchartdtl chdtl
on m.chart_name = chdtl.chart_name
and m.account = chdtl.account
and m.sub_account = chdtl.sub_account
where m.company = @Company
AND m.account between @StartAccount and @EndAccount
and m.sub_account >= @BegSubAcct
and m.sub_account <= @EndSubAcct
END
else --acct unit = ALL and select accounts
BEGIN
INSERT INTO @tmp_GLAccounts
select DISTINCT m.company
,m.var_levels
,m.account
,m.sub_account
,m.acct_unit
,m.chart_name
,n.description
,chdtl.account_desc
from dbo.glmaster m
inner join dbo.glnames n on m.company = n.company
and m.var_levels = n.var_levels
and m.acct_unit = n.acct_unit
inner join dbo.glchartdtl chdtl on
m.chart_name = chdtl.chart_name
and m.account = chdtl.account
and m.sub_account = chdtl.sub_account
where m.company = @Company
AND m.account in (select Account from #tmpAccounts)
and m.sub_account >= @BegSubAcct
and m.sub_account <= @EndSubAcct
END
------get beginning GL balances
DECLARE @tmp_GLBegAccounts TABLE
(company int
, fiscal_year int
, acct_unit nvarchar(100)
,account int, sub_account int
,account_desc nvarchar(60)
,description nvarchar(30)
, TMONTH int
,BegDebits decimal (24,2)
, BegCredits decimal (24,2)
)
------range or one account
insert into @tmp_GLBegAccounts
select glaa.company
,gla.fiscal_year
,glaa.acct_unit
,glaa.account
,glaa.sub_account
,glaa.account_desc
,glaa.description
,gla.TMONTH
,gla.DMTD as BegDebits
,gla.CMTD as BegCredits
from @tmp_GLAccounts glaa
left outer join @tmpGLAmts gla on glaa.company = gla.company
and glaa.acct_unit = gla.acct_unit
and glaa.account = gla.account
and glaa.sub_account = gla.sub_account
where glaa.company = @Company
and gla.fiscal_year >= @FiscalYearStart
and gla.fiscal_year <= @FiscalYearEnd
AND GLaa.ACCOUNT < 80000
UNION
select glaa.company
,gla.fiscal_year
,glaa.acct_unit
,glaa.account
,glaa.sub_account
,glaa.account_desc
,glaa.description
,gla.TMONTH
,gla.DMTD as BegDebits
,gla.CMTD as BegCredits
from @tmp_GLAccounts glaa
left outer join @tmpGLMemoAmounts gla on glaa.company = gla.company
and glaa.acct_unit = gla.acct_unit
and glaa.account = gla.account
and glaa.sub_account = gla.sub_account
where glaa.company = @Company
and gla.fiscal_year >= @FiscalYearStart
and gla.fiscal_year <= @FiscalYearEnd
AND GLaa.ACCOUNT > 79999
SELECT @rowcountGLBegBal= COUNT(*) FROM @tmp_GLAccounts
IF @StartAccount = 0 AND @EndAccount = 0
begin
select *
INTO #tmp_GLTransPullFromTmp
from GLTrans glt
where glt.company = @Company
and glt.fiscal_year between @FiscalYearStart and @FiscalYearEnd
AND glt.account in (SELECT Account from #tmpAccounts)
and glt.sub_account between @BegSubAcct and @EndSubAcct
and (glt.posting_date >= @FirstOfStartMonth
and glt.posting_date <= @EndDate) -- dateadd(dd,1,@EndDate) )
and glt.posting_date <> '1900-01-01'
and glt.status = 9
SELECT @rowcount = count(*) from #tmp_GLTransPullFromTmp
end
IF @StartAccount <> 0 AND @EndAccount <> 0 and @LRange = 1
begin
select *
INTO #tmp_GLTransPullBegEnd
from GLTrans glt
where glt.company = @Company
and glt.fiscal_year between @FiscalYearStart and @FiscalYearEnd
and glt.account between @StartAccount and @EndAccount
and glt.sub_account between @BegSubAcct and @EndSubAcct
and (glt.posting_date >= @FirstOfStartMonth
and glt.posting_date <= @EndDate) --dateadd(dd,1,@EndDate) )
and glt.posting_date <> '1900-01-01'
and glt.status = 9
SELECT @rowcount2 = count(*) from #tmp_GLTransPullBegEnd
END
----Transactions to add to beginning blances
----non sequential acct #s or one number
-----DECLARE @tmp_GLBegBalTrans TABLE above
------PRINT @StartAccount
------PRINT @EndAccount
------PRINT @StartDateFirstDay
------PRINT @FirstOfStartMonth
IF @StartAccount <> 0 AND @EndAccount <> 0 and @LRange = 1
INSERT INTO @tmp_GLBegBalTrans
select gla.company
,gla.fiscal_year
,gla.acct_unit
,gla.account
,gla.sub_account
,sum(case when (glt.tran_amount > 0 or glt.units_amount > 0)
and (glt.posting_date >= @StartDateFirstDay
and glt.posting_date < @StartDate )
then glt.tran_amount+glt.units_amount else 0 end)
as debittrans
,sum(case when (glt.tran_amount < 0 or glt.units_amount < 0)
and (glt.posting_date >= @StartDateFirstDay
and glt.posting_date < @StartDate )
then glt.tran_amount+glt.units_amount else 0 end)
as credittrans
,gla.description
from @tmp_GLBegAccounts gla
left outer join #tmp_GLTransPullBegEnd glt on gla.company = glt.company
and gla.fiscal_year = glt.fiscal_year
and gla.acct_unit = glt.acct_unit
and gla.account = glt.account
and gla.sub_account = glt.sub_account
where gla.fiscal_year between @FiscalYearStart and @FiscalYearEnd
--and gla.account between @StartAccount and @EndAccount
and gla.sub_account >= @BegSubAcct and gla.sub_account <= @EndSubAcct
group by gla.company,gla.fiscal_year
,gla.account,gla.acct_unit,gla.sub_account, gla.description
order by gla.company,gla.fiscal_year
,gla.account,gla.acct_unit,gla.sub_account, gla.description
--ELSE
IF @StartAccount = 0 AND @EndAccount = 0
begin
INSERT INTO @tmp_GLBegBalTrans
select gla.company
,gla.fiscal_year
,gla.acct_unit
,gla.account
,gla.sub_account
,sum(case when (glt.tran_amount > 0 or glt.units_amount > 0)
and (glt.posting_date >= @StartDateFirstDay
and glt.posting_date < @StartDate )AND
(glt.posting_date >= @FirstOfStartMonth
and glt.posting_date <= @EndDate )
then glt.tran_amount+glt.units_amount else 0 end)
as debittrans
,sum(case when (glt.tran_amount < 0 or glt.units_amount < 0)
and (glt.posting_date >= @StartDateFirstDay
and glt.posting_date < @StartDate ) AND
(glt.posting_date >= @FirstOfStartMonth
and glt.posting_date <= @EndDate )
then glt.tran_amount+glt.units_amount else 0 end)
as credittrans
,gla.description
from @tmp_GLBegAccounts gla
left outer join #tmp_GLTransPullFromTmp glt on gla.company = glt.company
and gla.fiscal_year = glt.fiscal_year
and gla.acct_unit = glt.acct_unit
and gla.account = glt.account
and gla.sub_account = glt.sub_account
where gla.sub_account >= @BegSubAcct and gla.sub_account <= @EndSubAcct
and gla.fiscal_year >= @FiscalYearStart and gla.fiscal_year <= @FiscalYearEnd
--and gla.account in (SELECT Account from #tmpAccounts)
group by gla.company,gla.fiscal_year
,gla.account,gla.acct_unit,gla.sub_account, gla.description
order by gla.company,gla.fiscal_year
,gla.account,gla.acct_unit,gla.sub_account, gla.description
END
-- ----BEGINNING BALANCES--adds GLTrans to month end totals if they start on a day other
-- -- than the first day of the month--just the range of GLTrans from the beginning
-- -- of the month to before the beginning date
-- ----add debittrans TO total transaction debits on the report
-- ----add BegCredits to total transaction credits on the report
-- ----DECLARE @tmp_GLBegBal TABLE above
--
INSERT INTO @tmp_GLBegBal
select glt.company
,glt.fiscal_year
,glt.acct_unit
,glt.account
,glt.sub_account
,gla.BegDebits as DebitsBeginning
,gla.BegCredits as CreditsBeginning
,gla.TMONTH
,gla.account_desc
,sum(glt.debittrans) as debittrans
,sum(glt.credittrans) AS credittrans
,glt.description
from @tmp_GLBegBalTrans glt
INNER join @tmp_GLBegAccounts gla on
glt.company = gla.company
and glt.fiscal_year = gla.fiscal_year
and glt.acct_unit = gla.acct_unit
and glt.account = gla.account
and glt.sub_account = gla.sub_account
group by glt.company,glt.fiscal_year,gla.TMONTH
,glt.account,glt.acct_unit,glt.sub_account,gla.account_desc
, gla.BegDebits, gla.BegCredits,glt.description
order by glt.company,glt.fiscal_year,gla.TMONTH,glt.account,glt.acct_unit
,glt.sub_account,gla.account_desc,glt.description
SELECT @rowcount3 = count(*) from @tmp_GLBegBalTrans
------CURRENT PERIOD TRANSACTIONS between date period they selected-includes those dates as beginning
------and ending
------DECLARE @tmp_CurrPeriodTrans table above
--
if @rowcounttmpGL = 0 and @rowcountGLBegBal = 0 and @rowcount = 0 and @rowcount2 = 0
and @rowcount3 = 0 and @NoBalAct = 1
BEGIN
PRINT 'There are no records'
IF OBJECT_ID('tempdb..#tmpAccounts') IS NOT NULL
DROP TABLE #tmpAccounts
IF OBJECT_ID('tempdb..#tmpGLTransPullBegEnd') IS NOT NULL
DROP TABLE #tmpGLTransPullBegEnd
IF OBJECT_ID('tempdb..#tmp_GLTransPullFromTmp') IS NOT NULL
DROP TABLE #tmp_GLTransPullFromTmp
return(0)
END
---- code to handle the dates to take care of the midnight thing
--IF @StartAccount <> 0 AND @EndAccount <> 0 and @LRange = 1
INSERT INTO @tmp_CurrPeriodTrans
SELECT glt.company
,glt.fiscal_year
,glt.acct_unit
,glt.account
,glt.sub_account
,isnull(glt.posting_date,'') as posting_date
,glt.tran_amount+glt.units_amount as tranamount
,glt.reference
,glt.description
,glt.update_date
,glt.Control_Group
,glt.system
from #tmp_GLTransPullBegEnd glt
where glt.posting_date between dateadd(dd,-1,@StartDate) and dateadd(dd,1,@EndDate)
--AND glt.account between @StartAccount and @EndAccount
order by glt.company,glt.fiscal_year
,glt.account,glt.acct_unit,glt.sub_account
-- else
-- INSERT INTO @tmp_CurrPeriodTrans
-- select glt.company
-- ,glt.fiscal_year
-- ,glt.acct_unit
-- ,glt.account
-- ,glt.sub_account
-- ,isnull(glt.posting_date,'') as posting_date
-- ,glt.tran_amount+glt.units_amount as tranamount
-- ,glt.reference
-- ,glt.description
-- ,glt.update_date
-- ,glt.Control_Group
-- ,glt.system
-- from #tmp_GLTransPullFromTmp glt
-- where glt.posting_date between dateadd(dd,-1,@StartDate) and dateadd(dd,1,@EndDate)
-- --AND glt.account in (SELECT Account from #tmpAccounts)
-- order by glt.company,glt.fiscal_year
-- ,glt.account,glt.acct_unit,glt.sub_account
---- select * from @tmp_CurrPeriodTrans
--
----
-- ------Get company, name, date period, account#/sub, acct_unit,
-- -----date and details of transactions
DECLARE @tmp_Details TABLE
(RowNum INT IDENTITY (1,1) NOT NULL
,company int NOT NULL
,fiscal_year int NOT NULL
,acct_unit nvarchar(100) NOT NULL
,account int NOT NULL
,sub_account int NOT NULL
,posting_date datetime NOT NULL
,reference nvarchar(150) NOT NULL
,SystemCode nvarchar(2) NOT NULL
,TotBegDebits decimal(24,2) NOT NULL
,TotBegCredits decimal(24,2) NOT NULL
,TMONTH INT NOT NULL
,account_Desc nvarchar(150) NOT NULL
,acct_unitname nvarchar(30) NOT NULL
,update_date datetime NOT NULL
,debits decimal(18,2) not NULL
,credits decimal(18,2) not NULL
,BegBal MONEY not NULL
,RunBal MONEY NOT NULL
,TransDescr nvarchar(150) NOT NULL
,JE int NOT NULL
,StartDate datetime NOT NULL
,EndDate datetime NOT NULL)
IF @StartAccount <> 0 AND @EndAccount <> 0 and @LRange = 1
INSERT INTO @tmp_Details
SELECT gla.company
,gla.fiscal_year
,gla.acct_unit
,gla.account
,gla.sub_account
,isnull(cpt.posting_date,'') as posting_date
,isnull(cpt.reference,'') as reference
,isnull(cpt.system,'') as SystemCode
,(gla.DebitsBeginning + gla.debittrans) as TotBegDebits
,gla.CreditsBeginning + gla.credittrans as TotBegCredits
,gla.TMONTH
,gla.account_desc
,gla.description as acct_unitname
,isnull( cpt.update_date, '') as update_date
,CASE
when cpt.tran_amount > 0 then cpt.tran_amount else 0 end as debits
,CASE
when cpt.tran_amount < 0 then cpt.tran_amount else 0 end as credits
,0 as BegBal
,0 as RunBal
,isnull(cpt.description,'') as TransDescr
,isnull(cpt.Control_Group,0) as JE
,@StartDate as StartDate
,@EndDate as EndDate
FROM @tmp_GLBegBal gla
LEFT OUTER JOIN #tmp_GLTransPullBegEnd cpt ON
gla.company = cpt.company
AND gla.fiscal_year = cpt.fiscal_year
AND gla.acct_unit = cpt.acct_unit
AND gla.account = cpt.account
AND gla.sub_account = cpt.sub_account
else
INSERT INTO @tmp_Details
SELECT gla.company
,gla.fiscal_year
,gla.acct_unit
,gla.account
,gla.sub_account
,isnull(cpt.posting_date,'') as posting_date
,isnull(cpt.reference,'') as reference
,isnull(cpt.system,'') as SystemCode
,gla.DebitsBeginning + gla.debittrans as TotBegDebits
,gla.CreditsBeginning + gla.credittrans as TotBegCredits
,gla.TMONTH
,gla.account_desc
,gla.description as acct_unitname
,isnull( cpt.update_date, '') as update_date
,CASE
when cpt.tran_amount > 0 then cpt.tran_amount else 0 end as debits
,CASE
when cpt.tran_amount < 0 then cpt.tran_amount else 0 end as credits
,0 as BegBal
,0 as RunBal
,isnull(cpt.description,'') as TransDescr
,isnull(cpt.Control_Group,'') as JE
,@StartDate as StartDate
,@EndDate as EndDate
FROM @tmp_GLBegBal gla
LEFT OUTER JOIN #tmp_GLTransPullFromTmp cpt ON
gla.company = cpt.company
AND gla.fiscal_year = cpt.fiscal_year
AND gla.acct_unit = cpt.acct_unit
AND gla.account = cpt.account
AND gla.sub_account = cpt.sub_account
----select * from @tmp_Details
--
--
IF OBJECT_ID('tempdb..#tmpAccounts') IS NOT NULL
DROP TABLE #tmpAccounts
IF OBJECT_ID('tempdb..#tmpGLTransPullBegEnd') IS NOT NULL
DROP TABLE #tmpGLTransPullBegEnd
IF OBJECT_ID('tempdb..#tmp_GLTransPullFromTmp') IS NOT NULL
DROP TABLE #tmp_GLTransPullFromTmp
--
-- --cursor to test numbers to Lawson-check period on both
-- --TO TEST AGAINST LAWSON NUMBERS
---- insert into @tmp_Totals
---- select company
---- ,fiscal_year
---- ,TMONTH
---- ,acct_unit
---- ,account
---- ,sub_account
---- ,TotBegDebits
---- ,TotBegCredits
---- ,TotBegDebits+TotBegCredits as begbal
---- ,sum(debits) as totaldebits
---- ,sum(credits) as totalcredits
---- FROM @tmp_Details
---- GROUP BY company
---- ,fiscal_year
---- ,TMONTH
---- ,account
---- ,acct_unit
---- ,sub_account
---- ,TotBegDebits
---- ,TotBegCredits
---- ORDER BY company
---- ,fiscal_year
---- ,TMONTH
---- ,account
---- ,acct_unit
---- ,sub_account
----
---- select * from @tmp_Totals
----
--have to create new table for each user
--found this on a forum when my dynamic sql wasn't working
-- ddr 9/30/08
CREATE TABLE #nisse (RowNum INT IDENTITY NOT NULL
,Company int NOT NULL
,Fiscal_year int NOT NULL
,Acct_unit nvarchar(15) NOT NULL
,Account int NOT NULL
,sub_account int NOT NULL
,posting_date nvarchar(10) NOT NULL
,update_date nvarchar(10) NOT NULL
,reference nvarchar(150) NOT NULL
,SystemCode nvarchar(2) NOT NULL
,TotBegDebits decimal(24,2) NOT NULL
,TotBegCredits decimal(24,2) NOT NULL
,TMONTH int NOT NULL
,account_desc nvarchar(150) NOT NULL
,acct_unitname nvarchar(50) NOT NULL
,debits decimal (18,2) NOT NULL
,credits decimal (18,2) NOT NULL
,BegBal MONEY NOT NULL
,RunBal MONEY NOT NULL
,TransDescr nvarchar(150) NOT NULL
,StartDate DATETIME NOT NULL
,EndDate DATETIME NOT NULL
,JE int NOT NULL)
ALTER TABLE #nisse ADD PRIMARY KEY CLUSTERED (RowNum)
CREATE NONCLUSTERED INDEX IX_TransHist_AccountID_Date
on #nisse (Account, Acct_unit, sub_account,posting_date)
insert into #nisse
(company
,fiscal_year
,acct_unit
,account
,sub_account
,posting_date
,update_date
,reference
,SystemCode
,TotBegDebits
,TotBegCredits
,debits
,credits
,BegBal
,RunBal
,TMONTH
,account_desc
,acct_unitname
,TransDescr
,StartDate
,EndDate
,JE
)
Select company
,fiscal_year
,acct_unit
,account
,sub_account
,case
when convert(varchar,posting_date,101) = '01/01/1900'
then ''''
else + convert(varchar,posting_date,101)
end as posting_date
,case
when convert(varchar,update_date,101) = '01/01/1900'
then ''''
else + convert(varchar,update_date,101)
end as update_date
,reference
,SystemCode
,TotBegDebits
,TotBegCredits
,debits
,credits
,BegBal
,RunBal
,TMONTH
,account_desc
,acct_unitname
,TransDescr
,StartDate
,EndDate
,JE
FROM @tmp_Details
--
-- --if user doesn't want to see zero balance/activity accounts, we're deleting them
-- --ddr 9/12/08
-- --this table declared above
DECLARE @tmpZero Table
(RowNum int, BegBal decimal (24,2), totdebits decimal(18,2)
,totcredits decimal(18,2), fiscal_year int
, account int, acct_unit nvarchar(15)
, sub_account int)
if @NoBalAct = 1
BEGIN
INSERT INTO @tmpZero --(BegBal, totdebits, totcredits)
SELECT RowNum, SUM(TotBegDebits+TotBegCredits) AS BegBal, sum(debits) as totdebits,
sum(credits) as totcredits, fiscal_year,account,acct_unit,sub_account
from @tmp_Details
group by RowNum, fiscal_year,account,acct_unit,sub_account
order by RowNum, fiscal_year,account,acct_unit,sub_account
DELETE FROM @tmpZero
WHERE BegBal+totdebits+totcredits <> 0
IF @@ROWCOUNT = 0
begin
set @NoBalAct = 0
end
if @NoBalAct = 1
DELETE
FROM #nisse
WHERE #nisse.RowNum In
(Select RowNum from @tmpZero)
END
--set the Beginning Balances on each line based on Beginning Debits and Beginning Credits
UPDATE #nisse
SET BegBal = TotBegDebits + TotBegCredits
FROM #nisse --WITH (INDEX(IX_TransHist_AccountID_Date),TABLOCKX)
--EXEC @sql
--THE @PrevRunBal resets the previous running balance when account, sub, or acct_unit
-- changes ddr 9/8/08
DECLARE @PrevRunBal2 MONEY
SET @PrevRunBal2 = 0
DECLARE @PrevAcct2 int
SET @PrevAcct2 = ''
DECLARE @PrevSubAcct2 smallint
SET @PrevSubAcct2 = ''
DECLARE @PrevAcctUnit2 nvarchar(15)
SET @PrevAcctUnit2 = ''
--set the beginning balances for Running Balance field ddr 9/11/08
UPDATE #nisse
SET RunBal = BegBal
FROM #nisse --WITH (INDEX(IX_TransHist_AccountID_Date),TABLOCKX)
--EXEC @sql
--update Running Balance based on whether it needs to include the Beginning Balance
--or not (if account, sub_account or acct_unit changes) ddr 9/10/08
UPDATE #nisse
SET @PrevRunBal2 = RunBal = Case
WHEN Account = @PrevAcct2
and acct_unit = @PrevAcctUnit2
and Sub_Account = @PrevSubAcct2
THEN @PrevRunBal2
+ debits + credits
ELSE BegBal + debits + credits
END,
@PrevAcct2 = account,
@PrevSubAcct2 = sub_account,
@PrevAcctUnit2 = acct_unit
FROM #nisse
SELECT *,convert(nvarchar(8),Account) as AcctSort
FROM #nisse
ORDER BY Company, fiscal_year,account
,acct_unit
,sub_account
,posting_date
END
--sort by acct_unit
else
October 2, 2008 at 4:08 pm
Lawson, ha?
OK.
I am not going to criticize this sproc, even so you are messing a lot with temp tables and table variables with the same names.
1. In the fragment where you are writing a definition of your #nisse table you are using IDENTITY column. Identity should follow with (1,1).
2. After that is going an insert statement in #nisse. Can you comment all the code after insert statement (your updates on #nisse are kind of weird) and simply add something like select * from #nisse
3. I do not really know why do you need your table #nisse at all. It looks absolutely the same as the table @tmp_Details. Only difference - table nisse contains an identity field, but I did not see anywhere in the code that you are using it.
Try to run your procedure from report again and let me know.
Something like that:
CREATE TABLE #nisse (RowNum INT IDENTITY(1,1) NOT NULL
,Company int NOT NULL
,Fiscal_year int NOT NULL
,Acct_unit nvarchar(15) NOT NULL
,Account int NOT NULL
,sub_account int NOT NULL
,posting_date nvarchar(10) NOT NULL
,update_date nvarchar(10) NOT NULL
,reference nvarchar(150) NOT NULL
,SystemCode nvarchar(2) NOT NULL
,TotBegDebits decimal(24,2) NOT NULL
,TotBegCredits decimal(24,2) NOT NULL
,TMONTH int NOT NULL
,account_desc nvarchar(150) NOT NULL
,acct_unitname nvarchar(50) NOT NULL
,debits decimal (18,2) NOT NULL
,credits decimal (18,2) NOT NULL
,BegBal MONEY NOT NULL
,RunBal MONEY NOT NULL
,TransDescr nvarchar(150) NOT NULL
,StartDate DATETIME NOT NULL
,EndDate DATETIME NOT NULL
,JE int NOT NULL)
insert into #nisse
(company
,fiscal_year
,acct_unit
,account
,sub_account
,posting_date
,update_date
,reference
,SystemCode
,TotBegDebits
,TotBegCredits
,debits
,credits
,BegBal
,RunBal
,TMONTH
,account_desc
,acct_unitname
,TransDescr
,StartDate
,EndDate
,JE
Select company
,fiscal_year
,acct_unit
,account
,sub_account
,case
when convert(varchar,posting_date,101) = '01/01/1900'
then ''''
else + convert(varchar,posting_date,101)
end as posting_date
,case
when convert(varchar,update_date,101) = '01/01/1900'
then ''''
else + convert(varchar,update_date,101)
end as update_date
,reference
,SystemCode
,TotBegDebits
,TotBegCredits
,debits
,credits
,BegBal
,RunBal
,TMONTH
,account_desc
,acct_unitname
,TransDescr
,StartDate
,EndDate
,JE
FROM @tmp_Details
select * from #nisse
October 3, 2008 at 9:07 am
Yes, I wondered if you'd recognize Lawson.
I'm kind of a SQL Server newbie and don't have any direction or standards here but my logic created something no one else, including consultants, has been able to do. I think it could be faster though.
1. Fixed--I should have caught that.
2. All that code is creating running balances in a general ledger type of report, so it can't come out. The code I got (after taking running balance code out of Reporting Services) off the Internet had it creating a table with one name which doesn't work with more than one user, so I went hunting for randomizing table names, and after some various attempts, this is what I eneded up with. But the Running Balance code is significant.
3. See #2--probably could do a DECLARE TABLE. I've read that #tmp tables are faster with bigger data sets and this is BIG, so...
The procedure runs but I can't get the project to even open in Visual Studio so it seems I've totally hosed it (changing report names or something??). I can't work on it today--I've been pulled off it for another project. Thanks for looking at it--maybe I can get back to it next week?
December 1, 2008 at 8:53 am
GardenLady,
Did you ever get the report to run?
December 1, 2008 at 9:26 am
Oh, the report has been through many iterations but it runs and is now in Crystal with the SQL Server stored proc as a data source. It's currently in testing and I'm adding enhancements today. I wanted to publish it in Reporting Services but did not have in-house knowledge of how to that securely (did not want company number in URL or browsing history).
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply