Anyone have suggestions on how to apply a filter for a dynamic report.
I have a BAQ that shows open jobs. I created dynamic report that uses that baq and i would like to let the users select the jobs they would like to see.
How do we apply a filter to a dynamic report?
Thanks,
DaveO
In the BAQ Report Designer, choose File → New Option. Then chooses the BAQ column, comparitor, and if you want a default.
Test the report and you’ll see new field that is used to filter the BAQ results.
OPTION gives you a control of the type for the column selected. It is intended to hold a single value.
FILTER will give you a grid so you can have multiple values to compare the results to. This would be better for having multiple jobs. Note that the rows are compared to the data and OR’d together.
Mr. Calvin: Thank you for the reply.
I am not using the “BAQ Report Designer” - this is a “Dynamic Report” (at least i think that is what it is called).
DaveO
A BAQ based RDD?
If so, you have to add a criteria set, and then add criteria to that set.
@DaveOlender, Please follow the below-mentioned steps under Report Data Definition.
Create Report Criteria Sets
In this example, we have already set up BAQ Defined parameters and table criteria in the BAQ Designer. Report Criteria Sets allow you to create and configure the prompts and filter controls that will accept user input of the required values when the report is submitted. Each prompt or filter is then mapped to a BAQ defined parameter.
Therefore when the report is submitted and the user enters prompted values, the BAQ retrieval is controlled by the entered values.
Translating all the idioms & phrases used to describe issues is an art form in itself.
Mr. Bruce: Thank you - I agree.
It looks to me like I can add a filter - however the filter is not applied to the main baq dataset. i suppose i can see that since the Dynamic Criteria Reports can contain multiple BAQ’s and therefore multiple datasets.
So from what i have found, the filter list is in a reportparameter called UserCriteria which is a big XML paragraph that contains Tags for the Filter list.
So If i want to create a Labor report (using Dynamic Criteria - I have to “dump” the ENTIRE LABOR DETAIL table to SSRS and then filter from there?
Do i have that right?
DaveO
No. When setup correctly (which is far from straightforward) the criteria is passed to the BAQ, and the datasets generated will be filtered before being passed to SSRS
Mr. Calvin: That sounds like exactly what i need.
Care to elaborate on “setup correctly”.
I have been able to setup a Prompt for Job Number and that works as expected. Now how does one setup a filter to only show selected Job numbers?
DaveO
you need to have a parameter in the BAQ that is used to filter the BAQ results (i.e. Table or Subquery Criteria)
Then in the Dynamic Report (RDD) the criteria set needs to be mapped such that the RDD’s criteria is passed to the BAQ.
It’s far from straightforward. I think if the BAQ has parameters, the Criteria tab in RDD has a button to automatically make criteria inputs for each BAQ parameter.
I no longer have access to an E10 system, so I can’t check the or post screen shots.
Mr Calvin ; again thank you for the help.
I will give that another try - I think I tried that already but could not get it working.
I’ve found that the BAQ needs to “complete” before making the RDD. What I mean by that, is changes to the BAQ (regarding parameters and criteria), after it has been added to the RDD, tend not to show up in the RDD
I would try making the RDD over, from scratch. Add the datasources (BAQ(s) ), create the relationships, then add a criteria set.
If my memory serves me, after adding the criteria set, there’s some way to have it automatically add the criteria fields, mapping them to the BAQ parameters.
TBH - It seemed like sheer luck when I finally got one working.
I put in a support request with Epicor.
I have been told that the “Filter” function for Dynamic Criteria Reports based on BAQ’s is NOT FUNCTIONAL.
Please keep this in mind in the future - for all of you that think Dynamic Criteria Reports are the best thing since sliced bread.
DaveO
If memory serves me right, if you create a BAQ report then change anything on the BAQ , you have to refresh the Datasource on the RDD. NOTE !!! Refreshing the Datasource on a regular SSRS report will ( or at least uses to ) trash the report. I have had sucess in the past on refreshing BAQ based reports.
@ckrusen Calvin does this sound familiar ??
Dean
Update - Now that i have been told officially by Epicor Support that we cannot filter using a Dynamic Criteria Report (we need a new name for this - like “Epicor Report” ) - Here is how to do it:
Add a Parameter to your BAQ and assign it as a “Item List”.
Add a table Criteria that uses the “IN” qualifier and uses the added Parameter:
Add a filter to your Report Data Definition.
Add a Report Criterial Prompt - Select the prompt and press the “Create Report Criteria Prompts …” then change the Control Type to “Filter” and Change the Control name to the name of the filter we created in step 3.
Your report will now be filtered by the list.
I must admit i did not try a really long list like all customers or all jobs or all labordtl - but i will cross that bridge when it comes up.
This works for me - however, if anyone has a better way (or correction on the way i am doing it) please feel free to add comments.
DaveO
So your form has:
Is that textbox on the main sheet editable? Like you could actually type a list in like:
12345-1-1, 12350-1-1, 12350-1-2
It seems like the filter sheet isn’t really used by the RDD, but rather it just populates the Criteria Prompt - which is used in the SQL query’s IN(...) condition of the WHERE clause.
Mr. Calvin: Great question – I just tested the “Prompt Field” and adding info to that field (even just one job number) is ignored. I get the whole job list.
So it looks like the Prompt is used to pass the Filter list to the BAQ however it is bound to the Filter.
DaveO
If you add some rows to the Filter, do they show up in the prompt on the main sheet?
