[Solved] Grouping not working when creating a report

Getting your data onto paper - or the web - Discussing the reports features of Base
Post Reply
REReader
Posts: 2
Joined: Wed May 25, 2016 2:28 am

[Solved] Grouping not working when creating a report

Post by REReader »

Hi! I'm new here, and just learning to use Base, and I've managed to make and relate tables and create and alter forms. I input some sample data so I could try creating a report to see if I could get that to work. (See attached--at least I hope I did that right!)

However. When I try to create a report from a table (Interactions), grouped by one field (ContactKey) and then sorted by one other field within the group (DateSent), the grouping part doesn't happen. There should be several small groups and then two large ones, but the large ones break up into numerous other groups (shown in the attachment). When I try to create with no groups and just sort by ContactKey, that works--but it's not very usable, to have the same field repeated over and over and over!

When searching I saw a number of responses pointing out that 4.1.2 has problems on a Mac, but I'm using 4.0.1, and also I'm not trying to create the report from a query but from a single table. Any ideas what I'm doing wrong and how to fix it?
Attachments
BookDesignContacts.odb
(95.72 KiB) Downloaded 288 times
Last edited by REReader on Wed May 25, 2016 11:49 pm, edited 1 time in total.
OpenOffice 4.0.1, running on Mac OS 10.6.8
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Grouping not working when creating a report

Post by chrisb »

hello REReader,
use the code below to create a query & use it as the data source for your report. it sorts the data first by "ContactKey" & then by "DateSent".

copy the code & paste it here>>> Database>Queries>CreateQuery in SQL View
hit the SQL icon or menu:Edit>Run SQL command directly. this bypasses the base parser thereby preserving our sort order. hit save. close window.
right click your query and select 'Report Wizard'.
when you create your report group by "ContactKey". you will not be be prompted for sort order.
open report for edit. right click on the date field. select Number Format & choose a date format.

Code: Select all

select "ContactKey", "DateSent", "MailingDescription", "Notes", "Response"
from "Interactions"
order by "ContactKey", "DateSent"
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
REReader
Posts: 2
Joined: Wed May 25, 2016 2:28 am

Re: Grouping not working when creating a report

Post by REReader »

Brilliant! That worked perfectly, thank you so much!
OpenOffice 4.0.1, running on Mac OS 10.6.8
Post Reply