Jazz Forum Welcome to the Jazz Community Forum Connect and collaborate with IBM Engineering experts and users

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

Hello,
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))

               release  

               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
Hubert

0 votes

Comments
When you say normalize, what does that mean? Could the multiple Y-axis, feature introduced in 606 help in this case? 

Here is link to multiple Y-axis in case it can help in this case https://jazz.net/pub/new-noteworthy/jrs/6.0.6/6.0.6/index.html#3. 

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!

Here what he wants to achieve:

Mock up



Be the first one to answer this question!

Register or log in to post your answer.

Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.

Search context
Follow this question

By Email: 

Once you sign in you will be able to subscribe for any updates here.

By RSS:

Answers
Answers and Comments
Question details
× 7,495
× 6,121
× 481

Question asked: Nov 28 '18, 3:55 p.m.

Question was seen: 2,540 times

Last updated: Nov 30 '18, 3:26 a.m.

Confirmation Cancel Confirm