?
Solved

Suppression

Posted on 2005-03-01
9
Medium Priority
?
1,013 Views
Last Modified: 2009-12-16
Hi all,
I have a main report and a subreport,
In my main report, its connecting to data table A and the subreport is connecting to table B.
I would like to make a suppression rule in my main report on the detail section that contains the subreport.
My problem is that I want to check if B.Records is empty, I will suppress the whole subreport.
Right now in my main report I have no way of getting accessed to table B.
Is there an easy way to do this? The way I am doing is basically suppress all the header, detail, footer inside subreport based on the same formula isnull({B.Records})
Seems that this is rather a stupid way of approaching the problem.
Would like to have any input please.

I have tried using shared variable so that in my subreport header i evaulate isnull({B.records}) and assign the result to a shared variable. I tried to use that variable value in main report to then make the suppress rule but it seems that I am not able to do so due to the reason that crystal probably checks the suppress rule before it went inside the sub report.

0
Comment
Question by:lordiano
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 13435145
What database and version of Crystal Reports are you using?  If you're using a SQL-based DB like SQL Server or Oracle, then you can create a SQL Expression field (CR7+).  At a very high level, you could create a SQL Expression.  You could then add the SQL Expression to conditional suppression formula in order to suppress the subreport when there's no data.  The SQL Expression will basically test to see if there is data in the subreport by using a SQL statement that is equivalent to the SQL generated by the subreport.  The SQL Expression will look something like this (a very simple example using SQL Server):

//Subreport Data Check
(
SELECT DISTINCT
  A.field
FROM
  db.table A
WHERE
  linked field from main report = linked field from table A
//or criteria matches the critiera from the subreport
)

Next, you'll need to apply the conditional suppression in both the section that contains the subreport AND on the subreport.  This will suppress the subreport and stop it from running when the SQL Expression value is NULL.  The suppression formula will look like this:

//sample suppression formula
IsNull({%subreporthasdata})

I think you'll find that this works well and is fairly simple to implement.

~Kurt
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 13436286
If you have CR9 or later there is an option to suppress a blank subreport.

Do the tables have a relationship so this could be done with groups and avoid the subreport?

mlmcc
0
 

Author Comment

by:lordiano
ID: 13444002
The tables do not have any relationship thats why I am not putting two tables in the same report.
I am using CR10 right now.

It has to be done. Another thing I want to mention is that it is not blank subreport even when there is no data because there are some static header fields that are there. I want to suppress even those static fields when there is no data in the subreport.

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:lordiano
ID: 13444021
rhinok, is the SQL working for two unrelated tables? there is no relationship between the main and subreport.
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 13444867
A SQL Expression is basically a subquery of the primary SQL Query generated by Crystal Reports (Database|Show SQL Query).  As such, it doesn't have to have any relation to the primary report.  In your case, identify a key field that will always be present if there's data in the subreport.  Then create a SQL Expression, similar to the following:

//Subreport Data Check
(
SELECT DISTINCT
  A.field
FROM
  db.table A
WHERE
  criteria matches the critiera used in the subreport - do Show SQL Query in the subreport and see what's in the WHERE clause...
)

~Kurt
0
 

Author Comment

by:lordiano
ID: 13454979
Kurt, just wanted to clarify , I am using CR10 but I dont see SQL expression field under the Field Explorer.. Is there anything I need to do to turn that on?
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 1000 total points
ID: 13455312
0
 
LVL 26

Assisted Solution

by:Kurt Reinhardt
Kurt Reinhardt earned 1000 total points
ID: 13456431
Is your report based off a stored procedure?  If so, SQL Expressions won't be available because all SQL is contained within the procedure.  Please refer to the following KB article:

http://support.businessobjects.com/library/kbase/articles/c2016092.asp

The same principle would still apply however.  You could modify the stored procedure to return a field based on a subquery that looks for data in the unrelated table.  The conditional suppression would be based on this field.

Another possible option is that you aren't reporting against a SQL based database such as Oracle or SQL Server.  What database are you using?

~Kurt
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 13513315
Glad i could help

mlmcc
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month15 days, 14 hours left to enroll

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question