Page 1 of 1

[Solved] Grouping not working when creating a report

PostPosted: Wed May 25, 2016 2:44 am
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?

Re: Grouping not working when creating a report

PostPosted: Wed May 25, 2016 10:40 pm
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   Expand viewCollapse view
select "ContactKey", "DateSent", "MailingDescription", "Notes", "Response"
from "Interactions"
order by "ContactKey", "DateSent"

Re: Grouping not working when creating a report

PostPosted: Wed May 25, 2016 11:48 pm
by REReader
Brilliant! That worked perfectly, thank you so much!