User Select of MONTH and YEAR in a BAQ or Dashboard

Epicor ERP 10
1

Hello,

I am trying to create what seems should be a simple task.

I want to be able to run a BAQ (or Dashboard if necessary) that will allow the user to select the single MONTH and YEAR for quotes created.
I currently have the following tables / fields utilized…
QuoteHed.QuoteNum
QuoteHed.TotalGrossValue
QuoteHed.DateQuoted
QuoteDtl.ProdCode

Any help / suggestions greatly appreciated.

2

You could create a customization with a few fields which will then filter the dashboard.

This would bring back all of the data into the grid first.

Or you can create a customization that will use BAQ Parameters. Have the BAQ being call use the parameters entered by the user.

3

Here is how I have done this:

  1. in your baq, create two calculated fields… One for “QuoteYear” and one for “QuoteMonth”.
  • QuoteYear calculation is: DatePart(yyyy,QuoteHed.DateQuoted)
  • QuoteYear calculation is: DatePart(mm,QuoteHed.DateQuoted)
  1. then in your DASHBOARD, you can apply a filter on those two columns.
4

I would try adding calculated fields to your BAQ.
Then see if you can filter on those, either by BAQ parameters or Dashboard Tracker view criteria.

e.g. if you wanted to use DateQuoted Year/Month

1st Calc Field Name: QuotYear
Data Type: int
Format: ->>>>9
Editor: datepart(year,QuoteHed.DateQuoted)

2nd Calc Field Name: QuotMonth
Data Type: int
Format: ->>9
Editor: datepart(month,QuoteHed.DateQuoted)

1 Like
5

And… one more cool trick I have done to do this kind of summary…

  1. create TWO BAQs… and put both of them on the dashboard. BAQ1 publishes the Year/Month, and BAQ2 Filters on it.
    BAQ1 is a fully summarized BAQ that only shows unique records for the Year and Month calculation
    BAQ2 also has the year and month calculation

In the dashboard, when you refresh, BAQ1 will run, and show all years and months that you have quotes for (you could sort from newest to oldest) then as the user clicks on each month, BAQ2 would automatically refresh to that year/month.
This type of design makes for elegant functionality with very little additional work.

6

FYI… all the documentation you need on the “DatePart” functionality: DATEPART (Transact-SQL) - SQL Server | Microsoft Learn

1 Like
7

What I’ve usually ended up doing in this case is one of two options:

  • Create a calculated field in the BAQ which is actually a string, and populate it with the month and year of the date, ideally in the form “YYYY MM” or similar. FORMAT([yourdatefield], ‘yyyy MM’)

  • Create a calculated date field in the BAQ, and populate it with the first day of the month of the actual date, then format it to show only the month and the year. DATEADD(d, 1 - DAY([yourdatefield]),[yourdatefield])

Either of those give you a field which the user understands can be filtered to a particular month in one step. I’ve found many people are confused by having to filter month and year in separate fields.

8

Everyone’s help has been great. Problem 1 solved. Now management has added extra requirement that I am also having difficulty dealing with.
Basically I need to categorize the quotes based upon the first line of the quote.
So, I added QuoteDtl_QuoteLine to the BAQ and Dashboard.
I thought I had that figured out by using a filter in the Dashboard as follows:
ColumnName: QuoteDtl_QuoteLine
Condition: =
Value: 1

My problem is if the salesman deletes line 1 of the quote in a revision, then the quote does not show up in my results.

I can’t figure out how to return the first line of the quote in the BAQ or the Dashboard.
Any suggestions…

9

Create a subquery to something like. (This is sql you will have to convert to BAQ innerSubquery)

select Company, QuoteNum, MIN(QuoteLine)
from QuoteDtl
Group by Company, QuoteNum

Then you can use the results of this to know which line is the “first line” based on number ie (1, 2, 3…)

This will replace your added condition.