Report with Multiple Tables Join

I am designing a Receipt using Crystal Reports 8.5 Developer, MS SQL 2000 Server and MS Visual Basic 6.0 sp6. My Receipt will grab some data from RCP, RCPGL, RCPDetail and Customer tables.

SQL = SELECT RCP.RCPID, RCPGL.RCPGLID, RCPDetail.RCPDetailID, RCP.RCPDate, RCP.RCPID, RCPGL.GLDate, RCPGL.ChequeNo, RCPGL.GrossAmount, RCPDetail.InvoiceNo, RCPDetail.AmountPaid, Customer.CustomerName FROM RCP, RCPDetail, RCPGL, Customer WHERE RCP.RCPID=RCPDetail.RCPID AND RCP.RCPID=RCPGL.RCPID AND RCP.CustomerID=Customer.CustomerID AND RCP.RCPID= glngRCPID

Let say RCPDetail has 3 Invoices data and RCPGL has 2 cheque transactions, my SQL query will returns 6 records.

What I want is to display a section with 3 records of Invoice and another section to display 2 lines of cheque transactions.

Anyone can give me some recommendation?Group on the record type. If this is implicit from the data (e.g. ChequeNo is non-0 for cheques) then create a formula to return a type and group on the formula.|||Hi JaganEllis,
Thanks for your suggestion. I have found an idea and it is using grouping too. I will create a temporary table call TempReceipt. I'll SELECT data from RCPGL table and INSERT "RCPGL" as the key into TempReceipt and then SELECT data from RCPDetail table and INSERT "RCPDetail" as the key. After that join the TempReceipt table with Customer table using ADO. When display the data in Report, I'll use Group by the key of TempReceipt to separate the data.sql

Report with Multiple DB Tables

Dear All,
I want to get the data from two tables (no relationship exist) and want to display the data in a single report.
From one table i just want to show one record but from the other table i want to show multiple records.
Is it possible ? If yes please suggest me how ?Do you mean thet in first table you have only one record, and in the second multiple records?

Report with Multiple Data sources: Can I do this?

Okay, so I have this ADO.NET dataset defined in my project. I created
an RDLC report and from the menu Report->Data Sources... I chose two
different tables that exist in my ADO dataset. So far so good. At this
point I can drag specific columns into various text boxes I have in my
report (I'm not using tables) and I end up with entries like
"=First(Fields!ContactName.Value)". That's all well and good, but what
I was hoping to do, in addition to that, is something where I can
specify a particular record from one of the tables. Basically what I
have is a table that has two different links to another table. Now
each of those links may be to the same record, or to two different
records. I have a "Job" table and a "Company" table. The Job table has
a link to a "Dealer" and an "Installer" (both of which are records--or
the same record--in the Company table). I was hoping to be able to
display the installer name in one text box and the dealer name in
another. I have two issues: I don't know how to refer to a specific
table when referring to a particular field (both the Job and Company
tables have a "ContactName" column), nor does there appear to be a way
to pick a particular record. I can't seem to be able to do something
like "=Fields!Job.ContactName.Value" when I want to refer to the
ContactName in the Job table as opposed to the Company table. Further,
I can't figure out how to do something like (kinda pseudo code here)
"=Fields!Company.ContactName WHERE Company.ID == First(Fields!
Job.InstallerID.Value)"
So it comes down to this: Can I do anything like what I have described
above? If I can't, my next thought it to create a stored procedure
that gets every value I need and stores it in a custom named field so
I have JobContactName, InstallerContactName, etc to refer to, but then
my next question is can I define a stored procedure that will let me
grab the data I'm looking for from my in-memory ADO.NET Dataset?So I discovered that using subreports would solve my immediate
problem, but I'm still curious if there is a way to do some of what I
describe above, which is: access each different datasource via Fields,
and access specific rows in that source. Right now we can access First/
Last and what ever is chosen as the default when you don't specify
First or Last, but why can't we access a specific record based on the
value of a field in that record? I just want to know if any/all of the
above is possible or not, and if not, what are the patterns people
follow to get around these limitations?
Thanks!

Report with multiple columns won't print in portrait

I have a report that has 6 columns and won't print in potrait mode. I have
set the page width to 8.5in and the page height to 5in. If I set the page
height to 11in it prints OK (but I need to limit the height to 5in.
Also, can I use this report as the report source for a subreport control?
Thanks,On Sep 11, 1:59 pm, "Tim Kelley" <tkel...@.company.com> wrote:
> I have a report that has 6 columns and won't print in potrait mode. I have
> set the page width to 8.5in and the page height to 5in. If I set the page
> height to 11in it prints OK (but I need to limit the height to 5in.
> Also, can I use this report as the report source for a subreport control?
> Thanks,
You might try modifying the Report Properties Layout, if you haven't
already: via: Layout view -> Report drop-down -> Report Properties... -
> Layout tab -> Page height:, Top margin:, Bottom margin:. If you are
using columns, reduce the Spacing: property. For the subreport
control, you can use the report; however, it it has report columns,
once it is exported it will remove the columns automatically. This is
evidently a flaw in the export functionality. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Report server spawning multiple w3wp.exe threads

We have Win2003 SP1, SQL 2000 SP4, RS 2000 SP2 installed on our dev
machine and it works ok - having problems using anonamous access under
IIS but that is another issue.....
On our production server ( win2003 SP1, SQL 2000 SP4 ) we installed RS
2000 and SP2, accepting all the default installation options accept
SSL, and we have the following problems....
When we try to access http:\\servername\reports it takes a good few
minutes to load the web page, after asking for a domain user account,
and then the whole server runs like a dog.
Just getting to the first web page creates 12-15 copies of w3wp.exe,
each of which swallow between 20 and 130 MB of memory, and ramps up the
CPU usage.
I you try to upload a report, or access an existing report it spawns
off another 8-10 copies of w3wp.exe and eats more memory, and seriously
slows the server down.
The only way to release the resources it has useems to be to restart
IIS.
I "think" that this may be due to some hardening or lock down of
production server, but I am struggling on what to do next.
The RS logs do not seem to show any problems, but the Win App log shows
"ID 124 ReportServer Service has been activated" 5-6 times for every
action you take against reporting services.
I have been through the "checking reporting services is installed"
guides and it all seems ok, so there is something key I am missing here
!!!
Anyone else had similar problems and found the solution, or could at
least point me in the right direction to progress this issue !An update...
Thanks our freinds at the hosting company, it turns out this was all
due to .Net framework and IIS
We have apps running under 1.1 and 2.0 frameworks, and RS was set to
use default app pool on the IIS config.
Created a new app pool, and let both RS directories use that instead -
bingo !
Lots of w3wp.exe still created, but not hosing the box, and the number
goes up and down as usage changes.
Advice has been to create a new app pool just for reporting services,
as it is a hungry beast !