SOLVED

How do you do a parameter query when not using Microsoft Query?

Copper Contributor

I have a workbook that uses the ODBC interface part of Microsoft Query to query a DB2 database.  The workbook actually queries over 20 different tables.  I have one cell in the workbook that is the main search parameter for all the queries (for example WHERE CUST_NUM = ?).  I have the queries to always use this value and automatically execute when the value changes.  This way I change one cell and the 20 queries all execute.

I'm trying to move this process from using ODBC under Micrsoft Query to use the JDBC interface under the "Get Data / From Database / From IBM DB2 Database" path.  I can't find how to do this same process and would appreciate any guidance.

9 Replies

Hi John,

 

I didn't work with DB2, just my guess if that could work

 

Source=DB2.Database("server", "database", Query=["SELECT something" & pFromMyCell])

where pFromMyCell is query/function which returns the string from your cell

Thanks for the tip, but I'm struggling getting something to work. 

In my spreadsheet I've put in cell C1 the value "D1" and then in cell D1 I put the value "123456" for example. 

Now in the query I modified it to:

Source=DB2.Database("server", "database", Query=["SELECT * " & "WHERE COL1 = 'ABC' and COL2 = '" & pINDIRECT(c1) & "'" "])

 

I get this:
Expression.Error: The name 'pINDIRECT' wasn't recognized.  Make sure it's spelled correctly.

John, pINDIRECT or how you'll call it is the query which you shall generate yourself.

 

In brief, within Excel give to your D1 cell name, for example, myParam. Stay on it, click in Ribbon Data->From Table/Range. In query editor convert column to Text and take the value from the cell, M script code will look like

let
    Source = Excel.CurrentWorkbook(){[Name="myParam"]}[Content],
    AsText = Table.TransformColumnTypes(Source,{{"Column1", type text}}){0}[Column1]
in
    AsText

After that you may add result to your query as ... ["SELECT * " & myParam & "...]"

 

@Wyn Hopkins published recently detailed post how to pick-up parameters from Excel cells into G&T, only i don't remember where. If Wyn is around hope he will give the link and/or comments. On the other hand you may find in other places.

 

Please check before what you connection works without any parameters, just add your "123456" directly into the Query= string.

 

Thanks a bunch and I'm getting a lot closer.  However.... as I'm following your steps in step 5 you indicate to "Home - Close & Load - Connection only" - I don't see an option to close as "connection only".

QEditor.jpg

I'm on the current channel so my Office365 has been updated with latest Get & Transform stuff.

Now I did finally get there by rmb on the saved query and select "Load to" which gave me this window where I could select "connection only":

QEditor2.jpg

I then went and edited my SQL query to now be

....... AND KUNNR = '" & QueryKUNNR  & "') ORDER BY KTOKD .......

 

When it tries running I now get this:

Formula.Firewall: Query 'KNA1 by KUNNR' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

 

As I said - oh so close now. 

best response confirmed by John Murray (Copper Contributor)
Solution
Hi John,

Keep going it's worth it!

To address your first point, Close and Load to... Connection Only. Those are actually 2 separate steps. In your screen shot your Load To.. is greyed out as you must have already loaded that query once and now you were editing it. In that case you did the right thing and used the Right Click option to access Load To... Connection Only

On the second issue you should (I think) resolve this by going to New Query - > Query Options - > Privacy > Always Ignore Privacy Level settings. There's a hyperlink there as well if you want to find out more about what turning off Privacy warnings does, however it is fairly standard practice to set it to Always Ignore (but find out about it and make your own call :)



My $0.02 regarding Formula.Firewall

 

1) I agree that's quit common to switch private settings off to avoid the issue. Here https://blog.crossjoin.co.uk/2017/07/04/data-privacy-settings-in-power-bipower-query-part-4-disablin... is step by step how to do that and why it's not always good practice.

 

2) I personally prefer to keep privacy settings by default and combine the query by the way to avoid Formula.Firewall. Security is the point, but not the main one here - when you distribute your workbook to other people you have to ask everyone to change default settings and explain how to do that.

 

In his another post Chris gives explanation how to avoid the issue practically for exactly the same case https://blog.crossjoin.co.uk/2017/06/26/data-privacy-settings-in-power-bipower-query-part-3-the-form...

 

I didn't try Value.NativeQuery, but combining Excel parameter and SQL queries in one works fine, used that approach before seen above post.

Thanks for sharing the links

 I looked at the other ways to do this in the other posts and while it was tempting to work on putting both of these in one query I went with this simple approach of setting the privacy settings to "always ignore".  The main reason for this is in reality I don't have just one query that I need to read this parm and execute, but I have about 20 different queries against other tables that need to use this same query value.

 

Thanks everyone for your great input on this. 

1 best response

Accepted Solutions
best response confirmed by John Murray (Copper Contributor)
Solution
Hi John,

Keep going it's worth it!

To address your first point, Close and Load to... Connection Only. Those are actually 2 separate steps. In your screen shot your Load To.. is greyed out as you must have already loaded that query once and now you were editing it. In that case you did the right thing and used the Right Click option to access Load To... Connection Only

On the second issue you should (I think) resolve this by going to New Query - > Query Options - > Privacy > Always Ignore Privacy Level settings. There's a hyperlink there as well if you want to find out more about what turning off Privacy warnings does, however it is fairly standard practice to set it to Always Ignore (but find out about it and make your own call :)



View solution in original post