Hello again!

I was wondering if anyone can help mw with this one: I have a report in Reportmaster called 'Key Accounts' with the following columns from left to right:

Customer ID
Name
CY-YTD Sales (Current Year-Year To Date Sales)
LY-YTD Sales (Last Year-Year To Date Sales)
$ Change (Difference between the two above)
On Order Amount
LY Total Sales (Last Year Total Sales)
Business Due (Calculated colum: 'LY Total Sales' minus 'CY-YTD Sales 'minus 'On Order Amount')

Account Managers running this report will specify a 'Report Group' which shows them only their customers.

For anyone familiar with Reportmaster, the main file for this report is a Report Back called 'On Order RB'. It also pulls information from the 'AR Customers' table.

The 'Order RB' (Order Report Back) pulls the following from the 'OE Order Headers' table:

Customer
Total Order Amount

The only record selection criteria I was able to find in the 'On Order RB' was "Order Complete=0" - which, I assume, means to only display orders that haven't been completed.

My question at this point is:

Can I start by using a Adagio Composite Table that contains both the 'AR Customers' table and the 'OE Orders Header' table? Is there such a thing? What are the differences between some of the composite tables (for instance 8.0A and 8.0C)?
Would I have to calculate the YTD and Total sales amounts using calculated colums or does that information already exist in some SR tables? I looked, but I haven't been able to find anything that would do that for me.

Thanks in advance.

Regards,

Andre

1) The difference between Composite 8.0A and 8.0C is the 8.0C is newer and will have additional composites that are not in 8.0A

2) Have a look at the *OE Ord Head w Cust, Terms composite table from 8.0C, I believe it has all the information you need.

3) You will need to define a Calculated Columns using the information from the table. Adagio tracks Invoice Total, CN Total and DN Total. Use GridView to show them all and then determine what your formula needs to be.

Hi Michael:

Thanks for your reply! Maybe I've been staring at this screen for too long. I can't seem to think straight anymore... But I also can't seem to find the three fields you had mentioned. I selected the Composite table 8.0C, then selected the *OE Ord Head w Cust, Terms, Sales table, but the only fields I'm able to find that I think may be relevant are "OE Head Total Dollar Value" and "OE Head Total Order Value".

The other thing I can't seem to wrap my brain around is how I would describe current year or last year in terms the software can understand. In Reportmaster, the formula for the current year amount is:

if d>=bofp1 then a
A = Amount
D = Date

I assume 'bofp1' means 'beginning of fiscal period 1'. Would you be able to give me an example of what something like this would look like in GV?

Thanks much!

Andre

Hi Andre,

OE Head - Total Dollar Value is the Invoice/Credit amount.
OE Head - Total Order Value is the Order amount.

For YTD formulas, you compare the transaction date against the value 'BeginYear', in an 'Excel-formula' syntax. Line breaks are optional but useful for read-ability:

Current year, YTD:

IF(
{Inv Date} >= BEGINYEAR()
,
{Total Dollar Value}
,
0
)

Last Full Year:

IF(
YEAR({Inv Date}) = YEAR(Today())-1
,
{Total Dollar Value}
,
0
)

Last Year, YTD:

IF(
AND(
YEAR({Inv Date}) = YEAR(Today())-1
,
MONTH({Inv Date}) <= MONTH(Today())
,
DAY({Inv Date}) <= DAY(Today())
)
,
{Total Dollar Value}
,
0
)

Remember - you can copy formulas like text and paste them. You can also export formulas to a Text file and Import them into different GridView inquiries, meaning you don't have to be typing these formulas over and over again.

Whew! Thanks so much! That helps!!! I truly appreciate the help!

I think I'll go home for today and start working on this one on Monday. Thanks so much!

Have a good weekend!

Andre

You are right, the fields you are interested in are "hidden"

Add /A to your GridView shortcut and you will see the "hidden" fields. As a matter of fact I always put the /A on my shortcuts.

Here are the Fields you will see:

Inv Total - PTD
CN Total - PTD
DN Total - PTD
Disc Total - PTD
Int Total - PTD
Inv Total - YTD
Payment Total - YTD
CN Total - YTD
DN Total - YTD
Disc Total - YTD
Int Total - YTD
Inv Total - LY
Payment Total - LY
CN Total - LY
DN Total - LY
Disc Total - LY
Int Total - LY

Put these fields on a View and determine what ones you need to calculate your numbers.

In GridView the equivalent of:
if d>=bofp1 then a
is:
if (d>=bofp1,a,0)

"d" might be {Order Date} , "bofp1" might be the result of a Querydate and "a" could be {Total Order Value}

so define a calculated column bofp1 to be QUERYDATE("Enter Start Date", Today())

Sales Amount calculated column would be

if ({Order Date} >= bofp1,{Total Order Value},0)

However, I would simply define a filter to get the OE Header records that were of interest and then you could use the {Total Order Value} without any other calculations.

Hi Michael:

Thanks for the tip about the "hidden" fields. I'll check them out on Monday. Thanks also for the information on how to manually calculate the information. I'll try the "hidden" fields first, but it's always good to have a backup.;-)

Regards,

Andre

Hello Andre,

Just a cautionary comment here that the Report Master report you are workling from is using a feature called "report back". This feature allowed the data from a first report (which might have lots of calculations and filters and summaries) be used in a second report. The YTD figures might be summaries coming from somewhere else.

Maybe this is a stupid question, but what does 'DN' stand for?

Thx,

Andre

Debit Note (as opposed to Credit Note). Debit and credit notes are entered as adjustments.