Sub-report Parameters 1

[LT2]

Using CRXIr2 w/SQL 2005

I have two reports created with a SQL command with the parameters as part of the command: {?StartDate}, {?EndDate} & {?ClientID}.

I've linked on all three parms. If I populate with a ClientID both reports display results for that ClientID.

But, if I don't populate the ClientID parm and use --Any Value-- for all, the report displays two sets of results where the sub-report isn't necessarily inclusive to the parent report.

How can I force the sub-report results to be ONLY for those clients that display in the parent report exclusively?

LT

 

[fisheromacse]

use subreport links, and link the client ID in the subreport to the ClientID in the main report.

in the main report, right click on the inserted subreport and choose 'Change SubReport Links'

 

[lbass]

Did you link using the dropdown in the lower left of the subreport linking screen to select {?Parm} instead of the default {?pm-?Parm}? Also in the sub, you have to reference the parms in the selection formula--just as you did in the command.

If you are still having trouble, please show both the command and the sub selection formula.

-LB

 

[LT2]

I linked the {?StartDate} to the drop down {?StarDate} and the like for EndDate and ClientID.

From the first tip, I then added another link of Command.ClientID from the container to sub-report ?Pm-Command.ClientID with the check to select sub-report based on field Command.ClientID - This rendered 44 clients for the container report and only 1 for the sub-report.

The {?StartDate} and {?EndDate} are in the SQL command, the ClientID parm was created on the Crystal side for both reports.

I'm not sure what you mean by sub selection formula.
This is what I have in my select expert for both the container and sub-report

({?ClientID}='--Any Value--' or
{Command.ClientID}={?ClientID})

The SQL command for the container:

Select ac.ClientID, (dg.Last+ ','+ ' '+ dg.First)as 'Client', ag.Agency, 
	sc.ServCat, t.Txdate, t.TxUnits, tc.TxCode, tc.TxDesc,
	pv.PayorAmt, p.Payor, pv.Co-pay, b.Enum, b.AdjDate, pv.PRS, cy.County, 
	ph.Short, p.ID,

	Case	When pv.PayorAmt < 0 Then 'Void'
			When pv.PRS = 99 Then 'Reallocate'
            Else 'none' 
            End as 'AdjustType'

From V_Pmts pv
	inner join tClient ac on pv.ClientNum = ac.ClientNum
	inner join tProv ag on ac.ProvNum = ag.ProvNum
	left join tPayor p on pv.PID = p.PID
	inner join tBillDet bd on pv.BDID = bd.BDID
	left join tbill b on bd.BID= b.BID
	inner join tTreat t on bd.TID = t.TID
	inner join tProvTxrCode atc on t.ProvTxCodeID = atc.ProvTxCodeID
	inner join tTxCode tc on atc.TCID = tc.TCID
	left join tTxDateSpec tcds on tc.TCID = tcds.TCID
	left join tServiceCat sc on tc.ServCat = sc.ServCatID
	inner join tDemog dg on ac.ClientNum = dg.ClientNum
	inner join tDemogrAdd dga on dg.ClientNum = dga.ClientNum
	left join tCounty cy on dga.County = cy.CountyNum
	left join PIHP ph on dga.PI = ph.PIID
	
	
Where bd.status = 2  --Paid
	and b.adj = 1    --Adjudicated
	and t.TxDate between isnull (dga.from, '1/1/1900') and isnull (dga.to, '1/1/2020')
	and tcds.UVC like '%HD%' -- Women Spec
	and t.TxDate between {?1_StartDate} and {?2_EndDate}

 

[lbass]

I guess you are using a command in the sub as well.

I'm wondering where the subreport is located in the main report. If it is in a report header or footer, it will only show one customer because you have linked on client.

Also, I don't see any {?Client} used in the main report command.

-LB

 

[LT2]

Hi LB,

Yes, I'm using a command in the sub as well. Do you want that code too? Sorry.

The subreport is located in Report Footer d

That is correct, {?ClientID} is not in the command of either report but is a parameter for both on the Crystal side.

Should I link to something else or? Where should I place a subreport if not in a report footer?

LT

 

[LT2]

Here is the code for the sub-report:

SELECT DISTINCT ac.ClientID, bda.Admit, bda.AdmitDate,
  ag.Agency, sc.ServCat, yn.Descrip as 'Preg',  bda.Age, yn2.descrip as 'MH', dga.City, 
  c.County, p.Payor, dga.Zip, bda.WSS, sx.Sex as 'Gender'
FROM Admission as bda
	inner join tClient ac on bda.ClientNum = ac.ClientNum
	inner join tProv ag on ac.ProvNum = ag.ProvNum
	left join tServiceCat sc on bda.ServCat = sc.ServCatID
	left join tYsNo as yn on bda.Preg = yn.RecordID
	left join tYsNo as yn2 on bda.MH =yn2.RecordID
	inner join tDemog dg on ac.ClientNum = dg.ClientNum
	inner join tDemogrAdd dga on dg.ClientNum = dga.ClientNum
	left join tCounty c on dga.County = c.CountyNum
	left join tTxPay tp on bda.TxID = tp.TxID
	left join tPayor p on tp.PID = p.PID
        left join tSex sx on bda.sex = sx.SexNum
WHERE bda.Admitdate between isnull (dga.from, '1/1/1900') and isnull (dga.to, '1/1/2020')
	and bda.AdmitDate between {?1_Startdate}  AND {?2_Enddate}
	and tp.PRS = 1--Primary Payor
	and TestProv = 0
	and bda.WSP = 1 -- YES
ORDER BY ac.ClientID,bda.Admit

LT

 

[lbass]

If you are using commands, you should not be adding a selection formula in the CR interface--it should be built into each command. Perhaps you did this though because it is a multiple value parameter? Could it be changed to a range parameter and built into the commands? If you are linking on the client ID field (not parameter), a subreport in the report footer would only pick up the value of the last client in the main report. Instead, the sub should be placed in the client group of the main report. If instead you linked on a clientID parameter and added a selection formula within each command that references the parameter, the sub could stay in the report footer.

-LB

 

[LT2]

I did build {?ClientID} in CR interface because there is either one known value or many unknown values. So I want the end user to be able to look up a client or all of them when running the report. So, I don't know how to get the same result using SQL when many values are unknown, therefor a range will not work.

Yep you are correct, it did pick up the last client from the container when I added the link for the command.clientid.

I can remove the 4th link so only {?StartDate}, {?EndDate} and {?ClientID} parms are linked to each other. Can you give me an example of how the selection formula you reference in, "If instead you linked on a clientID parameter and added a selection formula within each command that references the parameter, the sub could stay in the report footer.", would work?

If I build the {?ClientID} in the command than one will be required and if unknown the report is useless as there is no list to choose from.

Does this make sense?

 

[lbass]

If you are saying you want all clients or one, you can add an option to your picklist of 0 to mean 'all' and provide prompt text that explains this, e.g., "For all clients, choose '0'; otherwise, select a client ID." Then use a formula like this (assuming ID is a number):

and
(
{?ClientID} = 0 or
client.ID = {?ClientID}
)

-LB

 

[LT2]

The thing is, I don't have a picklist and can't generate one because the id's are unknown. Is there a way to generate a picklist for the sub-report based upon the container?

 

[lbass]

Please explain what you mean by "the ID's are unknown"--do you mean they change or are added to? Why do you want a parameter for ID in the first place then?

If you are just trying to get the same list of clients in both reports, then use the same selection criteria in each.

-LB

 

[LT2]

This report is being designed for multiple use.

The data is pulled by treatment date so within a specific time, one can see treatment history and costs for clients during that time. Depending upon the dates used, clientIDs will change and this is why I'm saying they are unknown. This is when the user is pulling data for all clients just to get the treatment info. (container)

The sub-report purpose is to supplement the data with demographic information for only those clients listed in the container.

There will be times when a user will want to pull the treatment history on a particular client where the clientID is known. And when used now, both reports do provide information for just that client.

My problem lies with the first scenario for the subreport to only provide information for those clientIDs listed in the container report when pulling for all.

~LT

 

[lbass]

Okay, you currently have the same parameters in both container and subreport, correct? And the {?ClientID} in the main report is linked to {?ClientID} in the subreport. So you can build the same selection formula into both main report and subreport selection criteria within the respective commands, as I showed earlier:

and
(
{?ClientID} = 0 or
client.ID = {?ClientID}
)

The user can choose a specific client or in the prompt text, tell them to choose 0 for all clients that meet your other criteria.

The sub can stay in the report footer then.

-LB