WIP Dashboard

Epicor ERP 10
1

Has anyone able to recreate this request?

I have a SQL Query I use for Crystal Reports but struggling to turn this into a BAQ :confused:

2

I worked on one in the past, but never got it completed. I may be able to generate a BAQ if youā€™re willing to share your SQL magicā€¦

3

Hi Doug,

Give it a goā€¦

Thanks
Aaron

4

That doesnā€™t look so bad. Is there anything that was throwing you off?

5

WIP!?
image

6

@aarong - hereā€™s an incomplete version of your SQL. But you should be able to follow it through to completion.

Just keep making InnerSubQueries to satisfy your sub queries g thru p, and add them to the top level.

Trickiest part is making sure that you use the right alias for PartTran in the sub queries. a uses PartTran, but d uses PartTran1, f uses PartTran2, etcā€¦

E10H-WIP-Dashboard.baq (58.5 KB)

I use a calculated field in place of your use of coalesce()

coalesce(mattodate,0.00) mattodate

becomes
(case when (a.Calculated_mattodate) is null then 0.00 else a.Calculated_mattodate end)

edit

turns out you can use COALESCE() in a calculated field.

4 Likes
7

image

1 Like
8

Is there anyway on a dashboard to select data from a period of time? Adding the date selection fields are fine but can this be customised to pull data from lets say End of Last Month?

Only show the data in that dashboard based on 31/08/2020 and nothing newer?

9

You have to add a calculated field that has the same value as the field youā€™re filtering on.

Example: My BAQ has the field PartTran.TranDate, And I want to filter for a date range.

  1. In the BAQ, create a calc field named EndDate, with the formula PartTran.TranDate (this is just a duplicate of PartTRan.TranDate
  2. On the dashboardā€™s Tracker, set a prompt on field TranDate, labeled: ā€œStart Dateā€, expression >=,
  3. Now add the prompt for Calc_EndDate, labeled: ā€œEnd Dateā€, expression <=
10

@ckrusen, Could you please share the correct WIP BAQ?

11

I didnā€™t finish it. But you should be able to from the BAQ I posted, and @aarongā€™s original SQL.

Or maybe @aarong will share it.

FTR - I have no idea that SQL aarong posted is general enough for any company, or if it is specific to his companyā€™s use of E10

12

There is still something off in the logic that I just canā€™t figure out. Our WIP report differs on some jobs, but without any more detail itā€™s proving to be a bearā€¦

EDIT: AHA! DMR is the culpritā€¦

13

The BAQ and SQL query was tailored to our businesses needs we use a separate EOM database to allow capture of data for the finance team. I will upload the finished BAQ on Monday.

1 Like
14

Iā€™m surprised that the sign of the transaction (based on the trantype) isnā€™t needed a lot more.

15

Thatā€™s what Iā€™m used to in our last system. I just updated my SQL and I had 14 jobs (out of 2400) that were off. When I ran the WIP report again for one of those jobs it matches my query. I guess I need to verify when weā€™re not doing production :face_with_symbols_over_mouth:

16

Did you use your own SQL query?

What is the data returned based on the query I shared? If this ran in live it returns additional data if itā€™s not ran in EOM database.

This down to jobs being carried out. Hence the snapshot in time.

17

Here is the finished BAQ

TEAGLE-WIP-BAQ.baq (147.0 KB)

1 Like
18

Afternoon everyone,

I have a quick question.

I have this BAQ: TEAGLE-WIP-BAQ.baq (187.0 KB) I would like to find a way of determining what type of job they areā€¦

(case when (SubQuery15.ProdGrup_Description like ā€˜Tuliā€™) then Tulip else NULL end) would this work?

for example.

Calculated_Field Job Number PartNum Rev ProdCode Tracked SerialNum ProdGrup_Description
Machine TED2214313 TED221 B 1400 True Blank
Tulip 837361 SX A 9000 False
PARTS 876333 SC1100 A 2400 False Blank

Would to have the calculated field find out what type it is and label itā€¦ :confused: This would be for a Pivot Table in Excel unless there is a better way.

Could we use if the ProdCode with different if statements e.g if 9000, 2400, 3500, 4005 then display Tulip if else then 3837, 9448, 2220 display Machine so on and so forth.

19

So SubQuery15 must contain the table ProdGroup. Just add a calculated field (with whatever expression you want) to that subquery. That field would then be visible in the top level query - assuming SubQuery15 is in the top level query. If itā€™s not just pass that value as a dsiplayed column from the subquery that Subquery15 is used in.

If your ProdCodes were:

ProdCode  Description
1400      Machine
2400      Tulip Widget
3387      Parts
3500      Tulip Wax
4005      Tulip Accessory
9000      Tulip Option

You could use
(case when ProdGrup.Description LIKE 'Tuli%' then 'Tulip' else null)
or
(case when ProdGrup.ProdCode IN ('2400', '3500', '4005', '9000') then 'Tulip' else null)

2 Likes
20

Can you have multiple in the same calculated field? or do they have to be separate calculations?