I think I may have backed myself into a corner but I thought I would check first.
I need to show the completeness level for three different stages for specific categories.
The users flag each individual record as a yes/no for each of the three stages and I need to create the high level view as a percentage of complete vs total.
The flags were stored in a seperate table using partId, metricType of 1,2,3 and metricState as T/F.
I calculated the proper values that I would like to display in a query grouped by the category and then the metric type so I have three lines per category in my query
cat, type , value
cat 1, metric 1, .34
cat 1, metric 2, .46
cat 1, metric 3, .18
I want to display them on a report so they format like this:
category, production, quality, manager (as the headers for metrics 1,2,3)
1 , .34 , .46 , .18 (as the values lined up under the headers)
and so on for each of the categories in the report.
I would even be ok with a report that listed the three types vertically on the page but with a descriptive label. It would look something like:
Production completeness: .34
Quality control: .46
Manager reviewed: .18
So my question:
How do I get the proper data source into the report field? I tried using the above query as the data Source for the report but I can't figure out how to get the proper value for the desired type.
What I want logically is: " metricValue where metricType is 1 " then repeated for type 2 and 3.
The only way I have figured out so far is to maintain three copies of the metrics query with the only difference being the value in the type field. I then create a combined query joining those three queries on the category. I really do not like this approach.
I tried using a prompt on the base query for the metric type but I could not figure out how to send that value to the query from the query that combines.