[Solved] Count months when grouping by month in report

[Joew]

I have a report built using the sun report builder extension that is based on a query of a table of “expenses”.
I’m trying to create a report that displays expense by month with monthly totals, yearly totals and average monthly expense by year.
The report is grouped by year and then month number.
Using the tutorial Base/Report Builder First Steps in this form, I’ve managed to create the user-defined functions to get the monthly total in a text box in the “month number” group footer and yearly totals in the year group footer. I’d like to also show the monthly average in the year group footer. To do that I need to determine the number of months in the year group, (not all months have expenses, so I can’t just divide the total by 12)
Can anyone tell me how to set up a counter for this? Something that would count the “groups” in the outer, (yearly) group would work I think but I can’t figure out how.
Thanks for any help provided.

[chrisb]

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.

[Joew]

Thanks chrisb. That works great. I'll study the code to learn more - I wasn't aware of the sql command to select "distinct month".