Table was used by subreport

Question

Table was used by subreport

lss

SSC-Addicted

Points: 431
More actions
June 1, 2004 at 8:59 pm

#108446

Hi,
   I used one table just mainly for the usage of sub report in the Crystal Report. There are fews report using this table. When users try to get different reports or same report, the data always not accurate. The final result might get from other user criteria due to this table is used by few reports.
   What I done for the table is:
   First I removed all the data than insert the data into table based on criteria. Ex: delete table A
     insert into table A select fields from other table.
     How to prevent this problem? Any good suggestions?

thanks and regards



lss

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Are you saying that table A contains the data as built for the report on the fly and is deleted between runs? If so then you potentially could have multiple users hit the table near the same time and they get one anothers results. Either use a temp table or flag the data in the table some way so that the individual users data is recognized. However I personally would use a temp table since a local temp table is only valid to the connection it is created in and a user could run multiple reports within the same site still, the other way creates the issue of deciding how to determine which window session is the processing window ofr the data.

lss SSC-Addicted Points: 431 More actions · Answer 2

Hi,

Yes you are right, the table is purposely used by Crystal Report. Each user might have different criteria. The data in the table going to remove before insert the new data based on user's criteria. As a result some of the users will get the wrong result. The temp table is only applicable for stored procedure. In the crystal report you must used either stored procedure or the real table. Of course you can declare temp table in the store procedure but for my case i really don't know how to solve it. This is because the table is used in a sub report.

thanks and regards

lss

barsuk SSChampion Points: 12280 More actions · Answer 3

I know it might sound tedious, but can you redesign the whole process?

Create a more comlpex stored procedure. It might allow you:

to incorporate temp tables, and

to remove the need for the subreport, which makes your app slow.

From my CR experience with subreports I could say that you can substitute them in 80% of the cases by elaborating more complex Stored Proc, which essentially increase the speed of the whole process and will benefit your clients.