Connecting excel to a custom query

[Former Member]

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?

[Johan_H · Accepted solution]

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

[Former Member · Accepted solution]

Thanks Johan, he was indeed using Server Management Studio but I didn't notice the steps he took in creating the view. I will ask to get access to Server Management Studio in order to try and follow the steps you described.

[Former Member · Accepted solution]

There is no big differences if you create a view or copy query to Excel. It is not as flexible as the query pasting is you always create views to support your goal unless the report is mandatory almost daily.

[Johan_H · Accepted solution]

Hi Gordon,

That is not entirely correct. The significant difference here is that Microsoft Query as available in Excel, is not nearly as flexible as B1's query generator or MS SQL Management Studio's query tool.

Also the sql syntax is not entirely the same, and some complicated logic cannot be achieved nearly as easily.

Regards,

Johan

[Former Member · Accepted solution]

You misunderstood me. I never say to use Microsoft Query in Excel. Copy query from B1 or SQL management studio is not that difficult at all.

[Johan_H · Accepted solution]

Hi Gordon,

Ok, now I see what you mean ! Copy the result set to Excel.

But then, that is still assuming that only one person needs to do it less than once a day. In my case, for example, that wouldn't be a smart option, because it would mean building and/or correcting my Excel file all the time.

It is a simple matter of efficiency. I spent 2 hours one time to create the view, and tie it to an Excel file, and now, although I need it maybe once or twice a week, I just have to open the file, whereas otherwise it would take me 10 to 20 minutes to prepare every time. I have been using this file for the last 4 years.

So instead of 2 hours in 4 years, I would spend 4 years x 47 weeks x 10 minutes = 31.33 hours (and that calculation assumes that I use it only once a week)

Regards,

Johan

[Former Member · Accepted solution]

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?

[jbrotto]

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.

[Former Member]

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)

[Former Member]

Hi,

Please share what you want to achieve.

Thanks,

Gordon

[KennedyT21]

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-exc...

Hope Helpful

Regards

Kennedy

[Former Member]

Philippe,

Please check e.g below:

Using the ODBC Driver in Excel:

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

[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.