1/3/5 year PPH added to BAQ/BAQ Report

ERP 10
,
1

So I created a BAQ and by extension a BAQ report for our manufacturing supervisors to track employee parts per hour on jobs. They are able to limit the data based on date range, part number, and/or employee ID. I was asked if I could add in historical data from 1/3/5 year increments.

Getting this information isn’t really a struggle but I am running into a road block on this by either adding it into the current BAQ as I am currently just pulling it from a separate BAQ with date criteria and a CTE to sum up the fields and do the calculations.

Or

How/ is it possible to add in this separate BAQ as a sub report to my current BAQ Report?

Anyone have any thoughts or suggestions?

1 Like
2

Just use a case statement with the corresponding date range you need for each column.

For last 3 years:

sum(
    case when [DateField] between Dateadd(YY,-3,getdate()) and getdate()
    then [QtyField]
    else 0 end
)
3

Would this then be a calculated field in the BAQ?

4

Yes.

Sidenote, while I used calendar date (Oct 23, 2022 to Oct 23, 2023) most users want some kind of month-based cutoff. So your actual date calculation may be more complex, but the gist of the case statement is the same.

5

Understood. I think for this specific request as its a broad time frame I can get away with a rolling 1/3/5.

Not sure if you would know the answer to this but when a BAQ is run from a BAQ Report Designer and has a date range selected will the full query still run or will it only run for that query date? I am asking since the 1/3/5 year sum would not calculate properly if it is only bringing in the date range selected by the option fields(i.e. last week).

image

This is extremely helpful and reminds me how much I need to learn code wise still.

6

You’d need to pull the whole set of data from 5 years ago. You’d use the parameters to populate another calculated field. For example

case when [DateField] between @StartDate and @EndDate

Note that I don’t 100% recall how BAQ Reports pass those option fields back to the BAQ.

Also, if they’re already wanting hardcoded 1/3/5 values, you might just want to hardcode the short term numbers too. Don’t give them any options. Just do (for example) this week/last week/MTD/1yr/3yr/5yr, etc.

Most of the time people don’t want to plug in a date range. They’re looking for a specific relative time period every time they open it up.

7

I wish they would let me hardcode all the numbers. They enjoy their flexibility on what information they pull outside of the 1/3/5.

When I use the case statement it is doing everything but the SUM. I tried to separate out the field data population to make it a CTE and sum it up after that but it seems to not be doing that either. Any idea why it would do that?

image

8

I can’t really comment on why you’re having an issue without seeing the whole query. I’m not sure why you’d need a CTE just to summarize LaborDtl data though. Just take the table and aggregate it.

9

Its mostly because I am a chimp and was looking at the data wrong. The CTE should run before hand to get the 1/3/5 numbers then when the date range is applied those fields are already populated and shouldn’t change. Or am I thinking of this wrong? I am not sure but will let you know!