Solved

Crystal Reports repeating the details section

Posted on 2011-02-16
8
4,566 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
Comment Utility
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
Comment Utility
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
Comment Utility
Can you upload the report?

mlmcc
0
 
LVL 1

Author Comment

by:elorc
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
Comment Utility
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
Comment Utility
You can use a SQL Expression using basically that syntax for the SQL

mlmcc
0
 
LVL 1

Author Comment

by:elorc
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

743 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now