No matching log data

[jharris]

I'm running into the problem (yet again) where a dataset returns records but the report always shows 'No matching log data'. This is m custom query to show how many times per category users clicked through the warning page:

select `user` as user, catdesc, count(*)as pair_count from $log where $filter and ( ( lower(trim(`msg`)) LIKE lower('%warning%')) AND (`action` = 'passthrough')) group by t10.user, catdesc

I expect to see:

UserA  Shopping   12

UserA  Social Networking 20

UserB Shopping 33

etc...

This works in dataset testing, but never on the report. The thing I've been focusing on is the group by user. If I just put user, the dataset throws the "must be used in the group by or an aggregate statement warning" but it references t10.user instead of just user. If I understand SQL correctly t10 is a temp table the query uses. If I ignore it and leave group by with 'user' instead of 't10.user'. the report is still blank. I'm at a loss here. Can anyone see what's wrong?

[AtiT]

Hello,

If I were you I would modify your dataset to something like this:

select `user` as user_src,        `catdesc`,         count(*) as pair_count from $log where $filter       and lower(`msg`) LIKE '%warning%'       AND (`action`='passthrough') group by user_src,          catdesc

It seems to me that you not need to use so much brackets.

I am not sure whether the trim() function s needed where the LIKE is used, maybe some performance tweaking? I don't know.