[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 970
  • Last Modified:

Supress Section in Crystal XI based on value passed from subreport

Hello Experts,

I need some assistance with suppressing a section based on a shared value passed from a subreport.
[Background]
I have a request to filter out students on my report that have less than let's say 5 absences this school year. I figured the best (perhaps more correctly the easiest way) to do this would be to suppress the section displaying student information when the student less that 5 absences. I plan to replace this arbitrary number of 5 with a parameter field later. Anyway for arguments sake I am using the sum of 5 absences.

At present, I have several groupings and my subreport is in GH3. The subreport sums up the total absences for each student. I link on the student's ID #. The report runs great. The shared value that I pass from the subreport (shr_Q1_AbsTotal) declared in the following formula:

//@shr_Q1_AbsTotal
WhilePrintingRecords;
Shared NumberVar shr_Q1_AbsTotal;
shr_Q1_AbsTotal

...is placed on a section below the subreport for the variable to correctly display the value for each respective student.

[Challenge]
I need to somehow display only those students with 5 or more absences. How to suppress the section with the subreport to do this? Is there a better method? I could use perhaps previous.record somehow but that method excludes the last record and errors out on the first record. Anyway, I am presenting this question to this forum for assistance.

Thanks!
0
shogun5
Asked:
shogun5
1 Solution
 
frodomanCommented:
The key to suppressing based on a subreport variable is that the section you want to suppress must be below the subreport in terms of when it is executed.  So, if you want to suppress an entire group (header, details, footer), what you would commonly do is to create an additiona group header and put the subreport there.  Then in the subsequent header/footer/details you can add conditional suppression based on the shared variable.

The conditional suppression is a simple formula:  (shared NumberVar shr_Q1_AbsTotal < 5)   This evaluates to true (true=suppress) unless the total is 5 or more.

frodoman
0
 
Kurt ReinhardtCommented:
If you're reporting from a SQL-based database, such as Oracle or SQL Server, then I would recommend using a SQL Expression field to identify those students who have five or more absences.  This will allow you to filter all other students out of the report completely rather than returning and suppressing them.

The syntax for the SQL Expression will depend upon your database, but it will be something like this:

(
SELECT
  'Y'
FROM
  TABLE
WHERE
  STUDENT.ID = "STUDENT"."ID"
GROUP BY
  'Y'
HAVING
  COUNT(STUDENT.ABSENTMARK) >= 5
)

"STUDENT"."ID" is the student ID that is passed in from the report so that you can correlate the data in the SQL Expression (basically a subquery) to the report.  Essentially, this SQL Expression will create a field in the SELECT clause created by Crystal Reports.

Next, you would refer to the SQL Expression from within the Record Selection Formula:

{%Absent5} = 'Y'

~Kurt
0
 
shogun5Author Commented:
rhinok,
given the nature of our setup I need to figure this out using Crystal XI.

frodoman:
Yes, you are correct in that the section I want to suppress must be below the subreport section. The problem I am having is that the I want the data from the subreport to display directly. Here is my current layout:

RH (suppress)
PH
 GH1 (suppress: Student Grade Level)
  GH2 (Homeroom Number)
   GH3a (Student Lastname) AttendanceSubreport.rpt
                         (underlay following sections used in section expert)
   GH3b (Student Lastname) [text objects]
                 Student: Firstname Lastname
                 Grade: Grade Level
                 DOB: Birthdate
                 Date Entered: 99/99/9999
    GH4 (Student ID#)
     Details
    GF4
   GF3
  GF2
 GF1
RF
PF

The underlaying following options check works to bring down the subreport from the GH3a. I suppress GH3b using the following criteria ({@shr_Q1_AbsTotal} <= 5) . Okay so this suppresses the GH3b but I cannot suppress GH3a without using the same formula because the formula is on the same section at the subreport and will not evaluate correctly causing the expressions to always be "true" and nothing prints on the report.  Ironically enough, the report shows only displays the text objects when the absent count is greater or equal to 5 (this is good) but the subreport values for all other students show up as well. Like this:

------------------------------------------------------------------------------------------------------------------------
                                                                                                         Marking Period    1       2       3       4
                                                                                                         Days Present     78.0
                                                                                                         Days Absent     1.0
------------------------------------------------------------------------------------------------------------------------
                 Student: Firstname Lastname                                        Marking Period    1       2       3       4
                 Grade: Grade Level                                                       Days Present     61
                 DOB: Birthdate                                                               Days Absent     18
                 Date Entered: 99/99/9999
-----------------------------------------------------------------------------------------------------------------------

I may have to resort to using a shared variable for each value in the subreport, supress the subreport and display the shared var value. But I'd rather not go this route as I would just like to use the numbers directly off the subreport. Is this possible? I hope I have explained this well enough. Thanks again for looking at this.

Mike

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
mlmccCommented:
How are the days absent calculated?

mlmcc
0
 
shogun5Author Commented:
mlmcc,

The days absent value is a sum of absences identified between the grading periods (provided as parameters). This formula is on the details line of the subreport:
//Q1_ABS
Select {DA_StudentAttendance.AttendanceDate}
    CASE is <= {?Q1_EndDate}:
        {DA_AttendanceCode.AbsentValue}
    Default:
        0;

The detail line is suppressed. The only section not suppressed is the report footer. In the footer I user the SUM function "Sum of Q1_ABS". This is the value that is assigned to shr_Q1_AbsTotal and passed to the main report.

0
 
Kurt ReinhardtCommented:
Shogun5 - a SQL Expression is a field you create within Crystal Reports, much like a Formula.  The benefit to using one is that you can filter the records out before they ever hit the client, whereas this approach returns all possible records and then suppresses them.  As long as you're directly connected to a SQL-based database and aren't going through a security layer (such as with the SAP Integration Toolkit), you should be able to create the expression within Crystal Reports.

~Kurt
0
 
shogun5Author Commented:
Kurt,

Please forgive my lack of understanding. If you say I can use your expression:

(
SELECT
  'Y'
FROM
  TABLE
WHERE
  STUDENT.ID = "STUDENT"."ID"
GROUP BY
  'Y'
HAVING
  COUNT(STUDENT.ABSENTMARK) >= 5
)

as a field created within Crystal Reports, "much like a formula" where would I place this SQL statement? In the subreport as a formula? In the subreport's Record Select expert? I am very interested to see how this would work. Any further assistance would be most appreciated.

-Mike
0
 
Kurt ReinhardtCommented:
If you look in your field explorer, you should see a variety of field types, such as:  Formulas, Parameters and Special Fields.  If you can use SQL Expressions you'll see SQL Expression listed as a field type and it will be enabled (meaning it's not grayed out and unclickable).

Simply create a new SQL Expression field, which opens up a formula window.  The tough part is knowing what to type as your expression.  You must know SQL (and it needs to be specific to the type of SQL used by your database and method of connectivity) in the example above or have access to somebody who does.  A SQL Expression in Crystal Reports is basically the equivalent of a subquery in the SELECT clause of a SQL query on your database.

In the example I provided (It should probably be a SELECT DISTINCT, btw), the SQL query basically returns a 'Y' as a field value for every student who has more than 5 absences.  If you were to look at raw data from your report (details section), it might look like this:

StudentID     Name         Absent5
12345678     Jones, Jon       Y

Keep in mind, this will be at a detail level, so if you have multiple records for Jon Jones, that flag will be on every record (which is OK).

Then, you can apply the filter in the record selection as I indicated above.  That will filter out all students for whom the Absent5 flag <> 'Y.

~Kurt
0
 
shogun5Author Commented:
Kurt,

I spent the entire day studying SQL Expressions. Duh! You are right. This is makes sense now. Thank you so much for pointing me in the right direction. I already know of several reports I plan to rewrite to do more processing on the server.
Thanks!
-Mike
0
 
Kurt ReinhardtCommented:
I'm glad I could help!

~Kurt
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now