Declare parameters for SQL query in Power BI

[peterhinton]

So i have the below code from SQL i use to populate some date,

DECLARE @CW INT, @CM INT, @CQ VARCHAR(4);
SET @CW=19; SET @CM = 5; SET @CQ = 'QTR2'

SELECT
--CHOOSE BRANCH INFO TO USE AND FILTER IN PIVOT
SA.BRANCHNO As ' Branch No'
,EU.[STORE NAME] As 'Branch Name'
,EU.COUNTRY
,EU.TERRITORY
,EU.DISTRICT
,EU.REGION
,EU.DSM
,EU.RSM
,EU.[STORE TYPE]


--SUM WEEK SALES FOR CURRENT WEEK
, SUM(CASE WHEN SA.FISCALYEAR = 2017 AND SA.FISCALWEEK = @CW THEN SA.SALESEXVAT/SA.EXCHANGERATEEURO END ) AS 'Week'
, SUM(CASE WHEN SA.FISCALYEAR = 2016 AND SA.FISCALWEEK = @CW THEN SA.SALESEXVAT/SA.EXCHANGERATEEURO END ) AS 'LY Week'
 ,sum(CASE WHEN SA.FISCALYEAR = 2017 AND SA.FISCALWEEK = @CW THEN SA.SALESEXVAT/SA.EXCHANGERATEEURO END ) / SUM(CASE WHEN SA.FISCALYEAR = 2016 AND SA.FISCALWEEK = @CW THEN SA.SALESEXVAT/SA.EXCHANGERATEEURO END ) - 1 as 'WEEK VAR'


-- FROM EU ACTIVE STORES, MANUALLY UPLOADED EACH FRIDAY BY PETER HINTON
FROM
[BHXSQL2014-DEV].[BMANALYTICS].[DBO].[EUACTIVESTORES] EU

-- JOIN WITH SALES AGGREGATE FROM MARK TRIMMERS LINKED SERVER
INNER JOIN
EUUKSQL01.DASHBOARD.DBO.SALESAGGREGATEWEEK SA
ON SA.BRANCHNO = EU.[STORE NO]

--SELECT ONLY MAIN CHAIN STORES
WHERE
EU.[UPLOAD TYPE]='MAIN' AND EU.COMPSTATUS = 'COMP'

GROUP BY 
SA.BRANCHNO
,EU.[STORE NAME]
,EU.Country
,EU.Territory
,EU.District
,EU.Region
,EU.DSM
,EU.RSM
,EU.[Store Type]

ORDER BY SA.BRANCHNO

Where i declare CW = 19, CM = 5, CQ = 'QTR2',


I need to be able to change these values each week, can i be propted to chnage these on work book open perhaps ?

Im very new to both PowerBI and kinda new to SQL,

I can do it in Excel by create a macro to replace the values with that of a cell vlaue which works fine, but my manager wants us to start using PowerBI more

 

[gazpage]

Just to check, but you are creating a Power Query query and then using the option to enter your own SQL statement?

Unless you are a SQL ninja, it's usually better to just create the transformations in Power BI and let it write the SQL query via query folding. So in Power BI desktop:

1. Get Data -> From SQL Server, enter your server details
2. Navigate to your database and tables
3. Do the transformations as necessary to get the table how you want it. At this point you should be able to right click the final applied step and see view native query, this is the query that will be sent to your SQL server. If you have done any steps that cannot be folded then this option will be greyed out.
4. Go to Manage Parameters -> New Parameters and create 3 parameters for CW, CM and CQ, use decimal number and text as appropriate. Set the default values to be your values above.
5. Open the Advance Editor for your original query. Fine where your parameters are used and replace e.g. 19 with CW, 5 with CM etc.
6. All going well you should still see your query table with no changes from step 5. It's now been parameterised.
7. Close the query editor to get back into Power BI proper
8. Save the file somewhere as the master copy. Save as -> choose .pbit file type. You need to keep the .pbix file as a master version to work on.
9. When the user opens the PBIT they will get a window to enter the parameters. Once they do the query will run based on the parameters