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)
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)