Unable to sort fields

[junction123]

Hi,

I have 4 fields that I have grouped by to fix the sort order.

ClassID
EntityID
SubEntityID
TradeDate

The order of records is now fixed but I have the page breaking after each record.

I tried the not onlastrecord but this did not help.

How do I prevent each record from showing on a new page?

thanks
junction123

 

[junction123]

After taking out the Record Sort Expert fields and just leaving the Address field it works perfect . Your suggestion on Summary of TradeDate helped. KUDOS to u!

Thanks a lot LB.

junction123

 

[junction123]

LB,

After going through all this, I am now told the sort order might need to change. But the positive is that I learnt something new.

Thanks
junction123

 

[junction123]

LB,

The user now wants the report sorted by date only and nothing else.

I have the report grouped by Address. For this group, I have a group sort expert for all based on Max of @TradeDate.

The report has a backend stored procedure that orders teh report by several fields. The stored procedure is also used by anotehr job and I cannot just modify the sort order in the stored procedure.

I want to override all those fields and only sort by tradedate in the report. tradedate is a field in the detail section.

Is this possible? Or should I just tell the user this cannot be done?

I tried various methods but am unable to sort the report just by tradedate.

Any help is much appreciated.

Thanks in advance for your help
junction123

 

[lbass]

If you ONLY have a group on address, then your current group sort should work. If you go into report->sort records, what do you see for groups?

-LB

 

[junction123]

LB,

I went to Report -> Group Sort Expert,

I see @AddressInformation

Then I see a dropdown which has All selected for the below

For this group sort

All based on Max of @TradeDate

Ascending.

The stored procedure orders columns by

ORDER BY ClassID, SeriesID, SubEntityID,TradeDate, TransactionID

In the below output, the data is sorted by date within the ClassID but across the entire data the date is not sorted by ascending order. I am not sure why the user wants it sorted differently but if it is very difficult then I will tell them I cannot get it to work in the required sort order.

Output example

ClassID SeriesID SubEntityID Trade_Date TransactionID

BCT43AUSDUR None 5191 6/1/2009 73240
BCT43AUSDUR None 5191 7/1/2009 75368
BCT43AUSDUR None 5191 8/1/2009 76883
BCT43AUSDUR None 5191 10/1/2009 52646
BCTA1AUSDUR None 5191 6/1/2009 73178
BCTA1AUSDUR None 5191 6/1/2009 73179
BCTBLBEUR None 17478 3/1/2010 107992
BCTBLBEUR None 17478 4/1/2010 111650
BCTBLBUSD None 1725 3/1/2008 57331
BCTBLBUSD None 1725 4/1/2008 57332
BCTBLBUSD None 1725 6/1/2009 42379
BCTBLBUSD None 1725 9/1/2009 48840
BCTBLBUSD None 1725 9/1/2009 48841
BCTBLBUSD None 5191 5/1/2006 57438
BCTBLBUSD None 5191 1/1/2007 57439
BCTBLBUSD None 5191 7/1/2007 57440
BCTBLBUSD None 5191 12/1/2008 57441
BCTBLBUSD None 5191 1/1/2009 57442
BCTBLBUSD None 5191 6/1/2009 42383
BCTBLBUSD None 15260 6/1/2009 73221
BCTBLBUSD None 15260 10/1/2009 52254
BCTBLBUSD None 15260 4/1/2010 112437

 

[junction123]

Under the Heirarchical grouping options, I have the below

Avaiable Groups
@AddressInformation

Sort Data HierarchicallyParentID Field
Detail.TradeDate, whic is the report field not the detail field.

I should probably change this to the detail field maybe and test?


Or should I use Min(TradeDate) instead of Maximum for the Group Sort Expert?

 

[lbass]

Why are you using hierarchical grouping? I think you should remove that and just insert a regular group on AddressInfo, making it your first group (make sure it appears as the highest order group in report->record sort) and then go into group sort and sort that group based on the max of the date field.

-LB

 

[junction123]

I just realized the backend converts teh TradeDate field to a string and the final output returns the data as a string MM/DD/YYYY format.

Does it make sense to convert to date type before I sort by the field? I am sorting a string field hence the output makes no sense.

The backend correctly sorts by date because I am ordering by the TradeDate before I do the conversion and do a final select for the output.

 

[lbass]

Yes. Create a formula:

date({table.stringdate})

Insert the summary on this formula and use it for the group sort.

-LB

 

[junction123]

LB,

I was able to sort by date, thanks for your help.

Now I have two group sections. I have the column header in the outer group section header. The column headings do not appear on the second page and appear twice on teh first page.

I have no formula (new page before/after) . How do I fix this.

I need this urgently.

Thanks in advance
junction123

 

[lbass]

Put them in the page header.

-LB