Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

Database Metrics

I'm wondering if it's possible to build views (or forms) that can count forms and field values and display them in such a way that they can be used for reporting.  

For a simple example:

My database is a repository for questions to be asked to software developers.  
The fields in the form include Topic, Status (open/assigned/closed),

I would like a view or form that can display counts of saved forms and might look like this:

Question Topics                   Open          Assigned         Closed        Total

Billing                                   3                   2                   11             16  
Print                                     2                   2                    8              12        
Web Features                       5                   2                   18              25
Testing                                 3                   3                    5              11
Hardware                             2                   2                    1               5

Can this be done?  If so, where can I find documentation in online help, etc. on how to do this?

  • 2
1 Solution
Yes, easily.

Make the first view column use field TOPIC and in that columns sort options, set it to CATGEORIZE.
Make the second columns based on formula @If(TOPIC="Open"; 1; 0) and on the sort tab, set it to not sort, but on the bottom of the tab turn on column totals

Repeat for three additional columns, but changing TOPIC="Open" to Topic="Assigned" etc.

Save the view, take a look at it.  When collapsed, it should give you what you want.

All this works so long as you ask only one question i each document.
Aquilon7Author Commented:
I think this is the right track.  A couple questions...
Is it possible to set the view so that it cannot be expanded?
How do I get the totals column as shown in my example all the way to the right to work?
For the totals column, the formula would be just the number 1, since I assume there is always one option selected (Open, Assigned, Closed) --  so that there is alway an answer present, and never multiple answers present.

You can't get the view to not be possible to expand.  There are some tricks around this.  You can mark the totals to not display individual values, in which case expanding causes only empty lines to show.  Make the fnt size real small on all the columns except the first one, so it takes up less room.  You can also set the view to open collapsed... does not stop someone from expanding it, but at least it starts collapsed.  Make sure the SHOW TWISTIE option is unticked on teh category column.

You can also not display the view itself, and nistead generate a report screen in LotusScript based on the view, but that'snot so easy to do.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now