Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Subreport and stored procedure issue 1

Status
Not open for further replies.

azoe

Technical User
Feb 21, 2005
175
US
I've pasted the stored procedure for my subreport below.
I'm a beginner with subreports so maybe I should be trying something easier first. Some info I read says not to have parameters in your subreport. Does that mean I need to comment out everything referring to @ProviderDescription, @StartDate and @EndDate in the procedure below?
I tried that and right now the subreport doesn't return any data.
The only way I'm getting it to work right now (in another version I've saved)is by putting in the parameters over and over for each of my 4 subreports (all the parameters are the same as in the one below).
Thanks for any ideas -

Alter Procedure IDPGetRenderReferSplit100Percent
@ProviderDescription varchar(30),
@StartDateStr varchar(10),
@EndDateStr varchar(10)

as

If (isNull(@ProviderDescription, '')='') or (@ProviderDescription = '*')
Set @ProviderDescription = '%'
Else
Set @ProviderDescription = @ProviderDescription + '%'

/* Convert date strings to dates */
DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

/*Set the from date */
IF ISDATE(@startDateStr) = 1 /* 1 is the return value of ISDATE for most date formats input */
SET @startDate = CONVERT(datetime, @startDateStr)
ELSE
SET @startDateStr = GETDATE()

/*Check the to date for a valid value and set the to date */
IF ISNULL(@endDateStr, '') = '' SELECT @endDateStr = @startDateStr
/* if you just want one day enter start date and leave end date blank '20040901', '' */
IF ISDATE(@endDateStr) = 1
SET @endDate = CONVERT(datetime, @endDateStr)
ELSE
SET @endDateStr = GETDATE()


SELECT t.type, td.paid_amt, td.adj_amt, pm.desc_30 as rendering, (
SELECT [desc_30]
FROM [Provider_mstr]
WHERE [Provider_ID] = [V].[Refer_Phys_ID]
) AS [Referring], ChargeSIM.service_item_id, v.visit_id, mlist.desc_30 as dept,
p.claim_type, p.payer_name, t.closing_date

from
trans_detail td
inner join transactions t on td.trans_id = t.trans_id
left outer join (
SELECT [Charges].[Charge_ID],
[Charges].[Rendering_ID],
[Charges].[Service_Item_ID],
(
SELECT TOP 1 [Department]
FROM [Service_Item_mstr] AS [Services]
WHERE [Charges].[Service_Item_Lib_ID] = [Services].[Service_Item_Lib_ID]
AND [Charges].[Service_Item_ID] = [Services].[Service_Item_ID]
) AS [Department]
FROM [dbo].[Charges])as chargeSIM on ChargeSIM.charge_id = td.charge_id
left outer join visits v on t.source_id = v.visit_id
left outer join provider_mstr pm on ChargeSIM.rendering_id = pm.provider_id
left outer join payer_mstr p on t.payer_id = p.payer_id
left outer join mstr_lists mlist on chargeSIM.department = mlist.mstr_list_alias

WHERE
t.closing_date BETWEEN @StartDate and @EndDate
and t.type <> 'A'
and Chargesim.service_item_id in ('93307', '93000', '93224', '94240', '95811')
AND NOT
(
(Chargesim.service_item_id is Null or Chargesim.service_item_id in ('BF', 'Int', 'Misc'))
or pm.desc_30 is Null or pm.desc_30 not in (a list of names goes here)
or chargeSIM.department is Null)

and ( v.refer_phys_id = 'PH14K14SJT6J' and pm.desc_30 like @ProviderDescription)

 
Since the parameters are in the SP, you've no choice but to have them in the subreport.

Avoiding subreports is key if they're in a group or detail section.

Knowing your software version, database/connecitivity affects the resonse I might give, so please remember to post the basics.

I think that if you create the parameters in the main report, and then link them to the subreports you can avoid the multiple prompts, check out:


-k
 
Sorry - I should have included that. I've just installed Crystal 11 (previously was using 8.5), and the database is MS SQL, and I'm connecting to the stored procedure (sp) with ODBC. The subreports are in report footers a, b, c and d.

The main report sp (pasted below) is virtually identical to all the subreports sp's except for the Where clause so if I knew of a way not to include the parameters in the subreports I would.

Thank you for the article but if I follow that I will have to change the static formulas each time I run it and I'm not sure if that's much better than re-inputting the parameters (I run the report 40 times (once for each of 40 different employees the report shows figures on) once a month.

I wonder if there is a way to use the list of employees in some way to be able to run the report by itself 40 times. Or maybe all the data should be in subreports and the main report could just have a group for these employees. I'm running out of ideas.
Thank you.

SELECT
t.type,
td.paid_amt,
td.adj_amt,
pm.desc_30 as Rendering, (
SELECT [desc_30] /* use this subselect to avoid linking to the provider_mstr table again */
FROM [Provider_mstr]
WHERE [Provider_ID] = [V].[Refer_Phys_ID]
) AS [Referring],
ChargeSIM.service_item_id,
v.visit_id,
mlist.desc_30 as dept,
p.claim_type,
p.payer_name,
t.closing_date

from
trans_detail td
inner join transactions t on td.trans_id = t.trans_id
left outer join (
SELECT [Charges].[Charge_ID],
[Charges].[Rendering_ID],
[Charges].[Service_Item_ID],
(
SELECT TOP 1 [Department] /*get Department the first time for any SIM - otherwise it is repeated for price changes */
FROM [Service_Item_mstr] AS [Services] /*subselect to get SIM only once - in there several times because price changes*/
WHERE [Charges].[Service_Item_Lib_ID] = [Services].[Service_Item_Lib_ID]
AND [Charges].[Service_Item_ID] = [Services].[Service_Item_ID]
) AS [Department]
FROM [dbo].[Charges])as chargeSIM on ChargeSIM.charge_id = td.charge_id
left outer join visits v on t.source_id = v.visit_id
left outer join provider_mstr pm on ChargeSIM.rendering_id = pm.provider_id
left outer join payer_mstr p on t.payer_id = p.payer_id
left outer join mstr_lists mlist on chargeSIM.department = mlist.mstr_list_alias

WHERE
t.closing_date BETWEEN @StartDate and @EndDate
and t.type <> 'A' /*don't need Adjustments (only pmts and refunds) */
/*use for RENDERING*/ and Chargesim.department in ('ML14K02CKUOW', 'ML14K02CKARR', 'ML14K02CKR2U', 'ML14K02CL1AB', 'ML14K02CMZH8', 'ML14K02CN6OT', 'ML14K02CNNGN', 'ML14P92AMI6A')
/*use for RENDERING*/ and Chargesim.service_item_id not in ('93307', '93000', '93224', '94240', '94060', '94010', '95810', '95806', '95805', '95811')
AND NOT
(Chargesim.service_item_id is Null or Chargesim.service_item_id in ('BF', 'NOS', 'RETFEE', 'Bd', 'Int', 'Misc'))

and (pm.desc_30 like @ProviderDescription)
 
I think that you can use the main report parameters in the statiuc variables, did you try that?

I didn't really delve into the architecture, reading SPs is a pretty inefficient way to understand requirements.

-k

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top