Help with grouping a ReportNet report by Month only

[JGirl]

Hi,

Here's my problem (sorry its so long!): I want to create a staff birthday list in ReportNet that simply includes the month name, and the names of staff who have their birthday in the month. I want the report grouped and sorted by month only (in calendar order).

ie.
MONTH STAFFNAME
January Amy
Sally
February Mark
April Ben
May Alex
July Peter
Sam
Julie
August Lisa
October Bob
November Tim
Paul
December Jenny

The problem I'm having is that the birthday month is extracted from the Date Of Birth field. I can get the report to display the month name correctly as in the example above, BUT....I can't group it properly because even though Im only displaying the month name, its grouping it by the birth day and the birth year as well (and since almost none of the employees were born on the same day in the same year), they are pretty much being grouped individually.

I tried extracting the month from the date of birth, using extract(month, DOB), but then I'm left with integer values 1 to 12 and without using a case statement can't get the month names to display (and even if I can, it then sorts them alphabetically so August is first).

What I've done to get around it is created a new data item, where im:
1. using extract(month,DOB)
2. converting that to a char value
3. attaching '01-' to the front (for first day of the month)
4. appending '-1900' to the end (so all years are the same)
5. converting the '01-mm-1900' to a date value
and then grouping and sorting by this date value while displaying the month name only.

The option above works correctly but it seems like a really long way to go about achieving something relatively simple. Is there an easier way to go about it?

J

 

[DoubleD]

You'll need a total of three date fields:

Hide a calculated Extract(month,DOB) field from the output. (This will be your highest level of grouping.)
Now create a calculated field to give you the month name (not sure of the function in ReportNet) Show this field in your report.
Lastly, group on DOB, but hide or show it in your output, depending on your preference.

Pain is stress leaving the body.

DoubleD

 

[kenndot]

DoubleD

You mention that it's possible to hide/show groups? Where is that option?


FYI:
I've had problems w/grouping, it's not as straightforward as you would expect. Furthermore, there is a known bug which I ran into (it was logged back in March I guess) where when you group and view the report in HTML, it will display several blank pages in between the groups. For example, I have a report that is a simple 6 lines when grouped. Yet, the report is hundreds of pages long. Some pages have 2 groups listed on it and several more are blank. It's almost as if the data is still there creating all these blank pages. Actually if you look at the XML, it really IS still there. There is no current workaround either. I was told I could have the salesperson "hot-issue" the problem and the developers would get assigned to the problem now rather than waiting for the next release. I asked that we take that option because grouping is such a common task that it could wreck our momentum if we told our execs that we bought this thing, but "oops, we can't group on any info"...

 

[DoubleD]

Sorry, I don't have access to ReportNet right now, hopefully someone else here can provide you the detailed instructions required.

Pain is stress leaving the body.

DoubleD