Print Page | Close Window

Hiding the detail section based on record count.

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7302
Printed Date: 03 May 2024 at 4:16pm


Topic: Hiding the detail section based on record count.
Posted By: Hammershell
Subject: Hiding the detail section based on record count.
Date Posted: 07 Aug 2009 at 3:38pm

Guys,

I want to hide the entire detail section based on the number of rows I have returning in my SQL query (command).  Specifically, row 1 will always show in my SQL query, even if there are no results.  Rows 2 through infinity may or may not show depending on how many medications a patient has.  However, I want to tell CR11 to hide the ENTIRE detail section if the row count is less than two.  So, even though the dates are showing in SQL, I want to hide the detail section WHEN the dates are the only data row showing.
 
CR11 is only letting me evaluate the rows using recordnumber, which, when you place a formula in the detail section to suppress, it hides the row numbers you are referring to.  This is not what I want.  I want to count the total number of SQL data rows and if the number of rows is less than two (or just one row, basically), I want to suppress the WHOLE DETAIL SECTION.

I hope this is clear, as I am leaving for vacation for a week from today and won't be back to check the responses until I get back on Monday the 17th. 

Thanks in advance!

Regards,

Joel


Replies:
Posted By: lockwelle
Date Posted: 10 Aug 2009 at 8:26am
assuming that there is a group, in the details section, format object, general tab, x-1 next to suppress,
 
count({table.field}, {group} = 1
 
 
should work...

Posted By: Hammershell
Date Posted: 17 Aug 2009 at 11:37am
Could you please be more specific.  I don't understand your instructions.  I have a subreport with a group by patient name and I have a detail section that shows all the results.  What are you saying, then?
 
Thanks,
 
Joel

Posted By: lockwelle
Date Posted: 18 Aug 2009 at 6:38am
to suppress a row, you would go to the section expert, and click on the suppression button for the detail section of the report.  There you need to enter a formula that will evaluate to 'true' when you want the section suppressed.
 
Where is the subreport?  is it in the detail section?  Do you have a group around the detail section?  the solution given, was for an individual item (silly me), but the logic behind it is the same.  If the number of rows in the group is 1 then suppress.  If you don't have a group around the detail section, then this isn't going to work, unless the report is for just 1 person.
 
Hope this is clearer

Posted By: Hammershell
Date Posted: 18 Aug 2009 at 9:09am
I tried to enter a formula to evaluate to true when I wanted the section suppressed.  So I checked the suppress checkbox and entered the following formula:
 
recordnumber < 2
 
The problem is that it would suppress rows 2 through infinity if there was more than one row.  I want it to suppress the whole section if only one row exists in the data set.  This requires the use of a different reserved word, as recordnumber does not refer to the logic I want to use.
 
To answer your question, the subreport is in the detail section of the main report.  The group (patient_name) is in the subreport and not in the main report.  There will be multiple subreports that will run with similar logic.  And the report is for one person, by the way. I added the group because I wanted to try something that ended up not working anyway.
 
You say that your solution should work "if the number of rows in the group is 1 then suppress".  But the question remains:  What reserved word do I use in CR11 to refer to the whole dataset so I can discern a dataset with one row from one with many rows?  If there isn't a reserved word that will do the job, what procedure do I use to get CR11 to hide the section anyway?
 
Please tell me if this is not clear.
 
Thanks,
 
Joel

Posted By: lockwelle
Date Posted: 18 Aug 2009 at 10:00am
you can't look at the entire database.  you are looking at only 1 person, so the data is filtered for the one person.  What you would do is look at a field in the dataset and count how many times it occurs.  I've never used RecordNumber, but it sounds like it just numbers the records, so it will not do what you are after.
 
checking suppress and entering formula does nothing...the formula will dominate the check mark.
 
since you are not using groups, Count({table.field})=1  should suppress the row.  if will look at all of the records that have made it through the filter, and count them.  if there is only 1, it will suppress the row.
 
HTH

Posted By: Hammershell
Date Posted: 18 Aug 2009 at 1:45pm

Thank you lockewell, sir.  You did it.  This works where it needs to work, based on my testing.

Kindest Regards,

Joel


Print Page | Close Window