Ways to dynamically set EOH and BOH inventory in universe

building semantic layer
1

Good morning,

Using XIr2 against DB2, I’m stuck with an issue in how to generate Beginning on Hand (BOH) and Ending on Hand (EOH) at the universe level for respective times - min(BOH) for week, min(BOH) for month, etc. max(EOH) for week, max(EOH) for month. Hopefully you get the idea…

Example would be a user would pull Department, Store, Sales BOH inventory, EOH inventory, and Current Month to Date pre-defined filter. What they’re expecting to see is if within the current month - they want to dynamically see the BOH on the first day of the month, and the EOH up through the date in the month. Displaying a time in the report (i.e. Week Beginning) the data comes out correctly. Not displaying time - the data sums up…which is what I would expect it to do since BO would be performing the calculation incorrectly (2-phase calculation as discussed previously).

Also applies for quarter, season, year - etc.

Some ideas that I’ve thought of trying is to create a derived table, stored procedures, possibly having the ETL group set up EOH / BOH date transformation tables based on the respective times users could select.

This can very easily be done at the report level, but the users have not budged in displaying any time field which would take care of the issue. So, what I’m hoping to find is additional ideas at the universe level. I’m going to continue pushing this can (and should) be done at the report.

Hope this is clear. Thanks in advance.

ngosz :us: (BOB member since 2003-09-25)

2

Yes, a derived table or something would do it. However, you’d want to name your measures carefully to ensure it is clear what information they provide. You’d also want to make it really clear to the business that once you’ve delivered this, you don’t want them saying “Oh great - now we have that, can we have a rolling representation of it over a progression of weeks”… which basically means “Can we have what you suggested we have in the first place - what you had designed and we can display the date in the report as you recommended”. :wink:

jac :australia: (BOB member since 2005-05-10)