Applying Sorts in objects

[ssudha]

Hi,

I am applying a sort on an object but when I run the report, the records are not sorted as required. I am using BO 6.1b.

Any ideas?

Thanks in advance
Sudhakar

 

[skrandel]

Are you sorting in the query panel? Those sorts are not brought over to the report. They are there in case you need to export data.

Sorting is meant to be done at the report level.

Steve Krandel
Westbay Solutions

http://www.westbaysolutions.com

 

[ssudha]

Hi Steve,

Yes, I am applying a sort in the query panel. I am overwriting the SQL generated by the query panel to use my ORDER BY on a column. But this is not working.

Thanks for the reply
Sudhakar

 

[nimmy99]

If I apply the sort for the first time, it's working. If I try to modify the dataprovider, I am not getting the correct report. But in that view data option, I am getting the correct result. May be you can try to purge and refresh the data provider for the correct result, i guess.

 

[skrandel]

That's how it works. The sort is honored only on the initial creation of the default report. After that, control has been turned over to the report block. You won't ever get it to sort again from the query panel.

Sorts in the query panel are useless. Sorting should be done on the report.

Steve Krandel
Westbay Solutions

http://www.westbaysolutions.com

 

[ssudha]

Yes, even if I use ORDER BY in the SQL query, it is not working. I had to use sorting in the report somehow.
I also had to change the query to use GROUP BY stuff to get the desired result.

Thanks Steve,
Sudhakar

 

[skrandel]

OK, now the truth comes out. If you had to modify the query to add "sum" on a measure and a Group By clause, then your universe is not built correctly.

Measures should have SQL aggregation on them in the Select portion of the object. eg. Sum(table.quantity) or Count(table.Order_number)

Without it you are simply dumping records to BO and having it do all the work.

The projection setting on the advanced tab tells BO how to deal with the data at the report level. That will be set to Sum 99% of the time. If you set your aggregation on the SQL, the other one will work by itself.

Steve Krandel
Westbay Solutions

http://www.westbaysolutions.com