Use a summary report where each row of data shown in your example is displayed in a Sub Summary layout part.
You can set up a calculation field as the "when sorted by" field to group your records by the ranges shown in the first column. I'm guessing that the first column shows a windspeed range.
Case ( WindSpeed < 5.5 ; 1 ;
WindSpeed < 6.5 ; 2 ;
WindSpeed < 7.5 ; 3 ; //and so forth....
You can sort your records by this calculation field to group them for your report.
Summary fields can give you the totals averages and counts for each sub summary group.
That worked perfectly. Thanks.
Now I need to add a field to this report (again for each wind speed category) that counts each RPM value that is > 100 then devide that by the total number of records for that wind speed and multiply by 100 (make it a percent). I would need to do this for Voltage & Current too.
So I want the report to look like this
I'd appreciate your help.
Let me first be sure that I understand:
Say you have 30 records in the 4.5-5.5 group. 5 of those records have RMP values > 100 so the value would be computed as (5 / 30 ) * 100 or 16.67%.
Is that correct?
Also, is this report based on all records in the table or do you perform a find to pull up just certain records? (Such as all records for a given range of dates...)
Yes, your example is exactly right. (Eventually I may need to do another calculation on this, but for now that is what we are looking for).
As for your question regarding whether it is being performed on all records or just some. This hasn't been decided yet.
Each day we will be importing the day befores data into the database and then running this report (it will tell us if everything is functioning correctly or not). I'm thinking it may be easier (and faster) to import the data into a temporary table, that just has that days data into it. Then, after the daily report is done, then move that days data into the archival table.
Do you think that makes sense? It there is a preference for running this report, please let me know.
I recommend that you take a careful look at running your report from a table that contains all of your records. You may not need it for day to day reporting, but it gives you the option of using the same layout to examine historical data or data over a longer time period than is normally used in your reports.
I was thinking in terms of a self join to match to records of the same windspeed category, but on further thought, the method I am about to suggest will work correctly whether your report is based on all records or just some records.
Define a calcualtion field for RPMs such as:
RPMfield > 100
Select number as the return type. This will show 1 if the value in RPMfield exceeds 100. (RPMfield must be of type number, not text!)
Define a summary field, sOver100, as the Total of this new calculation field and it will compute the count of how many records exceed 100 RPM.
Define a second summary field, sCount, as the Count of any field in this table that always has data in every record. It will give us the total count.
To get your % value, use this calculation in a calcualtion field:
GetSummary (sOver100 ; cWindspeedCategory ) / GetSummary (sCount ; cWindspeedCategory )
cWindspeedCategory is my name for the calculation field using the case function that I suggested back at the start of this thread.
Select number as the return type.
When you place this field on your report layout, use data formatting in the inspector to format the result as a percent and to specify the desired number of decimal places.
This same approach can be extended to each of the other similar columns in your report.
Thank You!!! This works great! And I've learned so much. I couldn't of done this report without your help.