BAQRpt & QuickSearch Pass Silent Parameters to BAQ

[hkeric.wci (Haso Keric)]

In addition to the previously requested feature:

Dashboard Pass Silent Parameters to BAQ

A Feature asked for ages; never delivered by Epicor yet it seems like a simple one… In a Dashboard allow Passing in Parameters to the BAQ Before Execution so we can stop running wide-open BAQs which hammer the server because Post-Filtering is not the answer. Example: Click on Parent Grid and have the Child Re-Run BAQ with specific Params. So if you are doing lets say CTEs Recursive or Sub-Queries and SUM() and MAX() you will get “accurate” number with little effort because each time the BAQ wil…

We the developer community would extremely find it useful to do something similar to BAQ Reports and Quick Searches, so we can pass one of those Form Bindable fields to the BAQ before it runs. Just like Dashboards… BAQRpt’s BAQ Grabs 10,000,000 rows (lets say you have that many Jobs) and then filters it after execution, thats the only reason I try to avoid them BAQRpts and Quick Searches etc… all have to run wide-open and can’t bind to a param.

Lets say a Quick Search if I can atleast bind the text-box to a BAQ Param and then when user types “123” I can do a LIKE “@txtBox1%”

Right now the workaround is… Grab everything but go back only 1 maybe 2 years (date range) and its filtered when it hits the Report Server / RDL… But somewhere it still hit the Main Server for 10,000,000 rows (assuming large jobs table)

[JeffLeBert]

It’s been a while since I worked on reporting so most of those neurons have died. Maybe I’m misunderstanding, but I’m pretty sure you can use the user supplied values from the report UI to filter your BAQs. I know I tested passing an Order Number and having it only return the appropriate orders in the main and child BAQs.

[hkeric.wci (Haso Keric)]

Filter is post, it probably grabbed all your orders from SQL and then filtered? Im more thinking run the BAQ with the Order # specified

What do you put into your BAQ, or do you join on a special table that houses those variables

SELECT * FROM Erp.OrderHed
WHERE OrderNum = [?something]

Right now we have to let the BAQ run wide-open (with no WHERE) and the Filter will happen, but after the fact, so if you have 5 million Orders, it might take 3min.

SELECT * FROM Erp.OrderHed

[Mark_Wonsil]

@hkeric.wci

Filter is post, it probably grabbed all your orders from SQL and then filtered? Im more thinking run the BAQ with the Order # specified

And you think that’s bad for Haso, imagine the Cloud Users!

[JeffLeBert]

Like I said, it’s been a while, but you set up your BAQ to have a parameter (through Actions/Design Parameters menu item). You then set up the filter:

The “SQL” generated by BAQ from the Phrase Builder looks like this:
… normal SQL stuff …
where (Tip.TipNum = @MyTipNum)

Search for “Add Criteria Mapping” in Epicor help and you should see this:

You hook up the control from the Criteria Set to hook up to that specific BAQ’s parameter and life is good. You can hook that up to multiple BAQs as well.

[hkeric.wci (Haso Keric)]

Jeff! Where have you been hiding! I dont know why I kept doing BAQ Reports still… This works almost as identical as a BAQ Report. I do recall you mentioning it before. Shame on me.

Little bit harder to setup because you have to create a RDL from Scratch but in this one I just used “Sync DataSet” and it even brought over my Calculated Field MEOW

The Help File guides you to use Base Definition, so I changed mine to SQL Server Reporting, I could Sync and Output types showed up. Nice!

You can mix also Filters into it and it will be stored in XML Format in RptParams Column UserCriteria, for me Sync Dataset didnt break it up but I found other Epicor Reports that show how to get your param value.

"  CAST(CONVERT(XML,CAST(UserCriteria as varchar(max))).value('UICriteria[1]/RptCriteriaPrompt[PromptName=""LCNRVReport""][1]/PromptValue[1]','VARCHAR(10)') as bit) as LCNRVReport "+
" ,CAST(CONVERT(XML,CAST(UserCriteria as varchar(max))).value('UICriteria[1]/RptCriteriaPrompt[PromptName=""InactiveSalesItemsReport""][1]/PromptValue[1]','VARCHAR(10)') as bit) as InactiveSalesItemsReport "+
" ,CASE WHEN  CAST(CONVERT(XML,CAST(UserCriteria as varchar(max))).value('UICriteria[1]/RptCriteriaPrompt[PromptName=""CalculationMethod""][1]/PromptValue[1]','VARCHAR(50)') as nvarchar(50))='Classes'  THEN 1 ELSE 0 END as CalculationByClass"

[JeffLeBert]

I thought we agreed we would never to use “Sync Dataset”.

If you build your RDD and then create your style in Report Style Maintenance, you can just do a Action\Create SSRS Report and it will create you the template report just like in BAQ Report. Plus, you get all the data columns are mapped in for you. It should only take a couple more clicks to do RDD + BAQs instead of BAQ Report.

There is no reason to use the old BAQ Report… ever… again…

[JeffLeBert]

Don’t forget you can use this for the “Electronic Compliance” reports as well. Build a couple BAQs, map them to XML, JSON or CVS output. Sweet!

[hkeric.wci (Haso Keric)]

Nice! I tried it in 400.10 got some Template Missing Error, but works fine in 500. Probably screwed up the TemplateReport.rdl or something

[JeffLeBert]

If you need to get this to work for earlier versions, back to 10.1.600 (I think), you could try copying the ReportTemplate.rdl from your 500 CustomReports area. The file should be there so something probably just got mixed up.

Also check out the code in the *.rdl created by Report Style Maintenance (also in the ReportTemplate.rdl). The methods help get criteria parameter values.

I’m so excited you are finally investigating this!

[hkeric.wci (Haso Keric)]

@JeffLeBert

The methods help get criteria parameter values.

Aha!! Found it in the Global Code, ok that makes it much easier than adding the XML Values to SELECT

[pferrington (Patrick)]

So does this mean you are planning to withdraw the request for BAQ Report enahncement? Honestly one of my personal if I ever have time, is to gut BAQ report - keep the elements but have it do the RDD creation using the elements you have discovered here. I’d rather just get rid of it but it has been heavily used for years and that is rarely an option. Someday - that is bigger than a one dark night project.

[josecgomez]

I’m gonna merge the answer here to that request and mark it solved!
Look at that you guys solved an enhancement request without even trying!!! WAY TO GO!

[Evan_Purdy]

Wow, I did not realize you could make a BAQ report this way. I feel like I should convert the existing reports now…

[hkeric.wci (Haso Keric)]

@josecgomez

Look at that you guys solved an enhancement request without even trying!!

Stuff sometimes happens like this when @Bart_Elia lets @JeffLeBert out of the dark room basement dungeon.

[amaragni (al maragni (USA - NY/NJ))]

how does one get the control data type to match up?
i have a RDD/BAQ report and the criteria filter datatype is empty and read only - it does not match up to my criteria mapping field datatype which is nVarChar and pulled in from the BAQ
TIA
i then get this error

[JeffLeBert]

Whenever you have an error like this, please click the Detail buttons and copy/paste the text. Sometimes the extra information will tell us exactly what the problem is. At worst, it gives me a place to start looking.

Unfortunately, I didn’t work on the UI portion of this so I would have to spend a bunch of time to guess at the specifics of your issue and try to reproduce it.

[amaragni (al maragni (USA - NY/NJ))]

sure thing…
Application Error

Exception caught in: Ice.Lib.EpiClientLib

Error Detail

Message: Control ‘grdFilterList’ of type EpiUltraGrid has an invalid binding to view FilterList1.
Program: Ice.Lib.EpiClientLib.dll
Method: EventSetup

Client Stack Trace

at Ice.Lib.Framework.EpiUltraGrid.EventSetup()
at Ice.Lib.Framework.EpiUltraGrid.set_EpiTransaction(IEpiTransaction value)
at Ice.UI.Rpt.DynamicCriteriaReport.FilterListPanel.set_ReportTrans(Transaction value)
at Ice.UI.Rpt.DynamicCriteriaReport.FilterPanel.BuildFilterPanel(Int32 filterID, String viewName, RptCriteriaFilterRow filterRow, Transaction trans)
at Ice.UI.Rpt.DynamicCriteriaReport.FilterPanel.AddCustomSheetToDockManager(RptCriteriaFilterRow filterRow, Transaction trans)
at Ice.UI.Rpt.DynamicCriteriaReport.FilterPanel.BuildDynamicFilters(RptCriteriaDataDataSet dsReportCriteria, Transaction trans)
at Ice.UI.Rpt.DynamicCriteriaReport.DynamicCriteriaReportForm.BuildUIControls()
at Ice.UI.Rpt.DynamicCriteriaReport.DynamicCriteriaReportForm.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at Ice.Lib.Framework.EpiForm.WndProc(Message& message)

[amaragni (al maragni (USA - NY/NJ))]

and then i get this when i try to associate the filter in criteria mapping
which basically is telling me they are different - but i am NOT able to adjust the filter type, and it IS nvarchar?

Business Layer Exception

Parameter data type is different than Control data type.

Exception caught in: Epicor.ServiceModel

Error Detail

Description: Parameter data type is different than Control data type.
Program: Ice.Services.BO.RptDataDef.dll
Method: RptCriteriaMappingBeforeUpdate
Line Number: 3365
Column Number: 21

Client Stack Trace

at Epicor.ServiceModel.Channels.ImplBase`1.ShouldRethrowNonRetryableException(Exception ex, DataSet[] dataSets)
at Ice.Proxy.BO.RptDataDefImpl.Update(RptDataDefDataSet ds)
at Ice.Adapters.RptDataDefAdapter.OnUpdate()
at Ice.Lib.Framework.EpiBaseAdapter.Update()
at Erp.UI.App.RptDataDefEntry.Transaction.Update()

[amaragni (al maragni (USA - NY/NJ))]

Thanks!