Aged Debtors Report - Passing In Date and Client ID Parameters

[sonnydeletejc]

Dear Community,

I’m in the process of porting an Aged Debtors report from Crystal over to Power BI, and looking for the best way to provide the user with a way to enter ‘As-Of Date’ and ‘Client ID’ parameters prior to running a SQL query and loading the data.

I’ve substituted these parameters in the query below with @AsOfDate and @ClientID.

What’s the best approach to achieve this, so that the parameters are applied before the query is run?

Any suggestions welcome.

Many thanks,
J

SELECT ars.client_id,

       c.name AS NAME,

       ars.bill_number,

       ars.aging_date AS "DATE",

       'TOTAL' AS TYPE,

       julian_day(@AsOfDate) - julian_day(ars.aging_date) AS AGE,

       round(sum(art.tx_amt),2) AS BALANCE,

       CASE WHEN julian_day(@AsOfDate) - julian_day(ars.aging_date) BETWEEN 0 AND 30 THEN ROUND(SUM(art.tx_amt),2) ELSE 0 END AS "0-30",

       CASE WHEN julian_day(@AsOfDate) - julian_day(ars.aging_date) BETWEEN 31 AND 60 THEN ROUND(SUM(art.tx_amt),2) ELSE 0 END AS "31-60",

       CASE WHEN julian_day(@AsOfDate) - julian_day(ars.aging_date) BETWEEN 61 AND 90 THEN ROUND(SUM(art.tx_amt),2) ELSE 0 END AS "61-90",

       CASE WHEN julian_day(@AsOfDate) - julian_day(ars.aging_date) BETWEEN 91 AND 120 THEN ROUND(SUM(art.tx_amt),2) ELSE 0 END AS "91-120",

       CASE WHEN julian_day(@AsOfDate) - julian_day(ars.aging_date) > 120 THEN ROUND(SUM(art.tx_amt),2) ELSE 0 END AS "121+"

FROM mydatabase.ar_sum_all ars

LEFT JOIN mydatabase.ar_transactions art ON art.client_id = ars.client_id

AND art.bill_number = ars.bill_number

AND art.tx_date < @AsOfDate

JOIN mydatabase.client c ON c.client_id = ars.client_id

WHERE ars.client_id = @ClientID

  AND CASE WHEN ars.payment_date < @AsOfDate AND ars.payment_date IS NOT NULL THEN 0 ELSE 1 END = 1

  AND aging_date <= @AsOfDate

GROUP BY ars.client_id,

         c.name,

         ars.bill_number,

         ars.aging_date,

         'TOTAL',

         julian_day(@AsOfDate) - julian_day(ars.aging_date),

         CASE WHEN julian_day(@AsOfDate) - julian_day(ars.aging_date) BETWEEN 0 AND 30 THEN orig_amt ELSE 0 END,

         CASE WHEN julian_day(@AsOfDate) - julian_day(ars.aging_date) BETWEEN 31 AND 60 THEN orig_amt ELSE 0 END,

         CASE WHEN julian_day(@AsOfDate) - julian_day(ars.aging_date) BETWEEN 61 AND 90 THEN orig_amt ELSE 0 END,

         CASE WHEN julian_day(@AsOfDate) - julian_day(ars.aging_date) BETWEEN 91 AND 120 THEN orig_amt ELSE 0 END,

         CASE WHEN julian_day(@AsOfDate) - julian_day(ars.aging_date) > 120 THEN orig_amt ELSE 0 END

ORDER BY ars.client_id,

         ars.aging_date ASC

[v-jiascu-msft]

Hi @sonnydeletejc,

The scenario here couldn't be the common practice in the Power BI. Usually, we connect to SQL db and fetch data once. And then manipulate data in the Power BI. Even in the Direct Query mode, we still do what we want in the Power BI and Power BI will send the queries to SQL db.

What do you want to achieve with this query? Maybe the approach is different in Power BI.

Best Regards,

Dale

[sonnydeletejc]

Hi @v-jiascu-msft,

Thanks for your reply. The purpose of the query is to look at client debts by age (1-30, 31-60 days etc), excluding debts which had been paid at the 'As Of' date, as well as debts which didn't exist yet exist on the 'As Of' date (created after that date).

So running with, say, an 'As Of' date of 1st October 2017... ignore any debts which had been paid prior to that date, as well as any new debt created beyond that date.

I could use less than and greater than filters to do this, but my aim is to filter prior to running the query, rather than pulling everything back and then filtering. Trying to be as efficient as possible. As you say, I guess this isn't the way PBI works.

J