Subreport Based On View

[jtrapat1]

I'm trying to link a subreport based on a view but I'm having trouble setting the links correctly for the one-to-many condition.
It worked OK in Access but I don't understand how to do this in Crystal.

Here's my View for the main report:

SELECT
vwHotBills_rev.BillNum, vwHotBills_rev.Sponsor, vwHotBills_rev.Title, vwHotBills_rev.CommitteeDate, vwHotBills_rev.OriginalCommittee, vwHotBills_rev.BAMCode, vwHotBills_rev.BillMemoCode, vwHotBills_rev.ActionCode, vwHotBills_rev.Analyst, vwHotBills_rev.Bill99Code, vwHotBills_rev.SenateNum, vwHotBills_rev.Requester, vwHotBills_rev.Comment
FROM
BT2001.dbo.vwHotBills_rev vwHotBills_rev

Now, my field for the one-to-many is Analyst; there may be many Analysts assigned to one BillNum (linking field).

From my subreport, which is based upon this same view, if I go to the Analyst field that I want, the Selection Formula based upon the links that I set is:
{vwHotBills_rev.BillNum} = {?Pm-vwHotBills_rev.BillNum}
and
{vwHotBills_rev.Analyst} = {?Pm-vwHotBills_rev.Analyst}

I'm not getting the many condition to show up on the report.

Any suggestions?
Thanks

 

[MalcolmW]

Perhaps you should explain what you are trying to accomplish in the report - there may be simpler way to do it.
Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[balves]

A sub-report is often used to link a 1-many relationship with the main report. However, the subreport will typically operate on different tables/views.

So if your main report and sub-report use the same view, perhaps you don't need a sub-report to get the results you're looking for. Brian J. Alves
Terrier Consulting, Inc.
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer

 

[jtrapat1]

I'll try to explain better of what I'm trying to do.
Here's how my output will look:

BillNum Analyst

A00144 Andriola
Anglin
Gantt
Smith

There may be more than one analyst attached to a Bill Number.
Are you saying that I need to create a separate view for my subreport?
The way I am trying it is the way that I did it in Access:

Create one view, called vwHotBills_rev (the one I posted) which gets all the data I need.
Create a standalone subreport called Analyst.
Then, reference the main view as the recordsource for my subreport, like this, with a dot operator:

SELECT vwHotBills_rev.BillNum, vwHotBills_rev.Analyst
FROM vwHotBills_rev

Maybe there is an easier way.

Any help would be appreciated.

Thanks in advance.

John

 

[balves]

Using just your original view, if you create a Group for BillNum and place the Analyst field in the Detail section you will get:

BillNum Analyst
123
A1
A2
456
A3
A4
...

Brian J. Alves
Terrier Consulting, Inc.
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer