Hi All,
I'm working on a report to show the number of early failures based on the total number of product repaired grouped by technician for the prior month.
The report is based on a table that stores all the repair work orders.
I'm 90% there with the report, all the numbers are correct with the exception the total repairs... the problem arises because the report detail is filtered on DOA="Y". This gives me the correct detail records but allow me to help to calculate the total repairs.
The goal is to see the ratio of quantity doa="y" / total repairs.
For Example, the attachment shows the month of September and Technician DL. Looking at Part Number 2320993-R, the total quantity should by 89 (based on total repairs for September). The total DOA is 2 which is correct. We can see the 2 work orders matching that criteria as well.
The red circled areas on the report should show the total of repairs for the month.
report.JPG
report_designer.JPG
Here is the filter:
year(date_in)=year(date()).AND.month(date_in)=month(date())-1.AND.doa="Y"
So my thoughts are:
1)ideally come up with some calculation that does not require an index addition (this table already have too many indexes)
2)create a filtered index, sorted by product so I can place a calculated field on the report and use db_count() to count those records
2)create a set... would probably need a Summarize operation but not sure how to structure it all
I'm working on a report to show the number of early failures based on the total number of product repaired grouped by technician for the prior month.
The report is based on a table that stores all the repair work orders.
I'm 90% there with the report, all the numbers are correct with the exception the total repairs... the problem arises because the report detail is filtered on DOA="Y". This gives me the correct detail records but allow me to help to calculate the total repairs.
The goal is to see the ratio of quantity doa="y" / total repairs.
For Example, the attachment shows the month of September and Technician DL. Looking at Part Number 2320993-R, the total quantity should by 89 (based on total repairs for September). The total DOA is 2 which is correct. We can see the 2 work orders matching that criteria as well.
The red circled areas on the report should show the total of repairs for the month.
report.JPG
report_designer.JPG
Here is the filter:
year(date_in)=year(date()).AND.month(date_in)=month(date())-1.AND.doa="Y"
So my thoughts are:
1)ideally come up with some calculation that does not require an index addition (this table already have too many indexes)
2)create a filtered index, sorted by product so I can place a calculated field on the report and use db_count() to count those records
2)create a set... would probably need a Summarize operation but not sure how to structure it all
Comment