Sort by calendar month

[forskare]

Hello folks,

I have a calc field in a group break header which I want to sort first by year(ascending)and then by month, sorted by calendar. The results should look like this.

January 1994
February 1994
January 1995
February 1995

I think you get the picture. I've tried various expressions but the results no success.

Thanks for the suggestions.

ken

[BRochford]

RE: Sort by calendar month

Ken, did you try DTOS(date_field)? DTOS returns a char. string in the form YYYYMMDD.

[BRochford]

RE: Sort by calendar month

Proving that you should always check the manual, look at CDATE, which is also much the same as DTOS.

[Mike Konoff]

RE: Sort by calendar month

I don't know much about A5 so I really haven't been able to provide much help to anybody on the A5 boards, but I sure feel useful when I can offer something. I hope the following helps you.

In Alpha Four I have a payroll table. The main date used is the Week Ending date. (w/e) I use the information stored to print an attendance report. It is grouped by month and sorted by w/e date. This is the calculated field that causes this to work. (This is the field that Group 1 is based on.)

CYEAR(WEDATE)+LTRIM(STR(MONTH(WEDATE)))+LTRIM(EMPNO)

Remember, the above calculattion is from Alpha Four, you may need to change something incase some of the above functions are different in A5. Also, in the report in the section "Record Selection and Print Settings" I have a "sort" spec listed. It is as follows...

TRIM(EMPNO)+TRIM(DTOS(WEDATE))

That keeps the report records ordered by employee number, then weekending date so the report will print by employee, then by month, based on the calculated field listed above.

If you have any questions, let me know.

Thanks,
Mike

[forskare]

RE: Sort by calendar month

Barry,

Don't recall if I tried cdate or not but will work with it.

Mike,

I tried this and but got the error message.

I've been able to come up with 3 1999 for example and it sorted by year ascending but sorted by month descending. This was a challenge in A97 as well. I solved the problem by creating calculated fields based on the date field and then a final calculated field changing the numeric number of the month to the month name. A long way around but it worked. Perhaps that is what will be needed here.

Would be great if A5 came up with a new finction that does just this as I believe there are many times when this must be done.

Thanks for the suggestions.

ken

[csda1]

RE: Sort by calendar month

Ken,

The equation you gave

CYEAR(WEDATE)+LTRIM(STR(MONTH(WEDATE)))+LTRIM(EMPNO)

will not work because the months will be sorted 1,10,11,12,2,3,4,5,6,7,8,9 because the leading 0 or space is not created by LTRIM(STR(MONTH(WEDATE))). You could have instead used STR(MONTH(WEDATE),2) or something else to pad the month, but this is the hard way. It is much easier to use an equation like;

LEFT(CDATE(datefield),6)

to sort by year and month. This is also very useful for using in group breaks by month where the sort by day is not desired (because you would have a subgroup under it grouping other items for the month). Of course, if you want to sort by year, month and date, just use

CDATE(datefield)

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[Mike Konoff]

RE: Sort by calendar month

Ken,

I believe that your are trying to sort a report as follows...

January 1994
February 1994
March 1994
April 1994
May 1994
...and so on. Am I correct?

Ira,

How will having a group break on CDATE(datefield) cause a group break based on the "month"?

Wouldn't only using CDATE(datefield) cause a new group with each change in the "date" and not in the "month"? I believe a calculated field that returns the "year" and "month" in ascending is what is needed, or am I missing something?

Upon further review of A5, of which I'm not that familiar with, wouldn't it be necessary to "order" the records in the Detail Properties on the Records Tab. Your expression CDATE(datefield) would do that there.

But then I think it would be required to insert a Group and set the break on each change in in month by either MONTH(datefield) or CMONTH(datefield)...setting the break to occur whenever the month changes. Also, instead of setting an order within the detail section could an order also be set within the New Group properties?

I'm curious.

Thanks,
Mike

[csda1]

RE: Sort by calendar month

Mike,

"How will having a group break on CDATE(datefield) cause a group break based on the "month"? Wouldn't only using CDATE(datefield) cause a new group with each change in the "date" and not in the "month"?"

That's what the LEFT(CDATE(datefield),6) does (if you read the text more carefully of my answer). It returns values like

199911
199912
200001
200002
etc

CDATE(datefield) sorts the entire database by Year, Month, day, which is useful for complete date sorts.

"Upon further review of A5, of which I'm not that familiar with, wouldn't it be necessary to "order" the records in the Detail Properties on the Records Tab."

Depends whether you specify it there or in your query, or in the group breaks. The group break sort order will override the details sort for that group if it does not agree with the details expression. The Details will override the natural sort of the query/index.

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[forskare]

RE: Sort by calendar month

Mike,

Pow! Pow! You hit it right square on the head.

Mike said,



YES!!!! Your are correct!!

I agree with your comment to Ira. That's where I've been running into the difficulty.

Mike said,



This is where I've been putting it and it leaves the Detail Property filter available for the date variables.

Sorry, but I haven't had the time to look into the HTML or I would've haad fun with this one.

I appreciate the thought and time going into this.

ken

[forskare]

RE: Sort by calendar month

Hi All,

Here's what I have so far:

In the Group Month Year properties, I have the following expression:

cmonth(Income->Date)+cyear(Income->Date)

Problem 1 - Only the records for the year 1998 are showing. There are several years of records so all years should show. There is no filter in the Detail properties. The report is based on a two table set with no filters.

Problem 2 - It sorts it by month in alphabetic order. I have the following expression in the calc field in the Month Year group:

cmonth( income->date )+cyear( income->date )

1. What do you suppose is causing only one year to show? (problem 1)

2. How can I get it to sort by calendar month rather than alphabeticly?

Thanks,

kenn

[Mike Konoff]

RE: Sort by calendar month

Ken,

Ira's suggestion for using LEFT(CDATE(datefield),6) now makes sense to me. (If I had only read the text more carefully) If you read his example and study the syntax of what he's provided, it makes sense, and is a good idea.

CDATE returns a date in the following format...20000629. (for today, 06/29/2000) If you add the LEFT function and specify the left 6 spaces, as in LEFT(CDATE(datefield),6)you will be returned the following...200006, returning only the year number and month number in a character format, which seems to be the easiest way to sort the years & months like you looking for. That way the group should only change when the month in a given year changes.

However, Ira indicated earlier that my first suggestion won't work. It does work, and it works every day. If he had read my post more carefully he would have seen that I add a sort spec to the report, sorting by employee number then weekending date and my overly lengthy calculated field group break breaks that sort on month which works exactly as I've indicated. Though, his suggestion is much simpler.

Ken, I'd try a calculated field as he suggested...LEFT(CDATE(income->date),6) and make the group break on that. I'd also sort the record in an ascending order based on (income->date).

After that, go to the report menu on the report design screen, then open Detail Properties, then click on the Records Tab. In the Filter section, choose to "select all". Then, in additional Record Order and Filter Criteria, choose none. Make sure that you have no other filter or query specified anywhere else. I'm not sure if that will do it, but give it a shot.

Also, are you printing your report from a Button? Within specifying your Print Report Button, is there any option to choose which records get printed within the button design process. Might want to check that out too.

Let me know if it works.

Thanks,
Mike

[Stan Mathews]

RE: Sort by calendar month

Ken,

I often try to design workarounds when I don't know that Alpha already has a function to do what I need. Sometimes they work and sometimes they don't. To do what you want I once developed the following expression for a group break:

str(month( yourdatefield)) + " " + str(year( yourdatefield))

It's not elegant, but it performs to your requirements as I understand them.

[Mike Konoff]

RE: Sort by calendar month

Stan,

Can you confirm something for me?

In a previous post on this thread Ira has indicated that using the MONTH won't work because it will order the months as follows...

1 for January
10 for October
11 for November
12 for December
2 for February
3 for March
4 for April...and then 5, 6, 7, 8, 9 for the rest of the months until September...and the resulting order wouldn't be what Ken is looking for.

But, if you place a sort on the records in the report based on (datefieldused), it would work because the records would be ordered per the sort spec and a group break would happend at each change in str(month(datefield)). Does that make sense to you?

Thanks,
Mike

[forskare]

RE: Sort by calendar month

Stan,

This works well but it sorts the months alphabeticly rather than in calendar order. Will check out the other suggestion at noon. Depending on the outcome, I will attach a file to my follow-up post.

Thanks

Ken

[Stan Mathews]

RE: Sort by calendar month

Please note I'm not using month(), I'm using str(month()), separating the year away with a space (" ") and then using the str(year()). If you were to go to the expression builder in a report while defining the group break, and pick a date field to use with my expression posted earlier, then use the evaluate button, you will see values like:

" 1 1999"
" 12 1999"
" 1 2000"
" 2 2000"

I don't know if the above will format correctly in this message, but basically the str(month()) takes care of sorting the month "numbers" in correct order.

Like I said originally, not elegant but functional.


I'm not sure I follow the last part of your post.