I am fairly new to Epicor and SSRS in general, I am having a tough time with a Daily Cast report that was created. I am hoping someone here may be able to shed some light on the problem. Our Daily Cast report is currently looking at the LaborDtl.CreateDate of a specific operation code in the BAQ to get the date cast. This works currently if we report the cast date for 2nd and 3rd shifts on the same date. We would like to transition to actual date and time using LaborDtl.ClockInDate and LaborDtl.ClockInTime and filter for a range of time (yesterday 12pm to today 12pm). I am having a hard time coming up with a solution being a novice in Epicor, SSRS (Report Builder 3.0) and my GoogleFu is coming up short. Any assistance would be greatly appreciated!!!
Since you said your new, lets make sure you understand how a BAQ report works.
- A BAQ is created to select specified data from the DB.
- A BAQ Report is created to make the user interface for running the report. Here you can select options and filters the user can select when they run the report. These options and filters then affect the data the BAQ retrieves. Creating the BAQ report automatically creates a SSRS RDL, and a Report Style
- The report style links the BAQ to the SSRS RDL
- The SSRS RDL created will be “blank” (it will have a defined page header, a the dataset from the BAQ). You then add the tablix and fields to make the report.
It seems like all of that has already been done.
If the current BAQ does not include those new fields you want to use, the BAQ will need to be updated.
You might want to make a calculated field “CastDate” to combine the ClockInDate and ClockInTime to make a date based on that criteria you mentioned. The expression for CastDate might look something like the following pseudo code:
if (ClockInTime < 12:00 PM ){
return ClockInDate - 1;
}
else{
return ClockInDate;
}
Then change the BAQ Report (using ‘BAQ Report’), so the option for the date is linked to the new calculated field you created.
If the report shows the date, you will need to update the SSRS RDL. This requires adding a field and updating the RDL’s query expression.(There are multiple examples of how to do this on this site)
Thank you for the insightful reply it helped. Upon further digging I have come to the realization that I need the BAQ Report Options;
LaborDtl.ClockInDate >= FromDate
LaborDtl.ClockInDate <= ToDate
supplied by the user to filter the data in the report builder. As each option is passed to the .rdl as Option1 and Option2, now I just need to figure out how to apply data from one dataset to filter another dataset. I have been testing different ways using parameters, but just cannot seem to get it.
Something like;
Option1.Value AND Fields!LaborDtl_ClockinTime >=12 OR
Option2.Value AND Fields!LaborDtl_ClockinTime <=12
Then the report should only display the rows that meet the criteria.
Can someone point me in the right direction?
The “Options” one creates for a BAQ Report are always ANDed together (while ignoring ones that are blank)
Within the SSRS designer, you can hide rows based on an expression. This would just hide these from being displayed on the report. They’re only hidden, so extra care would be needed to do any analysis like counting or summing…
I am working on a Quote Report and could use a little help. I have created the BAQ Report, but am trying to get it so they can select from a Checkbox to show a comment row or not.
I have created the Checkbox in the Option Fields, but when I go to the SSRS side to show it if checked, I can’t get this to work. Any thoughts?
For the issue in this thread, we ended up paying an Epicor consultant for an hours worth of time to complete the solution. I would suggest creating a new topic to see if anyone can assist.