Suppression

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.

lordianoAsked:
Who is Participating?
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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
 
mlmccCommented:
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
lordianoAuthor Commented:
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
 
lordianoAuthor Commented:
rhinok, is the SQL working for two unrelated tables? there is no relationship between the main and subreport.
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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
 
lordianoAuthor Commented:
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
 
Kurt ReinhardtConnect With a Mentor Sr. Business Intelligence Consultant/ArchitectCommented:
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
 
mlmccCommented:
Glad i could help

mlmcc
0
All Courses

From novice to tech pro — start learning today.