Hi,

I currently am having trouble about how to make a define/field to use in my WebFocus report.

Right now, I have different reports I made in DevStudio for all the different departments in the organization. Each report is currently a table for each department breaking down the number/count of a certain incident occurs.

I would like to make a "rate" of incidences that occurs as well, not just a count. Currently, my data comes from two sources: Table A contains a record of each incident (which I would use as my numerator and I currently use for the count/totals) and then table B contains my denominator. The two tables I have joined by location/department.

I am not really sure how to go about this. The field in Table A I use is one called “incident type” and it a text field with a few certain possibilities (eg. Fall, Human Error), etc. To make a “rate” field, I am thinking I need to somehow define a field to count the number of occurrences, then use that defined field in another define to calculate the rate (defined field/number of people in table B)?

If so, there are a few things I am having trouble with:

1. When making the numerator, how do I count the number of incidence types (eg. Falls) from table A if it is not a number but instead a text format

2. When creating the temporary field, do I need to apply all the filters when creating it (eg. Time frame, the location), or once I add that craeted field to my table in my report, the filters from that report will automatically be applied (ie.the report is already filtered in my Where Clause for Year 2013 and Department A, when I drag this Rate Field into the table, will the filters apply?

TABLE FILE A
SUM
CNT.Table A.Incident_Type AS 'COUNT OF INCIDENCES'
BY LOWEST J0.DIM_DATE.FISCAL_YEAR_NAME AS ''
BY LOWEST Table A.Incident_Severity_Level
ACROSS LOWEST J0.DIM_DATE.MONTH NOPRINT AS ''
ACROSS LOWEST J0.DIM_DATE.MONTH_NAME AS ''
ON J0.DIM_DATE.FISCAL_YEAR_NAME SUBTOTAL AS '*TOTAL'
WHERE ( Table A. Location CONTAINS 'Department A' ) AND ( Table A.Incident_Type CONTAINS 'FALL' ) AND ( J0.DIM_DATE.MONTH GE '2013-04-01' ) AND ( J0.DIM_DATE.MONTH LT '2014-03-31' );

Thanks for any input!

This message has been edited. Last edited by: <Kathryn Henning>,

Hi hfung1,

Welcome to Focal Point!

For issue #1, you can use the MacGyver technique to count the number of occurrences of certain values in your data. An example can be found at:

Filling in the Gaps

For issue #2, yes, when you create a temporary field using a DEFINE, the report filters apply to that field as well.

Cheers!

Kathryn