Change Sort Order

[treed]

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???

[kev1n]

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

[treed]

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.

[treed]

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.

[kev1n]

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

[treed]

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
)

[kev1n]

Yes I am ordering by date.

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

Code: Select all

ORDER BY
BarCode DESC

[treed]

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?

[kev1n]

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.

[kev1n]

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!

sry_layout.png

barcode_sortBy.png

Please try if that works for you too.

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