Displaying a Data Set in Crystal reports

Question

5.00/5 (1 vote)

See more:

I have an application that contains nearly 100 reports and I have been able to create most of them, often through trial and error, but I often run into problems where the data I want to display shows up multiple times on the report, or data that is not even in the data set I pass to the report shows up in the report!

I create the data set from data selected on a windows form, creating a SELECT statement, connecting to the DB, etc, and then pass the data set along with some parameters to the report. When I look at the data set before I create the report, it contains the proper information I want to put into the report.

When the report viewer opens however, I get a lot of data in the report that I didn't pass into it with the data set! Often times it is the "correct" data repeated multiple times, but other times it is data that was not even contained in the data set (but it is data that likely came from my database). I expect the problem is in the way I have setup the report using the Database Expert and Links, etc, but for the life of me I can't find anything that helps me find what I am doing wrong. I then proceed to change things through trial and error, and "most" of the time I stumble on the result I want. I have one report I am working with now that I have been fiddling with for 2 days and I can't seem to make it display the correct data.

Can anyone point me to a tutorial or article that might shed some light on what is happening here? I can post more details if someone needs them, but I was hoping someone might have some insight as where I might be able to find something that addresses this type of CR related issue.

Thanks for any information you can provide.

Posted 4-Dec-12 1:09am

pgeorge33

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

ahmad zrein · Answer 1 · 2012-12-04T02:47:00

Solution 1

use stored procedure connect it dataexpert in crystal report

Posted 4-Dec-12 2:47am

ahmad zrein

Comments

pgeorge33 4-Dec-12 10:35am

I'm afraid I don't understand your solution suggestion. The SELECT statement is different every time the user creates the report, based on their desired data (date ranges, time ranges, employee ranges, etc) Each piece of the SELECT is dynamically generated based on the setup of the Windows Form. The SELECT statement is pulling the data from the database I want to display properly, but somehow my report is not correct and the data gets displayed multiple times. For example, right now I am passing 6 rows of data in my data set to the report and the report is 244 pages long!

ahmad zrein 4-Dec-12 16:09pm

So check the select statement, check if data is duplicate
please show the select statement

pgeorge33 6-Dec-12 6:38am

Here is the select statement I use in my code. It will differ each time the report is run based on the selections the user wants to report on. I generate a data set from this select that is correct, and then push the data to the report.

Code Select statement:

SELECT Cards.[Employee Number], Employee.[Last Name], Employee.[First Name], Employee.[Badge Number], Employee.Department, Cards.Date, Rules.[Start Time], Rules.[Stop Time], Cards.RuleofPay, Cards.Notes FROM ((Cards LEFT JOIN Rules ON (Cards.RuleofPay = Rules.[Rule Name]) AND (Cards.[Company ID] = Rules.[Company ID])) LEFT JOIN Employee ON (Cards.[Employee Number] = Employee.[Employee Number]) AND (Cards.[Company ID] = Employee.[Company ID])) LEFT JOIN Company ON Cards.[Company ID] = Company.[Company ID] WHERE ((((((Cards.Date) >= #2012-11-26#) AND ((Cards.Date) <= #2012-11-26#)) AND ((Cards.[Company ID])= 100 )AND (((Cards.[Employee Number]) = '0000001113') OR ((Cards.[Employee Number]) = '0000001153') OR ((Cards.[Employee Number]) = '0000001182')))AND ((Employee.Classification) = 'Full Time' Or (Employee.Classification)= 'Part Time')) AND (Cards.[Special Code]) = 'Absent')

In the report, if I look at the select statement looks like this at the moment. I have tried several different configurations over the last 3 days. Some result in thousands of pages in the report, while others as few as 200 pages. The data set I am sending to the report typically will have only 5 - 10 lines.

Report Select statement:

SELECT DISTINCT `Rules`.`Start Time`, `Rules`.`Stop Time`, `Company`.`Company Name`, `Cards`.`Employee Number`, `Cards`.`Date`, `Cards`.`Notes`, `Cards`.`RuleofPay`, `Employee`.`Last Name`, `Employee`.`First Name`, `Employee`.`Badge Number`, `Employee`.`Department`
FROM ((`Cards` `Cards` LEFT OUTER JOIN `Rules` `Rules` ON (`Cards`.`Company ID`=`Rules`.`Company ID`) AND (`Cards`.`RuleofPay`=`Rules`.`Rule Name`)) LEFT OUTER JOIN `Company` `Company` ON `Cards`.`Company ID`=`Company`.`Company ID`) LEFT OUTER JOIN `Employee` `Employee` ON (`Cards`.`Company ID`=`Employee`.`Company ID`) AND (`Cards`.`Employee Number`=`Employee`.`Employee Number`)
ORDER BY `Employee`.`Department`

ahmad zrein 6-Dec-12 8:58am

the prolem is in left join
try to use where only
or inner join

ahmad zrein · Answer 2 · 2012-12-06T02:57:00

the problem is in left join.

wherever i use store procedure

example :

USE [AccDatabase]
GO
/****** Object: StoredProcedure [dbo].[ReportTrialBalance] Script Date: 12/06/2012 15:51:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <author,,name>
-- Create date: <create>
-- Description: <description,,>
-- =============================================
ALTER PROCEDURE [dbo].[ReportTrialBalance]

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
with TrialBalanceOutput as
(
SELECT dbo.acc_TransParent.iLedgerID, dbo.acc_TransParent.nPageno, dbo.acc_TransParent.dDate, dbo.acc_TransParent.ivhTypeID,
dbo.acc_TransParent.nRefno, dbo.acc_TransParent.cComment, dbo.acc_TransParent.lPosted, dbo.acc_TransChild.iRecnoTrans,
dbo.acc_TransChild.iLedgerID AS Expr2, dbo.acc_TransChild.nLine, dbo.acc_TransChild.iAccountID, dbo.acc_TransChild.cSubname, dbo.acc_TransChild.iSubacc,
dbo.acc_TransChild.iCurID, dbo.acc_TransChild.cDetail, dbo.acc_TransChild.nAmdebit, dbo.acc_TransChild.nAmcredit, dbo.acc_TransChild.nRatelocal,
dbo.acc_TransChild.nRateus, dbo.acc_TransChild.nAmlocal, dbo.acc_TransChild.nAmus, dbo.acc_TransChild.dValuedate, dbo.acc_TransChild.lBank,
dbo.acc_Account.cType, dbo.acc_Account.nAccountno, dbo.acc_Account.cFullname, dbo.acc_Account.lDiffexch, dbo.acc_Account.cSubacc, dbo.acc_Cur.cCur,
dbo.acc_Cur.cFullname AS Expr1, dbo.acc_Acctype.cAccType
FROM dbo.acc_TransParent INNER JOIN
dbo.acc_TransChild ON dbo.acc_TransParent.iLedgerID = dbo.acc_TransChild.iLedgerID INNER JOIN
dbo.acc_Account ON dbo.acc_TransChild.iAccountID = dbo.acc_Account.iAccountID INNER JOIN
dbo.acc_Cur ON dbo.acc_TransChild.iCurID = dbo.acc_Cur.iCurID INNER JOIN
dbo.acc_Acctype ON dbo.acc_Account.iAcctypeID = dbo.acc_Acctype.iAccTypeID
)
SELECT TrialBalanceOutput.nAccountno, TrialBalanceOutput.cCur,
SUM(TrialBalanceOutput.nAmDebit) as sumamdb,
SUM(TrialBalanceOutput.nAmcredit) as sumamcr,
sum(CASE WHEN TrialBalanceOutput.nAmdebit > 0 THEN TrialBalanceOutput.nAmlocal ELSE 0 END ) AS sumAmLocaldb,
sum(CASE WHEN TrialBalanceOutput.nAmdebit < 0 THEN TrialBalanceOutput.nAmlocal ELSE 0 END ) AS sumAmLocalcr,
sum(CASE WHEN TrialBalanceOutput.nAmdebit > 0 THEN TrialBalanceOutput.nAmus ELSE 0 END ) AS sumAmusdb,
sum(CASE WHEN TrialBalanceOutput.nAmdebit < 0 THEN TrialBalanceOutput.nAmus ELSE 0 END ) AS sumAmuscr
FROM TrialBalanceOutput
group by TrialBalanceOutput.nAccountno, TrialBalanceOutput.cCur

END

First i select information from different tabel the i will work on the result i have,
so you have to divide the work into two parts. select the information then make your conditions

Second you have left join which is wrong, it is preferable to use left right join, and the better inner join

this procedure is connect to any crystal report
so when you run report , run the procedure first

hope this will help

good luck