Passing Report Parameters to a BAQ Report

Epicor ERP 10
, ,
1

I’ve tried passing parameters to a BAQ report as described in previous posts.

  • created a baq with an ‘AsOfDate’ parameter which is used in calculated columns to display how old an invoice is based on the ‘AsOfDate’
  • created a baq report from that baq, downloaded the SSRS report, added totals and some formatting and uploaded the SSRS report
  • at this point, testing the report from BAQ Report Designer will run but does not use the AsOfDate parameter in the calculated columns since there was no way to provide it
  • created a Report Data Definition with the baq as the data source
  • created a Report Criteria Set in the Report Data Definition mapping a prompt for the AsOfDate parameter
  • created a Report Style for a SQL Server Reporting type with the above data definition and report criteria set

When I run the report from a menu item, the Report Options display the AsOfDate but I get the following error:

image
image1921×1041 123 KB

The report server shows the following error:
processing!ReportServer_0-76!a34!09/16/2020-11:10:05:: e ERROR: An exception has occurred in data set ‘BAQReportResult’. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘BAQReportResult’. —> System.Data.SqlClient.SqlException: Invalid object name ‘dbo.BAQReportResult_79aee6cd65f34a3ba36f6ba3ba5b6930’.
processing!ReportServer_0-76!1750!09/16/2020-11:10:05:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: [AbnormalTermination:ReportProcessing], Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘Company’. —> System.Data.SqlClient.SqlException: Invalid object name ‘Company_79aee6cd65f34a3ba36f6ba3ba5b6930’.

What am I doing wrong?

Thank you!

2

Pleas attach the report and baq.

3

NAC_AR_AgingAsOf.baq (29.1 KB) RDDNAC_AR_AgingAsO.xml (10.5 KB) BAQReport.xml (868 Bytes) NAC_AR_AgingAsO.rdl (70.2 KB)

4

Although Epicor is moving towrds using RDD’s for BAQ Reoprts too, you could have just added the "As Of Date " as an Option in BAQ Report Designer.

image

5

Its a longer process, but you can manually create a new Report Style, Report Data Definition (RDD), RDL file and link the BAQ to the new RDD.

1 Like
6

Thank you Calvin - I tried that but I need to use the AsOfDate parameter in calculated columns and those calculations did not get the AsOfDate when I did that. I was able to get the Options field to work for filters in the baq but not in calculated columns. Is there a way for me to access that options field value in the ssrs report? Then I can just do the calculations there.

7

Thank you Jason -
I thought that was what I did - what am I missing?

  • created a baq with an ‘AsOfDate’ parameter which is used in calculated columns to display how old an invoice is based on the ‘AsOfDate’
  • created a baq report from that baq, downloaded the SSRS report, added totals and some formatting and uploaded the SSRS report
  • at this point, testing the report from BAQ Report Designer will run but does not use the AsOfDate parameter in the calculated columns since there was no way to provide it
  • created a Report Data Definition with the baq as the data source
  • created a Report Criteria Set in the Report Data Definition mapping a prompt for the AsOfDate parameter
  • created a Report Style for a SQL Server Reporting type with the above data definition and report criteria set
8

You can add new Report Style in Standard AR Aging Report and modify the format as per your requirement. That will take care of all calculations and Aging.

9

Ahhh… I misunderstood. You parameter is truly an input and needs to be used inside the BAQ. Not as a “filter” on the results of the BAQ.

If you made the BAQ Report first, there’s a chance there’s a conflict in the Report Style’s Data Definition.

When you make a BAQ Report, that BAQReport ID becomes both the ReportID AND the Data Definition used in Report Style

image
image1019×716 40.8 KB

A “blank” RDD will be created using that ReportID (E10H-Test in my example). I can add the BAQ and criteria sets and everything.

But back in Report Style, the Data Definition field dropdown only lists one entry for E10H-Test. Is that for the BAQ Reprot or the RDD?

image

1 Like
10

That’s a good question since I’ve named both of them the same. Could that be adding to the confusion? Which one would I want it to be?

Thank you for you help!

11

I was able to accomplish this by

  • pulling the calculations out of the BAQ

  • adding ReportParam_Date01 as an option field in BAQ Report Designer

  • Downloading the ssrs report

  • Cross Joining the BAQReportParameter table and adding the Date01 from the BAQReportParameter table to the list of fields returned by the select in the query expression of the BAQReportResult dataset in the ssrs report

  • Adding the calculated fields to the Fields dataset property of the BAQReportResult dataset in the ssrs report using the Date01 added above and formatted the report

  • Uploading the ssrs report

1 Like