Count CHG.ref_num not counting properly

[Legacy User]

Hello all !!!

 

i would like to apply to your experience, in order to progress with a Report development!

 

In this particular case, we need to count the total of CHG.ref_nums and ISS.ref_nums from some select statements.

In theory, just adding the Measure defined as =Count([Chg.Ref_num]) should return the proper counter , but instead, the resultes returned are completely far away from the reality.

 

I mean, for a particular time range, my Statement returns 10 different Chg.Ref_nums values/rows, but when i set a new column on my TOTAL COUNTERS report sheet, with the measure Total Chg Ref Num , the value is completely wrong, for example " 4200" ... or " 78" for the next row ...

 

I've also created a new variable, set as "=Count([chg.persid]), but the result is the same!

 

Any idea or hint?

 

Greetings.

Ivo

[JEROME MAYER]

Hi, Can you then share your select statements for the community to have a look.

/J

[Legacy User]

Sure!

 

Translating to SQL, i have:

SELECT

  chgalg.analyst_combo_name,

  chg.chg_ref_num,

  chgalg.change_category_symbol,

  chgalg.change_status_symbol,

  chgalg.tenant_name,

  PdmSeconds(chgalg.time_spent),

  chgalg.time_stamp,

  chgalg.change_open_date,

  chgalg.change_summary,

  chgalg.change_priority_symbol,

  chg.modified_date,

  chg.resolve_date

FROM

  chgalg,

  chg,

  cnt

WHERE

  (

   chgalg.time_stamp  >=  PdmToday(-1, -7)

   AND

   cnt.userid  =  chgalg.analyst_userid

   AND

   chgalg.change_id  =  chg.id

  )

[Legacy User]