Highlighted
Absent Member.
Absent Member.
928 views

Test details based on Attributes

Jump to solution

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

0 Likes
4 Solutions

Accepted Solutions
Highlighted
Micro Focus Expert
Micro Focus Expert

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

View solution in original post

0 Likes
Highlighted
Absent Member.
Absent Member.
Hi Hubert,

Issue got resolved.

Regards,
Shruti

View solution in original post

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Hi shruti,

 

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

 

Thanks for your feedback,

Hubert

View solution in original post

0 Likes
Highlighted
New Member.

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

View solution in original post

0 Likes
6 Replies
Highlighted
Micro Focus Expert
Micro Focus Expert

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

View solution in original post

0 Likes
Highlighted
Absent Member.
Absent Member.
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
0 Likes
Highlighted
Absent Member.
Absent Member.
Hi Hubert,

Issue got resolved.

Regards,
Shruti

View solution in original post

0 Likes
Highlighted
Micro Focus Expert
Micro Focus Expert

Hi shruti,

 

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

 

Thanks for your feedback,

Hubert

View solution in original post

0 Likes
Highlighted
Absent Member.
Absent Member.
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
0 Likes
Highlighted
New Member.

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

View solution in original post

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.