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

Display the SQL for RTC Query

Questions
Tags
Users
Badges
Jamie Schena
(53●15●21) edited
Mar 26 '14, 3:52 a.m.
 Hello,

Is there any way to display the SQL for a query in the RTC Web UI or Thick Client? I have my query set up so it returns the correct results, but now I just need to translate that into an SQL query so I can pull the data from the data warehouse (via ODBC connection) rather than exporting the data from the Web UI.

Thanks!
Jamie

0 votes

3 answers
5,486 views
0 votes

3 answers

Permanent link
sam detweiler
(12.5k●6●195●201) Mar 25 '14, 12:07 p.m.
 what are u doing with the results?

you can 

1. export the workitems from a query
2. you can setup a feed to pull the workitems (once or on a schedule)

1 vote

Comments
Jamie Schena
Mar 25 '14, 12:21 p.m.

I am using the results for some metrics. Is there any documentation on setting up a feed to pull the work items?

sam detweiler
Mar 25 '14, 12:46 p.m.

In eclipse, edit the query, under the 'Work Item Query' label, dropdown and select


subscribe to query feed 


this will show in the 'My Feeds' in Eclipse, and u can take that url (edit the feed settings)  to execute it standalone

Permanent link
Krzysztof Kaźmierczyk
(7.5k●4●80●103) Mar 25 '14, 12:23 p.m.
  Hi Jamie,
I am not sure what you are doing but maybye using RRDI or Insight could resolve your issue?

0 votes

Comments
Jamie Schena
Mar 25 '14, 12:44 p.m.

I'm really looking to just export the results of a certain query to Excel with an established connection so that I can update regularly from Excel itself. 


I only have access to the Data Warehouse (DB Connection), RTC Web UI, and RTC Thick Client (RRDI and Insight are not options for me). Currently, I have a pull of work items using the DB connection, but I just can't get my SQL query to match my Web UI query. I really would love to just convert my Web UI query into SQL automatically, but I know that's wishful thinking ;)

sam detweiler
Mar 25 '14, 1:27 p.m.

no idea how to get from query definition to external SQL stmts. 


or when viewing the query output in web UI, select the printable view, then take THAT url into curl  to get closer to excel format. 

Krzysztof Kaźmierczyk
Mar 26 '14, 3:42 a.m.

Hi Jamie,
Your case is really weird. Work item queries are executed in RTC live database but you want to get the output from the query from warehouse which has different structure and is designed for different cases.

Or I missed something?

Also If my understanding is that you do not have an access only to RTC live database and RTC machine. Is that correct?

Permanent link
Krzysztof Kaźmierczyk
(7.5k●4●80●103) Mar 26 '14, 3:51 a.m.
You can also write OSLC query returning you list of work items you want to use. You will find more information here: https://jazz.net/wiki/bin/view/Main/ResourceOrientedWorkItemAPIv2#Querying_Work_Items
Then you need to write a script which translates the output of the query e.g. to csv.

Are you integrating the output from any external data? You can try creating BIRT report with all data you need. Or you can create BIRT report with all work items and using http client to execute http query fetching the results in e.g. xls format.

Let us know if this another option helps you.

0 votes

Your answer

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.

Ask a question
Guidelines
FAQ
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
× 12,014

Question asked: Mar 25 '14, 12:02 p.m.

Question was seen: 5,486 times

Last updated: Mar 26 '14, 3:52 a.m.

Confirmation Cancel Confirm