Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Crystal Reports repeating the details section

Posted on 2011-02-16
8
Medium Priority
?
5,161 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 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Screencast - Getting to Know the Pipeline
Suggested Courses

963 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