1

I want to be able to define my parameter SA.FiscalWeek in the below code from a cell in Excel

SUM(CASE WHEN SA.FISCALYEAR = 2017 
AND SA.FISCALWEEK = 19 THEN SA.SALESEXVAT/SA.EXCHANGERATEEURO END ) AS 'Week'

I will be using multiple Case When in my overall code all with individual parameters

I have found a way this is possible using a WHERE statement,

But was wondering if it is possible in a CASE WHEN statement ?

Nithin
  • 1,376
  • 12
  • 29
Quacks101
  • 293
  • 1
  • 3
  • 12
  • this is where I found out how to do it via a WHERE statement: https://stackoverflow.com/questions/3091908/how-to-add-parameters-to-an-external-data-query-in-excel-which-cant-be-displaye – Quacks101 Jun 15 '17 at 12:45
  • There are newer functions you may not be aware of like the [IFS](https://support.office.com/en-us/article/IFS-function-36329a26-37b2-467c-972b-4a39bd951d45) function but you are going to have to provide more information about your version. –  Jun 15 '17 at 12:47
  • im using Microsoft SQL Server Management Studio to write the query initially, then using it in Microsoft EXCEL 2010 to populate a pivot table – Quacks101 Jun 15 '17 at 12:48

1 Answers1

1

You can use vba to pass the query to the command text, Use a helper sheet with your query listed line by line

=" line 1 of query"

=" line 2 etc. "

=" line 3&E4&"rest of line etc. "

E4 would be a cell ref to make the query dynamic from Excel then concatenate these lines into one cell,

use vba similiar to the below, my range "SQLCOUNTRY" is the cell of concatenated query

 With ActiveWorkbook.Connections("EUUKSQL01 dashboard StockSalesAggregateWeek2") _
        .OLEDBConnection
    .CommandText = Sheets("CountryScript").Range("SQLCOUNTRY").Value
    End With

This way any part of the query can be driven off an existing excel Cell

PeterH
  • 975
  • 2
  • 14
  • 36