I am currently defining new dashboards with charts for additional quality reporting, etc. One of the charts I want to create is (for example) showing the trend of the amount of (field) state the (type) Defects are in for a given project. The project should be selectable in the dashboard-project field and not "hardcoded" in the chart, so that it is useable for each project. The type Defect has 7 possible states.
When I pick the "Trend" chart type, I do not have the option to use a query. When I select "State" as values To Chart and add the 7 states of the Defect type in the "values" field, I will see all the Defects, but I will also see all the other types that happen to share (some of) the states with Defect. (For example, Change Request shares all the same states in our solution)
When I pick the "Item Fields Trend" I can select a query, but I cannot pick a meaningful field, unless I add specific fields to the project type.
My solution so far:
- Create a query for each state in the Defect Type. For example "type = defect AND state = analyzing".
- Add a field (computated using the queries above, store historic values, static) per Defect-state to the project type. For example "Defects in state Analyzing Count".
- Create a chart "Defect State Trend" of type Item Fields Trend. Query "all projects", vertical bar graph, charting values -> add all those Defect-state fields from the project type.
That works.... but it seems like an awefully complex way for just one chart. I need to create 7 queries and 7 fields just for one chart. If I do this for every chart I want, I'll end up with *tons* of fields and queries.
Questions:
1) Is there a better way to achieve what I am trying to get?
2) If not, is it a problem to create so many fields (other than the work involved ofcourse 
), for example on database performance or anything else that I might be unaware of?
@ Q1: No, I'm afraid there isn't. You actually need a field to store the data (and thereby generating a historical development) you want to visualize in the chart. Otherwise you would need to compute all historical values on the fly while creating the chart; depending on the type/complexity of "metric" you are interessted in, this si REALLY slow or simply impossible.
Note:
When it comes to reports there might be a way to do some calculation on the fly (I'm currently trying some things) but in a report you have not charting options 
@ Q2: IMHO there shouldn't be a Problem. The fields you create can only be INT or FLOAT and the ammount of "extra data" is fairly limited. Additional Querys should be no problem at all. The only thing you should keep in mind is the "processing time". You need to take care that all computations are "static" and that you schedule the updates to non working hours.
HTH Matthias
Matthias Rump wrote:
@ Q1: No, I'm afraid there isn't. You actually need a field to store the data (and thereby generating a historical development) you want to visualize in the chart. Otherwise you would need to compute all historical values on the fly while creating the chart; depending on the type/complexity of "metric" you are interessted in, this si REALLY slow or simply impossible.
After I created the new fields I triggered a "refill" of the history calculation for each of the fields and indeed, that is way too slow to be used "on the fly".
Note:
When it comes to reports there might be a way to do some calculation on the fly (I'm currently trying some things) but in a report you have not charting options
Glad to know I'm not the only one who wishes we could use charts in reports 
@ Q2: IMHO there shouldn't be a Problem. The fields you create can only be INT or FLOAT and the ammount of "extra data" is fairly limited. Additional Querys should be no problem at all. The only thing you should keep in mind is the "processing time". You need to take care that all computations are "static" and that you schedule the updates to non working hours.
Yup, they are all static, and updated daily in the night.
Thanks for your answer, seems I was on the right track then. Still, it seems a very cumbersome way to achieve such a "basic" graph. By basic I mean these are the type of graphs that many projects will need/want and I'd expected it to be a lot easier to create them.
