CDR Tickets

BZDATETIME::2011-10-18 14:29:02
BZCOMMENTOR::Alan Meyer
BZCOMMENT::1

I don't see an easy way to produce ad hoc queries to provide this information. The "ad hoc" interface executes exactly one SQL statement to produce output, but I think a number of statements will be required for these reports.

I therefore propose to just produce a regular report invoked from the reports menu. I suggest a single report with two pairs of radio buttons, one pair each for:

o Accepted o Rejected

o English o Spanish

The user would click the desired inputs and I'd then produce the output. That would mean one report program covering both this issue and OCECDR-3435.

I can't think of any reason not to do it that way, so I'll proceed with that approach. Please let me know ASAP if there is a problem with that approach.

BZDATETIME::2011-10-18 15:33:32
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::2

A regular report is fine with me. I have updated the title to reflect the new direction. I am also going to mark OCECDR-3435 as a duplicate of this one.

BZDATETIME::2011-10-18 15:38:08
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::3

BZDATETIME::2011-10-18 16:27:51
BZCOMMENTOR::Alan Meyer
BZCOMMENT::4

The report will be pretty big. Just with current data, the
report for Approved terms in one language will include 3,386 rows
of terms. Printed out, I'm guessing that might be 50 pages,
depending on the font size. That will increase.

Some things I can do to make the data more manageable might
include:

Report data in reverse chronological order, bringing the
recent data to the top.

Allow a user to enter optional starting and ending file
names.

Another alternative is to just print summary data, e.g.,

For each week's data:
For each language:
For each user who has approved or rejected something:
Number approved.
Number rejected.

If we don't need it by week, it gets much smaller still,
just:

For each language:
For each user who has approved or rejected something:
Number approved.
Number rejected.
Subtotal
Grand total

That would easily fit on one page.

Another useful summary could be:

For each week:
For each language:
Number approved.
Number rejected.
Subtotal
Grand total

If summary data is what's really needed, maybe we don't
want the complete list of terms.

I'm going to work on other things for a while so that users can
think about whether a summary report would be more useful than a
complete listing. Neither approach is harder to implement than
the other, it's just a matter of whether 50+ pages of output is
desirable. I can do it either way.

BZDATETIME::2011-10-19 09:39:14
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::5

While summary data would be useful, seeing the terms is the main purpose of these reports. Would it be possible to allow the user to enter a starting and ending date instead of entering file names? If the date range feature would work, then this report would not be used to find counts of approved or rejected terms so there would not be the need for a user to run the report for all approved terms, for example. An ad hoc query may be appropriate in the case of counts.

BZDATETIME::2011-10-19 13:25:17
BZCOMMENTOR::Alan Meyer
BZCOMMENT::6

(In reply to comment #5)
> While summary data would be useful, seeing the terms is the main purpose of
> these reports. Would it be possible to allow the user to enter a starting and
> ending date instead of entering file names? If the date range feature would
> work, then this report would not be used to find counts of approved or rejected
> terms so there would not be the need for a user to run the report for all
> approved terms, for example. An ad hoc query may be appropriate in the case of
> counts.

Yes, I think we can do that. And of course if you want the full 50 pages, or whatever it comes to, that will still be possible just by entering the right dates.

There are different dates available. The most appropriate date is probably the reviewed date assigned when a zipfile is completed. That will be a date that includes all items in one zipfile.

Let's discuss it at the meeting tomorrow.

BZDATETIME::2011-10-20 13:58:20
BZCOMMENTOR::Alan Meyer
BZCOMMENT::7

We concluded at the meeting that I would program the report to the original specs but with optional inputs that allow the user to specify start and end dates for the approvals or rejections of terms to be included.

I'll use the actual date of approval or rejection, not the date of the completion of the entire zipfile. That will allow users to see terms that have been reviewed in zipfiles that are not yet completed. I'll show summary information about unreviewed terms for each zipfile as well as the subtotal for terms from that zipfile appearing in the report.

BZDATETIME::2011-10-25 23:37:37
BZCOMMENTOR::Alan Meyer
BZCOMMENT::8

I have the first draft of this running on Mahler, ready for test.

To access it, go to:

CIAT/OCCM Staff >
Reports >
Glossary Terms

Then go to the last item in the "Processing Reports":

"Glossary Term Audio Review Report"

I implemented it pretty much as requested but made two small additions. In addition to "Approved" and "Rejected" I allow a user to select "Unreviewed". It was a freebie, requiring hardly any extra effort, and I thought it might possibly be useful.

The other addition is at the bottom of each zipfile table. Instead of just giving the subtotal for the specific approval status of the listed items I give the subtotal for all three possible status values. That seemed to me to add some value.

Seeing the reports, I now realize that the review dates are misleading. When a user saved an Audio Review screen, I gave all of the terms the same date, i.e., the date of the save. Even "unreviewed" terms have that "review date". It was a dumb way to do it but since none of us ever looked at the saved data, none of us realized that what I had done was wrong.

If desired, I can fix that. What I can do during a save is, compare the approval status of the term with the stored approval status. If it's changed, I could update the review_date. If it hasn't changed, I can leave the review date alone. For unreviewed terms, it might make sense to make the review date the last date that the record was saved with an unreviewed status.

I can do that now for future use, but I don't have any way to fix the data in the past. If I should fix it for the future, please put in an issue for that.

BZDATETIME::2011-10-26 10:38:45
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::9

I have been testing the report and so far it's been working very well and it also looks really good. I just have a couple of issues that I have outlined below. I will also enter a new issue to fix the review date problem you mentioned in comment #8 but I am wondering why you would want to assign a date for the unreviewed terms? Is it because you need the date for this report? If the date is required, perhaps you should store the date the terms were imported as the unreviewed date and keep that until the status changes.

1. Could you add the calendar macro so that we can select the date from the popup calendar instead of typing the date?

2. If I want to run the report for just one day, I am entering the same date for Start Date and End Date but the report does not retrieve anything for even dates that terms were reviewed.

3. When a particular run does not retrieve any records, could we get feedback to indicate that no terms were reviewed for that date range?

4. Is there a way to include a grand total for all terms reviewed without having to run the report to display all the terms?

BZDATETIME::2011-10-27 21:39:37
BZCOMMENTOR::Alan Meyer
BZCOMMENT::10

(In reply to comment #9)

> ... I just have a couple of issues that I have outlined below....

I have fixed up the report with all four changes.

Looking at one of our reports that used the calendar widget I could see that the input controls looked a lot nicer than mine, so I dressed mine up too.

I've been producing pedestrian user interfaces for too long, concentrating only on the information content of the screens. Since Volker and Bob are both producing nice looking interfaces, I thought I really ought to get with the program and not have everyone think that my programs look about as old as I do.

The report is ready for renewed testing.

(But I'm still going to resist buying new clothes.)

BZDATETIME::2011-10-28 00:05:34
BZCOMMENTOR::Alan Meyer
BZCOMMENT::11

I looked at the problem of fixing the audio review program to save the date only if the approval status changed. It appeared to be a trivial change. All I had to do was add two lines of code.

I went ahead and made the change on Mahler without waiting for a new issue to be entered into Bugzilla.

To test it, call up the Glossary Term Audio Review program and change the status of some of the terms. Then go to the new Glossary Term Audio Review Report and examine the results.

I think it's working the way we want it to.

BZDATETIME::2011-11-01 11:17:45
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::12

(In reply to comment #10)
> I have fixed up the report with all four changes.
>
1. When a user enters a start date that is earlier than the end date, can you display a different error message?

2. The way you implemented #4 is fine. However, in order to get the grand total for all reviewed terms for 2 years for example, we still need to run the report to retrieve and display all the terms that were reviewed within the 2 years. Is there a way to include a button in the interface, for example, to get a count of the grand total of reviewed terms without producing all the terms in the results page? This is not critical so if it will take too much time to implement, don’t worry about it.

BZDATETIME::2011-11-02 11:37:36
BZCOMMENTOR::Alan Meyer
BZCOMMENT::13

(In reply to comment #12)

> 1. When a user enters a start date that is earlier than the end date, can you
> display a different error message?

Done.

> 2. The way you implemented #4 is fine. However, in order to get the grand total
> for all reviewed terms for 2 years for example, we still need to run the report
> to retrieve and display all the terms that were reviewed within the 2 years. Is
> there a way to include a button in the interface, for example, to get a count
> of the grand total of reviewed terms without producing all the terms in the
> results page? This is not critical so if it will take too much time to
> implement, don’t worry about it.

Done, I added a new pair of radio buttons for a full or summary only report. The summary only just produces the last line of the report, no tables of zipfiles and terms.

Since this is only a report and doesn't update the database, it should be safe to put on Bach where you can test with live data.

It might also be advisable to test the revised audio review program since, the sooner we put that into production, the sooner we'll get accurate review dates for each of the newly reviewed terms. However that program does update the database, so testing is important.

BZDATETIME::2011-11-02 12:20:07
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::14

(In reply to comment #13)
> (In reply to comment #12)
>

> > implement, don’t worry about it.
>
> Done, I added a new pair of radio buttons for a full or summary only report.
> The summary only just produces the last line of the report, no tables of
> zipfiles and terms.
>

This looks good. Thanks!

> Since this is only a report and doesn't update the database, it should be safe
> to put on Bach where you can test with live data.

Yes. Please promote to Bach.
>
> It might also be advisable to test the revised audio review program since, the
> sooner we put that into production, the sooner we'll get accurate review dates
> for each of the newly reviewed terms. However that program does update the
> database, so testing is important.

I tested this. I think it working correctly.

Could you also remove the report from its current location and place it under media?

CIAT/OCCM Staff >
Reports >
Media

Let it go under Other Reports

BZDATETIME::2011-11-02 14:19:18
BZCOMMENTOR::Alan Meyer
BZCOMMENT::15

I'll move the report program to Bach and change the menu position. I'll report back when that's done.

I wasn't absolutely certain from the previous comment whether I should also move the modified audio review program into production - the one that correctly sets the date of any status modification.

Should I do that?

BZDATETIME::2011-11-02 14:23:54
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::16

(In reply to comment #15)
> I'll move the report program to Bach and change the menu position. I'll report
> back when that's done.
>
> I wasn't absolutely certain from the previous comment whether I should also
> move the modified audio review program into production - the one that correctly
> sets the date of any status modification.
>
> Should I do that?

Yes. Please do. Thanks!

BZDATETIME::2011-11-02 15:17:31
BZCOMMENTOR::Alan Meyer
BZCOMMENT::17

I have made the changes and promoted everything to Bach.

Looking at the audio review data on Bach I decided that I was mistaken about what we were seeing on Mahler. I now think that the program was behaving correctly with regard to dates of review status change. The change I made was unnecessary.

Status date changes are modified in the old code under two circumstances. One was that a user changed an approval status. The other was that a user changed the review note without changing the review status - which he could not have done without reviewing the mp3 file.

I've left all of that alone. I think it's right.

The menu entry for the report is in the Media Reports menu. This now exists in two different forms.

On Mahler, there is an "Other Reports" section that now has two reports in it, an "Audio Pronunciation Recordings Tracking Report", and my new report which I've called "Audio Pronunciation Review Statistics Report", for naming consistency. I haven't changed the title header in the report itself to match but can do that if desired, or can change the menu entry, or can leave everything alone.

The Tracking report is a new report written by Bob that has not yet been promoted to production. I therefore promoted a version to production that has my new report but not Bob's. I still retained his "Other Reports" section and put my report in that.

Everything is changeable of course.

I'm marking this issue as resolved-fixed.

BZDATETIME::2011-11-03 11:49:44
BZCOMMENTOR::William Osei-Poku
BZCOMMENT::18

(In reply to comment #17)

>
> I'm marking this issue as resolved-fixed.

Verified. It looks good. Thank you!
Issue closed.