It's all about the answers!

Ask a question

How can I make two queries and divide the results within Report Builder?

Hubert Zenner (111) | asked Nov 28 '18, 3:55 p.m.
edited Nov 29 '18, 3:10 a.m. by Ralph Schoon (62.7k33643)

I have a task to solve and I do not find the right way to do this in Report Builder. As this is my first post I hope that I do everything right. Please tell me if something is wrong so that I can improve.
clm is version 6.0.5

The result of my report should be a bar graph with normalized number of weighted defects over two different releases split into cause classes. I already managed to build a graph in Report Builder that displays the number of weighted defects grouped by release and split into different cause class. However what's missing is the normalization of the bars. I have a screenshot available of the current state but not able to upload because of missing reputation points. 

Here an example: 

in a standard MS SQL Server 2014 express installation the following SQL code calculates the normalized defect density for a single value over a dummy database 'dbo.workitem' correctly:

Declare @numerator int;

Declare @denominator int;

Declare @trend int;


Set @numerator = ( Select                                  

                              (SUM (CASE WHEN severity = 'Low' THEN 1 ELSE 0 END) +

                              2 * SUM (CASE WHEN severity = 'High' THEN 1 ELSE 0 END))


               From dbo.workitem

WHERE Type = 'defect' AND release = 'Release 1.1');


Set @denominator = ( Select Count(type)

               From dbo.workitem

               WHERE Type = 'CR' AND release = 'Release 1.1');


if @denominator > 0

set @trend = @numerator / @denominator;

select convert (varchar(10), @trend);

The @numerator part works fine in the Report Builder as documented in the screenshot. But I did not manage to get the @denominator into the report. I tried with variable declarations in advanced mode but only got error messages on it. The same happens when I try to do the division in the select itself.
I tried the calculation possibilities in the table formatting area without success as I was not able to select a second type for the filter on 'CR'

Could anybody please help me and send tipps and tricks for Report Builder to do this different filtering (select on type 'defect' for numerator and type 'CR' for denominator and calculation of the division of both in the tool?
any kind of link to similar examples would be also fine.

If there is a better way to do this analysis please let me know. I would be very happy to learn more about the Report Builder functionalities available to resolve such analysis tasks.

many thanks in advance for all your help!
best regards

Ralph Schoon commented Nov 29 '18, 8:01 a.m.
When you say normalize, what does that mean? Could the multiple Y-axis, feature introduced in 606 help in this case? 

Fariz Saracevic commented Nov 29 '18, 8:12 a.m.

Here is link to multiple Y-axis in case it can help in this case 

Hubert Zenner commented Nov 29 '18, 8:42 a.m.

The result should be a single axis chart with the normalized weighted defect count. Normalized means that the number of defects (weighted) is divided by the overall number of changes in the same release. 

My problem is: how to get the count on type=defect and count on type=changes in the report and divide this two values for each cause defined in the table. 
This means the result will look similar the screenshot but the axis is named by a relative value (numerator/denominator) and not a number (count of defects on cause).

hope this explains more precise what I need
many thanks and best regards for your feedback!

Ralph Schoon commented Nov 30 '18, 3:26 a.m.

Here what he wants to achieve:

Mock up

Be the first one to answer this question!

Register or to post your answer.