| Author |
Message |
adavis
Senior Member
Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
|
   Topic: Selecting From Grouped Records
Posted: 15 Apr 2014 at 4:58am |
|
I have a set of records grouped by location_id. About 90% of the location id's return just one record.
What I would like to do is filter the results down so that I only view location id's that returned two or more records.
Has anyone developed a way to do this?
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 15 Apr 2014 at 5:19am |
it is a group select criteria
If you use a group summary you can use that result to filter on the entire group.
insert a count (or distinct count depending on your needs) of the field you are defing as teh 'count' at the group_id group footer.
It must be a group summary not a running total or variable formula.
Go into the select expert and change the formula expert toggle to use 'group criteria'.
use count(field,location_id)>1
Note that teh groups with a value of 1 are still part of the original report selection, will still appear in a group tree and will still be part of summary formulas. They will not be in Running total or while printing record formula.
|
|
IP Logged |
|
adavis
Senior Member
Joined: 30 Oct 2012
Online Status: Offline
Posts: 104
|
Posted: 15 Apr 2014 at 5:39am |
|
Got it to work. Thanks for sharing that information. That will make life much easier!
|
|
IP Logged |
|
hello
Groupie
Joined: 05 Feb 2014
Online Status: Offline
Posts: 85
|
Posted: 18 Apr 2014 at 9:26am |
Originally posted by DBlankit is a group select criteria
If you use a group summary you can use that result to filter on the entire group.
insert a count (or distinct count depending on your needs) of the field you are defing as teh 'count' at the group_id group footer.
It must be a group summary not a running total or variable formula.
Go into the select expert and change the formula expert toggle to use 'group criteria'.
use count(field,location_id)>1
Note that teh groups with a value of 1 are still part of the original report selection, will still appear in a group tree and will still be part of summary formulas. They will not be in Running total or while printing record formula.
THis is very helpful information. But...
Where is the 'group criteria' toggle? Are we talking about CR 2011? I don't see this toggle.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 18 Apr 2014 at 10:29am |
in XI
open the select expert
click on the "show formula" button
there is a Record select and a group select radio button option
The default selection depends on what field you have selected in the report when you open it or the field you choose to filter on in the Choose field step.
|
|
IP Logged |
|
hello
Groupie
Joined: 05 Feb 2014
Online Status: Offline
Posts: 85
|
Posted: 21 Apr 2014 at 4:32am |
|
OK... Found the Group Expert Formula editor in CR 2011.
(I think the differences are because CR XI and CR 2011 menus have changed.)
SO...
My formula in the Group Expert Formula editor is:
({Command.DAT_SEQ_NO} = NthLargest (1, {Command.DAT_SEQ_NO}, {Command.DAT_DBR_NO})) and message = 'YYY'
Just like the original poster of this thread, I want to ONLY print groups that meet the above criteria. Unfortunately for me, when the above formula is saved, the editor highlights the word message and displays the following error:
A number, currency amount, Boolean, date, time, date-time, or string is expected here.
NOTE: message is a variable I declared in a formula in the Record Expert Formula editor. All table variables are String and all declared variables are StingVar.
Would I also need to declare the variable called message at the group level? I was hoping that a variable could cross boundaries and be used in both the Record Expert formulas and the Group Expert formulas.
Edited by hello - 21 Apr 2014 at 4:36am
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 21 Apr 2014 at 5:02am |
|
the variable will not be available for use int he group select.
only results that can be garnered from a group summary function can be used. Basically if you cannot create the value by using the insert summary button you cannot use it in the select.
|
|
IP Logged |
|
hello
Groupie
Joined: 05 Feb 2014
Online Status: Offline
Posts: 85
|
Posted: 21 Apr 2014 at 8:13am |
|
Thanks DBlank. I now see what you mean. The formulas are processed at different read passes, I guess.
I may try to use the add command feature to create an SQL statement that gets the max of the table field before CR looks at it.
|
|
IP Logged |
|
hello
Groupie
Joined: 05 Feb 2014
Online Status: Offline
Posts: 85
|
Posted: 21 Apr 2014 at 9:07am |
|
DARN!
It looks like the add command won't work with SQL aggregates.
Back to the drawing board.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 21 Apr 2014 at 9:10am |
|
what exactly are you trying to do?
|
|
IP Logged |
|
|
|
