How can I display dynamic date parameters on a BAQ report?

Kinetic ERP
1

I have a BAQ report working almost 100% to requirements. Unfortunately, when I use a “dynamic” StartDate and/or EndDate parameter (for example, “Last Monday”), it comes through as “&amp;Mon-1” in my report header. I am trying to construct a string in the header that shows “Date range: <StartDate> to <EndDate>”. Works great when I supply a date, but dynamic values blow it up. When I supply a static date such as 4/1/2023, it comes through as “2023-04-01T00:00:00”, which FormatDate handles fine – chokes on the dynamic, obviously.

I am thinking I will need to add some code to the functions in the RDL. Anyone have some to share?

2

Try this

="Date range: " & IIF(Parameters!StartDate.Value Like "*-*", 
  "dynamic StartDate", FormatDateTime(Parameters!StartDate.Value)) & 
  " to " & IIF(Parameters!EndDate.Value Like "*-*", 
  "dynamic EndDate", FormatDateTime(Parameters!EndDate.Value))
3

That’s definitely an improvement over “#Error”, but I was hoping there’d be a way to translate the dynamic date parameter “Mon-1” (not sure why coded ampersand is in the output) to an actual date (right now that would be 5/15/2023).

4

You could add a calculated field in the BAQ. Then grab that field data.

image
image942×610 25.9 KB

image
image692×804 22.6 KB

1 Like
5

OK, yeah – I could see how to make that work. I’ll just manually pair up the dynamic date expressions Epicor presents in the report dialog with the BAQ constants from the designer interface. I suppose I hoped this could be brought across without bending myself over backwards, but here we go. Thank you for the inspiration!

6

This is the parameter… same kinda output.

image
image938×624 26.6 KB

image
image1169×744 34.8 KB

1 Like
7

I am not saying this is the best way… lol just a way to get the data over in the report server side.

8

This is how I’ve been handling it. It isn’t pretty or elegant. I also haven’t seen a measurable performance impact on the BAQ, the various latencies swamp out any difference.

At first I thought I’d get around to a solution that feels less clunky. After thinking about it for a while I switched to; I’d rather do this and get reliable results, than try to map out their homegrown date offset referencing and end up with something that could break when they change it.

9

Got around to implementing this, and it was even easier than I thought (I misinterpreted the suggestions a bit initially). The only drawback to piggybacking on an existing dataset is if you select a date range that returns no rows, you also get no date values – but at least it still runs and just shows a date of Jan 1st 1AD. :smile:

10

You could always check to see if the field is null and set it to today’s date… if that is what you want to do. or just leave it null ie. default date

 isnull(@begindate, getdate())
11

The solution, if it helps anyone in the future, would be to add another BAQ to your Data Definition that only brings your parameters to the report, independent of other datasets. I will likely not even bother with this because this should never happen under normal usage, doesn’t bomb, and in the past I have had to re-create RDLs from scratch after fussing around with the data definition and I am really ready to close the book on this one.