| Author |
Message |
Dr4ke
Senior Member
Joined: 09 May 2014
Online Status: Offline
Posts: 209
|
  
Topic: Section Expert based on Cross-Tab Total
Posted: 13 Aug 2014 at 1:20am |
|
Afternoon all,
I currently have a Crosstab report with Dates/Times on the left hand side, Computer Names on the top and the Memory Utilisation (for the computer on the date/time reported) as calculated fields.
In addition I also have Grand Total showing the maximum memory utilisation reached for each Computer.
How do I now display ONLY those computers which have over 95% in the Grand Total and then graph on those servers? Ideally I'd like to do it through Select Expert (as opposed to suppression) so it only shows the required fields in the graph, but there isn't an option...
|
 IP Logged |
|
|
|
Gurbs
Senior Member
Joined: 16 Feb 2012
Location: Ireland
Online Status: Offline
Posts: 216
|
Posted: 13 Aug 2014 at 3:59am |
|
I never worked with it before, but from my understanding, in the group expert, you can exclude entire groups after the data has been read. Maybe you could write a formula there excluding groups where the grand total is lower than 95%.
|
| IP Logged |
|
Dr4ke
Senior Member
Joined: 09 May 2014
Online Status: Offline
Posts: 209
|
Posted: 13 Aug 2014 at 4:06am |
|
Thanks Gurbs,
I'm not sure what the reference name, of the Grand Total field, is. Any ideas?
I.e. with a formula I'd have to put in {Database.[FieldName]}>95 but not sure what the field name would be for the grand total?
|
| IP Logged |
|
Gurbs
Senior Member
Joined: 16 Feb 2012
Location: Ireland
Online Status: Offline
Posts: 216
|
Posted: 13 Aug 2014 at 4:10am |
|
In your Crosstab, what is your calculation?
If it is a sum, then it would be something like
sum({table.field},{group table.field}) > 95
Again, I never worked with it or tested it, so not sure exactly how it should work, but give it a try.
|
| IP Logged |
|
Dr4ke
Senior Member
Joined: 09 May 2014
Online Status: Offline
Posts: 209
|
Posted: 13 Aug 2014 at 4:26am |
|
The actual crosstab calculations are Maximum (to then have a Grand Total showing the maximum). I've had a look at writing a formula in Group Expert but there is no ability to create a formula in there?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 13 Aug 2014 at 6:43am |
IN order to use a group select criteria your report structure has to use the grouping and an insert summary function.
You could mimic the CT structure as the report group structure however the group expert excludes groups after group caclulations are done so they groups would still appear in the CT.
|
| IP Logged |
|
Dr4ke
Senior Member
Joined: 09 May 2014
Online Status: Offline
Posts: 209
|
Posted: 13 Aug 2014 at 9:44pm |
|
Hi DBlank,
Sorry that has confused me a little and I'm not sure what to do?
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 14 Aug 2014 at 4:08am |
I was warning that the group select process would not do what you wanted to do and to not waste time on it.
I don't know that you can do what you are trying to do without a stored proc to identify your records before you get them into the report or some other process involved.
Perhaps a Command will suffice but It is hard to say without the specs and data source(s).
The problem you have is that you need all records to get your grand total which then gets you a percentage and then you want to exclude records.
This is better suited to stored proc.
It also has to do with dataq passes and when each item is calculated or generated in the pass. The CT in a report header or footer is created in a pass prior to the group select criteria being applied, so you cannot get the records excluded.
Maybe you can suppress entire rows in the CT but I do not think you can exclude them.
|
| IP Logged |
|
Dr4ke
Senior Member
Joined: 09 May 2014
Online Status: Offline
Posts: 209
|
Posted: 14 Aug 2014 at 4:19am |
|
Thank you!
I've tried a completely different approach (blank Report and Group Expert conditions) and I am very close to what I want.
Unfortunately you can't select by day on the report like you can with a Cross-Tab and summarising/grouping doesn't give the right results either.
You have given me some food for thought though, which I am very grateful for :-)
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 14 Aug 2014 at 4:40am |
If you are grouping on a datetime field you can set the group condition to be for a day (or any datetime increment). If youa re then using a group condition select on that grouping it is for 'the day'
|
| IP Logged |
|
|
|
