How to get the opposite value of a selected prompt value present in the table

Cognos 10 Business Intelligence Report Studio
1

Hi All,

I have a report with HMGNS_FLOW_ID Prompt. User will select a HMGNS_FLOW_ID and there is HMGNS_LNK_ID associated with each HMGNS_FLOW_ID in the table.

Data
HMGNS_FLOW_ID HMGNS_LNK_ID
746939 44091
746938 44091

For each HMGNS_FLOW_ID, there will be opposite HMGNS_FLOW_ID with common HMGNS_LNK_ID (as mentioned above).

So when user selects HMGNS_FLOW_ID(746939) in the propmt, report should get filtered for opposite HMGNS_FLOW_ID (746938).

I found the SQL query solution for this, but not able to convert this logic in Report Studio.
Please provide me the solution to this issue.

SQL Query:
SELECT HMGNS_FLOW_ID,HMGNS_LNK_ID FROM “db2DWH”.TIS_LOC_HTVN_HMGNS_FLOW_LNK_RLTSHP WHERE HMGNS_LNK_ID=
(select HMGNS_LNK_ID from “db2DWH”.TIS_LOC_HTVN_HMGNS_FLOW_LNK_RLTSHP WHERE HMGNS_FLOW_ID=746939) AND HMGNS_FLOW_ID<>746939

Data
HMGNS_FLOW_ID HMGNS_LNK_ID
746939 44091
746938 44091

Query OutPut
HMGNS_FLOW_ID HMGNS_LNK_ID

    746938        44091
2

Not sure if you are still looking for an answer. If you are…

Create two queries Q1 and Q2, both with HMGNS_FLOW_ID and HMGNS_LNK_ID.
In a third query Q3, join Q1 and Q2 based on HMGNS_LNK_ID and add a filter Q1.HMGNS_FLOW_ID <> Q2.HMGNS_FLOW_ID.
Also add a summary filter rank(Q1.HMGNS_FLOW_ID for Q1.HMGNS_LNK_ID) = 1.
In the data items section, add Q1.HMGNS_FLOW_ID and Q2.HMGNS_FLOW_ID. This will give you one row with the HMGNS_FLOW_ID and opposite HMGNS_FLOW_ID.

Hope that helps,

Sriram.
http://www.cognosonsteroids.com