在Crystal报表中显示数据集 [英] Displaying a Data Set in Crystal reports

问题描述

我有一个包含近100份报告的应用程序，并且我已经能够创建大部分报告，通常是通过反复试验，但我经常遇到问题，我要显示的数据在报告中多次显示，或者我传递给报告的数据集中甚至没有的数据显示在报告中！



我从Windows窗体中选择的数据创建数据集，创建SELECT语句，连接到DB等，然后将数据集和一些参数传递给报表。当我在创建报告之前查看数据集时，它包含我想要放入报告的正确信息。



当报告查看器打开时，我在报告中获取了大量数据，我没有用数据集传递给它！通常情况下，重复多次是正确数据，但有时候数据甚至不包含在数据集中（但数据可能来自我的数据库）。我希望问题在于我使用数据库专家和链接等设置报告的方式，但对于我的生活，我找不到任何可以帮助我找到我做错的事情。然后我通过反复试验来改变事物，并且大多数时间我偶然发现了我想要的结果。我现在正在处理一份报告，说我已经摆弄了2天，我似乎无法显示正确的数据。



可以有人给我指的是一篇教程或文章，可能会对这里发生的事情有所了解吗？如果有人需要，我可以发布更多详细信息，但我希望有人可能会有一些见解，因为我可以找到解决此类CR相关问题的东西。



感谢您提供的任何信息。

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.

推荐答案

使用存储过程在水晶报表中连接dataexpert

use stored procedure connect it dataexpert in crystal report

问题在于左连接。



无论我在哪里使用商店程序



例子：



USE [AccDatabase]

GO

/ ******对象：StoredProcedure [dbo ]。[ReportTrialBalance]脚本日期：12/06/2012 15:51:30 ****** /

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

- ========================= ====================

- 作者：< author ,, name>

- 创建date：< create>

- 说明：< description ,,>

- ============ =================================

ALTER PROCEDURE [dbo]。[ReportTrialBalance]



AS

BEGIN

- 添加SET NOCOUNT ON以防止额外的结果集

- 干扰SELECT语句。

SET NOCOUNT ON;

，TrialBalanceOutput为

（

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

来自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 Tr ialBalanceOutput.nAccountno，TrialBalanceOutput.cCur，

SUM（TrialBalanceOutput.nAmDebit）为sumamdb，

SUM（TrialBalanceOutput.nAmcredit）为sumamcr，

sum（例如，当TrialBalanceOutput.nAmdebit> 0那么TrialBalanceOutput.nAmlocal ELSE 0 END）AS sumAmLocaldb，

sum（CASE WHEN TrialBalanceOutput.nAmdebit< 0 THEN TrialBalanceOutput.nAmlocal ELSE 0 END）AS sumAmLocalcr，

sum （例如当TrialBalanceOutput.nAmdebit> 0那么TrialBalanceOutput.nAmus ELSE 0 END）AS sumAmusdb，

sum（例如当TrialBalanceOutput.nAmdebit< 0 THEN TrialBalanceOutput.nAmus ELSE 0 END）AS sumAmuscr

来自TrialBalanceOutput

group by TrialBalanceOutput.nAccountno，TrialBalanceOutput.cCur





END





首先我从不同的表格中选择信息，我将对我的结果进行处理，

so你必须把工作分成两部分。选择信息然后让你的条件





第二个你离开加入哪个错了，最好使用左右加入，和更好的内部联接



此程序连接到任何水晶报告

所以当你运行报告时，先运行程序



希望这会有所帮助





祝你好运

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

这篇关于在Crystal报表中显示数据集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！