Test details based on Attributes

[ratkals]

Hi,

I am trying to get the test details based on 2 attributes name used in query.

SELECT twa.TESTID, twa.TESTNAME, twa.AV_ATTRIBUTEVALUE, twa.ATTRIBUTENAME, twa.CHANGEDBY, twa.CHANGEDAT
FROM RTM_V_TESTSWITHATTRIBUTES twa
WHERE twa.PRODUCTID_FK=1109 AND twa.PROJECTID=${$PROJECTID}
AND twa.ATTRIBUTENAME='State'
OR twa.ATTRIBUTENAME='N/A Reason Code' AND twa.AV_ATTRIBUTEVALUE!='---'

In this we have 2 attributes 'Status' and 'N/A Reason Code' both are list type.

When i used above query. If test has both value used then it displays 2 row. As it is 2 row in BIRT it is showing as 2 rows.

Can anyone help me out to know how can i get this 2 rows to to deduce in BIRT.

Ex:

5301143 T20290 - Credit Memo: Billing Error Not Applicable State schmitzd 16-MAY-18 06.26.24.206000000 PM
5301143 T20290 - Credit Memo: Billing Error Obsolete N/A Reason Code schmitzd 16-MAY-18 06.26.24.206000000 PM

I was this to be displayed in BIRT  as,

Test ID    , Test Name                                     , Status            , N/A Reason Code   , Changed By ,  Changed Dat

5301143 , T20290 - Credit Memo: Billing Error , Not Applicable , Obsolete               , schmitzd      , 16-MAY-18 06.26.24.206000000 PM

Kindly let me know what i am missing to get this duplicate rows in single row.

Regards,

Shruti

[Hubert Inreiter · Accepted solution]

Hi Shruti,

I recommend to use the LQM table/views. There the attributes are stored "flat" and can be accessed without the need to join. As these tables are generic you must first query which columns are relevant:

1. Select the ReportingColumns for the attributes in context of your project:

SELECT ProjectID_pk_fk, Name, ReportingColumn

FROM TM_Attributes

WHERE ProjectID_pk_fk = ${$PROJECTID} AND Name IN ('State', 'N/A Reason Code')

In my case the ReportingColumns are "uda_23" and "uda_28".

2. Therefore my SQL would look like this:

SELECT TestID, TestName, uda_23 "Status", uda_28 "N/A Reason Code", ChangedBy, ChangedAt

FROM LQM_V_SimpleTests

WHERE ProductID_fk = 1109 AND ProjectID = ${$PROJECTID} AND ((uda_23 IS NOT NULL AND uda_23 != '---') OR (uda_28 IS NOT NULL AND uda_28 != '---'))

Please replace for your query "uda_23" and "uda_28" with the appropriate values from result of 1.

Regards,

Hubert

[ratkals]

Hi Hubert,

Thank you it worked!!!

I have one more issue.

In the status list has a value as "Can't be Automated".

I want to get the list of status having "Can't be Automated" value.

I am using SQL Developer.

As "Can't be Automated" has " ' " single quote used. Getting error as invalid identifier. as the single quote is not considered as part of data.

Kindly let me know how can i handle this.

Regards,
Shruti

[ratkals · Accepted solution]

Hi Hubert,

Issue got resolved.

Regards,
Shruti

[Hubert Inreiter · Accepted solution]

Hi shruti,

In Oracle you can add another single-quote, then the string would be: Can''t be Automated

Thanks for your feedback,

Hubert

[ratkals]

Hi Hubert,

Thank you.

I was trying to have link to Test ID. In BIRT included,

<a
href="<VALUE-OF>params["currFrontendURL"]</VALUE-OF>
TM/Test+Plan?pId=
<VALUE-OF>params["currProjectID"]</VALUE-OF>
&view=details&nTP=
<VALUE-OF>row["TESTID_PK"]</VALUE-OF>"
target ="_blank">
<VALUE-OF>row["TESTID_PK"]</VALUE-OF>
</a>

By referring to below link created above html link,

silkcentral.am.tsacorp.com/.../Test Plan

But with above HTML tag its going to dashboard page, when i clicked on Test ID from report,

Below link is generated,

silkcentral.am.tsacorp.com/.../Dashboard

Kindly let me know what i have missed out.

Regards,
Shruti

[Roland Kern · Accepted solution]

Hi,

as far as I see you are only missing the slash before TM/Test+Plan, so a URL like .../silk/DEFTM/Test+Plan is generated, that is redirected to a valid URL (/silk/DEF/TM/Dashboard)

<a href="<VALUE-OF>params["currFrontendURL"]</VALUE-OF>/TM/Test+Plan?pId=...

Regards,
Roland Kern