Editing Reports

[petemac]

Thank you very much, that's interesting. What are the year parameters - 1 January to 31 December? What I generally need are the financial year totals, which for me are 1 May to 30 April (hence needing the custom date function if going beyond the Current/Last Financial Years in the stock installation). I can see how to edit the years in the way you suggest, so that's good esp as I'm a SQL know-nothing. What could I edit to introduce the financial year parameters in a simple way?

I look forward to version 4.

[siowena]

Thank you very much, that's interesting. What are the year parameters - 1 January to 31 December? What I generally need are the financial year totals, which for me are 1 May to 30 April (hence needing the custom date function if going beyond the Current/Last Financial Years in the stock installation). I can see how to edit the years in the way you suggest, so that's good esp as I'm a SQL know-nothing. What could I edit to introduce the financial year parameters in a simple way?

I look forward to version 4.

Yes, the year is Calendar Jan-01 to Dec-31.

I've modified the report to now read the Financial Year start month/day form the MMEX Tools -> Options -> General -> Financial Year settings.

You'll need to change the line 20 in the SQL to FIN_YEAR and also change and line 44 for the years you are interested.

[petemac]

Thanks again. I've got it working, paddling into personally uncharted waters with that sort of data manipulation. I'm a graphic designer not a coder though I've worked with html and php. SQL is probably something I'll mostly leave to those with a talent for it, like yourself. Cheers.

[Nikolay]

Using substr('&single_date', 1, 4) and strftime('%Y', c.TRANSDATE) year you can create report that will change date by a date widget.

Code: Select all

with p as (select PAYEEID, PAYEENAME from PAYEE_V1),
c as (
select 
strftime('%Y', c.TRANSDATE) year,
c.payeeid, count(*) count, 
sum( TRANSAMOUNT) summ
from CHECKINGACCOUNT_V1 c
where c.STATUS !='Void' and c.TRANSCODE='Deposit'
group by c.payeeid
)
select 
-- c.year, 
p.payeename, c.count, 
c.summ,
case  when  c.summ>45 then '*****' when c.summ>35 then '****' when c.summ>25 then '***'  when c.summ>15 then '**' else '*' end stars
from c, p
where p.payeeid = c.payeeid
and c.year= substr('&single_date', 1, 4)
group by c.payeeid
order by 4 desc, 3 desc, 1

[AstridWilma]

Any Transcript rules that one needs to adhere to?
THE APA referencing style maybe?

[Renato]

Do not understand these questions