Saving Filtered Transaction List as Set of Reports

Page 1 of 1
5 posts
Saving Filtered Transaction List as Set of Reports

Mudplugger
New MMEX User

Posts: 5
Joined: Tue Nov 10, 2015 4:37 am
Hi guys,

I hope someone can help me, I have very little SQL knowledge at all so I am really at your mercy.

I love the Transaction Filter but I hate having to change it every time I want to use it to produce a set of reports so I would like a set of reports that I can just run when ever I need to.

I have several different values in the Notes field that I would like to filter, 1 value per report, So easy to do with the Transaction Filter but I have to change it 15 times for each value.  What I would like is a save a set of reports that have the same data as the Transaction Report but with the appropriate filter already applied. So 1 report where Notes contains ABC another report where Notes contains DEF and a third where Notes contains GHI etc.

I think that if someone could provide the SQL for 1 report I could adapt the WHERE clause (is that right?) for the others.

Thanks for the help.
Re: Saving Filtered Transaction List as Set of Reports

Mudplugger
New MMEX User

Posts: 5
Joined: Tue Nov 10, 2015 4:37 am
Ok so I've got this far

Code: [url=#]Select all[/url]
Code: Select all
SELECT
 ACC.TRANSDATE AS Date,
 LST.ACCOUNTNAME As Account,
 PAY.PAYEENAME As Payee,
 CAT.CATEGNAME As Category,
 SUB.SUBCATEGNAME As [Sub Category],
 ACC.NOTES As Notes,
 ROUND(ACC.TRANSAMOUNT,2) As Amount
FROM 
 ((((CHECKINGACCOUNT_V1 as ACC
 Left Join ACCOUNTLIST_V1 AS LST)
 Left Join PAYEE_V1 As PAY)
 Left Join CATEGORY_V1 As CAT)
 Left Join SUBCATEGORY_V1 AS SUB)
WHERE
 ACC.ACCOUNTID = LST.ACCOUNTID
 And ACC.PAYEEID = PAY.PAYEEID
 And ACC.CATEGID = CAT.CATEGID
 And ACC.SUBCATEGID = SUB.SUBCATEGID
 And ACC.NOTES LIKE '%3 Mill Lane%';
But I can't find the Type field which shows whether a transaction is a Deposit or a Withdrawal, I can't concatenate Category and Sub Category into a single output field and I seem to be getting Deposit records only.

Can anyone help?

Thanks
Re: Saving Filtered Transaction List as Set of Reports

Mudplugger
New MMEX User

Posts: 5
Joined: Tue Nov 10, 2015 4:37 am
I've refined my JOINS a bit and now I do get values for both Deposits and Withdrawals. But I still can't find the Type field, I can't concatenate Category and Sub Category and my withdrawal amounts are positive not negative.

Code: Select all
SELECT
 ACC.TRANSDATE AS Date,
 LST.ACCOUNTNAME As Account,
 PAY.PAYEENAME As Payee,
 CAT.CATEGNAME As Category,
 SUB.SUBCATEGNAME As [Sub Category],
 ACC.NOTES As Notes,
 ACC.TRANSAMOUNT As Amount
FROM 
 ((((CHECKINGACCOUNT_V1 as ACC
 Left Join ACCOUNTLIST_V1 AS LST ON ACC.ACCOUNTID = LST.ACCOUNTID)
 Left Join PAYEE_V1 As PAY ON ACC.PAYEEID = PAY.PAYEEID)
 Left Join CATEGORY_V1 As CAT ON ACC.CATEGID = CAT.CATEGID)
 Left Join SUBCATEGORY_V1 AS SUB ON ACC.SUBCATEGID = SUB.SUBCATEGID)
WHERE
 ACC.NOTES LIKE '%3 Mill Lane%';
Re: Saving Filtered Transaction List as Set of Reports

Mudplugger
New MMEX User

Posts: 5
Joined: Tue Nov 10, 2015 4:37 am
OK, I think I'm almost there but I have a problem with the formatting, The Amount field won't display to 2 decimal places when a value does not have any pennies.  i.e. £800.00 shows as £800.0 if I use the ROUND function and only as £800 if I don't.

Code: Select all
SELECT
 ACC.TRANSDATE AS Date,
 LST.ACCOUNTNAME As Account,
 PAY.PAYEENAME As Payee,
 CAT.CATEGNAME || Case IfNull (ACC.SUBCATEGID, -1) When -1 Then '' Else ':' || SUB.SUBCATEGNAME End AS Category,
 ACC.TRANSCODE AS Type,
 ACC.NOTES As Notes,
 CUR.PFX_SYMBOL || (Case ACC.TRANSCODE When 'Deposit' then 1 else -1 end * ROUND(ACC.TRANSAMOUNT,2)) As Amount
FROM 
 ACCOUNTLIST_V1 As LST
 Left Join CHECKINGACCOUNT_V1 as ACC ON ACC.ACCOUNTID = LST.ACCOUNTID
 Left Join PAYEE_V1 As PAY ON ACC.PAYEEID = PAY.PAYEEID
 Left Join CATEGORY_V1 As CAT ON ACC.CATEGID = CAT.CATEGID
 Left Join SUBCATEGORY_V1 As SUB ON ACC.SUBCATEGID = SUB.SUBCATEGID
 Inner Join CURRENCYFORMATS_V1 AS CUR ON LST.CURRENCYID = CUR.CURRENCYID
WHERE
 ACC.NOTES LIKE '%3 Mill Lane%';

I think that's as far as I can go, please help.
Re: Saving Filtered Transaction List as Set of Reports

Nikolay
MMEX Developer

Posts: 2284
Joined: Sat Dec 06, 2008 8:27 am
Location: Sankt-Petersburg, Russia
Code: Select all
SELECT
 ACC.TRANSDATE AS Date, 
 LST.ACCOUNTNAME As Account, 
 PAY.PAYEENAME As Payee,
 case ifnull(SUB.SUBCATEGID, -1) when -1 then  CAT.CATEGNAME else CAT.CATEGNAME||':'||SUB.SUBCATEGNAME  end As Category, 
 ACC.NOTES As Notes,
 case acc.TRANSCODE when 'Deposit' then ACC.TRANSAMOUNT else -ACC.TRANSAMOUNT end As Amount
FROM  
CHECKINGACCOUNT_V1 as ACC 
 inner Join ACCOUNTLIST_V1 AS LST ON ACC.ACCOUNTID = LST.ACCOUNTID
inner Join PAYEE_V1 As PAY ON ACC.PAYEEID = PAY.PAYEEID
 Left Join CATEGORY_V1 As CAT ON ACC.CATEGID = CAT.CATEGID
 Left Join SUBCATEGORY_V1 AS SUB ON ACC.SUBCATEGID = SUB.SUBCATEGID
WHERE 
ACC.TRANSCODE !='Transfer'
 --and ACC.NOTES LIKE '%3 Mill Lane%';
Page 1 of 1
Who is online

Users browsing this forum: Bing [Bot] and 15 guests

cron