8 replies [Last post]
jrowny's picture
Offline
Joined: 01/08/2009
Juice: 297
Was this information Helpful?

I've had a few people request custom reports that Ubercart does not have built in. Fortunately, thanks to the Table Wizard module, it's not hard to create them!

Required Modules:

* Ubercart
* Table Wizard
* Views
* Schema (required by Table Wizard)
* Views Bonus (for Excel output)
* Views Calc (to do calculations on views)

Step 1
Install and enable the required modules. If you're not familiar with how to do this, you should probably start with some more basic Drupal tutorials.

Step 2 - Setting up our Tables
In this example we will create a "sales by country" report. The first thing we need to do is go to the Table Wizard page and let it know which tables we want to work with. The table wizard page is at admin/content/tw

Click "Add Existing Tables" and add uc_orders and uc_countries. Now views will be able to see these two tables.

However, we do need to create a "relationship" between these two tables. So click on "analyze" next to uc_orders and scroll down to billing_country field and select "available key" checkbox. Save it. Now we can use the billing_country as a relationship.

Go to content management -> table wizard -> relationships. Add a relationship between uc_orders.billing_country and uc_countries.country_id and click "add"

Step 3 - Creating the View
Now we're ready to add a view. Go to site building -> views and add new view. From the view type, select uc_orders. You can name it sales_by_country or whatever you like.

Select country_name and order_total as the fields. Add a filter for uc_orders.order_status to complete.

Now, finally, select the style and select Views Calc table. Check "display calculation" for order total. Select "sum". Select "country" as the grouping field.

DON'T FORGET TO SET THE "Items to display" to 0, aka Unlimited

That's it! You're done. you've created a custom report for ubercart!

Author's note: I'm going to try and add screenshots to this tutorial and maybe a webcast later.

r557's picture
Offline
Joined: 06/12/2008
Juice: 34
Re: Creating Custom Reports using Table Wizard

This is really good material. Only part i'm struggling with is trying to provide a date range.

The view is treating the created field just as an integer. Any ideas?

jrowny's picture
Offline
Joined: 01/08/2009
Juice: 297
Re: Re: Creating Custom Reports using Table Wizard

The date, as stored in the database, is Unix time, which is simply an integer of seconds past 1970.

Example: 1252367446 = Mon, 07 Sep 2009 23:50:46 GMT you can see this for more info: http://www.onlineconversion.com/unix_time.htm

Here's some info on getting unix time with PHP's time() function: http://php.net/manual/en/function.time.php

A simple date comparison between two unix times can be done with a "greater than" clause. To make the dates more or less acurate, just replace the last numbers with zeros. So replacing the last number with 0 makes it accurate to within 10 seconds, replace both and it's minutes. Replace 3 and it's 10 minutes. Replace the last 4 and you're down to hours. Make sense?

allanp's picture
Offline
Joined: 05/27/2010
Juice: 75
Lost at Step 2

All of the modules are installed and enabled, and I have added uc_orders and uc_countries.
That's as far as I can get.
There isn't an "analyze" next to uc_orders and if I click on uc_orders there isn't an "available key" checkbox.

Any idea what I may have done wrong?

Thank you

Shai's picture
Offline
Joined: 03/06/2008
Juice: 37
admin/content/tw

@allanp,

Go to admin/content/tw and then click on uc_orders. Now check the "Foreign Key" box under the "billing.country" field.

In short, when you click on the table name at admin/content/tw, that is the "analyze" referred to in the original article.

Shai

allanp's picture
Offline
Joined: 05/27/2010
Juice: 75
dawn breaks over marblehead

@Shai,
Thank you for clarification.
I had become so totally hung up on finding an "analyze" button that I could not move forward with it.
Now I've got it running.
Thank you,
allan

Shai's picture
Offline
Joined: 03/06/2008
Juice: 37
How to deal with data which is serialized

@jrowny

Thanks so much for the tutorial!

I want to use a field from uc_order_products which has serialized data. Do you happen to know how I can get the view to unserialze the data?

Thanks,

Shai

jrowny's picture
Offline
Joined: 01/08/2009
Juice: 297
Re: How to deal with data which is serialized

Have you checked out uc views? It might be able to do what you want more easily. I'm not familiar with how Drupal serializes data, but in general serializing data keeps it in a format which is easily usable. I'm sure php or drupal has some function for it.

Bartezz's picture
Offline
Joined: 04/18/2008
Juice: 104
Re: Creating Custom Reports using Table Wizard

I'm trying to do the same and have added uc_orders in admin/content/tw

But tw is not showing any integer fields in my case. Since billing_country is a mediumint(Cool I can't select it?
Why is my setup different? Or have I misconfigured something?

Cheers