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
Create two queries
In the first one use the filter
HMGNS_FLOW_ID<>746939
and in the second one
HMGNS_FLOW_ID=746939
Joined them with the HMGNS_LNK_ID.
The in the query that is produced from the join
use the HMGNS_LNK_ID from the second query
and the HMGNS_FLOW_ID from the first query
Hi pricter
The logic which you gave is working fine.
Thanks very much