Community

# Can Views do math and complex queries?

I need to generate a report based on Ubercart purchases that involves some math and some complex queries. Can Views do that? The Views 3 docs don't seem to exist, yet, and I want to find out if Views are the right approach before I'm up to my neck in them.

Can Views sum up the results of a query to get one number, then report that number?

Can it apply a mathematical formula to that number?

Can Views handle very complicated queries where it checks multiple tables to extract and process the right result?

Can Views execute custom PHP code to get information? Is that a good idea?

### Details, details.

Some samples of what I need:

Note: Wholesale customers have a special role assigned to them.

Note: All reports are based on sales over a date range that should be selected by the user.

For each product, the total count purchases for the given time period (Yes, Ubercart does this one already.)

Twenty percent of total sales (in dollars) to wholesale customers. I.e., add up the total sales to wholesale customers and multiply by 20%.

Total sales tax paid by customers.

Thirty-seven percent of total sales to non-wholesale customers of products with product (node) type "Book".

More notes:

{uc_orders} contains order records, including the (customer) user ID, the order ID, date purchased, and the status of the orders. Only orders where the status indicates that the purchase is complete should be counted.

{uc_order_products} contains the products that are in orders. Each record contains the order ID, the node (product) ID, the quantity ordered, and the actual price paid.

{uc_order_line_items} contains tax and shipping line items in orders. Each record contains the order ID, the title of the item, the type of the item (tax/shipping) and the amount paid.

{role} contains the role name and role ID for each role.

{users_roles} contains the user ID and role ID of each role assigned to each user.

{node} contains the node ID and node (product) type of each product.

{node_type} contains the node ID and node name of the different node (product) types.

I could do all this by writing a module that would build SQL queries and put the results into some tables, but maybe Views is a better way to do it. Either way, it's going to involve a series of queries with PHP to apply logic, or some really gnarly selects somehow joining six of those tables at a time.

So, is Views the way to go?

--
www.ztwistbooks.com. Math books that are actually fun.

### Not much yet

Ok, I've stumbled on some docs that make me think this might be possible, but I haven't proved it yet.

I also haven't found anything that lets you do that 20% operation. Maybe Views Calc?

--
www.ztwistbooks.com. Math books that are actually fun.

### I have the same issue and I

I have the same issue and I don't find the required documentation with examples.

I need to put prices in a content typre related products.
then I need to put the percentage of the shop.
Automatically the price+percentage shoudl be calculated itnoa view, but I have not clue how to implement it.

i'm wonder if there is a valid method to do it.

HyperD

### I'm part way there. Turning

I'm part way there.

Turning on aggregation allows you to sum up fields from multiple records.

There is a field called Global: Math Expression that can do some calculation. It can use the results of the other fields.

This gets me part of the way there.

My current problem is that there doesn't seem to be any way to go from an Ordered Products view to the custom product type fields. I think there needs to be a relationship, but I haven't figured it out, yet.

--
www.ztwistbooks.com. Math books that are actually fun.

### The relationship has been

The relationship has been added in the 7.x-3.x-dev version of Ubercart. #1565484: Product custom fields not available under Orders/Ordered Products type views.

--
www.ztwistbooks.com. Math books that are actually fun.

### I partially solved today with

I partially solved today with the module "Views Calc"

it gives a lot of errors, but i hide them, because what I see into the fields is correct.

But still an issue: when I write the content type: Article, inside I have the following fields:

Payed Price
other costs
Charge Percentage

I would like to add directly into the content type a field that shows immediately the result of this formula:

( payed Price + other costs ) + ((( payed Price + other costs ) * Charge percentage) / 100)

Giving immediately the total price before the content is saved.
But I think this is not possible :-/

HyperD

### Did you try the Global: Math

Did you try the Global: Math Expression field in your View?

This is for Drupal 7 / Views 3.

Or, are you talking about when the content itself is displayed (not via the View)?

If that's the case, you can do what you want easily enough by Theming the content or using a module with hook_node_view(). You might even use http://drupal.org/project/computed_field. Of course, any of those solutions require a little programming....

If you want the field to be updated dynamically (before saving), you have to use Javascript or AJAX. I would recommend AJAX.

--
www.ztwistbooks.com. Math books that are actually fun.

### I didn't used Global: Math

I didn't used Global: Math Expression because when I set it up into the "fields" (view set up) and I set up the Math expressions, it returns me always 0 while the fields have not 0 value.
This because in one field, I have the value, but the Global: Math Expression detects always 0.
This field is used into multiplications and it "deletes" any result.

HyperD

### You have to tell Views to

You have to tell Views to interpret that as a number. Otherwise, it interprets it as a string, which has a value of zero.

Edit the fields you use for input to your math formula, and and set the formats to "numeric".

--
www.ztwistbooks.com. Math books that are actually fun.

### But it is Number. float with

But it is Number. Integer

HyperD

### Yes, but you still have to

Yes, but you still have to set the format for the field to make it work.

--
www.ztwistbooks.com. Math books that are actually fun.

### I think using Views here is

I think using Views here is the wrong idea. You want custom code.