Excel Parameters

  • Thread starter Thread starter Dan
  • Start date Start date
D

Dan

I'm creating an MS Query using SQL and am trying to find
out if there is a way around the limitation of not being
able to use parameters [ ] in the query if it cannot be
represented graphically, as excel states it. The Problem
is that the SQL query needs to have multiple outer joins
and excel states that a query with multiple outer joins
cannot be represented graphically, and in turn parameters
cannot be used in a MS Query that cannot be represented
graphically. A catch 22. Any ideas or workarounds?
 
Workaround: create a stored procedure that accepts the parameters you
want to pass.

BTW are you sure you *do* need multiple outer joins? I don't wish to
question your judgement, it's just from experience that often when I
start out with more than one outer join I find I can get away with
inner joins and just one outer join. Might be worth a quick double
check if it's causing you grief.
 
I know this thread is old but I hope this helps others.

The way to do this with a database query (note: pivot tables seem to be easily corrupeted by doing this) is to use script editor.

Setup your SQL

SELECT 'ABscore' = a.actualvalue ,
'PopScore' = b.actualvalue ,
a.externalid, 'Date' = b.perioddate,
c.Location,
'Branch' = c.group1,
'TL' = rtrim(c.[first_name(team_lead)])+ ' ' + rtrim(c.[last_name(team_lead)]),
'Emp' = rtrim(c.[first_name(emp)])+ ' ' + rtrim(c.[last_name(emp)])
FROM BPWAREHOUSEDB.dbo.abscores a, BPWAREHOUSEDB.dbo.popscores b, witness_bridge.dbo.sc_hierarchy_cco c
WHERE a.externalid = b.externalid AND a.perioddate = b.perioddate AND b.externalid = c.Staff_ID
and b.externalid = 12345

Return a data set with the hard coded value (bold section). Go to Tools -> Macro -> Microsoft Script Editor. When that opens up, hit control + F to find your bold section. In my example, I want to change 12345 to a ?. So it should look like this now: and b.externalid = ?

Save in MSSE. Go to refresh your database query and it will pop up the parameters box. Select a cell or enter a value and you are done.

~Error
 
Back
Top