SQL SSRS Declare Statement in Power BI

[cheid_4838]

I have a query that has @year, @month, @budget that I need to figure out how to get to work in Power BI. The query is referencing tables from Microsoft Dynamics Great Plains. Does anyone know how to get these to work in Power BI?  I know how to get them to work in SSRS, but not in Power BI. Your help is greatly appreciated.

create procedure gprv_actual_vs_budget
@year int, @month int, @budget varchar(15)
as

set nocount on

select
rtrim(an.ACTNUMST) Account,
rtrim(m.ACTDESCR) [Account Description],
coalesce(a.Monthly,ah.Monthly,0) [Monthly Actual],
coalesce(b.Monthly,0) [Monthly Budget],
coalesce(a.Monthly,ah.Monthly,0)
- coalesce(b.Monthly,0) [Monthly Variance],
coalesce(a.YTD,ah.YTD,0) [YTD Actual],
coalesce(b.YTD,0) [YTD Budget],
coalesce(a.YTD,ah.YTD,0)
- coalesce(b.YTD,0) [YTD Variance]
from GL00105 an -- account numbers

left outer join --actuals from open year
(select a.ACTNUMST,
sum(case when g.PERIODID = @month
then g.DEBITAMT - g.CRDTAMNT
else 0
end) Monthly,
sum(case when g.PERIODID <= @month
then g.DEBITAMT - g.CRDTAMNT
else 0
end) YTD
from GL11110 g
inner join GL00105 a
on g.ACTINDX = a.ACTINDX
where g.YEAR1 = @year and g.PERIODID <= @month
group by a.ACTNUMST) a --actuals open year
on an.ACTNUMST = a.ACTNUMST

left outer join --actuals from historical year
(select a.ACTNUMST,
sum(case when g.PERIODID = @month
then g.DEBITAMT - g.CRDTAMNT
else 0
end) Monthly,
sum(case when g.PERIODID <= @month
then g.DEBITAMT - g.CRDTAMNT
else 0
end) YTD
from GL11111 g
inner join GL00105 a
on g.ACTINDX = a.ACTINDX
where g.YEAR1 = @year and g.PERIODID <= @month
group by a.ACTNUMST) ah --actuals historical year
on an.ACTNUMST = ah.ACTNUMST

left outer join --budgets
(select a.ACTNUMST,
sum(case when b.PERIODID = @month
then b.BUDGETAMT
else 0
end) Monthly,
sum(case when b.PERIODID <= @month
then b.BUDGETAMT
else 0
end) YTD
from GL00201 b
inner join GL00105 a
on b.ACTINDX = a.ACTINDX
where b.BUDGETID = @budget and b.YEAR1 = @year
group by a.ACTNUMST) b --budgets
on an.ACTNUMST = b.ACTNUMST

left outer join GL00100 m --account master
on an.ACTINDX = m.ACTINDX

--only show rows that are not all zeros,
--if you want to see all accounts,
--remove the where clause below
where a.Monthly <> 0
or a.YTD <> 0
or b.Monthly <> 0
or b.YTD <> 0
or ah.Monthly <> 0
or ah.YTD <> 0

order by an.ACTNUMST

[Anonymous]

Hi, @cheid_4838 

Thanks for parry2k's reply. Could you please share the table containing all the required information without sensitive data, the desired output and the logic on how to achieve the desired result. We are more familiar with DAX than SQL, if you can give the above required information it will help us to solve your problem faster, thanks for your understanding.

Best Regards,
Yang
Community Support Team

[parry2k · Accepted solution]

@cheid_4838 have you looked at this post: Solved: Passing PowerBI Parameter To A Stored Procedure - Microsoft Fabric Community