VReport general idea
Thorsten Hohage
thohage at genericobjects.de
Thu Feb 26 07:29:48 CST 2009
Hi Fabian,
thanks for this GREAT question, which will most probably be the first
item in our yet to write FAQ section on out german page :-)
On 2009-02-26, at 12:25, Fabian Eschrich wrote:
> But I have no idea how to start for a task like this:
>
> I want an table with statistical sums of our sales per month.
> It should look like this:
>
>
> Jan Feb Mar
> Invoices 10 11 12
> Amount 1200 1300 1400
>
> Product A 1 1 1
> Product B 2 2 3
>
>
> The data is stored in 2 tables. One table holds records for all
> invoices, the other table holds all invoice items (each position on
> the invoices).
>
> I dont know if this can be done by a query.
While databases are smart and SQL is cool sometimes you need to write
code, much code, long, longe code - especially when thinking about
such pivot operations like you asked for.
So let's start pretty simple (I'm using here tables from one of my
apps so your table and column names may vary)
So first of all you want to count objects ... while a simple count(*)
may work in many situations for this sample, I choose another way, so
we start wit a simple "say here!" question
select
'count' rtitle,
if(month(master_date) = 1, 1, 0) month1,
if(month(master_date) = 2, 1, 0) month2,
if(month(master_date) = 3, 1, 0) month3,
if(month(master_date) = 4, 1, 0) month4,
if(month(master_date) = 5, 1, 0) month5,
if(month(master_date) = 6, 1, 0) month6,
if(month(master_date) = 7, 1, 0) month7,
if(month(master_date) = 8, 1, 0) month8,
if(month(master_date) = 9, 1, 0) month9,
if(month(master_date) = 10, 1, 0) month10,
if(month(master_date) = 11, 1, 0) month11,
if(month(master_date) = 12, 1, 0) month12
from tr_erp_master
we list EVERY record of our "invoice" table and and if the date match
the criteria we use the value "1". Perhaps it would be a good idea, to
restrict this search with a "where ..." clause one a given year, but
something must be left for you ;-)
So, now we get a hugh table of "0" and "1" ... just like a little
historic binary informations.
On the next step we use the query above as a sub-query to create some
style of "virtual table"
select
max(rtitle) title,
sum(month1) januar,
sum(month2) februar,
sum(month3) march,
sum(month4) april,
sum(month5) mai,
sum(month6) june,
sum(month7) july,
sum(month8) august,
sum(month9) september,
sum(month10) october,
sum(month11) november,
sum(month12) december
from(
select
'count' rtitle,
if(month(master_date) = 1, 1, 0) month1,
if(month(master_date) = 2, 1, 0) month2,
if(month(master_date) = 3, 1, 0) month3,
if(month(master_date) = 4, 1, 0) month4,
if(month(master_date) = 5, 1, 0) month5,
if(month(master_date) = 6, 1, 0) month6,
if(month(master_date) = 7, 1, 0) month7,
if(month(master_date) = 8, 1, 0) month8,
if(month(master_date) = 9, 1, 0) month9,
if(month(master_date) = 10, 1, 0) month10,
if(month(master_date) = 11, 1, 0) month11,
if(month(master_date) = 12, 1, 0) month12
from tr_erp_master
)
and count all the "1" (and the zeros, but they didn't count) together
and get a nice line with the number of invoices written in each month.
The next step, is to gather the informations about the total amounts.
Same concept, but of course another statement
While I'm getting some "misterious"
13:53:10 Kernel error: 0x42512. Aggregative function cannot be used
this way.
exception when try to use a where clause on the "straight forward" sql-
statement we need to do ONE more step. So first we look for all
invoices, which have a date field - obviously here are some null value
records in my table (bad boy - I know :-( )
select 'amount' rtitle, master_date, tr_position.sales
from tr_erp_master
join tr_position on tr_position.parent_serial = tr_erp_master.idserial
where master_date is not null
then we use this result set, to build our first step table, same
concept as above
select
rtitle,
if(month(master_date)=1, sum(sales), 0) month1,
if(month(master_date)=2, sum(sales), 0) month2,
if(month(master_date)=3, sum(sales), 0) month3,
if(month(master_date)=4, sum(sales), 0) month4,
if(month(master_date)=5, sum(sales), 0) month5,
if(month(master_date)=6, sum(sales), 0) month6,
if(month(master_date)=7, sum(sales), 0) month7,
if(month(master_date)=8, sum(sales), 0) month8,
if(month(master_date)=9, sum(sales), 0) month9,
if(month(master_date)=10, sum(sales), 0) month10,
if(month(master_date)=11, sum(sales), 0) month11,
if(month(master_date)=12, sum(sales), 0) month12
from
(select 'amount' rtitle, master_date, tr_position.sales
from tr_erp_master
join tr_position on tr_position.parent_serial = tr_erp_master.idserial
where master_date is not null)
group by master_date
note this "group by" is not necessary by our logic BUT Valentia is
requesting it, otherwise you'll get a
14:21:50 Kernel error: 0x42508. In the SELECT clause you can use only
fields listed in the GROUP BY and aggregative functions.
so now next step again, embed this in the final query
select
max(rtitle) title,
sum(month1) januar,
sum(month2) februar,
sum(month3) march,
sum(month4) april,
sum(month5) mai,
sum(month6) june,
sum(month7) july,
sum(month8) august,
sum(month9) september,
sum(month10) october,
sum(month11) november,
sum(month12) december
from(
select
rtitle,
if(month(master_date)=1, sum(sales), 0) month1,
if(month(master_date)=2, sum(sales), 0) month2,
if(month(master_date)=3, sum(sales), 0) month3,
if(month(master_date)=4, sum(sales), 0) month4,
if(month(master_date)=5, sum(sales), 0) month5,
if(month(master_date)=6, sum(sales), 0) month6,
if(month(master_date)=7, sum(sales), 0) month7,
if(month(master_date)=8, sum(sales), 0) month8,
if(month(master_date)=9, sum(sales), 0) month9,
if(month(master_date)=10, sum(sales), 0) month10,
if(month(master_date)=11, sum(sales), 0) month11,
if(month(master_date)=12, sum(sales), 0) month12
from
(select 'amount' rtitle, master_date, tr_position.sales
from tr_erp_master
join tr_position on tr_position.parent_serial = tr_erp_master.idserial
where master_date is not null)
group by master_date
)
Let's now look at the last data needed. So far we use a fixed constant
'amount' and 'count' as the line title, now we build this a little bit
different, but still in the same concept behind
select sub_id rtitle,
if( month(master_date)=1, quantity, 0) month1,
if( month(master_date)=2, quantity, 0) month2,
if( month(master_date)=3, quantity, 0) month3,
if( month(master_date)=4, quantity, 0) month4,
if( month(master_date)=5, quantity, 0) month5,
if( month(master_date)=6, quantity, 0) month6,
if( month(master_date)=7, quantity, 0) month7,
if( month(master_date)=8, quantity, 0) month8,
if( month(master_date)=9, quantity, 0) month9,
if( month(master_date)=10, quantity, 0) month10,
if( month(master_date)=11, quantity, 0) month11,
if( month(master_date)=12, quantity, 0) month12
from tr_position
join tr_erp_master on tr_position.parent_serial =
tr_erp_master.idserial
where sub_id is not null
this query builds a list of all invoice items / positions used in the
invoices and instead of using "1" and "0", we now use the quantity of
this position.
Again embed this query and you'll have
(select
rtitle title,
sum(month1) januar,
sum(month2) februar,
sum(month3) march,
sum(month4) april,
sum(month5) mai,
sum(month6) june,
sum(month7) july,
sum(month8) august,
sum(month9) september,
sum(month10) october,
sum(month11) november,
sum(month12) december
from(
select sub_id rtitle,
if( month(master_date)=1, quantity, 0) month1,
if( month(master_date)=2, quantity, 0) month2,
if( month(master_date)=3, quantity, 0) month3,
if( month(master_date)=4, quantity, 0) month4,
if( month(master_date)=5, quantity, 0) month5,
if( month(master_date)=6, quantity, 0) month6,
if( month(master_date)=7, quantity, 0) month7,
if( month(master_date)=8, quantity, 0) month8,
if( month(master_date)=9, quantity, 0) month9,
if( month(master_date)=10, quantity, 0) month10,
if( month(master_date)=11, quantity, 0) month11,
if( month(master_date)=12, quantity, 0) month12
from tr_position
join tr_erp_master on tr_position.parent_serial =
tr_erp_master.idserial
where sub_id is not null
)
group by rtitle)
So with a lot of copy & paste, some grep search & replace and even
still a lot of typing we have our three different queries ready to go.
You may notice, that all use the same names and order of columns so
for the final step we can use the
UNION
command and make a really HUGH sql query
select
max(rtitle) title,
sum(month1) januar,
sum(month2) februar,
sum(month3) march,
sum(month4) april,
sum(month5) mai,
sum(month6) june,
sum(month7) july,
sum(month8) august,
sum(month9) september,
sum(month10) october,
sum(month11) november,
sum(month12) december
from(
select
'count' rtitle,
if(month(master_date) = 1, 1, 0) month1,
if(month(master_date) = 2, 1, 0) month2,
if(month(master_date) = 3, 1, 0) month3,
if(month(master_date) = 4, 1, 0) month4,
if(month(master_date) = 5, 1, 0) month5,
if(month(master_date) = 6, 1, 0) month6,
if(month(master_date) = 7, 1, 0) month7,
if(month(master_date) = 8, 1, 0) month8,
if(month(master_date) = 9, 1, 0) month9,
if(month(master_date) = 10, 1, 0) month10,
if(month(master_date) = 11, 1, 0) month11,
if(month(master_date) = 12, 1, 0) month12
from tr_erp_master
)
union
select
max(rtitle) title,
sum(month1) januar,
sum(month2) februar,
sum(month3) march,
sum(month4) april,
sum(month5) mai,
sum(month6) june,
sum(month7) july,
sum(month8) august,
sum(month9) september,
sum(month10) october,
sum(month11) november,
sum(month12) december
from(
select
rtitle,
if(month(master_date)=1, sum(sales), 0) month1,
if(month(master_date)=2, sum(sales), 0) month2,
if(month(master_date)=3, sum(sales), 0) month3,
if(month(master_date)=4, sum(sales), 0) month4,
if(month(master_date)=5, sum(sales), 0) month5,
if(month(master_date)=6, sum(sales), 0) month6,
if(month(master_date)=7, sum(sales), 0) month7,
if(month(master_date)=8, sum(sales), 0) month8,
if(month(master_date)=9, sum(sales), 0) month9,
if(month(master_date)=10, sum(sales), 0) month10,
if(month(master_date)=11, sum(sales), 0) month11,
if(month(master_date)=12, sum(sales), 0) month12
from
(select 'amount' rtitle, master_date, tr_position.sales
from tr_erp_master
join tr_position on tr_position.parent_serial = tr_erp_master.idserial
where master_date is not null)
group by master_date
)
UNION
(select
rtitle title,
sum(month1) januar,
sum(month2) februar,
sum(month3) march,
sum(month4) april,
sum(month5) mai,
sum(month6) june,
sum(month7) july,
sum(month8) august,
sum(month9) september,
sum(month10) october,
sum(month11) november,
sum(month12) december
from(
select sub_id rtitle,
if( month(master_date)=1, quantity, 0) month1,
if( month(master_date)=2, quantity, 0) month2,
if( month(master_date)=3, quantity, 0) month3,
if( month(master_date)=4, quantity, 0) month4,
if( month(master_date)=5, quantity, 0) month5,
if( month(master_date)=6, quantity, 0) month6,
if( month(master_date)=7, quantity, 0) month7,
if( month(master_date)=8, quantity, 0) month8,
if( month(master_date)=9, quantity, 0) month9,
if( month(master_date)=10, quantity, 0) month10,
if( month(master_date)=11, quantity, 0) month11,
if( month(master_date)=12, quantity, 0) month12
from tr_position
join tr_erp_master on tr_position.parent_serial =
tr_erp_master.idserial
where sub_id is not null
)
group by rtitle)
order by title
and here we go ... exactly the data you want to have in your
report ... NO vodoo, just typing and simple(?) sql ...
regards,
Thorsten Hohage
--
Valentina Technology Evangelist
generic objects GmbH - Leiter Solution Center Nord
More information about the Valentina
mailing list