lordiano
asked on
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.
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.
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
Do the tables have a relationship so this could be done with groups and avoid the subreport?
mlmcc
ASKER
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.
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.
ASKER
rhinok, is the SQL working for two unrelated tables? there is no relationship between the main and subreport.
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
//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
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Glad i could help
mlmcc
mlmcc
//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