How to generate and use a counter or switch 2

[PhilB2]

I've tried many times, for many purposes, to create a counter, or some kind of switch, and never succeeded. I'm unsure of the concepts involved: Where to set up the counter (a formula field or a numberVar declaration), where to place the formula field if anywhere, how to increment the counter, etc. I'm similarly in the dark regarding setting up and using a switch of some kind (stringVar?).

Today, in a Detail Section within a Group Section, I created a formula field 'CubLabel', whose formula is:

"Cub:"

It appears on the same line as another formula field, CubTitle.

If CubTitle is going to appear more than once in the Detail Section, I want CubLabel to print "Cubs:"; if it is going to appear only once, I want CubLabel to print "Cub:".

I attempted to use an 'if then else' statement, testing NextIsNull({@CubTitle}), but it did not work. I tried PreviousIsNull just to see if it would work, but it did not.

 

[CR85user]

Try the following:

//{@Cublabel}
if count({cub.title},{group.field}) > 1 then 'Cubs:' else 'Cub:'


'J

 

[PhilB2]

Oddly enough, there is a field available in Report Fields in the formula editor; possible it showed up after I had entered your formula:

Count ({VTMTRACK.TITLE}, {VSTATEHISTORY.SYSTEM})

This field is preceded by the Sum symbol, and is not visible in the Field Explorer.

I need to test your formula further to make sure it works under all conditions; in the meantime, there are two other issues:

1) When there is more than one CubLabel, I need to suppress all but the first. I seem to have done that using a formula next to 'Suppress if duplicated' in the Format editor, but I need to test it further:

not (PreviousIsNull ({@CubTitle}))

2) In the same Detail line, I need to suppress CubDeveloper, whose formula is a database field, whenever it is equal to the previous CubDeveloper. I tried your formula and a few other things. Here again, one approach might be to set up a counter, if I only knew how.

 

[CR85user]

You can use the same logic as the count to suppress all except the last Cublabel.

//Suppression formula
not(maximum({@Cubtitle},{VTMTRACK.TITLE})

Or similar. This may need the fields reversing dependant upon your sort (Or if the vtmtrack.title is the grouping?)

'J

 

[PhilB2]

For the following:

not(maximum(count({@Cubtitle},{VTMTRACK.TITLE})))

I get the following message:

There must be a group that matches this field

with '(count({@Cubtitle},{VTMTRACK.TITLE})' highlighted by Crystal.


For the following (that's available in the Formula Editor, preceded by a summary symbol):

not(maximum(count(Count({VTMTRACK.TITLE},{VSTATEHISTORY.SYSTEM}))))

I get the following message:

This field cannot be summarized.

with 'Count({VTMTRACK.TITLE},{VSTATEHISTORY.SYSTEM})' highlighted by Crystal.


Quite apart from whether there is a direct solution such as you suggested possible, I'd still like to know the basics of declaring a counter and using it. (I'll try to find that information.)

 

[CR85user]

For:

not(maximum(count({@Cubtitle},{VTMTRACK.TITLE})))

Replace {VTMTRACK.TITLE} with the field you are grouping by (I just had to havea guess as I didn't know your report structure)

With:

not(maximum(count(Count({VTMTRACK.TITLE},{VSTATEHISTORY.SYSTEM}))))

You are performing the count function twice.

Again ensure that the field used is the one you are grouping by.

For counting rows and such you can use various methods for valuing fields. One of the easiest is in the example above.

I have a report which can show any number of results for each group and can have start and end dates over a large range.

I wanted to only show results showing 3 or more entries with at least one entry in the last full month and to suppress any result with a month date matching the current month.

To acheive that I added suppression on the following basis:

Grouped on event.time with frequency of once per second.

//Suppression formula
//Don't include if no entries in last month
(maximum(count({event.time},{unique.ID}) < minimum(lastfullmonth)) or
//Don't include if less than 3 records per Unique ID
(distinctcount({event.time},{unique.ID}) < 3) or
//Don't include if within current month
month({event.time}) = month(currentdate)

This shows a few ways to use the count function to affect display etc.

You can also use running totals / summaries to similar effects although each has its best type of use. Check the FAQ in this section for summaries for more info.

The idea of the logic demonstrated below:

Count

Returns a numeric value indicating the number of occurrences (count) of the supplied
database field or formula.

Count (f)
f – the database or formula field to summarize for the entire report.

Count (f1, f2)
f1 – the database or formula field to summarize for a report group.
f2 – the database or formula field indicating the group you wish to summarize for. An existing
group on the report must be based on this field.

Count (f1, f2, s)
f1 – the database or formula field to summarize for a report group.
f2 – the database or formula field indicating the group you wish to summarize for. An existing
group on the report must be based on this field.
s – a string value indicating how often to “change” the summary for Boolean, date, or datetime
grouping.

Examples:

Count({Sales.Amount})

returns the count of sales amounts for the entire report (in essence, this is the number of
records on the report).

Count({Sales.Amount}, {Sales.Date}, "monthly")

returns the count of sales amounts for the sales date group, summarized for each month
(in essence, this is the number of records in the group).

NOTE The Count function will count every record where the supplied field contains a nonnull value. If
any occurrences of the supplied field contain null values, those records won’t increment the count.

'J

 

[PhilB2]

I think I need to redefine my goals:

I have several Detail sections that comprise one printed Detail section.

When Detail field 'CubLabel' is printed more than once, I want the first occurrence to print, and succeeding occurrences to be suppressed.

I also want the field to print as 'Cubs:' when Crystal detects multiple records, and 'Cub:' when Crystal detects only one record.

I think that goal cannot be met by involving a group qualifier, because there can be multiple occurrences of the Detail section, within which 'CubLabel' will occur or not, and occur as multiple records or not.

Example of printed report:

To Production Assurance Certification [Group 1]
Data Warehouse [Group 2]
SSR 11762 [Title] [Detail section a]
[Description]

Ingenium [Group 2]
SSR 09883 [Title] [Detail section a]
[Description]

SSR 11680 [Title] [Detail section a]
[Description]
Cubs: [Cub Title] [Detail section b]
[Cub Title]

 

[lbass]

I think you should post some sample data of how your report is actually displaying compared to what you are showing in your last post. For one thing a detail b section will not show multiple values for the same record, since only one row is displayed per detail section_a and _b. Unless that is part of what the issue is--how to suppress detail_a when some value repeats in the next record?

-LB

 

[PhilB2]

Thanks, lbass:

My report is displaying exactly as I showed in the last post, except that 'Cubs:' is not suppressed in the second line:

SSR 11680 [Title] [Detail section a]
[Description]
Cubs: [Cub Title] [Detail section b]
Cubs: [Cub Title]

I suppressed the preceding detail section (actually overlapping sections, to be precise) containing the first two lines, allowing the section containing 'Cubs:' to print twice in succession.

I used brackets purposely so as not to post sensitive data, although I guess it would have been clearer had I used made-up data without brackets.

I hope I've answered your question clearly.

 

[lbass]

What was your suppression formula to suppress the detail_a section conditionally? Is your goal just to suppress the second "Cubs" and to conditionally add the "s" to Cub? If so, you should be able to insert a running total {#cntwingrp} that does a count of any recurring field, evaluates for each record, and resets on change of group #2. Then use a conditional suppression formula on the Cubs field (format field->suppress->x+2) like this:

{#cntwingrp} > 1

For the Cubs field, you should be able to use a formula:

if count({table.field},{table.group#2field}) > 1 then "Cubs" else "Cub"

-LB

 

[PhilB2]

The suppression formula to suppress the detail_a section conditionally is:

not ({VTMTRACK.TITLE} in {VREQUESTBASE.TITLE}) and
({UBR_BADGIR.TS_TITLE} = previous({UBR_BADGIR.TS_TITLE}))

The first condition is the same as the one that determines whether the Cubs detail section prints, and the second is true when the Cubs detail section prints.

As for a running total that is reset, I'll try that again. I think I've made mistakes setting it up and using it, and run into errors trying to qualify it as belonging to a group.

I'll communicate the results.

 

[PhilB2]

P.S. I figure I cannot attach a document unless I have a website from which to do so (and near as I can tell, that is not an option for me). Is that correct?

 

[PhilB2]

Thanks, lbass!

I used the Crystal Help to find out how to set up the running total.

I used the running total not only for the suppression formula, but also as the formula to decide whether to print 'Cub:' or 'Cubs:'.

The count() formula did not work in this case because I suppress the 'Cub' title when it matches the BADGIR title; therefore the number of Cub titles printed sometimes does not match the number counted. (I noticed that the running total dialog box offers the opportunity to use a formula; perhaps the running total would have worked with a formula.)

 

[reycons]

Table 1

EmpID Logindate Undertime_From UnderTime_To
1 8/27/08 8:00 AM 10:00 AM
2 8/28/08 10:00 AM 12:00 PM


Table2

EmpId Logindate Undertime_From Undertime_To
1 8/27/08
2 8/28/08


Hi... Good day..
I have two tables. The concept is this, there will be an SQL code where would search the EmpId and LoginDate in Table1 that would matches in Table2. Based on the given ex. i have a two records in Table1 that maches in Table2 so all those two would save in Table2. The problem is I just can't save the two records in Table2.The record that i will b going to saves is the record of Undertime_From and Undertime_To....

 

[lbass]

reycons,

Please start a new thread.

-LB