Crystal Reports 8.5 Missing Data from Report

[DevSupLady]

(For the record I did go through at least the first 80 solutions before posting this here. If there is a duplicate then please point me to it. I'm desperate!!!)

Ok Layout of Report is...

Report Header - Suppressed, no fields within content area
Page Header A - Subreport that displays county name and report title
Page Header B - Subreport that displays criteria that was selected before report was executed
Page Header C - Column Headers (text fields only, no db fields)
Group Header #1 - Election description db fields (grouped by election date) Keep Together option set
Details A - Voters name, precinct, approval date, ballot return date etc. Keep Together, New Page Before option set
Details B - Total for precinct Keep Together option set
Group Footer #1 - no data Keep Together option set
Report Footer - Total voters for entire report
Page Footer - Subreport for report date and time from db, page numbers and report file name

Database: SQL Server 2000
IDE: Visual Basic 5.0 [cheap employer thing, don't ask]
Crystal Version: 8.5

Data is created through a dynamic creation of a t-sql procedure for a report when executed. Cannot post t-sql statement here. Employer thing, don't ask. Data is pushed into a client sided Access database via RDO and a SQLPassThru query on the Access database. A table is created within the Access database to store the report data, the report metadata and the report's order by clause if any. (The order by clause gets processed in one of the formulas of the report.)

Problem: When there is more than one voter in the Access database the report displays the data just fine. When there is only one voter the report shows a Grand Total in the Report Footer as 1 but the voter information and the election information does not display at all. I can browse the field data for both Details and Group sections and there is indeed one row of data in each field.

There is a formula on the "New Page Before" option of the Details and Details A section. (Details shows up as a parent section to both Details A and B of course.)

Formula:
(
Not onFirstRecord
and Previous({AccessTable.PRECINCT})<>{AccessTable.PRECINCT}
and previous({AccessTable.LELEC_election_dt}) = {AccessTable.LELEC_election_dt}
and ({AccessOrderByClauseTable.order_by_clause}) = ''
)

Question: This was happening back when the report was on SQL Server 6.5 and in Crystal Reports 7.0 up to now, yet end-user did not detect this. Internal regression tester did. Why won't this report show the data for one voter?

 

[synapsevampire]

Perhaps because of this:

previous({AccessTable.LELEC_election_dt}) = {AccessTable.LELEC_election_dt}

And what is the no data portion of this?:

Group Footer #1 - no data Keep Together option set

It sounds like something is being suppressed to me, perhaps based on the same fromula you identified in the New Page Before, check the X 2 next to suppress fot the details sections.

-k

 

[DevSupLady]

previous({AccessTable.LELEC_election_dt}) = {AccessTable.LELEC_election_dt}

I think this line is saying that if the previous row's election date value equals the current row's election date then it returns true otherwise false. This was created by a previous developer who is no longer with the company and this can be changed or eradicated if necessary.

&quot;no data&quot; part of &quot;Group Footer #1 - no data Keep Together option set&quot; means that there are no fields whatsoever in the content area of the Group Footer #1 section.

Suppression Data:
Report Header - Suppressed (no drill down) set
Page Header A - Suppress Blank Section option set
Page Header B - Suppress Blank Section option set
Page Header C - Suppress Blank Section option set
Group Header #1 - No Suppression
Details A - No Suppression
Details B - No Suppression
Group Footer #1 - No Suppression
Report Footer - No Suppression
Page Footer - No Suppression

 

[DevSupLady]

Btw, New Page Before option is set for Details section where formulas are being applied.

When I put the word set I mean it's been checked. Sorry.

 

[synapsevampire]

Data does not show up in the details yet the total info is correct and there isn't a suppression used.

I'd guess that the total is incorrect and that you're not really returning any rows, post what's in the record selection formula.

-k

 

[DevSupLady]

T-SQL statement works fine. I can get it from the VB via a break point, c/p into SQL Query Analyzer and run it and this is the recordset I get back and this is what I see when I browse each field in the report. It is definitely getting a recordset back.

Group01: ''
Group02: ''
Group03: ''
Voting_Place: MAIL
Full_Name: SCHMOE, JOE
VRN: 123456789
PRECINCT: 19
Ballot_Returned_Date: 12/30/2003
Sequence_Num: MIL-1
AC_apprvl_dt: 20031231
AC_request_type: MAIL
LELEC_description: 2004 GENERAL
LELEC_election_dt: 11/2/2004
AC_absentee_corr_seq_num: 1
MUNICIPALITY: BOS
AV_av_type_cd: MIL

 

[DevSupLady]

But now that you mention it, when the report is run it usually shows in the CRViewer how many records it was able to return, however it shows zero. I wonder how is it that I am able to browse field Data and see the data yet the report thinks there were no records returned and the Grand Total shows a value of one rather than zero.

I'm sure it's something quite simple that I'm just missing here...

 

[DevSupLady]

Ok we've found a solution. Thanks for your help SynapseVampire.

Solution:
The order by clause table in the Access database was blank and those formulas that were applied to certain sections were trying to reference this table thus causing the data to not appear. Why this would only happen to one row of data as opposed to multiple rows of data is still beyond me, but when you put some data in that Access table then the data in the report shows up. I guess it pays to check all the data in each table that is referenced by any formulas in your report.