Automate your reports with the ODBC driver
Applies to: All versions
The ODBC driver is probably my favorite WebTrends feature. With the ODBC driver I can create a 1 click report in Excel that I can distribute to anyone in my organization. They don’t have to log in, they don’t have to figure out which profile has the report they want, they just need to click on the link and open a nice familiar Excel file that tells them exactly what they need to know.
The easiest way to use the ODBC driver is to use a program called Datalinks. But, if you’re like me and you don’t have a few thousand extra dollars lying around you can do much of what you need to do directly from Excel for free. It just takes a bit of hacking around in Excel. Here is what you need to do (this works in Excel 2003, it is probably slightly different in other versions) :
- Install the ODBC driver. If you’re on 8.0 or 8.5 you can get to it from the “install components” link in the left nav of the admin area. If you don’t see it you may not have rights to use it. Ask your administrator to set you up.
- In Excel go to Data –> External Data –> New Database Query. It will open up a data connection box. Choose new connection and OK. Then find the WebTrends ODBC driver on the list and select it.
- Next you’ll have to fill out your connection details. If you’re using On Demand they should be filled in, if you’re using your own software then you can fill in the default login URL and your username and password to connect. Click “refresh” and choose your profile and template from the dropdowns. Click OK to save.
- Now Excel will present you with a list of the available reports. The names will approximate the names you are familiar with from the normal interface, but they’ll be a bit different. Choose the report you want and click next. (NOTE: you can only pick ONE report. Sadly, the ODBC connection doesn’t do Joins.)
- Continue through the wizard. You can filter and sort on pretty much whatever you want.
- When you finish the wizard Excel will crunch for a while (the ODBC connection is not super speedy) and then it will ask you where you want to place the data in your spreadsheet. Choose a cell and the data will automagically appear. TaDa!
- If you click in the data area and then click on the exclamation point in the External Data toolbar (or click on Data –> Refresh Data) Excel will reach back out to WebTrends and pull down fresh data. Neato!
So now the bad news. This is cool and all, but WebTrends has practically snatched defeat from the jaws of victory. The fatal flaw is that the ODBC driver doesn’t recognize “parameters” as used by Excel. Excel Parameters allow you to use values in a cell on the spreadsheet in the query itself. This really comes in handy when you’re trying to change dates. Luckily, there is a workaround of sorts in the form of Relative Date Macros. Relative date macros allow you to query for data for “yesterday”, or “last week” or “last month”. Here is how to use them in Excel:
- Perform the steps 1-5 above. On the last screen of the wizard choose “edit this query in Microsoft Query”. If you’ve already retrieved the data go to Data –> Import External Data –> Edit Query.
- You’ll get a Microsoft Query window. The easiest thing to do is press the “SQL” button in the toolbar and type in a “WHERE” clause after the existing SQL query. Here is an example query with a where clause that uses a relative date macro:
SELECT Browsers_0.Browser, Browsers_0.Hits, Browsers_0.Visits, Browsers_0.TimePeriod, Browsers_0.StartDate, Browsers_0.EndDate FROM Browsers Browsers_0
WHERE (Browsers_0.TimePeriod='%start=day-5;duration=day+1%')
This query gets the browsers report for a single day 5 days ago. To see all the relative date macros search for “relative date macro” in WT help, it’s in the first link. - After you modify the SQL and close the little SQL window, close the Microsoft Query window as well. This will bring the data back into Excel. Format and you’re done!
16 comments
This is great, thank you for the primer.
I’d like to design an XLS that I can distribute to a few Corp Communications folks… but I’m worried about the set up that needs to happen before the spreadsheet will function correctly. Obviously the ODBC driver needs to be installed on the Corp Comm user’s machine, but is that all? Will they also need to define the datasources I’ve created using the “New Database Query” wizard in Excel?
Yes, if you want them to be able to update it themselves they’ll need the ODBC driver and a datasource with the same name on their machines. This isn’t really a big deal, but in most cases I find it easier to update the report myself and then distribute it.
As a user of DataLinks, I am a huge fan of that program for making this all happen very easily, including the date changes. It’s frustrating that DataLinks’ price has been going up over time instead of down, because if it were more available I think most WebTrends users would own it – they’d be crazy not to. I would have to say that even at the high price, DataLinks can easily pay for itself … but I don’t know if I could convince my management of that if I had to buy it today.
Anyway, I am really glad to see this post. Where I work, end users hardly ever see the WebTrends interface any more. They use Excel directly, in big workbooks with lots of sheets connected directly to WebTrends via ODBC, and the real action happens in what we call the “manipulation sheets” that pull from all the various data sheets. WebTrends becomes a data crunching engine and Excel produces the presentation layer: the graphs, the joins and merges and lookups … in other words, the dashboards and scorecards.
Boris, suppose I’ve got 5 different datasources used in my XLS… is there a way to copy those over to the users’ machines without physically recreating them through the ODBC manager?
bPap — to tell the truth I don’t really know, but maybe. When you create a connection you create a “File data source”. These live in C:\Program Files\Common Files\ODBC\Data Sources. I believe it also creates a “machine data source” at the same time. I think the data sources without stars on the data source pick list are file data sources. If you use one of these for your connection you may be able to just copy the file over to the same location on another machine. Or maybe not… I’ve never tried this to confirm that it works. If you try it and it works let me know!
Once you have a connection established to the WebTrends data source itself, all WebTrends profiles will be reachable through that conduit. The profile GUID will be part of the connection string embedded in each table, and that GUID will change from table to table, but they will all use the same connection.
You’ll have to have multiple datasources if you have different machines with WebTrends on them. Or other kinds of datasources, like a corporate customer DB.
Thank you so much for going over some of the things ODBC can do with WebTrends. Since my company can’t justify using DataLinks, I’m using Excel (not a problem), but with ODBC I can work smarter, not harder. Do you have any suggestions for someone who is looking to learn more about ODBC and Excel usage?
Terrible30 — Dive in head first! Pick a relatively simple report to automate first and bang your head against it until it works. It will probably take a while to get the first one to work, but once you do the second one will be easy. ChrisG outlines the right approach for report design above: pull the data into behind the scenes sheets and then reference that in your presentation layer. Use this post as a guide for getting data into Excel. Once it’s in Excel use The Goog as a guide. I guarantee someone out there has already solved any Excel problem you might have.
I tried out the usage of Relative Date Macros, but somehow it did not work. Every time I inserted a Relative Date Macro like
WHERE (Browsers_0.TimePeriod=’%start=day-5;duration=day+1%’)
MS Query did not recognize the phrase between the % as a macro but it would recognize it as a value. I eagerly checked the syntax but I have no idea why this won´t work
bPap: You can bypass datasources alltogether by writing VBA Macros. Using ADO dataobjects and connecting directly through the driver even improves performance somewhat. This technique requires some programming skills but will eliminate the need for creating datasources on the client, only the driver is required. Also the strain on the report database can be relieved by doing multiple queries with each connection.
A connectionstring would look something like this:
Const ODBCconn As String = “Driver=WebTrends ODBC Driver;DATABASE=59s7l1i7yuiio;SERVER=webtrends.server.com;PORT=80;AccountId=1; UID=uID ;PASSWORD=pAss; ProfileGuid=roG15BGu4556.wlp;SSL=0;”
How do you get around the database architecture which means that if you want daily information you have to rerun the data for each day?
I’ve done this in access and if I want 30 days of data then I have to loop through a list of data populated by the clients request and run the sql call to WebTrends 30 times?
Have you managed to do it successfully another way ?
Miles
Yes. The following code doesn’t work for me either.
WHERE (Browsers_0.TimePeriod=’%start=day-5;duration=day+1%’)
This can be my fav. post on your blog. I couple of things i want to know! My first query is this a alternate of Datalinks?
Can i refresh my reports using ODBC? Actually i have set up a number of profiles in my Datalinks report. My license is going to be expired and i haven’t budget to re license it. So i am trying to find the alternative for this. I tried the way you wrote but was unable to do it to change date and time, For example i want report for September 2008, how can i do that in microsoft excel 2007? Or can i write and execute the queries the way i am doing it thro’ Datalinks!
Please share if you have idea about similar tools like Datalinks.
you could also try InfoCaptor to connect to Excel or any ODBC datasource.
I suspect the link / site quoted for Datalinks is the wrong one and should be http://www.businessintelligencellc.com/products.html
Thank you! (I think this new DataLinks site needs to do some SEO!) I’ve changed the link in the post.
Leave a Comment