And you think that’s bad for Haso, imagine the Cloud Users!
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.
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"
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…
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!
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
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!
Aha!! Found it in the Global Code, ok that makes it much easier than adding the XML Values to SELECT
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.
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!
Wow, I did not realize you could make a BAQ report this way. I feel like I should convert the existing reports now…
Stuff sometimes happens like this when @Bart_Elia lets @JeffLeBert out of the dark room basement dungeon.
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
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.
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)
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()
Thanks!
@hkeric.wci and @JeffLeBert I created an RDD and a report style and successfully generated the RDL. I am now wondering how to call this from a menu item…? Is there a step I am missing? Do we still have to create a BAQ report?
Via Menu Maintenance, Create a New Menu and select Program Type: Report
Then you can select your Style which will tie to your RDD and which will have the form show up… kinda like a BAQ Report, you can then Customize it further.