3

I am sorry for what may be a long post in advance.

Background:
I am using Rational Team Concert (RTC) which stores work item data in conjunction with Jazz Reporting Service to create reports. Using the Report Builder tool, it allows you to write your own queries to pull data as a table, and has its own interface to represent the table as a graph.

There is not much options for of graphing; the chart type defaults as a count, unless you specify it to show a sum. In order to graph by sum, the data must be a number rather than a string. By default, the Report Builder assumes all variables in the SELECT statement are strings.

The data which I will be using are a bunch of work items. Each work item is associated to a team (A, B) and has a work estimation number (count1, count2). 

Item # | Team | Work   |
------------------------
123    |  A   | count1 |
------------------------
124    |  A   | count2 |
------------------------
125    |  B   | count2 |
------------------------
....

Problem:
Since the work estimation is entered as a Tag, the first step was to use a CATCH WHEN block when using SELECT to transform count1 -> 1, and count2 -> 2 (the string tag to an actual number which can be summed). This resulted in a table with numbers 1 and 2 in place of the typed tag (good so far).

Item # | Team | Work   |
------------------------
123    |  A   |   1    |
------------------------
124    |  A   |   2    |
------------------------
125    |  B   |   2    |
------------------------
....

The problem is that I am trying to graph by sum, which means getting the tool to identify the variables in the SELECT statement as numbers, except for some reason any variable I declare in a SELECT statement is always viewed as a string (The tool has a table of the current columns i.e. variables in the SELECT, along with that the tool identifies as its variable type).

Attempted Solutions:
The first query I did was to return a table of each work item with its team name and work estimate 

SELECT T1.NAME,
       (CASE WHEN T1.TAGs='count1' THEN 1 ELSE 2 END) AS WORK
FROM RIDW.VW_REQUEST T1
WHERE T1.PROJECT_ID = 73

Which resulted in 

 Team | Work   |
----------------
 A    |   1    |
----------------
 A    |   2    |
----------------
 B    |   2    |
----------------
....

but the tool still sees the numbers as strings. I then tried explicitly casting the CASE to an integer, but resulted in the same issue

...
CAST(CASE WHEN T1.TAGs='count1' THEN 1 ELSE 2 END AS Integer) AS WORK
...

Which again the tool still represents as a string.

Current Goal:
As I cannot confirm if the tool has an underlying problem, compatibility issues with queries, etc. What I believe will work now would be to return a table with 2 rows: The sum of the work for each team

       |Sum of 1's and 2's  |
-----------------------------
Team A |  SUM(1) + SUM(2)   |
-----------------------------
Team B |  SUM(1) + SUM(2)   |
-----------------------------

What I am having trouble with is using sub queries to use SUM to sum the data. When I try SUM(CASE WHEN ... END) AS TIME2 I get an error that "Column modifiers AVG and SUM apply only to number attributes". This has me thinking that I need to have a sub query which returns the column after the CASE, and then SUM that, but I am sailing into uncharted waters and can't seem to get the syntax to work.

I understand that a post like this would be better off on the product help forum. I have tried asking around but cannot get any help. The solution I am proposing of returning the 2 row/column table should bypass any issues the software may have, but I need help sub-querying the SUM when using a case.

I appreciate your time and help!

EDIT 1:
Below is the full query code which preforms the CASE correctly, but still causes with the interpreted type by the tool:

SELECT  
   T1.Name,
   CAST(CASE WHEN T1.TAGS='|release_points_1|' THEN 1 ELSE (CASE WHEN T1.TAGS='|release_points_2|' THEN 2 ELSE 0 END) END AS Integer) AS TAG,
FROM RIDW.VW_REQUEST T1
WHERE T1.PROJECT_ID = 73 
AND
(T1.ISSOFTDELETED = 0) AND
(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL
JakeTuero
  • 75
  • 1
  • 1
  • 6
  • You are looking for exctracting the number for the string eg 1 from string1? and the string alway have the same pattern eg: stringNNN? – ScaisEdge Jul 26 '16 at 19:46
  • I suspect the error is in a different part of the query. The way you are casting the CASE expression should work. Can you post a script that completely reproduces the issue? – Tab Alleman Jul 26 '16 at 19:48
  • @TabAlleman I have edited the post to show the full query. I still suspect this has to do with the software being used, and I think that my proposed solution under "Current Goal" would fix things, I just am not sure how to execute the SUM after the CASE – JakeTuero Jul 26 '16 at 20:15
  • Do you get an error when you execute this query by itself in SSMS? Or does the error only occur in the front end software? – Tab Alleman Jul 26 '16 at 20:17
  • In your "current goal" table you show SUM(1) and SUM(2); does this stand for an arbitrary number of SUM(N) results, or are there a small number? If the latter, you may be able to work around it by getting TEAM A/SUM(1) in a query; TEAM A/SUM(2) in a worktable, and doing an inner join to pick up the worktable data. Then they'll come up as separate columns; adding those together would give you the sum. – Mike Christie Jul 26 '16 at 20:22
  • @TabAlleman running the query by itself causes no issues. The problem arises in the front end software when trying to use the returned table for graphing as it does not detect the column to be integer data, but string data. This is why I think it would be easier to let the query do the summation and present the final number which doesn't matter if the front end detects it as a int/string, rather than let the front end software try and interpret the results and then try to sum. – JakeTuero Jul 26 '16 at 20:24
  • @MikeChristie Sorry for the confusing wording. Basically what I meant was that lets say I have 100 records, each record (after a successful CASE WHEN) has a 1 or 2 in the column. Of the 100 records who fall under team A, we would add all the 1's and 2's to get a final number. I made the data more simple for explanation, but the possible values range from 1 to 5 inclusive as integer values. Joining the tables may work, but I still need to sum after I used the CASE, which gave me an error. – JakeTuero Jul 26 '16 at 20:28
  • OK, I think I follow now. It looks like you should be able to do an outer sum on your "Attempted solution": does this work? "select a.name, sum(work) from (SELECT T1.NAME, (CASE WHEN T1.TAGs='count1' THEN 1 ELSE 2 END) AS WORK FROM RIDW.VW_REQUEST T1 WHERE T1.PROJECT_ID = 73) a group by a.name" That just wraps your query in another sum. – Mike Christie Jul 26 '16 at 20:37
  • For the issue when trying to do SUM(CASE WHEN ... END) and it telling me that it can only sum number attributes, I think that I need to first query a table using the case, then sum the returned table. I am just having trouble running the my query right now as a sub query, then summing that. – JakeTuero Jul 26 '16 at 20:38
  • @MikeChristie will try that and get back to you! – JakeTuero Jul 26 '16 at 20:46

1 Answers1

1

This small adjustment to your current query should work:

SELECT  
   T1.Name,
   SUM(CAST(CASE WHEN T1.TAGS='|release_points_1|' THEN 1 ELSE (CASE WHEN T1.TAGS='|release_points_2|' THEN 2 ELSE 0 END) END AS Integer)) AS TAG,
FROM RIDW.VW_REQUEST T1
WHERE T1.PROJECT_ID = 73 
AND
(T1.ISSOFTDELETED = 0) AND
(T1.REQUEST_ID <> -1 AND T1.REQUEST_ID IS NOT NULL
GROUP BY T1.Name
Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • Thank you!!! This works perfectly. I get a table which has Team A and Team B, along with the total sums for each (I have cross validated to ensure these numbers are correct). Unfortunately, the frontend software is still detecting the results as strings, and when graphing since it is graphing by count, it still will only show 1 under team A and 1 under team B (since it is counting each number as 1). Nonetheless, I now know that its the front end which is causing the issue. Thanks again! – JakeTuero Jul 26 '16 at 23:08