12282 - General summary reports.

Status: RESOLVED FIXED

(Bugzilla :: Reporting/Charting, enhancement, P1)

Description

[Matthew Tuck [:CodeMachine]]

I've noticed the introduction of most doomed reports, and I think it would be
useful to extend these to more general summary reports.  This would work as
follows:

Firstly, you would have a selection criteria like you have for queries.  Then
you would have a bunch of reporting fields.  For each of these you report a
value and how many bugs have this value.  For fields that are sets (ie cc
field), you could report for one document more than once.  If you have more than
one column, the second is a subcategory.

Perhaps an example is in order.

Selection: area is {browser, mailnews}, status is {open, reopened, ..}
Categorise On: Milestone, CC

1004 bug reports found

M9 - 900
   [e-mail] - 40
   [e-mail] - 101
   ...

M10 - 400
   ...

Everything gets sorted automatically within it's category.

This is basically the same as a most doomed report where you take all bugs and
report on Milestone, Engineer.

You could also specify a limit on the first field, so you would only report,
say, the first milestone.

For this to work exactly like the most doomed report, you'd need to ensure you
can't reopen bugs with a milestone of M8 during M9.

Comment 1

[Matthew Tuck [:CodeMachine]]

Each category name should be hyperlinkified, ie in the example above, you'd have
a link for M9 bugs, then a link for M9 bugs assigned to [e-mail].

Comment 2

[Matthew Tuck [:CodeMachine]]

See bug #9789 and bug #15806 for two different specific reports that this would
support.

Comment 3

[Matthew Tuck [:CodeMachine]]

Also useful would be the ability to just summarise, and not include the
individual bugs, ie just the categories and their counts.

Comment 4

[Matthew Tuck [:CodeMachine]]

Actually you'd probably want the ability to limit the display of every category,
as well as the documents themselves.  For example a Top 10 voted for report
summarised on Product would be

Product -> Max 10 (Documents)

You could also do:

Milestone (Max 5), Engineer (Max 10) -> Max 10 (Documents)

You could possibly provide a a link to expand these maxima.

Comment 5

[Matthew Tuck [:CodeMachine]]

I don't know what I was talking about two comments up since my original proposal
didn't show the individual bugs.  But it definitely should be able to, for
example for most voted reports.

Comment 6

[Terry Weissman]

[e-mail] is the new owner of Bugzilla and Bonsai.  (For details,
see my posting in netscape.public.mozilla.webtools,
news://news.mozilla.org/38F5D90D.F40E8C1A%40geocast.com .)

Comment 7

[Tara Hernandez]

and also 9789

Comment 8

[Matthew Tuck [:CodeMachine]]

*** Bug 17604 has been marked as a duplicate of this bug. ***

Comment 9

[Andreas Franke (gone)]

Well, this sounds very much like SQL, doesn't it?
SELECT count(bug_id) FROM bugs ... WHERE ... GROUP BY ... ORDER BY ...

Can you post the SQL queries for the examples here, compare them, and come up
with a generic pattern for these queries? Or is there no such pattern?

How many open bugs in product p, targeted at milestone m, are assigned to x? 
http://lxr.mozilla.org/mozilla/source/webtools/bugzilla/reports.cgi#728 :
select count(bug_id) from bugs,profiles where target_milestone=\"$ms\" and
userid=assigned_to and userid=\"$person\" 
and bugs.product=".SqlQuote($FORM{'product'} and (bugs.bug_status = 'NEW' or
bugs.bug_status = 'ASSIGNED' or bugs.bug_status = 'REOPENED')

How many untargeted NEW non-enhancement Browser/MailNews bugs with empty status
whiteboard are assigned to x?
http://lxr.mozilla.org/mozilla/source/webtools/bugzilla/reports.cgi#843
select count(bug_id) from bugs,profiles where bugs.bug_status='NEW' and
userid=assigned_to and userid='$person' and target_milestone='' and
bug_severity!='enhancement' and status_whiteboard='' and (product='Browser' or
product='MailNews')

Comment 10

[Stephan Niemz]

Adding default QA contact to all open Webtools/Bugzilla bugs lacking one.
Sorry for the spam.

Comment 11

[Dave Miller [:justdave]]

moving to real milestones...

Comment 12

[Zach Lipton [:zach]]

To the bugzilla 3 component

Comment 13

[Dave Miller [:justdave]]

The Bugzilla 3 component is going away.  We're going to depend on the Milestones 
for this.  At the time this component was created, we didn't have milestones for 
Bugzilla.

Comment 14

[Dave Miller [:justdave]]

Taking all of cyeh's Bugzilla bugs.

Comment 15

[Matthew Tuck [:CodeMachine]]

Moving to new Bugzilla product ...

Comment 16

[Matthew Tuck [:CodeMachine]]

Would be nice if you could get percentages along with counts.

And when you're categorising on multi-valued fields, an explicit message should
appear saying that the sum of the categories may add up to more than the total
because of this.

Comment 17

[Nyles Harris]

As a feature on the reports page, it would be nice to have monthly breakdowns
of both charts and reports (even quarterly).  Is there any new progress on
reports for version 2.14?  Any patches available yet or schedule for new 
patch features to be ready soon?

Comment 18

[Gervase Markham [:gerv]]

When the query.cgi templatisation gets checked in, generic reporting will soon
become a reality.

I am currently writing generic charting as we speak.

Gerv

Comment 19

[Dave Miller [:justdave]]

Sounds like this will probably make 2.16, resetting milestone appropriately.

Comment 20

[Myk Melez [:myk] [@mykmelez]]

Gerv, could you estimate the amount of time it would take you or another
Bugzilla hacker to implement this feature?

Comment 21

[Gervase Markham [:gerv]]

Myk: this one's not that tricky. It basically involves:

- splitting query.cgi so you can steal the middle bit of the interface
- writing the code to select the two (or three) axes for the table of data
- getting all the SQL and hard stuff from the patches someone submitted over in
some bug whose number I've forgotten

I got some way on that, and hope to get further on it this weekend.

Gerv

Comment 22

[Matthew Tuck [:CodeMachine]]

Gerv, if you're doing the grunt work on this I'd be prepared to do some of the
other stuff I wanted (idented report type, detail rows, etc) after you've
finished for its first milestone release.

I'm wondering whether each report type should be a different template, and let
the report page automatically determine what report types are available.

Another idea is that attachments are fast becoming first class citizens - we may
want to add attachment reports at some stage, but that's probably off into the
future.

Comment 23

[Andreas Franke (gone)]

Gerv: Maybe bug 92676 is the one you keep forgetting. :-)

Comment 24

[Dave Miller [:justdave]]

This doesn't look like it'll make 2.16
 -> 2.18

Comment 25

[Matthew Tuck [:CodeMachine]]

We should probably do stored reports too, for consistency's sake.

Comment 26

[Gervase Markham [:gerv]]

Attachment: Patch v.1

Here it is :-) Version 1 of the new reporting mechanism.

Gerv

Comment 27

[Gervase Markham [:gerv]]

*** Bug 92676 has been marked as a duplicate of this bug. ***

Comment 28

[Gervase Markham [:gerv]]

Attachment: Patch v.2

This tweaks a few things. 

Known bugs: the names of the different types of report and the way they are
accessed will change after bug 146945 cleans up the format system. Also, some
of the names (e.g. format=report for query.cgi, and report.cgi for the report
CGI) may be too generic. Neither of these things should prevent review.

Gerv

Comment 29

[Joel Peshkin]

I tried applying this to the tip (first v1 and then adding v2) and was unable to
apply v2.

This mechanism looks pretty cool, but I am not quite sure how to invoke it.  I
didn't see anything that uses search-report.

If I understand this correctly, we should also tie this mechanism in to
collectstats.

Comment 30

[Gervase Markham [:gerv]]

> I tried applying this to the tip (first v1 and then adding v2) and was unable 
> to apply v2.

That's because the patches aren't cumulative :-) All patches I attach (to any
bug) are standalone. Also, note the "obsolete" flag on v.1. Just apply v.2, and
you'll be fine.

> This mechanism looks pretty cool, but I am not quite sure how to invoke it.  I
> didn't see anything that uses search-report.

It uses the format mechanism. http://bugzilla.mycompany.com/query.cgi?format=report

> If I understand this correctly, we should also tie this mechanism in to
> collectstats.

Er... you don't :-) This displays data about the current state of Bugzilla. It
has nothing to do with the historical data that collectstats collects.

Gerv

Comment 31

[Joel Peshkin]

Comment on attachment 99444 [details] [diff] [review]
Patch v.2

Something is missing here.

When I apply this patch, it does not include the search-report template.

file error - search/search-report.html.tmpl: not found

Comment 32

[Gervase Markham [:gerv]]

Attachment: Patch v.3

Joel: my deepest apologies. A failure to cvs add the files meant they weren't
included in the diff.

Try this one.

Gerv

Comment 33

[Joel Peshkin]

I'm not sure where ValidateOutputFormat is supposed to come from....

Undefined subroutine &main::ValidateOutputFormat called at
/home/bugzilla/tip/report.cgi line 118.

Versions of modules follow.....

Checking perl modules ...
Checking for       AppConfig (v1.52)   ok: found v1.52
Checking for       CGI::Carp (any)     ok: found v1.20
Checking for    Data::Dumper (any)     ok: found v2.102
Checking for     Date::Parse (any)     ok: found v2.22
Checking for             DBI (v1.13)   ok: found v1.30
Checking for      DBD::mysql (v1.2209) ok: found v2.1017
Checking for      File::Spec (v0.82)   ok: found v0.82
Checking for        Template (v2.07)   ok: found v2.07
Checking for      Text::Wrap (v2001.0131) ok: found v2001.0131

The following Perl modules are optional:
Checking for              GD (v1.19)   ok: found v1.19
Checking for     Chart::Base (v0.99)   ok: found v0.99
Checking for     XML::Parser (any)     ok: found v2.31   

Comment 34

[Joel Peshkin]

I see what is happening....

This conflicts with the checkin for bug 146945 which removed ValidateOutputFormat

Comment 35

[Gervase Markham [:gerv]]

Yes; if you are testing this, replace:
+my $format = ValidateOutputFormat($::FORM{'format'}, "report", "reports");
with
+my $format = GetFormat("reports/report", $::FORM{'format'}, $::FORM{'ctype'});

However, CSV won't work until I've renamed some of the files, I don't think. I
will update the patch this evening.

I work too fast for myself sometimes ;-)

Gerv

Comment 36

[Gervase Markham [:gerv]]

Attachment: Patch v.4

This should work with the current tip.

Gerv

Comment 37

[Gervase Markham [:gerv]]

I should add that the new access URL is:
http://bugzilla.mycompany.com/query.cgi?format=report-table
This is to avoid hogging the namespace; there may well be other types of report
later.

Gerv

Comment 38

[Gervase Markham [:gerv]]

Attachment: Patch v.5

Patch v.5 - unrotted, with a few UI tweaks.

Gerv

Comment 39

[Joel Peshkin]

Comment on attachment 100378 [details] [diff] [review]
Patch v.5

r=joel 

There are a few items that I would like to see enhanced, but they can be
handled as seperate bugs.

Comment 40

[Gervase Markham [:gerv]]

Fixed. Fantastic! :-) (This hopefully shouldn't conflict with Request Tracker -
it only adds one error, and most other things are in new files.)

Thanks again to [e-mail] for inspiration and code.

Checking in report.cgi;
/cvsroot/mozilla/webtools/bugzilla/report.cgi,v  <--  report.cgi
initial revision: 1.1
done
Checking in globals.pl;
/cvsroot/mozilla/webtools/bugzilla/globals.pl,v  <--  globals.pl
new revision: 1.204; previous revision: 1.203
done
Checking in checksetup.pl;
/cvsroot/mozilla/webtools/bugzilla/checksetup.pl,v  <--  checksetup.pl
new revision: 1.192; previous revision: 1.191
done
Checking in query.cgi;
/cvsroot/mozilla/webtools/bugzilla/query.cgi,v  <--  query.cgi
new revision: 1.107; previous revision: 1.106
done
Checking in t/004template.t;
/cvsroot/mozilla/webtools/bugzilla/t/004template.t,v  <--  004template.t
new revision: 1.19; previous revision: 1.18
done
Checking in Bugzilla/Search.pm;
/cvsroot/mozilla/webtools/bugzilla/Bugzilla/Search.pm,v  <--  Search.pm
new revision: 1.18; previous revision: 1.17
done
Checking in template/en/default/global/code-error.html.tmpl;
/cvsroot/mozilla/webtools/bugzilla/template/en/default/global/code-error.html.tmpl,v
 <--  code-error.html.tmpl
new revision: 1.11; previous revision: 1.10
done
RCS file:
/cvsroot/mozilla/webtools/bugzilla/template/en/default/search/search-report-table.html.tmpl,v
done
Checking in template/en/default/search/search-report-table.html.tmpl;
/cvsroot/mozilla/webtools/bugzilla/template/en/default/search/search-report-table.html.tmpl,v
 <--  search-report-table.html.tmpl
initial revision: 1.1
done
RCS file:
/cvsroot/mozilla/webtools/bugzilla/template/en/default/reports/table.html.tmpl,v
done
Checking in template/en/default/reports/table.html.tmpl;
/cvsroot/mozilla/webtools/bugzilla/template/en/default/reports/table.html.tmpl,v
 <--  table.html.tmpl
initial revision: 1.1
done
RCS file:
/cvsroot/mozilla/webtools/bugzilla/template/en/default/reports/table.csv.tmpl,v
done
Checking in template/en/default/reports/table.csv.tmpl;
/cvsroot/mozilla/webtools/bugzilla/template/en/default/reports/table.csv.tmpl,v
 <--  table.csv.tmpl
initial revision: 1.1
done

Gerv

Comment 41

[Joel Peshkin]

*** Bug 77764 has been marked as a duplicate of this bug. ***

Comment 42

[David G King]

If this is a dup of 77764, then technically, this should block the meta bug #75488

Comment 43

[Gervase Markham [:gerv]]

Wrong way round, dgk. Also, this bug is FIXED already.

Gerv