[Solved] Grouping not working when creating a report

Getting your data onto paper - or the web - Discussing the reports features of Base

[Solved] Grouping not working when creating a report

Postby REReader » Wed May 25, 2016 2:44 am

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 167 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
REReader
 
Posts: 2
Joined: Wed May 25, 2016 2:28 am

Re: Grouping not working when creating a report

Postby chrisb » Wed May 25, 2016 10:40 pm

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   Expand viewCollapse view
select "ContactKey", "DateSent", "MailingDescription", "Notes", "Response"
from "Interactions"
order by "ContactKey", "DateSent"
open office 4.1.7 & LibreOffice 6.3.2 using HSQL 1.8.0.10 (Embedded) and HSQL 2.5.0 (Split) on Windows 10
chrisb
 
Posts: 200
Joined: Mon Jun 07, 2010 4:16 pm

Re: Grouping not working when creating a report

Postby REReader » Wed May 25, 2016 11:48 pm

Brilliant! That worked perfectly, thank you so much!
OpenOffice 4.0.1, running on Mac OS 10.6.8
REReader
 
Posts: 2
Joined: Wed May 25, 2016 2:28 am


Return to Reporting

Who is online

Users browsing this forum: No registered users and 2 guests