nuBuilder Forum

Built a report in the fast builder where there was an option to choose a sort. I went in to the report sql and changed the sort order, however this does not change it in the report. So how does one change the sort order on a report???

Are you ordering by Date column? You could try: ORDER BY CAST(your_date_column as date)

Have you set a different sort order in the Group Properties? https://wiki.nubuilder.cloud/ ... Properties

Yes I am ordering by date. This is kind of like a aging report. I'll try the cast clause on the date and see if there is any joy.

So this is really odd, tried the cast clause and the report returned 0 records. So I figured let's simplify the issue and change the sort order on the character field from ASC to DESC, no joy there either. The SQL generated executes fine in PHPMyAdmin.

Can you post the report SQL and a screenshot of your table structure where we see the data types?

Report SQL:
SELECT
BinOut.BarCode,
BinOut.OutDate,
BinOut.OutContidion,
BinOut.InDate,
BinOut.InCondition,
BinOut.InVolume,
Organizations.OrgName,
Organizations.OrgCity,
1 as BinCount

FROM
BinOut
JOIN Organizations ON BinOut.OrgID = Organizations.OrgID

WHERE
((InDate is null))

ORDER BY
BarCode DESC

CREATE TABLE `BinOut` (
`BinOutID` int(11) NOT NULL,
`BarCode` varchar(6) NOT NULL,
`OrgID` int(11) NOT NULL,
`OutDate` date DEFAULT NULL,
`OutContidion` int(11) DEFAULT NULL,
`InDate` date DEFAULT NULL,
`InCondition` int(11) DEFAULT NULL,
`InVolume` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `Organizations` (
`OrgID` int(25) NOT NULL,
`OrgCode` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
`OrgName` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
`OrgAddress1` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
`OrgAddress2` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
`OrgCity` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
`OrgState` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,
`OrgPostcode` varchar(300) COLLATE utf8_unicode_ci DEFAULT NULL,
`OrgTypeCode` int(2) DEFAULT 0,
`OrgOfficeNumber` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
`OrgOfficeEmail` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
`OrgNotes` text COLLATE utf8_unicode_ci DEFAULT NULL,
`ResaleNumber` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL
)

treed wrote:Yes I am ordering by date.

I'm a bit confused. BarCode isn't a date, is it?

The code posted was simplified to see if I could just change the existing report sorting from an ASC to DESC order. That didn't work either. Are you available for some consulting?

I've created your table in my db, added some dummy data and created a report. The sorting isn't working for me either. I'll have a look and see if I can find the cause.

Somehow, the "ORDER BY" in the SQL has no effect.

Next, I was trying to change the sort order in the report from Ascending to Descending. But It won't accept the change.

See my screen recording: https://streamable.com/6d9ojf

To be honest, I'm not that familiar with the reporting engine. Maybe I am doing something wrong or is it really not working as it should?

Last I manipulated the JSON in the database by chainging sortBy of the BarCode field to "d" (d = descending). That worked then!
Please try if that works for you too.

Again, either there really is a bug or I'm doing something wrong....