mpoole

Hi Peter / Charles

Apologies for the late reply – I’m back on this today. No – Charles’ proposal didn’t / won’t work for the reasons I previously outlined. The date range criteria needs to be applied server-side before the aggregation summing is done e.g.

SELECT
retailer_id, vendor_code, company_name,
SUM(num_sales) AS total_trans_count,
SUM(revenue) AS total_revenue
FROM vrpt_rev_daily_by_retailer_v1
WHERE trans_date BETWEEN <<DATE 1>> AND <<DATE 2>>
GROUP BY retailer_id

As such, the Search Builder doesn’t work either. Essentially what’s required is the ability to build an aggregate query dynamically then submit to the server to get the result set.

WPDA seems to be very much a client-side-centric tool so you can only work with the data once it’s been returned to the browser. In theory this should be a really simple requirement in order to produce reports which are more often than not based on date-ranged grouping of data.

Am I missing something fundamental here with WPDA?

Thanks,

Matt

Hi @charlesgodwin – many thanks for your response, however I don’t think that will work because I need the result of the query to be the aggregation based on the date range – so sum of revenue by retailer between any two given dates. This can only be achieved with the WHERE being specified before the GROUP BY.

Creating the above new view will simply give the aggregate value across the entire row set since day one.

That said, breaking down the original view might be the answer somewhere along the line.

I’ve looked at the Search Builder feature which may provide an answer, but there are a lot of rows, so a server-side solution is required really.