Joew, i only noticed this post today.
i don't know how to count the number of times a group is executed within a report.
i have however written a query which calculates the required values & can be used as the data source for your report.
this query is a workaround & not the perfect solution. it contains a sub-query which is executed once for every row encountered.
1. copy the code below.
2. load your database & hit the 'Queries' icon.
3. hit 'Create Query in SQL View'
4. paste the code.
5. hit the SQL icon on the tool bar. if you don't do this you will receive a syntax error.
6. hit F5 to execute the query.
7. save with a name of your choice & close the window.
8. because your report uses grouping the base parser must be enabled therefore we need to create a view from our query which can then be used as the data source.
9. right click on the query & select 'Create as View'. save with a name of your choice.
- Code: Select all Expand viewCollapse view
--use a view created from this query as tha data source for your report
select E."ExpenseDate", year(E."ExpenseDate") "Year",
month(E."ExpenseDate") "Month No", monthname(E."ExpenseDate") "Month Name", E."ExpenseAmount", M."MonthCount"
from
(
select "ExpenseDate", "ExpenseAmount" from "Expenses"
where "CustomerID" = 14
and
"ExpenseDate" between '2010-12-31' and '2019-01-01'
) E
join --count the number of months in each calendar year
--this sub query requires that 'Run SQL command directly' is selected
(
select year("ExpenseDate") "Year", count(distinct month("ExpenseDate")) "MonthCount" from "Expenses"
where "CustomerID" = 14
and
"ExpenseDate" between '2010-12-31' and '2019-01-01'
group by "Year"
) M
on M."Year" = E.year("ExpenseDate")
order by E."ExpenseDate"
Open your report ifor edit.1. open report navigator(F5).
2. hit Report (1st line in navigator).
3. open properties pane(F4).
4. hit Data tab.
5. Content type = 'Table'
6. Content = the view you created from my query.
7. in navigator expand Groups.
8. expand Year.
9. expand Functions.
10. right click 'MonthCount' & delete.
11. right click 'YearlyAverage' & delete.
12. close navigator.
13. in the report Year Footer.
14. hit the text box called [MonthCount].
15. from the preferences pane(F4) hit Data tab. DataField Type = 'Field or Formula', Data field = [MonthCount].
16. hit the text box called [YearlyAverage].
17. from the preferences pane(F4) hit Data tab. DataField Type = 'Field or Formula', Data field = clear the field then copy & paste this
[SumYear]/[MonthCount].
18. execute report.
open office 4.1.8 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.1 (Split) on Windows 10