Saving Filtered Transaction List as Set of Reports

Build and share your cool customized reports built w/ one sql, Chart.js and Lua

Moderator: Renato

Post Reply
Mudplugger
New MMEX User
Posts: 5
Joined: Tue Nov 10, 2015 10:37 am
Are you a spam bot?: No

Saving Filtered Transaction List as Set of Reports

Post by Mudplugger »

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.
Mudplugger
New MMEX User
Posts: 5
Joined: Tue Nov 10, 2015 10:37 am
Are you a spam bot?: No

Re: Saving Filtered Transaction List as Set of Reports

Post by Mudplugger »

Ok so I've got this far

Code: Select all

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
Mudplugger
New MMEX User
Posts: 5
Joined: Tue Nov 10, 2015 10:37 am
Are you a spam bot?: No

Re: Saving Filtered Transaction List as Set of Reports

Post by Mudplugger »

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%';
Mudplugger
New MMEX User
Posts: 5
Joined: Tue Nov 10, 2015 10:37 am
Are you a spam bot?: No

Re: Saving Filtered Transaction List as Set of Reports

Post by Mudplugger »

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.
Nikolay
MMEX Developer
Posts: 1535
Joined: Sat Dec 06, 2008 2:27 pm
Are you a spam bot?: No
Location: Sankt-Petersburg, Russia

Re: Saving Filtered Transaction List as Set of Reports

Post by Nikolay »

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%';
Post Reply