How to get parameters to crystal sub report

mgmhicks
mgmhicks used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
right click on sub rereport          
Commented:
right click on sub rereport   > change subreport links > 

select " For subreport"  keep/set your subreport which you want to link

select  "avialable fieds " (your main report field to link)

it creates parameter automatically

check the box for "select data subreport based field" choose the sub report field you want to select

if you want to add  one more or add as many as you want for multiple fields based linking

Commented:
see below image
subrreport.png
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

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

Author

Commented:
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

Author

Commented:
I wont let me relate parameters to parameters

Author

Commented:
Here is what the report shoud look like.

thanks
SampleReport.bmp
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
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

Author

Commented:
I wont let me link to parameters.  Do I have to create formula's for the parameters?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
I was looking at the wrong box.

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

mlmcc

Author

Commented:
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.  

Commented:
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 McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

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

mlmcc

Author

Commented:
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.

 

Commented:
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
Hopefully you've got this working, or are at least getting close, but here are a few thoughts:

 Since you're using a stored procedure in the subreport, you presumably want to link objects in the main report to the stored procedure parameters in the subreport, as opposed to having the subreport filter the records by comparing the linked value with a field (as shivkasi suggested).

 When you add a field/parameter/whatever to the "Field(s) to link to" box in the subreport link dialogue, select that item, and then open the dropdown list under "Subreport parameter field to use", you should see a list of any compatible parameters in the subreport.  CR will only list the parameters that are the same basic data type.  For example, if you added the @LocationID parameter from the main report, the dropdown list should show the @LocationID parameter from the subreport and allow you to link the main report parameter to the subreport parameter.  CR will not show the other subreport parameters there, because they're different data types (datetime and char).

 If you add something to the "Field(s) to link to" box and the dropdown list under "Subreport parameter field to use" doesn't list any of the stored procedure parameters from the subreport, that normally means that the item that you're trying to use for the link is not the right data type.  For example, the @AgentCode parameter in your subreport stored procedure is char.  If the agentcode column in your main report stored procedure is actually an int, you won't be able to link agentcode to @AgentCode.  In a case like that, you could either change the @AgentCode parameter in the subreport stored procedure to also be an int, or create a formula in the main report that converts agentcode to a string, and link that formula to @AgentCode.

 Hopefully this may make some things a bit clearer.

 James

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial