How to supply value to SQL param in Crystal Designer from Crystal Rep? 1

[crash4ever]

Hi,
I use the SQL statement with date parameters from Crystal Designer as a source for Crystal Report's Sub Report. These parameters will show up when you try to run the report. I do not want the user to enter the date parameter twice (for the main report and the sub report).

Is there a way to connect these date parameters from SQL Designer to the main report's date parameters? Right now the date parameter show up as different parameters in the main report. So I have two Begin_Date and End_Date parameter.

Thanks,
Vicky

 

[rhinok]

I'm confused. You took the SQL statement that was generated from the master report and used the actual SQL as the basis for the subreport? That's what I interpreted from your message.

Next, I read that you have parameters in both the master and the subreport. Have you tried linking the subreport to the master report on the date parameters? This is done specifically so that you only have to enter the parameters once. You can start this process by right-clicking on the subreport object(from within the master report)and select the change subreport links option.

 

[olichap]

I don't know how you'd do it directly from Crystal (linking the sub-report parameters to the main report) but through code you have to set them one report at a time.
If you are using the Report Designer Component and VB this is one way to do it:

Note: I have a main report called "Report" that gets its parameters through the user interface. Each of the 4 sub-reports uses the same parameters. The names of my reports are similar so this line "Set rptSubRpt = Report.OpenSubreport("rptSavPatientSumm" & iint)" changes the name or the report easily based on the iint number and allows me to set variables and parameters for each sub-report. You have to loop through each sub-report and set the parameters to those on the Main report.

Private Sub SetReportVarsAllP(Optional ByVal ParNPar As Boolean)
Dim sClient As String
Dim iint As Integer
Dim rptSubRpt As New CRAXDRT.Report

sClient = sClientName
If ParNPar = False Then
sClient = sClient & " (Par)"
Else
sClient = sClient & " (Non-Par)"
Report.Database.Tables(1).Location = "SQL.dbo.Proc(rptSavPatientSum_pg1NPar;1)"
End If
For iint = 2 To 5 'report has 4 sub-reports
Set rptSubRpt = Report.OpenSubreport("rptSavPatientSumm" & iint)
With rptSubRpt
If ParNPar = True Then
.Database.Tables(1).Location = "SQL.dbo.Proc(rptSavPatientSum_pg" & iint & "NPar;1)"
End If
.ParameterFields(1).AddCurrentValue (sClient)
.ParameterFields(2).AddCurrentValue (txtCodeVar.Text)
.ParameterFields(3).AddCurrentValue (dteFromTo(0).Value)
.ParameterFields(4).AddCurrentValue (dteFromTo(1).Value)
.ParameterFields(5).AddCurrentValue (chkUmbrella.Value)
End With
Next iint
Report.ParameterFields(1).AddCurrentValue (sClient)
Report.ParameterFields(2).AddCurrentValue (txtCodeVar.Text)
Report.ParameterFields(3).AddCurrentValue (dteFromTo(0).Value)
Report.ParameterFields(4).AddCurrentValue (dteFromTo(1).Value)
Report.ParameterFields(5).AddCurrentValue (chkUmbrella.Value)
Report.EnableParameterPrompting = False
End Sub

Hope this helps; let me know if you have any questions/problems with this.

Oliver

 

[crash4ever]

Thanks for the information Oliver,
But I'm afraid that I still could not find how to link those input parameters from my SQL statement I used in my sub report to the input parameters of my Main Report.

These are the input parameters that shown on my Main Report when I try to run them:

DetailLevel
Opt1
Opt2
StartDate
EndDate
StartDate (Summary_Level_1) -- Sub Report 1
EndDate (Summary_Level_1) -- Sub Report 1
StartDate (Summary_Level_2) -- Sub Report 2
EndDate (Summary_Level_2) -- Sub Report 2
StartDate (Summary_Level_3) -- Sub Report 3
EndDate (Summary_Level_3) -- Sub Report 3

Summary_Level_# is the name of my sub report.

See how the StartDate and EndDate parameters repeated 3 times? Is there a way to link those parameters with my StartDate and EndDate parameters so that they don't show when I run the main report?

Thank you,
Vicky

 

[olichap]

Are you running the report through Crystal itself (having CR loaded on each computer) or through an application written by you?

 

[crash4ever]

I'm running the report through CR report itself since it's only a simple report. The reason is that only 2 person will have access to this report.

 

[olichap]

OK, I couldn't remember where this was yesterday since I deal with a lot of my report issues through code instead of CR itself.

Open your main report. Under EDIT you should have an option called SUBREPORT LINKS.

At the top you'll see a combo-box listing all available sub-reports. For each sub select the appropriate parameter fields from the Main as your link. On the left-hand side your parameters should be towards the bottom of the "Available Fields" list, under "Report Fields" (the have a "?" in front of them). The right-hand list-box should be populated with your parameter fields. At the bottom you then determine/link the Main report parameter to a specific sub-report parameter.

You will have to configure this for each subreport associated with the Main.

 

[crash4ever]

Hi Oliver,
I've tried that option and the sub report parameter are not listed there so I could not link my main report parameters to them.

 

[olichap]

In the sub-report itself do your parameter fields appear as Parameter Fields in the Field Explorer?

 

[crash4ever]

Yes,
They appear as parameter fields.

 

[olichap]

OK. In Subreport Links screen go ahead and select all your main report parameters into the right list-box. At the bottom in the "Subreport Parameter to use:" combo click on the drop-down arrow.

When you click on this you're probably seen nothing in the little list that is displayed. The combo box has a very small spinner control to the right of that list. I think one of the problems I had with this is that it is hard to see the UP/DOWN arrows that appear when you click the down arrow on the combo, thus leading to my doing all this through code.

You should be able to scroll up or down. I believe the Sub-report fields are listed at the bottom under "Parameter Fields."

 

[crash4ever]

Hi,
There are no "Parameter Fields" listed there. These are the only fields that listed there:

ReportField -- Have all variable I use for grouping
ReportArea -- Pretty much listed all the grouping also
DatabaseField -- List all fields I use on the sub report

I use 'String' for the input parameter fields for my subreport (both StartDate (Summary_Level_1) and EndDate (Summary_Level_1) are string). And I also use 'String' data type for my MAIN REPORT parameter fields (both StartDate and EndDate are string). Still the Parameter Fields not listed on my "Select Data in subreport based on fields".

Any other option?

Vicky

 

[olichap]

But on the reports themselves, when you are looking at the Field Explorer (Insert, Field Object) you see your parameters listed under "?Parameter Fields:"?

 

[crash4ever]

Yeah,
If I look into the sub reports, I see them listed under Parameter List. That is why I feel so strange. Is it because I use the SQL statement from SQL Designer as a source?

 

[olichap]

That I don't know. It sounds like they should be available to you in the list. Have you considered (or is it even an option) taking the SQL statement and making it a stored procedure? You are actually using the parameter fields within the report aren't you?

If not you might try dropping them in the Header section and Suppressing them. It might be as simple as that (CR might not see them as "Report Fields&quot. Check this first. In your Field Explorer do the parameters have a check next to them indicating they are used on the report itself? If not, that might be the problem.

How do you pass the variable to the SQL query? Can you paste the query here?

Here's another thought. Add a new Parameter to each sub-report for each user-input variable. On the Main report add them as Formulas. Set the formula to equal the appropriate user-input variable. Then go through the linking screen and see if THESE appear (you may have to drop them on the report and suppress them, but they should be in the list somewhere). So basically you are going to attach the new sub-report variables to equal your Main report formula.

All of this assumes the new parameters are visible in the "Subreport parameter field to use:" box.

 

[crash4ever]

Hi,
Thanks for your information and patience. I'll try your suggestion first but here is the SQL query I use in case you want to know. All of those {?<variable_name>} are the input parameter I use for the SubReport. The report itself runs perfectly but it's just too annoying the way the user has to provide input parameter several times for each subreport eventhough they all use the same input parameter variables.

SELECT
1 Active_Group,
T.NAME as TPA_Name,G.ID as Group_ID, G.NAME as Group_Name,
pm.pk, pm.id as Plan_Name,
nvl(SUM(DECODE(M.RELATIONSHIP,'1',1)),0) CRDHLD,
nvl(SUM(DECODE(M.RELATIONSHIP,'2',1)),0) SPOUSE,
nvl(SUM(DECODE(M.RELATIONSHIP,'3',1)),0) CHILDS,
nvl(SUM(DECODE(M.RELATIONSHIP,'1',0,'2',0,'3',0,1)),0) OTHER,
nvl(pbmadm.ClaimCnt.get_ClaimCnt(g.id, pm.pk, To_Date('{?Eff_Beg}','mm/dd/yyyy'), To_Date('{?Eff_End}','mm/dd/yyyy')),0) CLAIM_COUNT,
nvl(pbmadm.ClaimCnt.get_GroupPaid(g.id, pm.pk, To_Date('{?Eff_Beg}','mm/dd/yyyy'), To_Date('{?Eff_End}','mm/dd/yyyy')),0) GROUP_PAID,
nvl(pbmadm.ClaimCnt.get_PatPaid(g.id, pm.pk,To_Date('{?Eff_Beg}','mm/dd/yyyy'), To_Date('{?Eff_End}','mm/dd/yyyy')),0) PAT_PAID
FROM
PBMADM.PBM_MEMBER M,
PBMADM.PBM_MEMBER_PLAN MP,
PBMADM.PBM_PLAN_MASTER PM,
PBMADM.PBM_TPA T,
PBMADM.PBM_GROUP G
WHERE
M.PK = MP.MEMBER_PK AND
PM.PK = MP.PLNMST_PK AND
G.ID = M.GROUP_ID AND
G.IS_TEST_USAGE_ONLY = 'N' AND
G.TPA_ID = T.ID AND
MP.EFFECTIVE_BEGIN <= To_Date('{?Eff_End}','mm/dd/yyyy') AND
(MP.EFFECTIVE_END IS NULL OR
MP.EFFECTIVE_END > To_Date('{?Eff_End}','mm/dd/yyyy')) AND
PM.EFFECTIVE_BEGIN <= To_Date('{?Eff_End}','mm/dd/yyyy') AND
(PM.EFFECTIVE_END IS NULL OR
PM.EFFECTIVE_END > To_Date('{?Eff_End}','mm/dd/yyyy'))
GROUP BY
T.NAME,
G.ID, G.NAME,
PM.PK, PM.ID
UNION
SELECT
2 Active_Group,
T.NAME as TPA_Name,G.ID as Group_ID, G.NAME as Group_Name,
pm.pk, pm.id as Plan_Name,
nvl(SUM(DECODE(M.RELATIONSHIP,'1',1)),0) CRDHLD,
nvl(SUM(DECODE(M.RELATIONSHIP,'2',1)),0) SPOUSE,
nvl(SUM(DECODE(M.RELATIONSHIP,'3',1)),0) CHILDS,
nvl(SUM(DECODE(M.RELATIONSHIP,'1',0,'2',0,'3',0,1)),0) OTHER,
nvl(pbmadm.ClaimCnt.get_ClaimCnt(g.id, pm.pk, To_Date('{?Eff_Beg}','mm/dd/yyyy'), To_Date('{?Eff_End}','mm/dd/yyyy')),0) CLAIM_COUNT,
nvl(pbmadm.ClaimCnt.get_GroupPaid(g.id, pm.pk, To_Date('{?Eff_Beg}','mm/dd/yyyy'), To_Date('{?Eff_End}','mm/dd/yyyy')),0) GROUP_PAID,
nvl(pbmadm.ClaimCnt.get_PatPaid(g.id, pm.pk,To_Date('{?Eff_Beg}','mm/dd/yyyy'), To_Date('{?Eff_End}','mm/dd/yyyy')),0) PAT_PAID
FROM
PBMADM.PBM_MEMBER M,
PBMADM.PBM_MEMBER_PLAN MP,
PBMADM.PBM_PLAN_MASTER PM,
PBMADM.PBM_TPA T,
PBMADM.PBM_GROUP G
WHERE
M.PK = MP.MEMBER_PK AND
PM.PK = MP.PLNMST_PK AND
G.ID = M.GROUP_ID AND
G.IS_TEST_USAGE_ONLY = 'N' AND
G.TPA_ID = T.ID AND
MP.EFFECTIVE_BEGIN <= To_Date('{?Eff_End}','mm/dd/yyyy') AND
(MP.EFFECTIVE_END IS NULL OR
MP.EFFECTIVE_END > To_Date('{?Eff_End}','mm/dd/yyyy')) AND
PM.EFFECTIVE_BEGIN <= To_Date('{?Eff_End}','mm/dd/yyyy') AND
PM.EFFECTIVE_END < To_Date('{?Eff_End}','mm/dd/yyyy') AND
EXISTS (SELECT 1
FROM
RPT_PLAN_SUMMARY PS
WHERE
PS.GROUP_ID = G.ID AND
PS.PLNMST_PK = PM.PK AND
PS.MONTH BETWEEN To_Date('{?Eff_Beg}','mm/dd/yyyy') AND
To_Date('{?Eff_End}','mm/dd/yyyy') AND
(PS.CLAIM_COUNT <> 0 OR
PS.PAID_AMOUNT <> 0 OR
PS.PAT_PAID_AMOUNT <> 0))
GROUP BY
T.NAME,
G.ID, G.NAME,
PM.PK, PM.ID