Page 1 of 1

Report layout with columns

Unread postPosted: Sat Feb 13, 2021 8:40 am
by nickrth
Hi there,

I've had so much fun building my nuBuilder system over the past few months and have found it exceptionally good and the online documentation and forum incredibly helpful. But I'm stumped on this one...

I have a report that lists a bunch of items, but the report is quite long so I'm wanting to condense it by presenting the data in two columns. I tried to add the field a second time in the Details group, hoping it would advance to the next record for each one, but alas it just repeats the same record.
report.jpg
report.jpg (45.02 KiB) Viewed 493 times

Any ideas as to how I can get it to flow the data across two columns rather than just repeat the same record?

Thanks in advance.

Re: Report layout with columns

Unread postPosted: Mon Feb 15, 2021 6:22 am
by steven
nicktrh,

The only way you can do it is by faking it by creating 2 fields in the database and putting half the records in each field.

The same goes for printing multiple labels.


Steven

Re: Report layout with columns

Unread postPosted: Wed Feb 17, 2021 3:17 am
by apmuthu
Can use a view that populates two fields - one with the odd ones and the other with the even ones....or using two fields with half the number of items in one and the others in the rest using a WHERE clause.

Re: Report layout with columns

Unread postPosted: Wed Feb 17, 2021 3:33 am
by apmuthu
The SQL construct can be constructed like:
Code: Select all
CREATE TABLE `twocol` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Item` varchar(40) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `twocol`(`ID`,`Item`) VALUES
(1,'Lettuce iceberg whole (1 whole piece)')
,(2,'Margarine (1000 gram gram tub)')
,(3,'Onions brown sliced (1000 gram bag)')
,(4,'Onions red sliced (500 gram bag)')
,(5,'Beetroot (850 gram can)')
,(6,'Salad dressing coleslaw (330 ml bottle)');

-- The actual query to get the alternate Items in different columns

SELECT a.Item AS Col1
     , b.Item AS Col2
FROM `twocol` a LEFT JOIN `twocol`  b ON (a.ID = b.ID-1)
WHERE (a.ID%2);

Re: Report layout with columns

Unread postPosted: Fri Jun 04, 2021 9:41 pm
by nickrth
Many thanks. I'll try this approach.