How to get Custom BIRT reports in SilkCentral Test Manager to pull the currently selected project?

Question

I posted this question on StackOverflow and did not get any answers, so re-posting here.

We are using Eclipse (Galileo) to create some custom BIRT reports (version 2.5) for SilkCentral Test Manager (version 11). When creating the report in SCTM, we have to specify the SQL query to be used for the report. We also have to specify the SQL query when designing the BIRT report in Eclipse.

I know that in the SCTM SQL statement we could specify parameters to be pulled based on the current settings, such as the currently selected project:

WHERE TPN.PROJECTID_FK = ${$PROJECTID}

I thought this would carry over to the BIRT report that is associated with the SCTM report, but this is not the case. I created a new report in SCTM and specified the following query (which contains no information that will be displayed in the BIRT report):

SELECT      proj."ProjectName", proj."ProjectID_pk"
FROM        "Silk"."SCC_Projects" AS proj

I then went to the report tab and associated a custom report and the report displayed correctly, proving that it is using the Data Source/Data Sets from BIRT and not the SQL provided in SCTM.

The problem I'm facing is that I want the custom report to display the currently selected project, but I'm not able to specify it properly in Eclipse. I need this because I want to only have one report template to be used for multiple projects, instead of having to upload a custom report for every single project.

• A Hardcoded value for the project definitely does not work:

  WHERE TPN.PROJECTID_FK = 34

• I tried setting it as a parameter (Name=projectID, DataType=Integer, DisplayType=Text Box, DefaultValue = 34).

  WHERE TPN.PROJECTID_FK = ?

  When this runs, the report pulls the Default Value, and not the currently selected project.

• I tried setting it as a Dynamic Parameter (Name=projectID, DataType=Integer, DisplayType=ListBox, DataSet=ProjectID, SelectValueColumn=ProjectID_pk, SelectDisplayText=ProjectName, DefaultValue=36)

  This also pulls the Default Value when the report runs.

Default value is a required field, so I don't know what to do to get this to work properly.

Any suggestions/solutions?

Answer 1

Here is the solution that I came up with using and example from BirtReports.doc from the Borland site:

In SCTM, I edited the SQL query from

WHERE TPN.PROJECTID_FK = ${$PROJECTID}

to

WHERE TPN.PROJECTID_FK = ${currentProject|34|ProjectId}

This creates a parameter named currentProject which passes the value 34 to the BIRT report under the parameter name rp_currentProject.

I then edited the BIRT report's projectID parameter to have the following values: Name=rp_currentProject, DataType=Integer, DisplayType=TextBox, DefaultValue = Empty/Null.

In the DataSet for the report, I edited the ? parameter to be: Name=param_1, DataType=Integer, Direction=Input, DefaultValue=GreyedOut/Disabled, LinkedToReportParameter=rp_currentProject.

So, I upload the same report template to multiple projects and under the Report > Parameters Tab in SCTM I change the value of the currentProject parameter to the correct project ID. This ID is passed to the Report and the correct information is displayed.

BTW, I also tried this format but I was getting conflicting type errors:

WHERE TPN.PROJECTID_FK = ${currentProject|${$PROJECTID}|ProjectId}

I also tried using DECLARE/SET in the SQL to set the passed value to a variable, but I was getting an error stating that the SQL statement is invalid because it is no longer read-only.

Hope this helps anyone that has this same problem!