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.

Thanks, Andrew!

I assume there is no 'Inv Total LY-YTD field' in one of the tables?

Sorry, no - but that's because LY-YTD changes every day. You have to calculate it doing your own summary (which is probably why ReportMaster was using a Read Back file. The formular for Last Year-YTD Invoices, if using the OrderEntry History file would be:



Then you'd need to summarize the view with this column totalled.

Does this help?

Hi Andrew:

the description of the FISCALSTART() function says the start of the fiscal year gets defined on the GridView Default screen. Where is that? I've looked everywhere. I can't find that setting. Thx!

Makes sense. Thanks. We use the fiscal year instead of calendar year, so I'll have to adapt the formula a little bit. One thing I have come across while trying to do that is that last year's fiscal year started on July 1, 2006, which was two years ago when using the YEAR() function - since we're now in 2008.
That's why I was hoping I could use the FISCALSTART() function, but I have been unable to find where to specify the beginning of the fiscal year.

From the Edit menu, pick Defaults. Or press Shift-F2 on the keyboard. You specify the day and month (not year) that the Fiscal Year begins with.

Duh! Thanks much!;-)

Andre

How does one define a PERIOD - as in PTD? My numbers for YTD and PTD appear to be identical. Thx!

Hi Andrew:

Can you please explain to me what the OE Header table contains versus the OE Hist Header table? I don't use ACCPAC, so I don't really know how this all works. I'm just trying to recreate these Reportmaster reports for our sales team, so please forgive my ignorance.
I assume that OE information, at some point, gets moved from OE to OE History. Correct? Where would I find out when that happens?

I just tried to use the OE Hist HEader table with Cust, Terms and put only three fields into that report: Cust#, Inv Total YTD annd Inv Total LY. I didn't get anything back in GridView, which makes me think there's nothing i that OE Hist table.
The SR table appears to be huge... more than 700000 entries. Takes forever to query. Would that be my only hope to catch last (fiscal) year's sales?

Thx,

Andre

I'm about to give up. I have tried for three days now to piece together this report. No matter what I try - somehow either the numbers aren't right or all I'm getting back for my LY calculated columns is 0.00... Would it be possible to hire Softrak to create this report for us? If so, how much would it cost?

Cheers,

Andre

Adagio GridView is meant to provide fairly straight forward reports from single tables or from tables that can be hooked together as "composite" tables. GridView does not replace all the capability of Report Master, in particular when ReadBack tables are used!

Seems likely that GridView is not the tool for this report. Perhaps what you need is a Custom Report from Sales Analysis.

Michael: Thanks for your reply! I'm afraid you may be right.
Would you happen to know someone who would be willing and capable to write such a report?

Cheers,

Andre

Hi Andre

I am following this thread with amusement, because my sense from the first post was that you are trying to perform surgery using an axe. I was just wondering when someone would tell you that GridView was not meant for this sort of thing. Of course I was hoping I was wrong, that's why I didn't chime in.

It turns out that creating a report using a combination of sales and bookings (that's what I call "on sales order" figures) is one of the hardest things to do in Adagio. This is because you are essentially mixing apples and oranges. In any case, your only hope is to do it using OrderEntry data; Sales Analysis is not an option.

This begs for Crystal Reports for Adagio, and for the services of a professional.

There are many of us whom you can hire to do this report, but the Softrak people will not do it; that's what their consultants (like me) do.

Your profile doesn't say where in the US you are, but if you don't find anyone capable locally, you can contact me at steve@sschwartzcpa.com. I will know within an hour of seeing your data and talking with you whether what you want can be done.

Steve Schwartz

Hi Steve:

thanks so much for your posting. I'm very much inclined to take you up on your offer later today to look at our data and see if this report is something you can create for us.

I was hoping I would be able to somehow put together the report in question. I had already recreated some of our simpler Reportmaster reports. I liked how easy it was. But once I started having to deal with Report Backs and data from all over the place - that's when I got stuck. Well, clearly, GridView can't do that sort of thing - but I'm sure it will be useful for lots of other reports.

I'll send you a direct e-mail later today. Thanks again!

Regards,

Andre

Hi Andre

For the benefit of others (I emailed you this already)...

The solution to creating your report starts with using GridView to create a two column Excel spreadsheet - column A is the customer number and column B is the On Order Amount for that customer. Once the Excel spreadsheet is created, highlight the two columns and use the Define Name function of Excel to give the two columns a Name. Let's call this a named range. Save the spreadsheet.

Now use Crystal Reports to create a basic sales report using data from SalesAnalysis. Crystal Reports for Adagio can link to the named range in your Excel spreadsheet to get the On Order Amount and include it on the sales report. Voila!

This is the concept. There is a lot more to the implementation, that's why I have a job. But you get the idea.

Steve