Report and WorkPeriod for Account Transaction - {Start} {end}

Hello,

I have read (I think in a emre post) that {Start} & {end} can be used in SQL and I guess this refers to the selected Working Period.

I try to use it in REPORT SQL DETAILS but without success. (I am also tired :slight_smile: )

{REPORT SQL DETAILS:
SELECT ...
FROM [AccountTransactionValues]
WHERE ... AND [Date] > '{Start}' AND [Date] < '{End}' 

Thank you for your help,

Marc

First, determine the values in start/end are what you expect…

{REPORT SQL DETAILS:SELECT '{Start}' as [A], '{End}' as [B]:F.A,F.B}
2 Likes

Thanks @QMcKay, it works … but it doesn´t :slight_smile:

I am comparing:
Date (from AccountTransactionValues) which is YYYY-MM-DD
with
{Start} (from system) wich is YYYY-MM-DD HH:MM:SS

Is there a way to get the account transactions done between Time1 and Time2?
Some sort of Timestamp?

1 WorkPeriod for me is:
From 6:00 in the morning till 2-3:00 at night.(so the next day)

Marc

(there is a colon : too much in your code)

Convert the Date column in the SQL to the format you want/need, for example, this will convert to include Time:

CONVERT(varchar(50),[Date],126)

So maybe:

[Account Tx Vals:1, 1]
>>Start|End
{REPORT SQL DETAILS:SELECT '{Start}' as [A], '{End}' as [B]:F.A,F.B}
>>Transactions
>Date|Amount
{REPORT SQL DETAILS:
SELECT
 REPLACE(CONVERT(varchar(50),[Date],126),'T',' ') as [Date]
,[Exchange]
FROM [AccountTransactionValues]
WHERE 1=1
 AND CONVERT(varchar(50),[Date],126) > '{Start}' AND [Date] < '{End}'
:F.Date,F.Exchange
}

I must have a problem … maybe some locales.

(my check out report works but as soon as I try to modify something related to date in the sql, the corresponding line disappears in the report view, and more bizarre undo-ing the change will not bring back the corresponding report line.)

Also, I created a new report with your code, but no result:

Q: Milliseconds will not create problems?

(yes I have transactions between these dates),

Any idea where to look at?
I am working on a terminal, not on the server, I don’t know if it can impact.

By the way, you gave me 2 aditional functions I can play with : CONVERT and REPLACE : Thanks :slight_smile:

Marc

Ok looking at it …

Well, the Report Preview tries to work off of the Date Filters that are set in the Report Explorer. You cannot necessarily rely on the Preview to show the data you want.

So sometimes, you need to Save the changes to your Report and then go to Report Explorer, set the Date Filters at the top-right, and click Apply to see your changes.

I suppose that is possible, but the CONVERT(varchar(50),[Date],126) makes the date ISO8601 format which is a standard. You might need to do the same to the Start/End … I know it appears to display it in the standard format on the Preview, but my Local settings for example are set for that format in the first place - yours might not be.

Try this:

[Account Tx Vals:1,1, 1, 1, 1,2]
>>Start|End
{REPORT SQL DETAILS:SELECT SUBSTRING(REPLACE(CONVERT(varchar(50),'{Start}',126),'T',' '),1,16) as [A], SUBSTRING(REPLACE(CONVERT(varchar(50),'{End}',126),'T',' '),1,16) as [B]:F.A,F.B}
>>Transactions
>Date|Account|Debit|Credit|Exchange|TxName
{REPORT SQL DETAILS:
SELECT
 SUBSTRING(REPLACE(CONVERT(varchar(50),tv.[Date],126),'T',' '),1,16) as [Date]
,a.[Name] as [Account]
,tv.[Debit]
,tv.[Credit]
,tv.[Exchange]
,tv.[Name] as [txName]
FROM [AccountTransactionValues] tv
LEFT JOIN [Accounts] a on a.[Id] = tv.[AccountId]
WHERE 1=1
 AND SUBSTRING(REPLACE(CONVERT(varchar(50),tv.[Date],126),'T',' '),1,16) >= SUBSTRING(REPLACE(CONVERT(varchar(50),'{Start}',126),'T',' '),1,16)
 AND SUBSTRING(REPLACE(CONVERT(varchar(50),tv.[Date],126),'T',' '),1,16) <= SUBSTRING(REPLACE(CONVERT(varchar(50),'{End}',126),'T',' '),1,16)
ORDER BY tv.[Date], tv.[Id]
:F.Date,F.Account,F.Debit,F.Credit,F.Exchange,F.txName
}

Thanks @QMcKay

It is surely some syntax problem.
As “Date” from account transactions also contains the time, it will be ok, just a matter to find the correct date format.
When I got it I’ll post it.

Thanks for the 3rd function you gave me (SUBSTRING), I already know where I will use it.

Maybe a newbie question: My reports start to be quite heavy with all that sql.
Is there a better way to do it? Like a script and then use that script in the report?

Thanks again,

Marc

REPORT SQL DETAILS can use a @@SQL handler, so yes, you can do more in Script like using @TEMP tables and multiple operations, with the final result being a SELECT statement.

{REPORT SQL DETAILS:@@GetStatement:
F.TxDate.desc,F.TicketId,F.TicketNo,F.Description,F.Amount,F.Balance}

And you can also just use a SQL Handler by itself, but Start/End is not designed to work with that.

[#Account Statement:2, 1, 1, 1, 1, 1]
>Date| TID| TNo/Doc| Description| Amount| Balance
@@GetStatement

Those both use @@GetStatement Handler which is this script:

declare @entityType varchar(255) = 'Customers'
declare @entityId int = 0
declare @dateFilterBeg datetime = GETDATE()
declare @entName varchar(255) = '@1'
declare @entId int = 0

SET @dateFilterBeg = (SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='AS Date Filter Beg')

SET @entId = (SELECT isnull([Id],0) FROM [Entities] WHERE [Name]=@entName)

IF @dateFilterBeg = ''
 BEGIN
  SET @dateFilterBeg = '2000-01-01'
END

SET @entityId = (SELECT [Value] FROM [ProgramSettingValues] WHERE [Name]='AS Entity Id')
IF @entId > 0 SET @entityId = @entId


declare @txcount int = 0
declare @i int = 1
declare @balance decimal(7,2) = 0.00
declare @lastZeroDate varchar(50) = '2000-01-01'

declare @tbl_tx_all table (
[Id]   INT IDENTITY(1,1) NOT NULL
, [TxDate] Datetime null
, [TicketId] int null
, [TicketNo] int null
, [Description] varchar(255) null
, [Amount] decimal(6,2) null
, [Balance] decimal(7,2) null
)

declare @tbl_tx_filtered table (
[Id]   INT IDENTITY(1,1) NOT NULL
, [TxDate] Datetime null
, [TicketId] int null
, [TicketNo] int null
, [Description] varchar(255) null
, [Amount] decimal(6,2) null
, [Balance] decimal(7,2) null
)

INSERT INTO @tbl_tx_all ([TxDate], [TicketId], [TicketNo], [Description], [Amount], [Balance])
SELECT
 tv.[Date] as [Date]
,tkt.[Id] as [TicketId]
,CASE
  WHEN tkt.[TicketNumber]>0 Then d.[Id]--tkt.[TicketNumber]
  ELSE d.[Id]
 END as [TicketNo]
,CASE
  WHEN [Credit] > 0 Then 'Payment [' + d.[Name] + '] ' + tx.[Name]
  WHEN [Debit] > 0 Then 'Purchase'
 END as [Desc]
,[Debit]-[Credit] as [Amount]
,0 as [Balance]

FROM [AccountTransactionValues] tv
LEFT JOIN [AccountTransactions] tx on tx.[Id] = tv.[AccountTransactionId]
LEFT JOIN [AccountTransactionDocuments] d on d.[Id] = tv.[AccountTransactionDocumentId]
LEFT JOIN [AccountTransactionTypes] tt on tt.[Id] = tv.[AccountTransactionTypeId]
-- Accounts
LEFT JOIN [AccountTypes] at on at.[Id] = tv.[AccountTypeId]
LEFT JOIN [Accounts] a on a.[Id] = tv.[AccountId]
-- Ticket
LEFT JOIN [Tickets] tkt on tkt.[TransactionDocument_Id] = d.[Id]
LEFT JOIN [TicketEntities] te on te.Ticket_Id = tkt.[Id] and te.[EntityTypeId] IN (SELECT [Id] FROM [EntityTypes] WHERE [Name]=@entityType)
-- Entity
LEFT JOIN [Entities] e on e.[Id] = te.[EntityId]
LEFT JOIN [EntityTypes] et on et.[Id] = e.[EntityTypeId]

WHERE 1=1
--AND tv.[AccountId] in (SELECT TOP 1 [AccountId] FROM [Entities] WHERE [Name] = @entityName)
AND tv.[AccountId] in (SELECT TOP 1 [AccountId] FROM [Entities] WHERE [Id] = @entityId)

ORDER BY [Date] ASC


-- get count of records
SELECT @txcount = count([Id]) FROM @tbl_tx_all


-- loop all records to set Last 0 Balance Date
WHILE @i<=@txcount
BEGIN
SET @balance = @balance + (SELECT [Amount] FROM @tbl_tx_all WHERE [Id]=@i)
UPDATE @tbl_tx_all SET [Balance] = @balance WHERE [Id]=@i
--IF @balance = 0 UPDATE [ProgramSettingValues] SET [Value]=(SELECT [TxDate] FROM @tbl_tx_filtered WHERE [Id]=@i) WHERE [Name]='AS Entity Date_ZeroBalance'
IF @balance = 0 SET @lastZeroDate = replace((SELECT convert(varchar(50),[TxDate],126) FROM @tbl_tx_all WHERE [Id]=@i),'T',' ')
SET @i = @i + 1
END

UPDATE [ProgramSettingValues] SET [Value]=@lastZeroDate WHERE [Name]='AS Entity Date_ZeroBalance'
--SELECT isnull(max([TxDate]),'2000-01-01') FROM @tbl_tx_all WHERE [Balance] = 0.00
--print '------------------------------------------- ' + @lastZeroDate

-- loop filtered records to get Balance Brought Forward previous to @dateFilterBeg
SET @balance = 0.00
SET @i = 1

WHILE @i<=@txcount
BEGIN
IF @dateFilterBeg > (SELECT [TxDate] FROM @tbl_tx_all WHERE [Id]=@i)
BEGIN
SET @balance = @balance + (SELECT [Amount] FROM @tbl_tx_all WHERE [Id]=@i)
UPDATE @tbl_tx_all SET [Balance] = @balance WHERE [Id]=@i
END
SET @i = @i + 1
END


-- insert Balance Brought Forward
IF @dateFilterBeg > (SELECT MIN([TxDate]) FROM @tbl_tx_all)
BEGIN
INSERT INTO @tbl_tx_filtered ([TxDate], [TicketId], [TicketNo], [Description], [Amount], [Balance])
SELECT @dateFilterBeg, 0, 0, 'Balance Brought Forward', @balance, @balance
END

-- insert Filtered Transactions
INSERT INTO @tbl_tx_filtered ([TxDate], [TicketId], [TicketNo], [Description], [Amount], [Balance])
SELECT [TxDate], [TicketId], [TicketNo], [Description], [Amount], [Balance] FROM @tbl_tx_all WHERE [TxDate] >= @dateFilterBeg


-- loop records to use filtered amounts and Balance Brought Forward
SELECT @txcount = count([Id]) FROM @tbl_tx_filtered
SET @i=1
SET @balance = 0.00

WHILE @i<=@txcount
BEGIN
SET @balance = @balance + (SELECT [Amount] FROM @tbl_tx_filtered WHERE [Id]=@i)
UPDATE @tbl_tx_filtered SET [Balance] = @balance WHERE [Id]=@i
SET @i = @i + 1
END

select [TxDate], [TicketId], [TicketNo], [Description], [Amount], [Balance] from @tbl_tx_filtered ORDER BY [Id] DESC
1 Like

SUBSTRING of the first 16 characters does it.

Thanks,

I’ll play with the scripts tomorrow, thanks again.

Marc

Great !!

That’ll make my reports cleaner and reusable !!!