how to add a condition to the query of a summary??

Applications Grid / Reports
, ,
#1

Hello everyone,

I’m doing this report and i need to add a condition to my query. But it takes a field (user input in search)as a variable and that why i can’t put this condition to the view from which i have build this summary-grid.

There are three groupings, by company->Tv->Spots. I need to show only the new companies, which hadn’t had any tv commercials (spots) the previous month.

Here is my basic query (the view):
** select date_format(blloku.dataora,’%m/%d/%Y’) AS Data,
date_format(blloku.dataora,’%Y-%m’) AS Muaji,
kompania.kompania AS kompania,
produkti.produkti AS produkti,
televizioni.idtelevizioni AS idtelevizioni,
televizioni.televizioni AS televizioni,
spoti.spoti AS spoti,
count(ngjarja.idngjarja) AS countall
from ((((((kompania join marka on((kompania.idkompania = marka.idkompania)))
join produkti on((marka.idmarka = produkti.idmarka)))
join spoti on((produkti.idprodukti = spoti.idprodukti)))
join ngjarja on((spoti.idspoti = ngjarja.idspoti)))
join blloku on((ngjarja.idblloku = blloku.idblloku)))
join televizioni on((televizioni.idtelevizioni = blloku.idtelevizioni)))
group by kompania.kompania,produkti.produkti,spoti.spoti **

…and here it is the condition i want to add:

** where (select count(spoti)
from klient_i_ri
where klient_i_ri.Muaji = DATE_FORMAT({Muaji} - INTERVAL 1 MONTH, ‘%Y-%m’) = 0; **

…therefore i need to find a way to take the {muaji} value before executing the summary.

Hope you’ll give me some answers…
Thanks in advance,
Regards

#2

I tried this macro:

Ex. 1: It adds the contente between the quotes (") in the grids select.
if (empty({sc_where_current}))
{
sc_select_where(add) = “where campoX > [global]”;
}
else
{
sc_select_where(add) = “AND campoX > [global_variable]”;
}

my code:
if (empty({sc_where_current}))
{
sc_select_where(add) = “where (select count(spoti)
from klient_i_ri
where klient_i_ri.Muaji = DATE_FORMAT({Muaji} - INTERVAL 1 MONTH, ‘%Y-%m’) = 0”;
}

…in the ‘groupby’ event in summary but it’s for during grid execution time to add a field/condition to the search WHERE clause and here it gives a error…