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

[v-yuezhe-msft · Accepted solution]

@peterhinton,

Based on your SQL statement, I note that you only use the @CW parameter in your SQL query.

In Power BI desktop, click “Get Data->Blank Query”, then click Advanced Editor, paste the following code in it(replace server name and database name with your owns). 

let
    SQLSource = (CW as number) =>

let
    Source = Sql.Database("servername", "databasename", [Query="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 = "& Number.ToText(CW) & " THEN SA.SALESEXVAT/SA.EXCHANGERATEEURO END ) AS 'Week'
, SUM(CASE WHEN SA.FISCALYEAR = 2016 AND SA.FISCALWEEK = "& Number.ToText(CW) & " THEN SA.SALESEXVAT/SA.EXCHANGERATEEURO END ) AS 'LY Week'
 ,sum(CASE WHEN SA.FISCALYEAR = 2017 AND SA.FISCALWEEK = "& Number.ToText(CW) & " THEN SA.SALESEXVAT/SA.EXCHANGERATEEURO END ) / SUM(CASE WHEN SA.FISCALYEAR = 2016 AND SA.FISCALWEEK = "& Number.ToText(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"])

in
    Source
in
    SQLSource

Then you can enter parameter values and click “Invoke” button to generate filtered table as shown in the following similar screenshot.



Regards,

[Meerakhanna]

Hi!

How would you declare for Date field?

I tried below query and getting some error

let SQLSource = (Week as date) =>

------

In main query have included below condition:

[PrdDate] BETWEEN DATEADD(WW,-5,Convert(Date,(Week))) AND DATEADD(W,1,convert(Date,(Week)))

getting below error on invoking the function,

DataSource.Error: Microsoft SQL: Invalid column name 'Week'.
Invalid column name 'Week'.
Details:

DataSourceKind=SQL
DataSourcePath=.;DBName
Message=Invalid column name 'Week'.
Invalid column name 'Week'.
Number=207
Class=16

Kindly help!

[peterhinton]

@v-yuezhe-msft

You Sir,

Are a freeking GENIOUS,

Thank YOu so much, i literally spent my whole Friday Afternoon trying multiple ways of achieving this,

I think i was missing

    SQLSource = (CW as number) =>

And also this part

Number.ToText

So onw i will play round with these until i fully understand what they do But again Thank You it works for now 🙂