Adding subreport to AR Form

LynnDavid · June 17, 2019, 4:45pm

I am trying to add a subreport to AR Form in E 10.2.300. I have 4 Parameters on the Subreport and added the subreport to the Parent Report. I added the parameters to the subreport on the Parent report as well. The subreport will not display. I checked the report server log and it says "One or more parameters were not specified for the subreport.
Any information would be greatly appreciated. Also do not know what information is needed. Trying to display all Tracking numbers from CartonTrkDtl table.

Thank you!!!

utaylor · June 17, 2019, 6:43pm

When you right click the subreport in the parent report and go to subreport properties>parameters… are all of the subreport parameters mapped with the correct fields from your parent report?

LynnDavid · June 17, 2019, 7:13pm

Here is my Query for SubReport.
="SELECT T1.InvoiceNum, T1.PartNum,T1.SalesUM,T1.PartNum_PartDescription,T1.InvoiceNum as InvcDtl_InvoiceNum,T1.PackNum,T1.DocUnitPrice,T1.DocExtPrice,T2.RptLiteralsLPartNum,T2.DisplaySeq as Label_DisplaySeq ,T2.RptLiteralsLDescription,T2.UnitPrice as RptLiteralsLUnitPrice,T2.RptLiteralsLExtPrice,T1.XPartNum, T1.Calc_LineDesc, T3.Calc_CustPartOpts, T2.RptLiteralsLINDiscount, T1.DocDiscount

FROM InvcDtl_" + Parameters!TableGuid.Value + " T1

LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T2 ON T1.RptLanguageID = T2.RptLanguageID

LEFT OUTER JOIN CartonTrkDtl_" + Parameters!TableGuid.Value + " T3 ON T1.Company = T3.Company AND T1.PackNum = T3.PackNum
LEFT OUTER JOIN InvcHead_" + Parameters!TableGuid.Value + " T4 ON T1.Company = T4.Company AND T1.InvoiceNum = T4.InvoiceNum

WHERE T1.InvoiceNum = ‘" + cstr(Parameters!Pm_INVCHEAD_InvoiceNum.Value) + "’ " + " AND T1.PackNum= ‘" + cstr(Parameters!Pm_INVCDTL_PackNum.Value) + "’ " + " AND T1.RptLanguageID = ‘" + cstr(Parameters!Pm_INVCHEAD_RptLanguageID.Value) + "’"

Here is my Parameters in SubReport.

And here is Parameters from Parent Report for SubReport.

utaylor · June 17, 2019, 7:32pm

Does the sub report run by itself when you pass those parameter values?

LynnDavid · June 17, 2019, 8:08pm

No. For some reason it says Cannot set the command text for dataset ‘InvcDtl’. (rsErrorSettingCommandText). I am using the same datasource for AR Form.

utaylor · June 17, 2019, 8:18pm

I am not certain what is wrong with the syntax in your query, but I bet it has something to do with the where clause. Try taking out the where clause and then do each of the joins one at a time with a test between each additional join. Then you can narrow it down to the problem.

LynnDavid · June 17, 2019, 8:23pm

I took out the Where clause and get the same error.

utaylor · June 17, 2019, 9:01pm

So this doesn’t work in a new, blank sub report?

    ="SELECT T1.InvoiceNum, T1.PartNum,T1.SalesUM,T1.PartNum_PartDescription,T1.InvoiceNum as InvcDtl_InvoiceNum,T1.PackNum,T1.DocUnitPrice,T1.DocExtPrice,T1.XPartNum, T1.Calc_LineDesc, T1.DocDiscount

    FROM InvcDtl_" + Parameters!TableGuid.Value + " T1"

LynnDavid · June 18, 2019, 12:41pm

I will try just that query and see what happens.

LynnDavid · June 18, 2019, 12:47pm

That does work and I am able to run the subreport with Guid.

utaylor · June 18, 2019, 1:10pm

Okay, start adding each join to the query. Then you will know which one is causing an issue.

LynnDavid · June 18, 2019, 1:13pm

This query is working and are the 2 tables I need.
="SELECT T1.InvoiceNum, T1.PartNum,T1.PackNum,T1.DocUnitPrice,T1.DocExtPrice, T2.TrackingNumber

FROM InvcDtl_" + Parameters!TableGuid.Value + " T1 LEFT OUTER JOIN CartonTrkDtl_" + Parameters!TableGuid.Value + " T2 ON T1.Company = T2.Company AND T1.PackNum = T2.PackNum"

Now I need to add the parameters which seems to be where it all goes wrong.

LynnDavid · June 18, 2019, 1:30pm

I added the parameters on the subreport and the subreport runs with the parameters. I add the subreport to the parent report and it says Error: Subreport could not be shown.

utaylor · June 18, 2019, 1:34pm

I know I’ve ran into this before, but I didn’t document how I resolved it. Maybe someone else on the forum knows.

utaylor · June 18, 2019, 1:35pm

If you just pass 2 parameters like the invoicenum and the guid and take out your joins to carton dtl, does it work?

LynnDavid · June 18, 2019, 1:49pm

Does not work. In the Parent Report I tried the Parameters for SubReport for InvoiceNum as Pm_INVCDTL_InvoiceNum to InvoiceNum (which is InvcHead) and Pm_INVCDTL_InvoiceNum to InvcDtl_InvoiceNum. Neither works. Still get error.

utaylor · June 18, 2019, 1:55pm

Can you post the error message again? From the system monitor?

LynnDavid · June 18, 2019, 2:31pm

There is no error showing for the subreport for the AR Invoice. Only place it is showing is in the Report Server Log.