Sage 100

I have a request from a client to see a list of What was done in the Sales Order Module for the Day/Month/Period on the Header Level (i.e. Order/Invoice Total, customer, etc). They use both 1-Step Invoice in SO Invoice Data Entry, and Sales Order Entry. It seems like it should be a simple report and one that many customers would like to see, but I don't see a report that shows both Sales Order and Invoice Info.
I guess I shouldn't be surprised since the is the first request I've had in 10 years for that report? Just want to make sure I am not missing anything before I re-invent the wheel?

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470
------------------------------

I think I used a sub report once, to do this. It was just a pain to put it into an exportable format.  But after asking "why" 5 times, I found out that what they really wanted was to find out who bought last July, but not THIS July. :-P

------------------------------
==================
Rhonda McNamara
Customer Success Manager
Stewart Technologies, Inc.
rsm@stewarttechnologies.com
------------------------------

I have had several clients request reports than compared orders to invoices. I designed them by combining sales order and invoice history into a single source and not using sub reports. Usually, I used to do this with MS Access append queries, but now with Excel Power Query that's a feature (Combine Queries - Append). I am beginning to appreciate the 'M' language.

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------

@Rhonda McNamara That is what I was thinking. Making the main report Invoice, and sub-reporting the Sales Order - but I also like @Dan's Idea of using Power Query. time to go over the 90 Minds Material again I suppose. ​​​​

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470
------------------------------

Original Message:
Sent: 08-01-2019 15:14
From: Dan Burleson
Subject: Sales/Invoice Report?

I have had several clients request reports than compared orders to invoices. I designed them by combining sales order and invoice history into a single source and not using sub reports. Usually, I used to do this with MS Access append queries, but now with Excel Power Query that's a feature (Combine Queries - Append). I am beginning to appreciate the 'M' language.

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------

I've used SQL Views with "Union All" statements, to bring data from multiple sources into a single report.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 08-01-2019 16:22
From: Michelle Forsey
Subject: Sales/Invoice Report?

@Rhonda McNamara That is what I was thinking. Making the main report Invoice, and sub-reporting the Sales Order - but I also like @Dan's Idea of using Power Query. time to go over the 90 Minds Material again I suppose. ​​​​

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470
------------------------------


Original Message:
Sent: 08-01-2019 15:14
From: Dan Burleson
Subject: Sales/Invoice Report?

I have had several clients request reports than compared orders to invoices. I designed them by combining sales order and invoice history into a single source and not using sub reports. Usually, I used to do this with MS Access append queries, but now with Excel Power Query that's a feature (Combine Queries - Append). I am beginning to appreciate the 'M' language.

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642

@Kevin Moyes can you share that statement? I am not sure I've ever used that.​

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470
------------------------------

Original Message:
Sent: 08-01-2019 16:26
From: Kevin Moyes
Subject: Sales/Invoice Report?

I've used SQL Views with "Union All" statements, to bring data from multiple sources into a single report.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------


Original Message:
Sent: 08-01-2019 16:22
From: Michelle Forsey
Subject: Sales/Invoice Report?

@Rhonda McNamara That is what I was thinking. Making the main report Invoice, and sub-reporting the Sales Order - but I also like @Dan's Idea of using Power Query. time to go over the 90 Minds Material again I suppose. ​​​​

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470


Original Message:
Sent: 08-01-2019 15:14
From: Dan Burleson
Subject: Sales/Invoice Report?

I have had several clients request reports than compared orders to invoices. I designed them by combining sales order and invoice history into a single source and not using sub reports. Usually, I used to do this with MS Access append queries, but now with Excel Power Query that's a feature (Combine Queries - Append). I am beginning to appreciate the 'M' language.

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON
------------------------------

Original Message:
Sent: 08-01-2019 16:28
From: Michelle Forsey
Subject: Sales/Invoice Report?

@Kevin Moyes can you share that statement? I am not sure I've ever used that.​

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470
------------------------------


Original Message:
Sent: 08-01-2019 16:26
From: Kevin Moyes
Subject: Sales/Invoice Report?

I've used SQL Views with "Union All" statements, to bring data from multiple sources into a single report.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 08-01-2019 16:22
From: Michelle Forsey
Subject: Sales/Invoice Report?

@Rhonda McNamara That is what I was thinking. Making the main report Invoice, and sub-reporting the Sales Order - but I also like @Dan's Idea of using Power Query. time to go over the 90 Minds Material again I suppose. ​​​​

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470


Original Message:
Sent: 08-01-2019 15:14
From: Dan Burleson
Subject: Sales/Invoice Report?

I have had several clients request reports than compared orders to invoices. I designed them by combining sales order and invoice history into a single source and not using sub reports. Usually, I used to do this with MS Access append queries, but now with Excel Power Query that's a feature (Combine Queries - Append). I am beginning to appreciate the 'M' language.

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642

This example may help get you started (attached)

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------

Original Message:
Sent: 08-01-2019 16:28
From: Michelle Forsey
Subject: Sales/Invoice Report?

@Kevin Moyes can you share that statement? I am not sure I've ever used that.​

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470
------------------------------


Original Message:
Sent: 08-01-2019 16:26
From: Kevin Moyes
Subject: Sales/Invoice Report?

I've used SQL Views with "Union All" statements, to bring data from multiple sources into a single report.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 08-01-2019 16:22
From: Michelle Forsey
Subject: Sales/Invoice Report?

@Rhonda McNamara That is what I was thinking. Making the main report Invoice, and sub-reporting the Sales Order - but I also like @Dan's Idea of using Power Query. time to go over the 90 Minds Material again I suppose. ​​​​

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470


Original Message:
Sent: 08-01-2019 15:14
From: Dan Burleson
Subject: Sales/Invoice Report?

I have had several clients request reports than compared orders to invoices. I designed them by combining sales order and invoice history into a single source and not using sub reports. Usually, I used to do this with MS Access append queries, but now with Excel Power Query that's a feature (Combine Queries - Append). I am beginning to appreciate the 'M' language.

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642

THANKS GUYS!!!

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470
------------------------------

Original Message:
Sent: 08-01-2019 16:40
From: Dan Burleson
Subject: Sales/Invoice Report?

This example may help get you started (attached)

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------


Original Message:
Sent: 08-01-2019 16:28
From: Michelle Forsey
Subject: Sales/Invoice Report?

@Kevin Moyes can you share that statement? I am not sure I've ever used that.​

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470


Original Message:
Sent: 08-01-2019 16:26
From: Kevin Moyes
Subject: Sales/Invoice Report?

I've used SQL Views with "Union All" statements, to bring data from multiple sources into a single report.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
Toronto ON


Original Message:
Sent: 08-01-2019 16:22
From: Michelle Forsey
Subject: Sales/Invoice Report?

@Rhonda McNamara That is what I was thinking. Making the main report Invoice, and sub-reporting the Sales Order - but I also like @Dan's Idea of using Power Query. time to go over the 90 Minds Material again I suppose. ​​​​

------------------------------
Michelle Forsey
Eide Bailly, Inc.
801-456-5470


Original Message:
Sent: 08-01-2019 15:14
From: Dan Burleson
Subject: Sales/Invoice Report?

I have had several clients request reports than compared orders to invoices. I designed them by combining sales order and invoice history into a single source and not using sub reports. Usually, I used to do this with MS Access append queries, but now with Excel Power Query that's a feature (Combine Queries - Append). I am beginning to appreciate the 'M' language.

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642