Skip to Content
avatar image
Former Member

Connecting excel to a custom query

I report out of B1 using excel.

From excel I create an odbc connection, change the definition from table to sql and paste my previously designed sql statement within the definition.

It works fine, but I have seen a consultant designing a custom query, and I do mean a custom query made of several tables and joins, using the sap b1 query generator, and then linking excel to that same query without pasting anything within excel.

I would like my next queries to be based on the same method, but haven't worked out how to do this.

The only options available for an odbc definition are table or sql, and it seems to me that I can only paste an sql in there or specify a table, which doesn't work for what I am trying to achieve.

What is the correct way to do this?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

6 Answers

  • Best Answer
    May 21, 2013 at 11:57 AM

    Hi Philippe,

    Of course I do not know how your consultant did it, but I have done this using Views.

    You write a query that results in a table, and save that query as a view in MS SQL Server Management Studio. Then you can call on this table via the normal Excel ODBC connection, like you have done before.

    Regards,

    Johan

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Johan Hakkesteegt

      The query saved in the Excel can be functioned as a view. Whenever you refreshed, it can get the latest data. Have you read my book?

  • avatar image
    Former Member
    May 21, 2013 at 04:03 PM

    Philippe,

    Please check e.g below:

    Using the ODBC Driver in Excel:

    File:ExcelOtherQuery.png

    1. In Excel open the Data tab.
    2. Under From Other Sources open From Microsoft Query.
    3. Select ServiceNow as your database (the default DSN name).
    4. Clear the Use the Query Wizard to create/edit queries check box.
    5. Supply the ServiceNow user name and password that was pre-configured during the ODBC driver configuration procedure.
    6. Select a table from the ServiceNow instance and click Add.
    7. Close the dialog box.
    8. Select the table columns from which the Query Builder will retrieve data. Use the list above the table, or type the names directly into the columns, and then press Enter.
    9. To retrieve the data and create the Excel record, click the Return Data icon or select File > Return Data to Microsoft Office Excel.

    The requested data is brought into Excel.

    Regards,

    Marcelo Silva Santos

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Marcello, thanks for your reply but I find it incomplete and/or unhelpful.

      What you are suggesting is taking me to the excel query designer. In this case the view is not stored or accessible from b1 but only from excel and it doesn't correspond to my original question.

      Unless you can complete your answer and describe how I can access the query built within excel from sap b1.

  • May 21, 2013 at 05:43 PM

    HiĀ  Philippe Le Mounier...

    Pls check with the Link

    http://scn.sap.com/community/business-one/blog/2013/05/16/how-to-browse-sap-b1-database-using-ms-excel-for-reporting

    Hope Helpful

    Regards

    Kennedy

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 21, 2013 at 05:56 PM

    Hi,

    Please share what you want to achieve.

    Thanks,

    Gordon

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    May 24, 2013 at 11:03 AM

    As far as I know you cannot do this.

    The query's that you save in B1 are stored in the table OUQR. Perhaps you could write a bit of vba to use the result of a query to perform another query but I don't really how this would be of any real benefit.

    You could perhaps build a query of all the information you want to filter in excel and utilise a pivot table.

    Could you not ask the consultants that have done this?

    EDIT

    ------

    You could even write some SQL that executes the value of a query using the exec function so query OUQR for the query you want to run store it into a variable then execute the store

    Second Edit

    ----------------

    The following code will run your latest user query when put into the query option in the connection properties, you could also put a where clause and define which one to run.  Have fun!

    declare @sql as nvarchar(max)

    select

    top 1 @sql = qstring

    from OUQR

    order by IntrnalKey desc

    exec (@sql)

    Add comment
    10|10000 characters needed characters exceeded

  • May 25, 2013 at 09:20 PM

    I tried doing this but the biggest issue is the lack of flexibility in excel. Maybe the power pivot add on is better suited for this.

    Add comment
    10|10000 characters needed characters exceeded