Exclude (supress) based on complex formula

[MVisconte]

Hi folks, I hope someone has some insight... my brain is getting bent on this one.

I am re-writing someone else's report. The report documents outages for qualifying circuits under associated networks:

Network Name (Group)
Circuit ID (sub-group), Sum(outage duration)
Individual Records (hidden, drillable)

Only records for circuits whose outage durations are larger than a threshhold should be in the report.

Currently, she's just supressing sub-groups based on a summary calculation > Threshhold calculation. The "empty" groups as well as supressed sub-groups still show up in the Group Tree, and curious users can still drill into the sub-group and associated records. I have further supressed the record-level details, which is better, but not good enough, yet.

I would LIKE to include/exclude records based on whether the cct's total outage is greater than a cct threshhold. The problem being, I need to EXCLUDE records based on a sum of the group -- and in order to accurately calculate the sum, I have to read (and mangle) the outage duration (i.e., tickets w/ outages extanding beyond the time period's boundaries). Catch-22: I have to know the answer before I exclude the records, and in order to know the answer, I have to read the records so I can exclude them. Argh.

I calculate whether a group's records should be excluded, but that is based upon a conditional sum, which can't be put in the select statement, hence, the groups can still be seen in the Group Tree. I want to find a way to exclude the records or supress their Group Tree appearance. I don't think it's possible to do the latter, so... anybody have ideas for the former? When I try to use the Sum(Outage, Cct ID) in the select statement, I get the standard "Summary / Running Total field could not be created."

Like I said, my brain is bent on this one.





Notes:
The threshhold is the number of hours in a period (i.e. 1 month = 744 hours) multiplied by a value associated w/ a particular circuit, i.e., Cct A, w/ 1% threshhold, has to have more than 7.44 hours outages before it shows up.

One cct has a 1% outage threshhold, another may have 20% outage threshhold. The first cct may have 12 records, w/ outage totaling less than the 1% (7.44 hours) for the period -- those records (and cct) shouldn't show in the report.
The second circuit may have one (or more) tickets and if the total outage time is greater than the threshhold, the group, sub-group, and individual records should be available.

This is my brain:

This is my brain, on Crystal: :-D

This is my brain, on THIS REPORT: #$@#%$%^@#^@#$%@!&^^^*

 

[lbass]

You need to use the group select, not the record select:

Sum({Outageduration},{CctId}) > {@threshold}

//where {@threshold} equals:

744*{circuitvalue} //or whatever your criteria are

This will display only those circuits that meet the criteria, along with related details. However, all circuits will appear in the Group Tree on the left, since they underlay the displayed data. There is a way, though, to mask the items in the Group Tree (see Jim Broadbent's solution in thread149-512615). You also need to use running totals to do calculations only on the displayed data--more usual summaries, like counts will pick up the non-displayed data.

-LB

 

[MVisconte]

Thank you, LB, I'll try the group select again. I attempted it before and couldn't get it to work, and figured it was an extension of the woes being experienced through the Record Select attempt.

I'll also print Jim's post and review it. Much thanks, and I'll report back on my results.

Marc

 

[MVisconte]

Reporting back...

I tried the group select again, and now I remember WHY it wouldn't work... "This formula cannot be used because it must be evaluated later." Sigh. Because it isn't just a simple sum(), it's a Sum({@PracticalOutage}, {CctId})

Where @PracticalOutage takes into account that some tickets may be open across the report-time-period boundaries and corrects to just look at the time-in-boundary:

If {Ticket.TimeIn} > {@ReportEnd} then
// I also test for null above
TicketClose := {@ReportEnd}
else
TicketClose := {Ticket.TimeIn}

If {{Ticket.TimeOut} < {@ReportStart} then
TicketOpen := {@ReportStart}
else
TicketOpen := {Ticket.TimeOut}
}
// then
@PracticalOutage := (TicketClose - TicketOpen) / 3600


The @PracticalOutage is calculated on a per-ticket basis, then summed in the CctId to get practical outage duration, for that circuit, for the given time period. Catch-22...

Any magic that I'm forgetting to sprinkle on for this one? (&quot;What is it?&quot; &quot;Magic Server Dust.&quot; &quot;Magic Server Dust?&quot

Also, the Broadbent group-name supression is an interesting approach, but won't work for the same reasons given above... I was less clever - I just wanted to set the group name to {null} or &quot;&quot; and have all un-wanted groups &quot;become invisible&quot; and get lumped under the one &quot;empty&quot; group in the group tree.

So, I'm back to where I was. Any other suggestions?

Anybody? Anybody? (Ben Stein)

 

[MVisconte]

Update:

After re-reviewing the Evaluation Time and Two-Pass Report Processing Model pdf from Xtal Decisions, I took my main formula and split it up greatly, and re-wrote it in portions prepending the &quot;whilereadingrecords;&quot; statement. Now, I have a couple of simpler formula to calculate &quot;practical&quot; start and stop times for a ticket and a very simple formula that just subtracts the two (and divides by 3600). I re-wrote the threshhold calculation formula yesterday, and that's available on a &quot;whileprintingrecords;&quot; basis. NOW, the
Sum({@PracOutageDuration}, {CctID}) > @Threshhold
will work in the Group Select formula:
Sum({@PracOutageDuration}, {CctID}) > @Threshhold

I still get the unwanted groups in the group tree (I figured that the Group Select would get rid of that problem!?), but currently, all of the associated records are blank -- seriously... all that shows are the field names. Strange.

Anyway, I'm one step closer... Instead of supressing groups from the Format Section, Supress (formula), I can do it from the Select Groups. Anybody see any reason my groups are still there?

 

[lbass]

I am not familiar with methods for distributing reports to users, so I'm not sure this is possible, but if you can control the view available to users, you could eliminate the group tree altogether (just uncheck group tree) and create parameter(s) which conditionally suppress different levels of groups, e.g., {?ReportLevel} could be a prompt for showing a &quot;summary&quot; or &quot;detail&quot; view. In format section, go to the group or detail section, check suppress, and click on the E-2 button and enter:

{?ReportLevel} = &quot;summary&quot;

This will give users access to the information they would get through drilldown on the group tree (although on an entire-report basis, not on a per ID basis), and only the records you have group-selected would be available for viewing. The question is whether the user would have the ability to toggle the group tree back on or not in the distributed report, and I don't know the answer to that--but I bet someone does!

As I mentioned before, the group tree shows the non-displayed groups, because those groups are still in the dataset for the report. You can test this out by comparing a distinct count of circuit IDs and a running total of circuit IDs. The distinct count will count the non-displayed records, while the running total will not count those records which were not group-selected. (Although running totals will count suppressed records unless the suppression criteria are built into the running total).

-LB

 

[MVisconte]

The report format has to be the same as our other reports (some 20-odd, repeated across 5 locations), so turning off the tree wouldn't work... by definition, we have to have it.

Oh well.

Oh, BTW, I just took the Crystal Reports Qualification test at Hammerman Associates' website. Even tho' I can't get this problem nailed down, I still pulled 33 out of 35... a 94%. At least I have that much consolation. ;-)

http://www.hammerman.com/test.cfm

It's free for the next couple of weeks, then they're gonna start charging. Take it, and they'll send you a cert if you get > 85%.

 

[lbass]

Congratulations! P.S. You could just change the other 19 reports

-LB