Announcement

Collapse
No announcement yet.

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

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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..

    Comment

    Working...
    X