Creating an Excel Report Template for Silkcentral Test Manager Report

Creating an Excel Report Template

For SilkCentral Test Manager Report

Firstly it is good to know what information will be returned from SilkCentral Test Manager to the report. So download an excel report from test manager from one of the samples

Open the report

Go to the DATA_SHEET tab

You will see that it shows information similar to the following:

We can see that there will be standard information sent to the report regardless of the information that your SQL statement returns, it will return:

The name of the project that the report was run against
The user that ran this
The report description
The report generated date

The Information to display

For this example we have the Following SQL statement:

SELECT sn.StatusName AS Status,

COUNT(td.TestID) AS "No. of Test Definitions"

FROM (SELECT * FROM TM_TestDefStatusNames tdsn

WHERE tdsn.StatusID_pk < 5) sn LEFT JOIN

(

SELECT tds.TestID,

CASE when tds.CurrentExecStatus=8 then 1

else CASE when tds.CurrentExecStatus=9 then 2

else tds.CurrentExecStatus end

end AS StatusId

FROM RTM_V_TestDefinitions tds

WHERE ProjectID = ${$PROJECTID}

) td

ON (td.StatusId = sn.StatusID_pk)

GROUP BY sn.StatusID_pk, sn.StatusName

ORDER BY sn.StatusID_pk

This can be entered by going to the properties tab

Click on Edit

Click on “Advance Query”

Paste the SQL query above in.

Click on “Check SQL”

This should return the following dialog

Click on “Finish”

The SQL statement is now sorted – next is to create the template.

Creating the Template

Open Excel

Name the first sheet “Bar” and the second sheet “DATA_SHEET”

Note this is NOT something necessary – this is just a step to make sure that THIS example works as desired and to avoid any confusion

Go to DATA_SHEET sheet – remember from above that SilkCentral Test Manager provides some extra information – the information with regards to this simple example will need to be put in specific places

Place “Statuses” in B1 and No. of Test Definitions in C1
From B8 – B11 place Passed, Failed, Not Executed and Not Scheduled
If you want some sample data you can place values in C8 – C11

Go to the Bar Sheet

Go to Insert | Chart

Select the Chart type – in this casewe select clustered column

Click Next

ClickDataRange field to set focus on it
Then Click on DATA_SHEET tab and highlight the values in c8 – c10

Click Next

Give a name for Chart Title, x axis and y axis

Click Next

Select to place “As object in:” the Bar Sheet
Click Finish

This will show as the following:

Right-click on the chart and select “Source Data”

This brings up the “Source Data” dialog

Click on the field for “Category (X) axis labels:”

Go to DATA_SHEET sheet
Highlight the statuses

This populates the field with the cells to use as the labels on the graph.

Click OK

You should now have the template that looks like this

Save this.

The next step is to upload this to SilkCentral Test Manager.

Uploading the template to SilkCentral Test Manager

Go to Administration | Reports in SilkCentral Test Manager

Click on the “Upload” button

This will bring up an upload dialog

Enter the name and description, Select the projects that you want this template to be available in.
Select if you want this to be a Test Manager or Issue Manager Report template.

Select the file that you want to upload

Click OK

Getting the Information into the new template

Go to the “Data” tab

This tab should have some information in it to show that the SQL statement has worked