It's all about the answers!

Ask a question
QuestionsTagsUsersBadges
Follow this question
Question details

×42,787
×11,924
×1

question asked: Mar 25 '14, 12:02 p.m.
question was seen: 4,873 times
last updated: Mar 26 '14, 3:52 a.m.

Related questions

FAQ - How this Forum works

Display the SQL for RTC Query

0
Jamie Schena (53●15●21) | asked Mar 25 '14, 12:02 p.m.
edited Mar 26 '14, 3:52 a.m. by Krzysztof Kaźmierczyk (7.4k●3●75●103)
 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

3 answers

Most liked answers|Newest answers|Oldest answers ↑

1
permanent link
sam detweiler (12.5k●6●195●201) | answered 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)

Comments
Jamie Schena commented 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 commented Mar 25 '14, 12:45 p.m. | edited 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

0
permanent link
Krzysztof Kaźmierczyk (7.4k●3●75●103) | answered 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?
Comments
Jamie Schena commented 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 commented 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 commented 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?

0
permanent link
Krzysztof Kaźmierczyk (7.4k●3●75●103) | answered 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.

Your answer

Register or 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.