Crystal Reports: Need to a formula to check if a field is suppressed 1

[kebbusobj]

Hi,

I have a need to perform a row count in Crystal Reports and then suppress a field based on the value of row count. Then I have to increase the row count by one if the field is not suppressed. I have tried everything I can think of, but nothing works. Does anyone know if there is a function in Crystal Reports that I can call in a formula that will check to see if a field is suppressed in Crystal Reports?

 

[dgillz]

What condition makes the field suppressed? Whatever that condition is, use it in a formula:

@CountUnsuppressed
If Not(<Insert supression logic here>) then 1 else 0

Then insert a SUM, not a count, on this formula field.



Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[kebbusobj]

Hi,

Thanks for responsding.

I have tried something very similar to this, but it's not working for me 100% of the time. Here is the actual scenario...

I have one detail (description) field that is grouped by a case statement formula built off another database field.
The report must display the data in two columns on each page. So, in other words, I am having to use the 'Format with Muliple Columns' option in the detail section. Up to this point, I am not having a problem. The problem I am having is getting the Group Names to display according to requirements. The Group Name is supposed to appear at the beginning of a new group, and at the top of every column for every page. A line is also supposed to separate one group's detail from a new group's detail, whenever a new group begins in the middle of a column. Here is a mock-up...

Page 1:

GroupName1 GroupName2 (Again)
Detail Detail
Detail Detail
Detail Detail
__________ Detail
Detail
GroupName2 Detail
Detail Detail
Detail Detail

Page2:

Group2Name (Again) GroupName4 (Again)
Detail Detail
Detail Detail
___________ Detail
Detail
GroupName3 _____________
Detail GroupName5
Detail Detail
Detail Detail
____________ Detail
Detail
GroupName4 Detail


Page 3:

GroupName5 (Again)
Detail
.....
.....

I have the GroupName in Detail Section a, so that it will repeat in multiple columns as well, and the detail field in Detail Section B. I have the following conditional suppression formula on the GroupName field:

{#RecordCount} <> 1 and {#RecordCount}<=Count({table.detailfield}, {@casestatementformula}) and
not (RowCount (a number var) in [2,41]

Note: #RecordCount is a running total I built that does a count on the detail records and resets on each new group.

I use this same logic in a formula that produces a boolean result, and I call that result in a rowcount formula in an if-then-else statement. It is not working.

 

[kebbusobj]

I have tried it seems 10 different ways to get this to work 100% of the time. I have gotten very close, but either it does work right for every page, or it pushes the rowcount over to another page, even though I have in the section expert it starting a new page after rowcount = 80. I am performing a reset on rowcount on every page as well.

The last thing I tried was to create two rowcount formulas, 1 for each detail section,but it is not working either. I am about to go crazy over here!

 

[dgillz]

Please post {@casestatementformula} and tell us where in the report this field is placed.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[kebbusobj]

select {table.field}
case 1:'Group Descprition1'
case 2:'Group Description2'
.
.
.
case 10:'Group Description10'

I am grouping on this formula in GH 1. I am suppressing GH 1 and displaying the GroupName for GH 1 in Detail Section A so that it will appear in both columns of the report. This was the only way I could get it to format in multiple columns, since this option is only available for the Detail Section in CR.

 

[dgillz]

I cannot even follow your logic. Please post sample raw data and desired results.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[kebbusobj]

I am ready to send you a mock-up, but I do not have access to an FTP site, and it appears the only way I can attach it here is to upload it to this site that wants my credit card info. Would you be willing to allow me to send the mock-up to your e-mail?

 

[dgillz]

Sure, buy I cannot give you my email address - against tek tips policies.

Can oyu not post it on the forum? I don't need a picture just text will work fine.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[kebbusobj]

I tried posting it, but when I do the formatting changes. I am a member of a BusinessObjects forum that does allow non-url attachments. Is that an option for you? A member of that forum attempted to help me over IM, but we took it as far as we could together, and still couln't get it to work. If this is not an option for you, then I will try again with the text in this window. Let me know and thanks.

 

[kebbusobj]

Hello lbass (LB),

If you happen to read this thread, my scenario is the exact same scenario as in thread 149-1092146, which you responded to back in 2005. I was just taking my report down a different solution path, which is why I posted this thread the way I did.

The solution you posted in the aforementioned thread is more straightforward then mine. I have tried to implement it following your instructions, but it is not working out.

When I initially count the records in the detail section, the record number is different for each column, because the column header appears already in the first column and is not yet there above the 2nd column. In other words, I have 37 detail records in the first column and 38 in the 2nd column (one more than the first because the column heading is not above column 2 yet). When I try suppressing the group and basing the formula on 38 detail records (38*2n-1), it doesn't work either. Can you please tell me what I'm doing wrong?

Thanks,
KB

 

[lbass]

You should create a formula {@cnt} like this:

whileprintingrecords;
numbervar cnt := cnt + 1;

Place this in the group header and in the detail section.

Also add a reset formula to the page header:

whileprintingrecords;
numbervar cnt := 0;

Suppress the report header and the group footer. Note the number at the bottom of the first column. Then insert a detail_b section. Make sure you have "repeat group header on each page" set in the group expert. Place a copy of the groupname in detail_b, and use a suppression formula like this, assuming that cnt = 38 at the bottom of the first column:

remainder({@cnt},2*37-1) <> 38

Note that the formula is: remainder({@cnt},2*(n-1)-1) <> n.

This works for me for two columns. I used a different formula for three.

-LB

 

[kebbusobj]

lbass,

This worked!!! I can't believe it!! I have worked on this for over 3 weeks, and couldn't get it to work 100% of the time, but this solutions does. Thank you so much!

I have to say though, I am so confused as to why this solution worked and mine did not.

What I had b4 was this:

1. An {@RowCount} formula in Detail Sections a and b:

whileprintingrecords;
numbervar rowcount;

if {@IsSuppressed} = True
then RowCount := RowCount + 0
else RowCount := RowCount + 1

2. An {@resetRowCount} formula in the Page Header:

whileprintingrecords;
numbervar rowcount := 0;

3. An {@IsSuppressed} formula in Detail Section a:

whileprintingrecords;
BooleanVar IsSuppressed;

if {#RecordCount} <> 1 and {#RecordCount} <= Count
({detailfield, group#1}) and {@RowCount} <> 1 and
{@RowCount} <> 40 then IsSuppressed:=True
else IsSuppressed:=False

4. An {#RecordCount} in Detail Section b and a count summary field in GF 1 called.

5. Group Header and Group Footer sections were suppressed and I moved the Group Header Name to Detail Section a and the detail records to Detail Section b.

6. The following conditional suppression on the Group Header Name in Section a:

{#RecordCount} <> 1 and {#RecordCount} <= Count
({detailfield, group#1}) and {@RowCount} <> 1 and
{@RowCount} <> 40

This is the solution I had, and it never would get the RowCount right everytime. Plus, I only had one instance of the GroupHeader and it was in Detail Section a, not b. Any ideas as to why this solution wasn't working for me?

Also, do you know if your solution will work with an additional statement in the coniditional suppress to only print at the top of column 2 if there are remaining records in the group to print? I noticed on one page that it repeated the group name at the top of column two, as it should, but there were no more records to print for that group, so it immediately printed the next GroupHeader name, as it should. So, I have an instance in the report where on one page it shows two Group names listed at the top of column 2? I was thinking that if I added my #RecordCount and Summary back into the report and modified my conditional suppress formula to match the following (I have 39 records in my first column) that it might fix this:

remainder({@cnt},2*37-1) <> 38 and and {#RecordCount} <= Count ({detailfield, group#1})

Your thoughts?

Thanks,
KB

 

[lbass]

I spent a lot of time trying to figure this out, and don't want to spend more time thinking about it! Sorry.

There is very simple solution if you want to always start a new column when there is a new group, and that is to format the group footer to print at the bottom of the page.

-LB

 

[kebbusobj]

I understand. Thanks again for responding to me before and helping me out on this.

 

[kebbusobj]

OK. For those of you with the same scenario that are looking to this thread for a solution, I came up with a solution to the case (that I pointed out above) where two column headings appear at the top of the 2nd column. This happens when you are at the end of the group and starting another group, and it just happens to be at the point where the 2nd column starts. In other words, your data may look like this on some of the pages with the solution that lbass was kind enough to provide:

Soft Drinks (GH) Beer & Wine(GH)
coke Specialty Drinks (GH)
sprite Pina Colada
root bear Margaritta
Beer & Wine (GH) Margaritta Swirl
coors light ...
miller light
house wine
<end of page>

Therefore, the entire solution will be:

1. Perform the steps provided by lbass above
2. Create a Running Total called #RecordCount, summarize
a field in your detail section, with a summary type
of count. Evaluate for each record and reset on change
of group.
3. Insert a count type summary field in the Group Footer
section for the same detail field chosen in step 2
above.
4. Go to Section Expert for Detail Section b (where you
have a copy of the Group Header field, which is con-
ditionally suppressed. Choose x+2 by Suppress (No
Drill-Down) and adde the following formula:

{#RecordCount} = Count ({table.Detailfield}, {groupname})

where {#RecordCount} is the Running Total field create in Step 2 and Count ({table.Detailfield}, {groupname}) is the Summary field created in Step 3.

5. Preview your report. Your data should now appear
like the following, when you have a group end and
another one begin right at the top of column 2:

Soft Drinks (GH) Specialty Drinks (GH)
coke Pina Colada
sprite Margaritta
root bear Margaritta Swirl
Beer & Wine (GH) ...
coors light
miller light
house wine
<end of page>

Hope this information is helpful to someone else out there. Thanks again, lbass, for your help with this!

KB

 

[kebbusobj]

Sorry, I notice that the formatting messes up for column 2, once I submit post. So, here is the before and after for column2:

Before:

Beer & Wine(GH)
Specialty Drinks (GH)
Pina Colada
Margaritta
Margaritta Swirl
...

After:

Specialty Drinks (GH)
Pina Colada
Margaritta
Margaritta Swirl
...


Thanks,
KB

 

[lbass]

Thanks for taking the time to share this.

-LB

 

[kebbusobj]

You bet! I couldn't have done it without you, and I really appreciate it!

KB