Solved

Crystal Reports repeating the details section

Posted on 2011-02-16
8
4,886 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 101

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 101

Expert Comment

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

mlmcc
0
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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 101

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 101

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

Technology Partners: 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!

Question has a verified solution.

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

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

623 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