Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Crystal Reports repeating the details section

Posted on 2011-02-16
8
Medium Priority
?
5,038 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

721 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