Click here to Skip to main content
15,903,203 members
Please Sign up or sign in to vote.
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

use stored procedure connect it dataexpert in crystal report
 
Share this answer
 
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
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
 
Share this answer
 
Comments
pgeorge33 6-Dec-12 10:09am    
Thanks for the feedback and the ideas. I'll keep plugging along and see if I can get it to work.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900