asked on

How to get parameters to crystal sub report

I have a report that list a location as a group and then list the property as another sub group. Here is the stucture of the report
Westgate Group - Group1
Hampton Count - Group 2
Agentid Agent Name 0 0 0 0 detail sub-report
AgentID Agent Name 1 3 3 3 detail sub-report
Spring Meadown =- Group2
Agentid Agent Name 0 0 0 0 detail sub-report
AgentID Agent Name 1 3 3 3 detail sub-report

Heathwood Village - Group1

So I have the sub report in the detail, I want to send the subreport the agentID and a couple of dates (begin and End) to have it generate a number from sql statement to show sales. The sub report get all sales from that agentid for a certain date period. The sub report just shows the count of the detail items.

My problem is getting the agentid and the dates to the subreport for each agentid in the detail lines. Can anyone help? Thanks in advance

Shiv

right click on sub rereport

ASKER CERTIFIED SOLUTION

Shiv

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shiv

see below image
subrreport.png

James0628

That's one method. Personally, I like to manually create the parameters in the subreport and then link the objects in the main report to the subreport parameters, rather than having CR create the subreport parameters, as in shivkasi's example. I'm not saying that my way is better. Doing as shivkasi described should work fine. This is just a slightly different way to handle it.

To use this approach, you'd edit the subreport, add parameters for the Agent ID, start date and end date to the subreport, change the record selection in the subreport to use those parameters to filter the records, and then in the main report go to the subreport link screen and link the objects in the main report to the parameters that you just created in the subreport.

James

mgmhicks

ASKER

Here is the code for the 2 reports. Somwhere along the line I'm missing why on each detail line, I can not get the amount of leases signed during that payperiod for that agentid. So the top code creates the report, and then I have the subreport getting the total leases, that I would like to show on each line of the agent totals.

Question is how to pass variables from main report to subreport to pull total leases.

thanks

code to get employee payroll info

CREATE PROCEDURE [dbo].[PayPeriodReport_Summary]  @LocationID int, @PayPeriod int
AS
if @LocationID<> 99
select time_employees.id,fname,lname,locationid,agentcode,time_curtrans.*,day1+day2+day3+day4+day5+day6+day7+AutoOT1 as week1_hours, day8+day9+day10+day11+day12+day13+day14+AutoOT2  as week2_hours
,time_payperiod.begindate,time_payperiod.enddate  from time_employees 
left outer join time_curtrans on time_employees.id = time_curtrans.empid
left outer join time_payperiod on time_payperiod.id = time_curtrans.payperiod
where time_employees.locationid =@LocationID and time_curtrans.payperiod = @PayPeriod 
else
select time_employees.id,fname,lname,locationid,Agentcode,time_curtrans.*,day1+day2+day3+day4+day5+day6+day7+AutoOT1 as week1_hours, day8+day9+day10+day11+day12+day13+day14+AutoOT2 as week2_hours
,time_payperiod.begindate,time_payperiod.enddate  from time_employees
left outer join  time_curtrans on time_employees.id = time_curtrans.empid
left outer join time_payperiod on time_payperiod.id = time_curtrans.payperiod
where time_curtrans.payperiod = @PayPeriod
GO

Code to get total leases for each employee.  This is in different database.

CREATE PROCEDURE [dbo].[TAG_GetSignedLeases]  @BeginDate datetime, @EndDate datetime, @LocationID int, @AgentCode char(5) AS

select leasesigndate,leaseagentcode from lease 
where(leasesigndate >=@BeginDate and leasesigndate <=@EndDate)
 and leaseAgentcode =@AgentCode
 and propertyid in (Select propertyid from locationlist where locationid = @LocationID)
GO

Open in new window

mgmhicks

ASKER

I wont let me relate parameters to parameters

mgmhicks

ASKER

Here is what the report shoud look like.

thanks
SampleReport.bmp

Mike McCracken

Link it to @datetime - that is the stored procedure parameter.

The method being suggested above assumed you were linking to a Crystal parameter or just a database field.

mlmcc

mgmhicks

ASKER

I wont let me link to parameters. Do I have to create formula's for the parameters?

Mike McCracken

I was looking at the wrong box.

What shows in the Subreport fields to link to dropdown?
WHat about the parameter dropdown?

mlmcc

mgmhicks

ASKER

There is nothing showing as a link. However if I choose lets say @agentID there is nothing to match it to on the subreport, cause the parameters arent listed.

Shiv

then try with james comment ...

To use this approach, you'd edit the subreport, add parameters for the Agent ID, start date and end date to the subreport, change the record selection in the subreport to use those parameters to filter the records, and then in the main report go to the subreport link screen and link the objects in the main report to the parameters that you just created in the subreport

Mike McCracken

The selection criteria is in the Stored Procedure. Unless that is changed adding Crystal criteria still leaves the same issue

mlmcc

mgmhicks

ASKER

What looks like is working now, is pulling all the records in the subreport. Create formula fields in the sub report for the selection criteria variables. Attach the main report variables to the formuala fields of subreport. This is what I'm testing now.

Shiv

did you try with giving some/default values for the parameter and then link the reports...like 1) first without linking place the reports with query 2) then run and give the default values 3) then link sub report to main report

SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial