Hello Everyone!

We are developing a ASP.NET application that has a bunch of Crystal Reports in it. Having created the template for our reports in Crystal report XI IDE and intergrating the same into Visual Studio .NET 2003, we have run into a strange problem. We still couldn't fix the problem ourselves. The problem is like this...

Having intregrated a report called Sample into VS.NET 2003 which resulted in Sample.cs class for the report. We haven't made any changes to IDE generated code. We then created a .aspx page to display the report in a WebForm at runtime. This .aspx has an instance CrystalReportViewer whose ReportSource was an instance of the Sample. This sample was bound was to a DataSet which has only rows in it. Unfortunately, the report comes up with 4 records.

The SQL Query which resulted in the dataset has been thoroughly checked and it always resulted in just 2 rows. The query does contain a JOIN between two tables and when the JOIN is removed, the report renders only two rows.
The JOIN was meant to get the name from a reference table. We somehow couldn't go to the root cause of this strange behaviour exposed by Crystal Report XI with .NET.

Is there something really wrong with the rendering aspect of the report or is there wrong with the Quert? All the queries associated with all the reports do have some sort of joins with one or more than one table... How come the Sql Query is bothering the Report? We just couldn't find any solution.

Could there some kind of a relation between the report and the backend? Hope... no.

It's been quite frustrating and all our reports have come to a halt just bcoz of this snag. Hope some body who has faced this kind of a problem before could guide us and help us in resolving this issue asap.

The query is as follows...
SELECT
    DISTINCT NET_POSITIONS_PROCESS.BOUGHT_QTY,
    NET_POSITIONS_PROCESS.BOUGHT_AVG_RATE,
    NET_POSITIONS_PROCESS.BOUGHT_VALUE,
    NET_POSITIONS_PROCESS.SOLD_QTY,
    NET_POSITIONS_PROCESS.SOLD_AVG_RATE,
    NET_POSITIONS_PROCESS.SOLD_VALUE,
    NET_POSITIONS_PROCESS.NET_QTY,
    NET_POSITIONS_PROCESS.NET_AVG_RATE,
    NET_POSITIONS_PROCESS.NET_VALUE,
    NET_POSITIONS_PROCESS.CLIENT_CODE,
    NET_POSITIONS_PROCESS.MARKET_RATE,
    NET_POSITIONS_PROCESS.MARK_TO_MKT,
    NET_POSITIONS_PROCESS.SCRIP_CODE,
    NET_POSITIONS_PROCESS.stlmnt_type_code,
    SCRIP_MASTER.SCRIP_SHORT_NAME
FROM
    NET_POSITIONS_PROCESS NET_POSITIONS_PROCESS,
    SCRIP_MASTER SCRIP_MASTER
where
    NET_POSITIONS_PROCESS.EXCHANGE_CODE = SCRIP_MASTER.EXCHANGE_CODE
AND
    NET_POSITIONS_PROCESS.SCRIP_CODE = SCRIP_MASTER.SCRIP_CODE
and
    NET_POSITIONS_PROCESS.exchange_code = 1
and
    stlmnt_no = '2006003' and stlmnt_type_code = 'N'

Thanks in advance...


Shashi Kumar Nagulakonda
Software Developer