SQL Declare Statements in Power BI

[cheid_4838]

I am working to convert a lot of SSRS reports to Power BI.  Most I have been able to use the existing SQL logic from SSRS.  For my current issue I have a lengthy SQL statement that is used to create an SSRS report that I want to use in Power BI.  I had to delete most of the logic to get it to fit in the message below.  These statements are positioned in various spots througout the logic.  The problem I am having is that the SQL statements below references a start, end, and a delivery date which is based on the DECLARE start and end date. that is selected in the SSRS report.  I have a date between slicer in the Power BI dashboard I want to use, but I am not sure if a SQL statement can be written to reference a slicer in place of the these date statements so the logic works in Power BI .  Any suggestions or tips would be greatly appreciated.  Thanks.

--DECLARE @StartDate AS DATETIME  DECLARE @EndDate AS DATETIME   SET @StartDate = '10/29/2023'  SET @EndDate = '11/3/2023'

--DECLARE @StdRate as money   SET @StdRate = 42.00     DECLARE @OTRate as money   SET @OTRate = 58.00  

AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1

, isnull((select top 1 ivd_rate from invoicedetail id (nolock) inner join invoiceheader ih (nolock) on ih.ivh_hdrnumber = id.ivh_hdrnumber where ih.ivh_billto = 'CATGRI' and ih.ivh_deliverydate >= @StartDate and ih.ivh_deliverydate < @EndDate+1 and id.cht_itemcode = 'FSCMI'),0) as FSCRate

, isnull(case when s.stp_ord_mileage = 0 then s.stp_lgh_mileage else s.stp_ord_mileage end,0) *

isnull((select top 1 ivd_rate from invoicedetail id (nolock) inner join invoiceheader ih (nolock) on ih.ivh_hdrnumber = id.ivh_hdrnumber where ih.ivh_billto = 'CATGRI' and ih.ivh_deliverydate >= @StartDate and ih.ivh_deliverydate < @EndDate+1 and id.cht_itemcode = 'FSCMI'),0) as FSC

, 'CATGRI' + '-' + cast(year(@EndDate) as varchar(max)) + '-' + cast(month(@EndDate) as varchar(max)) + '-' + cast(day(@EndDate) as varchar(max)) as InvoiceNum

[Anonymous · Accepted solution]

Hi @cheid_4838 

Here's a concise action plan to reference a slicer in SQL queries within Power BI, which will allow you to dynamically filter your data based on slicer selections:

1. Create Dynamic M Query Parameters: In Power BI Desktop, navigate to Home > Transform data > Transform data to open the Power Query Editor. Use the Manage Parameters option to create new parameters that will represent your start and end dates. For detailed guidance, please refer to :Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn .
   
   

2. Reference the Parameters in Your SQL Query: Modify your SQL query within the Power Query Editor to reference the created parameters for start and end dates. Instead of using statements, you'll pass these parameters directly into your query where needed.
   
   

3. Bind the Parameters to Slicer Values: To ensure the slicer controls the parameters, you'll need to create a table of date values in Power BI and bind these to your parameters. This step is crucial for allowing slicer selections to dynamically update your SQL query.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.