Group by month then sort by full date, and give total

[erythisis]

I have a database filled in with some simple income information. What I want to do for a report though, is to group the income into the months from the dates I have listed. Then inside each group have the entries be ordered by the full date. Additionally, I want to include totals for each month plus an overall total at the end.

Here's the idea

Code: Select all

January
          Date                 Description         Income
          01/08/2020           Commission1          $100
          01/13/2020           Commission2          $600
          01/19/2020           Commission3          $200
          01/22/2020           Commission4          $300
                               January Total       $1200

February
          Date                 Description         Income
          02/06/2020           Commission1          $300
          02/14/2020           Commission2          $100
          02/23/2020           Commission3          $400
          02/27/2020           Commission4          $200
                               February Total      $1000

Total Income: $2200

I created the database in open office (HSQL), so importing isn't an issue. So for the big question, how do I achieve what I'm after? I imagine it should be possible (the concept itself is simple), I just can't wrap my brain around how to go about it.

In case it helps, I currently have the fields DBID (auto key), Date, Description, Income, Expense (next step), and Notes (not for display).

[DrewJensen]

Howdy,

To do this I am going to use the SUN Report Builder extension, release 1.0.4. ( This is the latest version available at the extensions download site and has a bug in it under 2.4.1, but the patched file should be available very shortly ).

The next thing is that there are really two ways to go - you can either do all the sorting and grouping in the report writer or you can prep the data in a query first. I like to go the second route and that is what I am going to do here.

So, the first step is to get the data into a nice format - by which I mean that I will add a couple of Calculated Fields and sort on these in the query.

One thing also, I am going to add one more grouping and this is by year...

I created a table that mimics the one you described and named the table tblIncomeExpense - just open a query design window and add the table to the designer window and select all fields by double clicking the asterisk. Now I want to add 3 Calculated fields, each is going to be based on the Date field, so I just double click the Date field 3 times. What I will use are the functions YEAR, MONTH, MONTHNAME. I will then sort the results based by YEAR first, then MONTH. The query looks like this then:

Notice that the functions YEAR and MONTH return the year and month numbers, so they sort properly.

Also, it is important to give your calculated fields alias names. I simply named then for the functions used. Year, Month, Monthname.
You can find information on the functions that are available at this wiki page:
Base Query designer functions

And that is all the preparation that is needed for this report, so I saved the query as qry_rpt_income.

Now with the Query section displayed in the Base window I just right click on the query name and select "Report". The report builder will open, with the query selected as the data source and the Add Fields dialog box open.

Next post we start laying out the actual report.

[DrewJensen]

OK, the first thing to do then is to add some grouping to the report.

To do this you open the Sorting and Grouping window. View>Sorting and Grouping or CTL-G
You add a field by clicking in the "Field/Expression" grid in the top section of the window, a drop down lets you select from the fields in your data set - if you have created any User defined functions in the report those wold be listed also. ( We will not need any for this report )
I am going to add to groups based on the calculated fields Year and Month.
By default default when you add a group only the group header is added to the report, I will add a group footer to each group by changing the property "Group footer" to present in the Sorting and Grouping window when you add the fields.

The empty report now looks like this:

Fine - now how about adding some fields to the report.

• To add the year field into the "Year" group header:
  • Click any where n the Year Header band ( Notice that the "Bands" are named on the left of the report design window and color coded - Blue for group headers and footers )
    The Group band name ( the blue region ) will now have a white outline added to let you know it is the active band.
    At this point you can just double click on the field you want to add to the currently "active" band in the "Add Field" window. So, double click on the field "Year"
  Next the Month field
  • click in the band area for the "Month" group header. ( the white outline changes to let you know this is now the active band )
    Double click the field "MonthName" in the Add Fields window.
  Finally , add the fields "Date", "Description", and "Income" to the detail band of the report.
  • Again, click anywhere in the band named "Detail" ( this is the red band ) and double click the fields in the Add Field window.

Here is what the report should look like now:

Alright - now lets re-arrange the fields.

Remember that the detail band will repeat for each record in the data set. For now lets just make so that the text boxes for the Date, Description and Income fields line up under the labels for the each.
Also, maybe tighten up some of those bands..you can grab the bottom edge of each with your mouse and make them smaller - or you can change the height property in the property editor on the right of the screen.

This is the way the report looks now:

next post - we finish up this report.

[DrewJensen]

Well, how about we add those "Report calculated" fields - month total income, total income ( which in this case is actually for the year...)

What I am going to do first is just add the field, Income into the group footer bands for the MonthName and Year groups - just like I did for the header..click in the band and then double click on the field name in the Add field window.

Then I will change the data setting for the text boxes to get the "Accumulated" value for the groups.

To do this, after adding the fields click on the text box for the Income field in the MonthName group footer.
In the property editor, the area on the right side of the window, I select the Data tab.
There are now Four properties listed:
Date Field Type
Data field
Function
Scope

In "Data Field Type" select "Function" from the drop down list. Notice that the selection in the next property goes blank.
In "Data field" select Income form the drop list again.
In "Function" select "Accumulated"
The "Scope" defaults to "Group:Month" ( remember we grouped on the field "Month", but added the actual field "MonthName" for display )

Next I select the label "Income" in the Month group footer and in the property editor I select the General tab.
Change the property Label to "Month total:"
Change the property Alignment to "Right"

Great now I repeat these steps for the Year group footer..

Finally I will select both the Label and Text box in the Month group footer and move them to the right so they line up under the Income field in the detail section.

Here is how the report should look now:

This looks like a good point to do a test run of the report...and here is the output:

Test_run_1.pdf

(14.21 KiB) Downloaded 654 times

hmmm - not exactly what I wanted..the grouping is good, the totals are good...but the labels on each detail line is not what I want and the income vlaues are not displayed as Currency.

To handle the labels for the detail fields you can use a little trick - the group header is only displayed once..so.
Select all three labels ( income, Description, Income) and using the mouse move them up till they are displayed in the bottom of the group header and drop them.
In fact while I have all three labels select I will also go to the property editor and in the General tab change the font setting ( use the button to the right of the font name ) to select bold italic for these labels.
Next I will grab the three text boxes in the detail section and move them to the top of the band, then shrink the band just as small as I can get it.
I will also make a couple more changes...I think I will delete the Year label and the MonthName labels in the respective group headers. Then slide the MonthName field text box all the way to the left of the page.
Next I will expand the Month group band a bit, slide the label and text box down just a litte and use the horizontal line tool to place a single line above the text box.
Then shrink the label for Total Income: by half, slide it to the left of the page also and slide the text box left to touch it.

Here is the report layout now:

Last step - format for Currency...

[DrewJensen]

Formatting the income fields for Currency...easy right..

Simply select the three text boxes that display currency values and in the property editor change the format property to Currency in the format dialog box.

OK - now run the report:

Test_run_2.pdf

(21.18 KiB) Downloaded 540 times

Not so great...why are all the fields negative and red.

Simple it is a bug...but a bug that has a simple fix.

DO THIS EXACTLY

Select the three text boxes that display dollar amounts again and again bring up the format dialog. ( click the button to the right of the format property in the property editor )

The bug has two parts:
1 - you must select "Automatically" in the drop list for "Format" - first.
2 - the report engine uses the format string in reverse order form how it is input...meaning we need to switch the format string at the semi-colon
( you can highlight a section, copy, delete, paste to do this )
When you do the green check mark becomes active, click this to make your change permanent for this report.

Now run the report..

Final_report.pdf

(21.72 KiB) Downloaded 576 times

One last thing...

[DrewJensen]

Give the report a name.

Select "Edit>Select Report"
In the property editor select the General tab
Change the property Name - I used "Income Report"

Save the report, save the database...

Edit: LOL and after all that I finally realized I did not add the final sort, by date inside each month... Well, that is the nice part of basing your report on a query.. Open the database attached, open the query, add the field Date one more time into the query, add a sort to this column and in fact you don't even need to to include it in the result set - so remove the tick mark the query now looks like this: save the query and run the report..

and here is the database w/ report...enjoy...

income.odb

(43 KiB) Downloaded 493 times

[Villeroy]

OK, while we're in the matter, let me introduce another tool to analyse, aggregate and print data. No need to create any queries. You may create a query in order to restrict data exchange to the required fields, which may reduce your system's payload.

Menu:File>New>Spreadsheet...
Data>Data Pilot>Start...
1st Dialog: [X] Datasource registered in OpenOffice.org, [OK]
Next dialog:

[Read "Sheet" as "Table". You could specify a query as well.]

My table has 4 fields "ID", "Date", "Item" and "Amount". We'll ignore "ID".

Data pilot wizzard:
1. Hit button [More]
2. Make shure that the target cell is the top-left cell of a free range (in case your spreadsheet has already data) and check all additional options (they won't hurt).
3. Drag grey boxes [Date] and [Item] into "Row Fields", and [Amount] into "Data Fields".
4. Button [OK]

You get a special table (a "data pilot", also known as "pivot table") with dates, items, and the sum of amounts per item and date. If the same item occurs in the same day, the amount for the respective item and day is summed. Such a pilot can be refreshed from it's context menu or Data>Refresh from the main menu. This will reread the underlying data from datasource.

Let's split up the results by days, months, quarters and years. You may leave off quarters, but include the years in any case.
Select a date-value and call Data>Outline>Group (key F12):

This will split up the date-field by additional time intervals as shown in the background of the following screenshot.

[DrewJensen]

Hi Villeroy,

By any chance would you be willing to grab the odb file from this thread, move the data to a Calc file and create that data pilot report laid out the way the report is and attach the ods file and the output as a pdf back to the thread..it would be a very nice comparison actually.

[Villeroy]

I called "Start..." from the pilot's
context menu, dragged [Year] and [Quarter] to "Page Fields", double-clicked
[Month] and set the option to show the sum for each month. Button
[Options...] loads a third dialog where I set an option to show the subtotal
on top(just for the screenshots). You may prefer the default which inserts
extra rows with bold subtotals.

After confirming all the dialogs I get a pilot which shows the sum for every month beneath the month name and a column of sums for each item per day.

There is an important thing to keep in mind: The summed amount of 5740,45 in
the screenshot sums all amounts of all Januaries. This way you can compare
months and quarters across years. If you want the sums for the months of a
particular year simply select the year from the page field or drag the page
field [Year] back into the table body, which will show the months within their
respective years.
You can drag around the grey boxes on the sheet as on the wizard. Try
column-fields as well in order to show the sums for all items in a separate column. Mind the limit of 256 columns per sheet, extended to 1024 columns in version 3.

Call the stylist (F11) and click some cells in order to see which cell style
is responsible for which element. You have to edit the used styles in order to
change the look of the cells persistently since all direct formatting would be
lost after refreshing the data. You may want format cell-styles "DataPilot_Result" and "DataPilot_Value" to show currencies.

Advantage: With simple drag&drop you can change the layout and content of such report. The following screenshot shows the sums for items in second quarter of 2007 with a column for each month ("Month" dragged to column field, "Date" dragged off the sheet). Data field "Sum - Amount" has cell style "DataPilot_Value" with a currency format.

Known issues with data pilots:
- Formatting through predefined styles implies that all pilots in the same
document share the same formatting. This is particulary painful with
number formats when you want to show one field as percent and another one as
currencies.
- Pilots from databases tend to be slow. Use queries to restrict the columns
and rows to be imported. You may prefer a pilot from imported sheet
cells. Drag the table or query from the left pane of the datasource window
into a blank sheet and create a pilot from the imported range.
- Pilots from databases don't import times nor timestamps. Workaround: create
the pilot from imported sheet cells.
- Take care that a new pilot does not overwrite other data or intersects with
other pilots. Best practice is to place each pilot on it's own sheet.
- You can not change the source of a pilot.

[Villeroy]

Hi Villeroy,

By any chance would you be willing to grab the odb file from this thread, move the data to a Calc file and create that data pilot report laid out the way the report is and attach the ods file and the output as a pdf back to the thread..it would be a very nice comparison actually.

The sheet took less than 3 minutes. Registered your database, new spreadsheet, Data>Pilot>Start...

Download Drew's database, register it (Tools>Options...Base>Databases) as datasource "income", open the attached spreadsheet and drag around fields, call the wizard (context menu:Start...), change cell styles,...

[DrewJensen]

Yes - actually the report took about the same amount of time to produce...well, actually more like 10 minutes all told. ( The post with text and screen shots took a lot longer by far ).

OK - I grabbed the ods file and exported it as a pdf file

income_data_pilot.pdf

(36.01 KiB) Downloaded 466 times

And I export to pdf the final report from the database ( with that last change to the query - done after the report layout was created )

Income by month.pdf

(21.72 KiB) Downloaded 480 times

The bottom line is that there are two tools that can arrange the data in equivalent fashion - the questions is then Why choose one tool over the other. The answer to that question is I suppose dependent on what the final goal is. In other words it is not so much right / wrong but what is the right tool for this particular moment.

[Villeroy]

Thank you for the pdf. Actually I had some problem with my spreadsheet template. On my system it created bloated pdf due to missing fonts.
Regarding your db-report, I could not produce a pdf because ... well ... *cough* ... it's blank! I have report builder to version 1.0.4. I see the report in design view but it does not show anything.

There is an important difference between the db-report and the pilot as you can see after adding another record to the table: 2020-02-27, Commission4, 1000
This will add another row to the db-report below February 2020 whereas in the pilot 'Commission4' on 2020-02-27 will sum up to 1200 (adding 1000 to the existing 200 for the same commission on the same day).
The following query produces the same aggregated report as in the pilot:

Code: Select all

SELECT "Date", "Description", SUM( "Income" ) AS "Income", YEAR( "Date" ) AS "Year", MONTH( "Date" ) AS "Month", MONTHNAME( "Date" ) AS "MonthName" FROM "tblIncomeExpense" AS "tblIncomeExpense" GROUP BY "Date", "Description", YEAR( "Date" ), MONTH( "Date" ), MONTHNAME( "Date" ) ORDER BY "Date" ASC, "Year" ASC, "Month" ASC

However, I can not prove it because of my broken report builder add-on.

[DrewJensen]

LOL - you really ought to see someone about the cough..not good to let those types of things go unchecked.

If you happened to look at the topic on the forum about the 1.0.4 report builder you would find that the developer released a replacement .jar file to correct the problem - if you like I can email it to you..it is not "official" yet...but seems to work without a hitch.

But back to the question.

What you are doing now is mixing in new requirements - if the user wants to aggregate income by day then it can be done with either tool, if you want to list each separate item as a transaction that is supported also. I am not sure I understand the point you are trying to get across there.

To be sure that my point is not being missed also - I am not saying that a Data Pilot is inherently the wrong way to go, nor that use of Report Builder absolutely the correct. What I am saying is that each tool was built with different usage scenarios in mind and each has a place in an OO.o users "toolbox". The fact that this topic actually pointed to both as a possible solution I think is great.

[erythisis]

Thank you for the detailed information. I got exactly what I needed and I feel a lot more comfortable with Base itself now that I've actually done something useful.