Question

Filtering information for a dashboard

  • 19 October 2021
  • 9 replies
  • 921 views
M

I’m working on a complex dashboard where I have several If-Then scenarios coming from demand consumption. The dashboard is pulling information from Job Demand and Issued figures, On-Hand demand, and Open PO Lines. In a case where the demand is satisfied in the job, I don’t want it to show the On-Hand, nor PO information. If it’s still an open line item and is Satisfied by On-Hand Material then I don’t want it to show the Open POs for that part. I also need to tackle the issue that if there is more than one On-Hand location and/or multiple POs that it not duplicate the demand figures in each line. Is there a way to either tackle this kind of logic in the BAQ output or the Dashboard information? I would prefer to show a single line for the demand and and supply portions (skipping what is not needed) and be able to drill down into the detail as needed.

 

 

9 replies

T
Userlevel 3

If I’ve understood the question correctly (it’s been a long day and it’s late here so I may be overthinking this) I think that I’ve done something similar in the past by using calculated fields to show/hide the different tables/subqueries. 

In the primary BAQ you do the calculations to indicate if you want to ‘show’ the joined table (e.g. cShowPOLines = true/false) that field then gets added to the join between the two tables with a static value of true on the side of the Open PO Lines. Then if cShowPOLines = false the join is not made.

You will need to make these outer joins.

To get round the issue of multiple locations/Open POs create a subquery and perform a sum on the fields you want to total.

M

Thank you so much. This has given me the clue I need in a number of places that has been aggravating me. I hadn’t realized you can type the calculated field name into the table relation section. It’s almots too easy and obvious now.

mwilson
Userlevel 4

You can, and in “Subquery Options” although in the top level query, you can filter by calculated field values.

..Monty. Monty Wilson, Vice-President North America, Epicor Users Group
B
Userlevel 3

Consider using the PartDtl table - it is the heart of the Time Phase view in the database.

It will help you sort through a lot of what you are looking for.

This joined with PartBin in another subquery should help you get to your answer of supply / demand.

 

Bruce

 

Bruce Larson Principal Consultant bruce@larsonsolutions.com Larson Solutions
M

All of these have been good suggestions, however, they have been resulting in other errors. Since I’m disconnecting some parts of the inquiry at times and not others it is resulting in some fields in my calculated fields to produce “unbound” errors that are referring to those parts of the inquiry. Trying to put those in calculations in “case” phrases resulted in other errors since, in these situations, there is nothing for the column to report.

I have been trying different ideas about how to fix these errors and my solutions just create more. This inquiry is linking more than 15 tables and has 4 subqueries. 

At present, I’m asking the client if I can present this as 4 reports that can be exported to Excel that will have to be manipulated outside of Epicor. That will reduce this to a much simpler data-gathering exercise and eliminate the duplicated data I’m trying to avoid.

mwilson
Userlevel 4

Have you tried making the PO lines and the PartBin records into an “IsSummary” table?  This way you can aggregate (sum, average, count, etc) on for example quantities on hand, with not so many subqueries?

 

Also, I assume you’re displaying the main supply/demand lines, then in the dashboard include other queries that use publish/subscribe so that when you click on a line, the details of its supply and demand appear in other grids down below?  If you can make that work, you may end up with something more powerful than a spreadsheet, at least more maintainable!

 

HTH

..Monty.

..Monty. Monty Wilson, Vice-President North America, Epicor Users Group
M

Management is looking for a one-page list that basically summarizes the whole path from open Purchase orders through jobs to project. We recently switched from buying to Job to buying to Inventory and drawing the lines through on-hand and through POs is the crux of the issue. I’ve come up with an acceptable compromise for that, but we’re still encountering problems. 

Your suggestion of the “IsSummary” table sounds like something I want to explore, but I’m not sure how that is done.

T
Userlevel 3

Monty, I am also working on this issue for McKessonHVS and my understanding is that IsSummary is a legacy E9 item and does not work with Subqueries.   McKesson is on 2021.2.3 (aka E 11).  This is related to my other topic thread about subquery results.  Thanks for the suggestion though.

-Todd

 

Todd Caughey Epicor Contract Consultant, Randstad USA, Plymouth, MN
mwilson
Userlevel 4

I haven’t tried it with subqueries, but I thought if we could replace them with summary tables, you might not need the subqueries.  The feature is an E9 legacy item but still working properly as of 10.2.500 and I think the current version as well.

 

Best,

….Monty.

..Monty. Monty Wilson, Vice-President North America, Epicor Users Group

Reply


Terms & Conditions