Hi Experts,
I have a query, where I have to create a report in power bi desktop based on the output coming from the query. I need help on power bi like how to approach or how to use that query in power bi in the form of measures/Dax/columns/filters or if I use in the form of native database query how to do it.
Below find the query.
Thanks in Advance.
with Monthly_activity as
(
select Registration.*from
(
with x AS
(
select product_am,currenycd,lei,legaljurisdiction,
registration_month,renewal_month,renewal_month_1,
Next_renewal_date,REGISTRATIONSTATUS,EMAIL_ID,INVOICE_TY,prc_grp_id
from
(
select
lei_rqst,lei_rqst_type_nm AS PRODUCT_AM,
lei_ordr.lei_ordr_crncy_cd as CURRENCY_CD,
GC.lei,
o.orgnztn_gleif_lgl_jrsdctn_nm as LEGALJURISDICTION,
TO_char(lei_ordr.lei_ordr_ts,'yyyy-mm') as ORDER_MONTH,
TO_char(gc.lei_rgstrtn_initl_rgstrtn_date,'yyyy-mm-dd') AS REGISTRATION_MONTH,
TO_CHAR(to_date(to_char(gc.lei_rqstrtn_next_renewl_date,'yyyy-mm-dd')-365,'YYYY-MM') AS RENEWAL_MONTH,
TO_CHAR(to_date(to_char(gc.lei_rqstrtn_next_renewl_date,'yyyy-mm-dd')-495,'YYYY-MM') AS RENEWAL_MONTH_MINUS_1,
to_char(gc.lei_rqstrtn_next_renewl_date,'yyyy-mm') AS NEXT_RENEWAL_DATE,
GC.lei_rgstrtn_stats_nm as REGISTRATIONSTATUS,
lei_ordr.lei_ordr_email_adrss_text as EMAIL_AD,
lei_ordr.lei_ordr-pymnt_mthd_text as INVOICE_TY,
prc_grp_id
from gmi_lei.lei_rgstrtn GC
join gmi_lei.orgnztn o ON GC.lei_rgstrtn_id=o.gmei_intrnl_orgnztn_id
AND GC.lei_rgstrtn_mngng_lou_lei='EVKO5'
join gmi_lei.leirgstrtn_ordr rgstrtn_ordr on rgstrtn_ordr.pblshd_lei_rgstrtn_id=GC.lei_rgstrtn_id
join gmi_order.lei_ordr lei_ordr ON rgstrtn_ordr.lei_ordr_id=rgstrtn_ordr.lei_ordr_id
join gmi_lei.lei_rgstrtn_work rgstrtn_work ON rgstrtn_work.lei_rgstrtn_id= gc.lei_rgstrtn_id
join gmi_order.lei_rqst ON lei_rqst.work_lei_rgstrtn_id =rgstrtn_work.lei_rgstrtn_id
where lei_rqst.lei_rqst_type_nm LIKE '%RENEWAL%'
AND lei_rqst.lei_rqst_type_nm LIKE '%maintenance%'
)AR1
where ORDER_MONTH <= RENEWAL_MONTH
AND ORDER_MONTH >= RENEWAL_MONTH_MINUS_1
AND REGISTRATION_MONTH < RENEWAL_MONTH)
SELECT
TO_CHAR(TO_DATE(to_char(CDF.lei_rgstrtn_next_renewl_date, 'YYYY-MM-DD')-365,'YYYY-MM') AS EVENT_MONTH,
'1 REGISTRATION' AS TYPE,
(CASE WHEN(X.EMAIL_AD = 'info@nordlei.org' AND CDF.orgnztn_glief_lgl_jrsdctn_nm IN ('SE','DK','NO','FI')) THEN 1 ELSE 0 END) AS NORD_STANDARD_SE_DK_NO_FI,
(CASE WHEN(X.EMAIL_AD IN ('info@nordlei.org','support@nordlei.org') AND CDF.orgnztn_glief_lgl_jrsdctn_nm NOT IN ('SE','DK','NO','FI')) THEN 1 ELSE 0 END) AS NORD_STANDARD_OTHER,
(CASE WHEN(X.EMAIL_AD = 'support@nordlei.org' AND CDF.orgnztn_glief_lgl_jrsdctn_nm IN ('SE','DK','NO','FI')) THEN 1 ELSE 0 END) AS NORD_PREVAL,
(CASE WHEN(X.EMAIL_AD NOT IN ('info@nordlei.org','support@nordlei.org') OR X.EMAIL_AD IS NULL) AND CDF.orgnztn_glief_lgl_jrsdctn_nm IN ('SE','DK','NO','FI') AND X.INVOICE_TY != 'Post-Payment') THEN 1 ELSE 0 END) AS GMEI_SE_DK_NO_FI,
X.*
from
(SELECT *from
gmi_lei.lei_rgstrtn l, gmi_lei.orgnztn o
where lei_rgstrtn_mngng_lou_lei LIKE 'EVK0%'
AND l.lei_rgstrtn_id = o.gmei_intrnl_orgnztn_id
AND o.pni_ind ='false'
AND to_char(to_date(to_char(L.lei_rqstrtn_next_renewl_date,'yyyy-mm-dd'),'YYYY-MM-dd')-365,'YYYY-MM') >= '2022-01'
AND to_char(L.lei_rqstrtn_initl_rgstrtn_date,'yyyy-mm')<TO_CHAR(to_date(to_char(l.lei_rqstrtn_next_renewl_date,'yyyy-mm-dd'),'YYYY-MM-dd')-365,'yyyy-mm')
) CDF
LEFT OUTER JOIN X ON X.lei=CDF.lei)Registration)
select TYPE, RENEWAL_MONTH, SUM(TOTAL) AS COUNT
from
(
select count(*) AS TOTAL, TYPE,
PRODUCT_AM,
CURRENCY_CD, prc_grp_prc_mnth_nbr AS RENEWAL_MONTH
from Month_activity MA
LEFT OUTER JOIN gmi_order.prc_grp_elmnt prc_grp on prc_grp.prc_grp_id= MA.prc_grp_id
where typr is not null
group by 2,4,3 desc)CDS_COUNT
group by type,renewal_month
order by 2,3 desc;
