summary, group by and excluding a value?

Applications Grid / Reports
#1

hi guys, today i have new simple Q for those work with summary/group by things, i’ve been doing lot of tests and reached good idea about them so far…

i have 8 fields, group by field1 all fine - this field one is showing e.g. 10 records “YES” and 5 Records “NO”

now how i can make another group by and exclude the “NO” from the rest of the groupBy rules?

make sense? only records YES can apply to the next groupBy rules, if i just change the group-by view it will show them anyway…

more info: say you have 15 records, for people that are available/not available somewhere… now if this field is YES = available, then the other groupBy rules will make sense, as they talk about those available people in details… but if Not avavilable? why should i keep having in summary those “No” values and a “-” for their count for nothing…

group by more than one field is disaster, didn’t work as i expected, any other hint?

#2

what? nobody is using the group by or the summary? even no charts???

#3

Not sure I understand.,can you post with Some data and an example of the result you try to get?

#4

Hi Giu, dude, it is clear up there

assume we have this records in a db

id name available nationality can do origin status category
1 testname1 yes SB yes 10 active main
2 testname2 yes SN yes 1 disabled main
3 testname3 yes SR no 3 active third
4 testname4 yes ST no 2 inactive secondary
5 testname5 yes SU yes 1 active main
6 testname6 no - - - - -
7 testname7 no - - - - -
8 testname8 no - - - - -

as you can see, you can group by field “available” and have 5 items as yes, then 3 items as no…

now if the item is no, then no need to have him in the other group by rules… because, if not “available” then no other data is there to display…

this way, the group by rule by “can do” for example, will have “3 as yes” “2 as no” and “3 as -”

i want to exclude the field “available” if the value is “no” only in the other group by rules… as I want also at the first stage to know how many are available and how many are not…

hope it make sense…

#5

well, seems nobody is working on groupby thing???

i guess if we could select different data, or to “pre-filter” it then we can exclude values from viewing in summary and charts

but how we can do that? i really have this now as wall need to overcome it… there must be a way to exclude data from summary or conditional selection

i tried the sc_ select thing but couldn’t success :frowning:

#6

Sorry itsme3, too busy.

Just your question it’s a little weird :smiley:

You have the option of “GROUP BY IF()” and you can just to “SELECT DISTINCT available = no” to just return “yes” records, or concatenate SELECTS, there are a lot of possible solutions.

#7

hi Giu, you are the king of the weird stuff dude :slight_smile:

ok i couldn’t find anything related to “group by if()” in manual

also this select distinct thing where to use it? i have the fields as foreign keys getting primary keys from different table, hence, i’m using lookup part of the field to return the data instead of the id in the summary…

example:
first default groupby rule shows available, not available (group by status) it is ok, shows 5 available, 5 not available… that is fine…

when switch to second rule (group by nationality) then i have 5 people not available, so their nationality data is N/A…

in the groupBy field >> lookup part automatic >> i have this in the field {nationality}

SELECT nationalityid, nationalityname
FROM nationalities
WHERE nationlaityid = {nationality} AND nationalityid > 0 // this to exclude the N/A nationality... (N/A id = 0)
ORDER BY nationalityid

this returns the nationality name instead of its value (0,1,2,3…etc) so will show in summary as 5 people each one with its nationality, and keep a dash “-” for those “not available” people… moreover, if i removed the part “AND nationalityid > 0” then i will get 5 (N/A) and 5 devided into 1 and 4 (NL and OM)

please see this image and advise, i think it is more clear now to solve…

giu20140623.jpg