I have a report that contains date columns that I'm displaying in HMDYY format. When it's run as an active report and the user sorts the report on either of these date fields, the result puts 2/19/2009 ahead of 10/23/2007 because the active report is sorting the column contents as a string.

I can fix the problem by using HYYMD format, but that's not how I want to display the date.

Does anyone know of a way both to display AND to sort these columns the way I want? Is there a different date format I should be using in the .mas and the .fex? Can I coax WebFOCUS' active reports functionality to let me specify a sorttable_customkey so I can display the table contents one way but sort them another way?

I appreciate any insight anyone can provide.

Regards,
Doug

This message has been edited. Last edited by: DougPowers,

The silence is deafening. Can we really be the first people ever to encounter this problem?

Hi Doug,

Here is a good document to start with:
How to add additional SORT fields with Active Reports

You may want to run some search on Focal Point and TechSupport website for additional techniques as well.

Cheers,

Kerry

Here's one way...
If you're using SQL, you could list the column twice in your SELECT statement and alias them to different names. Format one the way you want it to be displayed, and format the other to a sortable format. Use the sortable date format in your report sorts and then display the other in your report.

Or you could do the same in your DEFINE section.

BDAVIS, thanks for the suggestion. I'm not sure it will solve my problem, though, because those are server-side sorts--and those work just fine. The problem is that I'm talking about the client-side sorts that take place in Active Reports. Those sorts will only apply to the fields that are actually displayed in the final report (since the user will need to click on the column header to activate them), and any sorting I do on the server side in the .fex or the stored proc will have no impact on what Active Reports does with my result set once the user starts clicking on those sort options.

If there is some data format other that HMDYY I can use that will change how Active Reports treats the contents of those columns, that'd be great. I'm currently doubting that such a format exists--it appears to me that Active Reports treats everything as a character string regardless of its underlying data format.

Alternatively, if there were some way to get into what Active Reports is doing so that I could customize it, that would also be great. I'm also doubting that such a way exists.

I'm open to still other options, if they'll work; I just have yet to envision what those options might be.

kerry, I appreciate the link to the article. It's good to know that it's possible to sort on multiple fields in Active Reports (I'd wondered), though that's not exactly what I'm trying to do in this case. I'd be happy if I could get it to sort the way I want it to on just one date field. :-) The suggestion to search TechSupport is a good one; I'd done so before and I'm doing so again. I haven't yet found anything helpful, but I might. I'd searched Focal Point pretty extensively before I posted here--I hate posting questions that already have answers, so I always look to see if I can find my same problem before I post a new thread. I didn't find any other posts related to this situation.

Just to satisfy my curiousity, have you tried MDYY and I8MDYY instead of HMDYY? I don't have access to Active Reports so I can't test this myself.

This message has been edited. Last edited by: Dan Satchell,

Doug,

As you have identified the sorts treat the values as string, that's because within HTML there are no such thing as date formats. So the only was that you are going to get these sorted the way you want, is, as others have suggested (including yourself), to use an alternative format that will sort correctly when treated as a string.

However, if you were to create the active report as a MAINTAIN grid, then your quest might be a possibility - Mr Derwin any confirmation?

T

Dan, thanks for your suggestions. I tried both of those formats to no avail. When I used MDYY, all the years came out as "1901." When I tried to use I8MDYY, I got a "requested conversion is not supported" error.

Tony, thanks. The client has asserted that YYMD format, which would sort correctly, is not what she wants to see. So unless Active Reports will allow me to get into its workings to customize how it sorts (and I don't think it will), I would seem to be at a dead end. Hmmm. Well, I appreciate everyone's suggestions anyway.

Well what do you know? I seem to have stumbled upon a combination of formats that actually works.

The data in question is coming from the SQL stored procedure as a SQL DATETIME. In the master file, I set each date column to USAGE=MDYY and ACTUAL=DATE. Then in the Focus procedure I call the column with {COLUMN_NAME}/MDYY.

Now the date columns appear on the active report in MM/DD/YYYY order, and they sort correctly when I invoke Active Reports' sorting functionality.

This seems to be yet another case of the best stuff's being discovered by accident.

Thanks everyone for your input. Now if you're faced with a similar situation, I hope this same solution will help you.