• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 684
  • Last Modified:

true/false statements

I have a student who did some testing example
10/30/2008 - did not pass any test so the result is FALSE
12/11/08 - passed one test so the result is TRUE
I only want this person to be counted once for TRUE and not for the FALSE.  Is there a statement out there that when building a crystal report will do this.  There will be two reports.  One will be a TRUE report and the other will be a FALSE report.
Not sure if this make sense
0
TCHGirl
Asked:
TCHGirl
  • 3
  • 2
5 Solutions
 
GSQLCommented:
The best way is to filter the data in SQL.  Are you using a stored procedure?  Pass a parameter to the stored procedure for True or False, and filter in the where claused based on the parameter.

If you are using a SQL Command in the Crystal report, you can do the same thing.  Filter based on Pass/Fail.  
0
 
James0628Commented:
So, you have multiple tests for a student and if they passed _any_ of the tests, you want them on the "TRUE" report, and if they did not pass any of the tests, you want them on the "FALSE" report?

 Is each test in a separate record?

 How do you define passing a test?  Is there a score that you would check or what?

 James
0
 
TCHGirlAuthor Commented:
yes, each test is a separate record.  These are state tests that we give to our students every quarter.  The score very for an increasement depending on the test they take.  My SQL table is a bit field called NRS, so that on my web design it is a check box.  When entering the test scores, I am able to see if the student increased a literacy level and if they did I check the box.  When the box is checked it is a true answer otherwise it is false.  As I previously stated once they are counted as a true, I don't want then counted as a false also.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
James0628Commented:
Well, the TRUE report shouldn't be a problem.  Have CR look for only the "true" records (eg. go to Report > "Selection Formulas" > Record and have it test the NRS field for TRUE or 1 or whatever value corresponds to "true").  Then the report will only get the "true" records.

 If you don't include any test details on the report (like the test date), so the only fields that you're using are basic ones like student name, you can go to File > "Report Options" and check the "Select Distinct Records" box.  Then the report should only get one record per student.

 If you want to see some test details (so "select distinct" won't work), you can have the report group on the student ID, suppress the detail section and put your "detail" fields in the group footer, so you only get one line per student.


 The FALSE report is trickier.  You can't just include all of the "false" records, because some of those students will also have a "true" record, meaning that they should not be included.

 For the FALSE report, the best answer would probably be to create a query (eg. a view or stored procedure) that would gather the required information and have the report use that.  Otherwise, the report basically has to read every record for each student (within the appropriate dates or whatever other restrictions you may have on the data), then it decides whether or not to show a student, depending on whether or not any of those records were "true".  It's not too difficult, but it is inefficient.

 So, which approach would you like to try?

 James
0
 
GSQLCommented:
I still have to make some assumptions:

1.  That the latest score in the quarter is the one that matters.  Since 10/30 and 12/11 are in the same calendar quarter, it would seem that the student in question took the test twice and passed it the second time.  

2.  Since there is no need to retake the test after passing it, the only score that matters is the last one in the quarter (presumably a date range is being used to filter the report data).

  a. Based on assumption #2, the you would expect to see either all falses for any student or some falses and one true or just one true (passed the first time).

Based on the above, you would always need the most recent True/False value.  This is best done in SQL.

You can do it with a query like this:
set nocount on;

declare @t table (Studentid char(8), TestId char(5), TestDate smalldatetime, Score int, NRS bit)
insert @t (Studentid, TestId, TestDate, Score, NRS) values ('Student1', 'TestA', '20081030', 70, 0)
insert @t (Studentid, TestId, TestDate, Score, NRS) values ('Student1', 'TestA', '20081211', 80, 1)

select Studentid, TestId, TestDate, NRS
from @t t
where TestDate = (
      select max(TestDate)
      from @t tInner
      where t.Studentid = tInner.Studentid)

If my deduction is valid, this query could solve the whole problem for you, since you will also see the Falses in the result.  

When you create the report, you can create a group on NRS (sort the group in descending order if you want the Trues first), and put the False group on a new page.  

You can even do this if you need different data on both reports, as long as all of the data is included in your query.  If you need it to look like two different reports, treat the Group header as the Page header and base the contents of the header on the value of the NRS group.

HTH
0
 
GSQLCommented:
And if you need to be able to run the report for either passed or didn't pass, you can add a parameter to the report and filter the query based on the parameter.  

If the parameter is {?PassOrNot}, pass this parameter to your where clause, so your query would look like:

-- SAMPLE DATA
set nocount on;
declare @t table (Studentid char(8), TestId char(5), TestDate smalldatetime, Score int, NRS bit)
insert @t (Studentid, TestId, TestDate, Score, NRS) values ('Student1', 'TestA', '20081030', 70, 0)
insert @t (Studentid, TestId, TestDate, Score, NRS) values ('Student1', 'TestA', '20081211', 80, 1)
insert @t (Studentid, TestId, TestDate, Score, NRS) values ('Student2', 'TestA', '20081030', 70, 0)

select Studentid, TestId, TestDate, NRS
from @t t
where TestDate = (
      select max(TestDate)
      from @t tInner
      where t.Studentid = tInner.Studentid)
and t.NRS = {?PassOrNot}
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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