Sorting by month

[drcraig232]

Fresh and new to Strata, I whipped up a quick report with month-to-date totals with one detail line per month. I first created a calculated column in the source data with Monthname -- and of course, my report comes out in February-January-March order. Next I created another calculated column using Monthnum and that report comes out 1-2-3 order, which is nice, but not too professional-looking. I tried to find a way to use a lookup table to convert month number to text but apparantly the report writer can only accept one data source. Finally I tried to create another calculated column in the source by concantenating month num to month name, but couldn't get that to work. Just as well, since I would have a definite problem again when we get to the 4th quarter of the year. Any thoughts as to how I might go about getting the month names and their data to print in month order? I'm not sure if Strata will allow a nested if to a depth of 12, but if it comes down to it, I'll try it and hope I can get all the parentheses correct!

Don Craig

[Ken]

Hi Don,

Try this (let's call your date field "datefield" for example purposes):

1) In your table, create two calculated fields:

month(datefield) [we'll name this field "monthnumbers"]
monthname(datefield) [we'll name this field "monthnames"]

2) In your report settings, create your group/sort on "monthnumbers"

3) In your report design view, add the field "monthnames" to your group header.

Effectively, you'll be sorting on the number ascending but the group header will show the corresponding name.

You'll also probably want to use the settings to also sort on "datefield" so that your detail is sorted in proper ascending order as well.

Lemme know if that works,
ken

P.S. "I'm not sure if Strata will allow a nested if to a depth of 12, but if it comes down to it, I'll try it and hope I can get all the parentheses correct!" -- In a pinch, Strata can handle this sort of thing without a problem if you ever need it -- but I think the above solution should get you what you need.

[drcraig232]

Yep, your suggestion worked. Took me a few minutes to realize that since I wanted a summary report I could eliminate the detail line and get the month name as GroupHeader and the totals I want in the GroupFooter with no detail. It leaves them on 2 separate lines, but works just fine. Thanks.

Any clue on the print setting I asked about in my post just prior to this one?

[Ken]

Great to hear.

Regarding printing -- very strange. I posted a PDF workaround that should get you what you need in that post just now:

viewtopic.php?f=20&t=1702

Cheers,
ken