Solved

Crystal Reports repeating the details section

Posted on 2011-02-16
8
4,752 Views
Last Modified: 2012-05-11
I have a report in CR 10 that has a header, details section with three parts, and a footer. Details A and C contain subreports. Details B is just one field. This works fine and the report displays as intended.

I created a foruma field named "FileCount" which simply contains:
Count ({View_MainTable.Filename})

Open in new window


If I add this field anywhere to the report -- the header or any of the details sections -- it causes the details section to repeat 4 times. If I remove the field, the report goes back to working as normal.

What would cause this?
0
Comment
Question by:elorc
  • 4
  • 4
8 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 34907967
What is the FileCount formula?

What SQL are you using for the recordset?

My first guess is that you are including a NEW field from a linked table and it is causing you to get "duplicate" records.

mlmcc
0
 
LVL 1

Author Comment

by:elorc
ID: 34908288
That formula I posted is the only formula behind FileCount. It's just the Count() function. I'm linking to a view by a left join. A column from that view is what I'm using the Count() function on.

This behavior also occurs if I insert a column into the report from the view. If I create a subreport containing the view's columns and insert that, it works fine.

There are several other tables linked to this report, and they all seem to work fine. I'm assuming I've linked it wrong somehow, but I don't know how to prevent this duplication of the details section since I've never seen it happen before.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34909045
Can you upload the report?

mlmcc
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 1

Author Comment

by:elorc
ID: 34909779
I can't upload the report unfortunately since it has some private information on its formatting.

I do believe I've figured out the issue though. Somewhat, at least. I mentioned that it repeats the details section 4 times. Well, the FileCount value is 4, because there are 4 records that tie back to this main report.

Is there a particular way I should be joining/linking the view to the other tables that make up this report so that it doesn't cause this issue?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34909998
Not really.

The issue is there are 4 records in the table that relate to the rest of the records.  If you include a table in the table selection but don't include any fields from it, Crystal doesn't include that table in the SQL passed to the database.  Thus you don't see the issue until the field from that table is added.

If the records all contain the same information or the fields you select all do then you can use the SELECT DISTINCT records option.  

mlmcc
0
 
LVL 1

Author Comment

by:elorc
ID: 34910333
Is there a way I can do this as a SQL command in addition to the normal table linking? On the database expert it shows my data source, and also has the ability to add a SQL command. The way I generate the report is that I pass the record ID number to the report as a parameter, but the SQL command window doesn't show that parameter (it only allows me to add new ones). I don't know how that would work.

Really, all I need to run is a simple query, "SELECT COUNT(Filename) FROM View_MainTable WHERE Record_ID = '{Record ID parameter}'" and have a way to make the result of that query appear on the report.
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 34910873
You can use a SQL Expression using basically that syntax for the SQL

mlmcc
0
 
LVL 1

Author Comment

by:elorc
ID: 34911077
Ok, so I created a new SQL Expression Field called NewFileCount. The formula behind it:

(SELECT COUNT (*) FROM View_MainTable WHERE Record_ID = {"MainRecordTable"."Record_ID"})

Open in new window


I removed the link to the view from the database expert and it seems to work fine now. Thanks for your help. :)
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question