interaction between reports from two different queries on same datasource

[vopatel]

I have two queries on same datasource/database in Oracle.

1) Returns sales data by sources on a timeline

select 
(CASE
WHEN
x.SOURCE='Online-Brand'
THEN
1
WHEN
x.SOURCE='Online-Dealer'
THEN
2
WHEN
(x.SOURCE='Parts' or x.SOURCE='Sales' or x.SOURCE='Stock') 
THEN
3
ELSE
null
END) AS SOURCE,
CAST(TRUNC(x.createdon) AS DATE) AS ORDER_DATE,
TRUNC(to_date(to_char(x.createdon,'MM/DD/YYYY'),'MM/DD/YYYY'), 'IW')-1 AS WEEK_START_DATE,
(TOTALPRODUCT +TOTALTAX + TOTALADJUSTMENT +TOTALSHIPPING +TOTALTAXSHIPPING) AS TOTAL
FROM ecom_acc.orders o, ecom_acc.xorders x
where o.status not in ('P','X') and o.orgentity_id != '7000000000000001703' 
and x.status not in ('NEW', 'INC', 'CAN', 'RET')
and o.orders_id = x.orders_id
and CAST(TRUNC(x.createdon) AS DATE) >='01-SEP-17'

2) Returns total sales by a product 

select unique a.catentry_id, b.name, sum(a.totalproduct) from ecom_acc.orderitems a, ecom_acc.catentdesc b, ecom_acc.xorders c 
where a.catentry_id = b.catentry_id and b.language_id=-1 and a.orders_id=c.orders_id 
and a.status not in('P','X') and c.source='Online-Brand' and a.LASTCREATE >= to_date('01-09-2017 07:00:00', 'DD-MM-YYYY HH24:MI:SS') 
group by a.catentry_id,b.name order by sum(a.totalproduct) desc

I want report data from 2nd query changed based on timeline I select on report from 1st query. If I select Jan, 2018 in first report, I want 2nd report to show product wise sales for Jan, 2018. Any idea how I can achieve it? 

The dashboard with two reports looks like this.

https://prnt.sc/jck6l9

[v-xjiin-msft]

Hi @vopatel,

Based on your queries, even they are using the same datasource, they don't have relationships (Related columns). So with current structure, I'm afraid it is not possible to make interaction between these two queries.

And to achieve your requirement, you need to create related columns in the two datasets first, then create relationship between the two datasets. Something like below sample:

Thanks,
Xi Jin.

To create relationships, you need