Using ADP Subreports with Stored Procedures with Parameters

[Gal]

Hi - anyone out there that can help a tormented soul??!!

I am in the process of doing my first MDB to ADP conversion, and have
come across my first sub-report which I am trying to pass parameters to
via a SP.

Having read through the groups I understand the conflict between using
an SP as a record source and the link parent/child settings on the
form, and found an entry under "solution! subreport based on
parameterized sp" which sounded promising, where the one SP contains
both real queries and uses a "type" parameter to make the parent
query run the child query effectively.

Having tried to implement it, I get no obvious errors, but neither do I
get any data in the subreport.

One thing I am struggling with is how to test my TSQL (in Query
Analyser say) to call the "child".

Alternatively, I am open to any suggestions for alternatives.

Just in case it helps, here is my TSQL (although I have been chopping
it up a bit to try to make it work - so apologies in advance).


ALTER procedure dbo.usp_ContractsChargeOLDetByDate @QType int = 0,
@ContractID bigint ,
@RptContact varchar(100),
@StDate datetime, @EndDate datetime,
@DrCrSet smallint,
@ConUID bigint

AS
--Based on usp_ContractsChargeOLDet
--Used for rptContractChargeDateOLDet
if @QType =0 --Main Form Data
begin
SELECT 'From ' + convert(Char(10),@StDate, 103) + ' to ' +
convert(Char(10),@EndDate, 103) AS RptTitle,
I.ItemUID, I.ItemCode, ConUID,
CASE WHEN CTo.ParentContractUID is null
THEN CTo.Contract
ELSE CPar.Contract END AS ParentContract,
CASE WHEN CTo.ParentContractUID is null
THEN CTo.[Report Contact]
ELSE CPar.[Report Contact] END AS RepContact,
CTo.Description as ConToDesc,
TypeDesc + CASE WHEN ItemDesc IS NULL THEN '' ELSE ' ' + ItemDesc
END AS FullDesc,

sum(CASE DrCr WHEN -1 THEN [Qty]*CCOL.[itemValue]*[DrCr] ELSE 0 END)
AS DrChg,
sum(CASE DrCr WHEN 1 THEN [Qty]*CCOL.[itemValue]*[DrCr] ELSE 0 END)
AS CrChg,

10 AS QType ,-1 AS DrCrSet

FROM tblContractChgWOFFScrap CCOL
LEFT JOIN tblItems I
ON I.ItemUID = CCOL.ItemCode
LEFT JOIN tblTypes T
ON T.TypeUID = I.ItemType
JOIN tblContracts CFrom
ON CFrom.ContractUID = CCOL.ConFromUID
JOIN tblContracts CTo
ON CTo.ContractUID = CCOL.ConUID
right JOIN tblContracts CPar
ON CPar.ContractUID = CTo.ParentContractUID


WHERE CASE WHEN CTo.ParentContractUID is null
THEN CTo.[Report Contact]
ELSE CPar.[Report Contact] END >= (CASE @RptContact WHEN '' THEN
'0' ELSE @RptContact END) AND
CASE WHEN CTo.ParentContractUID is null
THEN CTo.[Report Contact]
ELSE CPar.[Report Contact] END <= (CASE @RptContact WHEN '' THEN
'Z' ELSE @RptContact END) AND
CCOL.ConUID >= (CASE WHEN @ContractID Is null THEN 0 ELSE
@ContractID END) AND
CCOL.ConUID <= (CASE WHEN @ContractID Is null THEN 9999999 ELSE
@ContractID END)
AND ChgDate>= @StDate AND ChgDate<= @EndDate

GROUP BY I.ItemUID, I.ItemCode, ConUID,
CASE WHEN CTo.ParentContractUID is null
THEN CTo.Contract
ELSE CPar.Contract END ,
CASE WHEN CTo.ParentContractUID is null
THEN CTo.[Report Contact]
ELSE CPar.[Report Contact] END ,
CTo.Description,
TypeDesc + CASE WHEN ItemDesc IS NULL THEN '' ELSE ' ' + ItemDesc
END

ORDER BY CASE WHEN CTo.ParentContractUID is null
THEN CTo.[Report Contact]
ELSE CPar.[Report Contact] END

end

if @QType=10
begin
SELECT CCOL.ItemCode, ConUID,
ChgDate, Qty ,
[Qty]*CCOL.[itemValue]*[DrCr] as TotChg

FROM tblContractChgWOFFScrap CCOL
LEFT JOIN tblItems I
ON I.ItemUID = CCOL.ItemCode
LEFT JOIN tblTypes T
ON T.TypeUID = I.ItemType
JOIN tblContracts CFrom
ON CFrom.ContractUID = CCOL.ConFromUID
JOIN tblContracts CTo
ON CTo.ContractUID = CCOL.ConUID
right JOIN tblContracts CPar
ON CPar.ContractUID = CTo.ParentContractUID


WHERE CASE WHEN CTo.ParentContractUID is null
THEN CTo.[Report Contact]
ELSE CPar.[Report Contact] END >= (CASE @RptContact WHEN '' THEN
'0' ELSE @RptContact END) AND
CASE WHEN CTo.ParentContractUID is null
THEN CTo.[Report Contact]
ELSE CPar.[Report Contact] END <= (CASE @RptContact WHEN '' THEN
'Z' ELSE @RptContact END) AND
CCOL.ConUID = @ConUID
AND ChgDate>= @StDate AND ChgDate<= @EndDate
AND DrCr = @DrCrSet
end

 

[Gal]

Having shut down for the night, I just had one more go, and this time
got a load of parameter prompts that I wasn't getting before...

Consequently I have added these parameters to the output of the first
Qtype 0 query, and now get a message "You can't set the record source
property in print preview or after printing has started"...

The code setting the record sources is on the open event of the main
report and the sub report respectively.

 

[giorgio rancati]

Hi Gal,

view this solution

 

[Gal]

Thanks - will read through and have a go.