Forum Discussion

John Murray's avatar
John Murray
Copper Contributor
Aug 07, 2017
Solved

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

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.

BI & Data Analytics
Parameter Query
  • Wyn Hopkins's avatar
    Wyn Hopkins
    Aug 12, 2017
    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 :)



9 Replies

Sort By
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Aug 07, 2017

    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

    • John Murray's avatar
      John Murray
      Copper Contributor
      Aug 11, 2017

      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.

      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Aug 11, 2017

        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.

         

Resources