How to generate Excel reports from dashboard in hpqc10.

[Former Member]

Hi Everyone,

I have to generate an excel report from dashboard in hpqc 10. In that report I have to have total no. of scenarios from each work

stream, How much percenatge of testing completed.It is a great help.

Thanks,

Naveen.

[Former Member · Accepted solution]

Well you have two choices.

Firstly you need to create a SQL query to fetch the execution details.

once you are done with that, you have two ways to do it. either just take the SQL query dump in an excel sheet loaded with pivot tables and formulas to create the desired charts/tables

or you can use the Post-Processing tab to create a vb function to do the job of creating pivot tables and chart for you, so that when you download the excel everytime, you get the charts/tables right away. For using the Post-Processing, you need to be good at vb scripting.

Hope this wud help.

Regards

Avijit

[Former Member · Accepted solution]

Hi Avijit,

I got to know what to do... Could you pls give me detailed steps how to do.. I dont know VB script.

[Former Member · Accepted solution]

Hi Naveen,

I would advise you to resort to fetching the dumps from QC using SQL queries, and create the pivot charts and tables in the Excel. I tried VB scripting once, and it appeared more cumbersome to create and maintain it. Then I used QC to derive the most appropriate extract and played with Excel 2007 Pivot tables section and the result was amazing.

<<Text removed>>

Regards

Avijit

Edited by: Matt on Jun 22, 2011 2:41 PM

[Former Member · Accepted solution]

This message was moderated.

[Former Member · Accepted solution]

Hi naveen,

under dashboard module, under Excel reports tree structure on the left hand side, create a blank excel report and then on the right hand side window, click on "Configuration" tab. Under that you will see another tab named "Query". And under the Query tab there will be another tab named "Query Builder".

Click on "Query Builder" tab and you will get a new window, for creating an SQL query. In this window, on the right hand side you will be able to see the whole schema of all QC modules.

Create the query first and then click on "Test Query" button in this window to see that you are getting the desired results. And once you are fine with the results, paste the query udner Configuration->Query-> tab and click on "Generate" to get the dump being saved into an external excel sheet.

Hope you will be able to achieve the desired results with the above explanation and i will send you the template.

Regards

Avijit

[Former Member · Accepted solution]

Hi Avijit,

What you told is I am able to do, but my question is I don't know how to write a sql command ,for the fields I need in my report. Let me explain you my actual requirement. We are recording Diff Sap Modules data in the QC . I have OTC, P2P & Vistex data. I have to generate the report showing how many total scenarios for each module, among that how many passed and how many failed, & how much percentage completed. And total of all these scenarios. Do they need to tell me their data base structure.because I am newly joined.

Module, Total scenarios, passed, failed, to be executed, % completed.

BI

FICO

Vistex

Total :

Thanks for your time.

I hope now I explained my problem clearly.

Thanks,

Naveen.

[Former Member · Accepted solution]

Here you go man.

This is the complete SQL query, which will give you the extract in this format

Test Set Folder Test Set Name Test Script Name in the Test Set Execution Status of the Test Script

SELECT

CF_ITEM_NAME AS TEST_SET_FOLDER, CY_CYCLE AS TEST_SET, TS_NAME AS TEST_SCRIPT, TC_STATUS AS TEST_SET_EXECUTION_STATUS

FROM

TESTCYCL,TEST,CYCLE,CYCL_FOLD

WHERE

TS_TEST_ID = TC_TEST_ID

AND

TC_CYCLE_ID = CY_CYCLE_ID

AND

CY_FOLDER_ID = CF_ITEM_ID

After you take out the extract, just manipulate it in Excel, put pivots, make charts and you will be good to create the report.

regards

Avijit

[Former Member · Accepted solution]

Hey Avijit,

I am very happy you are answered 100%. If I can assign more than 10 points I would assign you 1000 points. That way you replied me and I am very happy............ Thanks Again.It is working perfect.

Thanks,

Naveen.

[Former Member · Accepted solution]

Great that its working fine for you. let me know if you face any issues further.

[Former Member · Accepted solution]

Hi Avijit,

I need one more help from your end.

In my QC. we had Master data, business process and interfaces.

Under Master data again I created folders for each module like OTC, P2P, VISTEX. Is this possible to get the data, if I had like this folders. Can I get the data in excel reports.

Thanks,

Naveen.

[Former Member · Accepted solution]

Hi Naveen,

Does the folder structure you mentioned exists in your Test Lab? or is it in Test Plan?

Regards

Avijit

[Former Member · Accepted solution]

Hi Avijit,

Thanks for your quick reply..

I have that folder structure in requirements, test plan & test lab.

if I generate the report I am seeing the folders names for reqs, test plan & test lab.In the query builder I executed the SQL code then it is showing 3 times the folder names.

1 for reqs,

1 for test plan,

1 for test lab.

Could you pls help me how to getout of this.

Thanks,

Naveen.

[Former Member · Accepted solution]

Thats strange, because the SQL query I gave you should generate results only out of Test Lab.

[Former Member · Accepted solution]

BTW in such a situation, your best bet is to generate a "Graph" and not a "Report" with the "Graph Type" field as progress, if you are generating the Test Lab report and you want it to be arranged in a fashion that it shows you test sets when you have multiple parent and child folders under which you have lined up your test sets

Once you generate a graph, you can go to the Data Grid section of the graph, where you can get your excel downloadable data.

try to play around the above mentioned way and let me know if you get any success, coz even I go the same way and rarely resort to the SQL queries. The reason is that through graphs you can generate your desired excel downloadable data and later in EXCEL, you can combine, manipulate, picot and produce graphs of your own interest.

Regards

Avijit

[Former Member · Accepted solution]

Avijit,

I was generating the graph since 2 weeks, I am trying to get the excel report. But in the graph, in the data grid, there is no option to generate that data into excel sheet. Could you pls help me on this one.

Thanks,

Naveen.

[Former Member · Accepted solution]

HI Naveen,

In the data grid under, you get the Floppy icon (Save As) , clicking which gives you the option to save the data in an xls format. try it out I am sure you will get it.

regards

Avijit

[Former Member · Accepted solution]

Hey Avijit,

I come up with an issue.

I was exporting the data from Microsoft excel to QC. But the export wizard is getting struck in the middle and is not responding, I Cannot able to upload the data into QC.Could you please tell me what's wrong. I tried re - installing the Excel Add In also.I have tonnes of scenarios to upload into QC.

Thanks,

Naveen