Epicor 9.05.700C - BAQ return summary data

Yahoo Archive
1
Jenn,



Got it to work perfectly!!



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Turgeon, Bill
Sent: Monday, January 28, 2013 2:06 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Re: Epicor 9.05.700C - BAQ return summary data





I just made the SQL view and tried to call it in a BAQ as an external
query, but I can't get past eh ODBC connection.

I know the ODBC is installed and working fine as I use it with
Access/Excel all the time.

Maybe because it's a view and Epicor is as for a database name?

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf
Of Jennifer
Sent: Monday, January 28, 2013 12:39 PM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Re: Epicor 9.05.700C - BAQ return summary data

Since you are on 9.05 and already have your query written in SQL, you
could try creating the query as a SQL view in your database. Then go
into the BAQ and select File / New External BAQ to point to the view you
created.

I've only tried it 1 other time and had an issue with the way I believed
Epicor was running the view. When I called the tools support they told
me they would never create a temp table of all records and they
themselves didn't support SQL view writing. My view was written properly
so I dropped it and created a custom crystal report instead of using BAQ
reports. If you try it and it works please let me know - I'm going to
have to stick within the software (e.g. BAQ reports rather than
SQL/Crystal) more since I may have to support multiple languages now...

Jenn

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
2
I need to create a BAQ that will only return the summary data as shown
in the SQL statement below:



use epicor905

SELECT Part.PartNum, Part.PartDescription, PartOpr.RevisionNum,
Sum(PartOpr.EstProdHours) AS SumOfEstProdHours,
Sum(PartOpr.ProdStandard) AS SumOfProdStandard

FROM Part INNER JOIN PartOpr ON (Part.Company = PartOpr.Company) AND
(Part.PartNum = PartOpr.PartNum)

GROUP BY Part.PartNum, Part.PartDescription, PartOpr.RevisionNum,
Part.TypeCode, Part.InActive

HAVING (((Part.TypeCode)='m') AND ((Part.InActive)=0))

ORDER BY Part.PartNum, PartOpr.RevisionNum;



I can get it to return the data, but it returns all rows..... I just
want the summary for each item/revision. Any thoughts?





Regards,



Bill Turgeon

Director, Information Technology

Coto Technology, Inc.

66 Whitecap Drive

North Kingstown, RI 02852

Ph: 401-583-7223

Cell: 401-368-9938

Fax: 401-942-0920

E-mail: bturgeon@... <mailto:jbentley@...>



P Please consider the environment before printing this email.



This e-mail and any attachments are for the sole use of the intended
recipients and may be privileged or confidential. Any distribution,
printing or other use by anyone else is prohibited. If you are not an
intended recipient, please contact the sender immediately, and
permanently delete this e-mail and attachments.





[Non-text portions of this message have been removed]
3
You should add the PartRev table and join it to the Partopr table by
company, partnum, and revisionum to narrow the number of records.



I have logged calls to Epicor when you use the summary tables in BAQ's -
not sure if it is fixed in Epicor 9 but, it does not work like a group by in
SQL.





From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Turgeon, Bill
Sent: Monday, January 28, 2013 6:50 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Epicor 9.05.700C - BAQ return summary data





I need to create a BAQ that will only return the summary data as shown
in the SQL statement below:

use epicor905

SELECT Part.PartNum, Part.PartDescription, PartOpr.RevisionNum,
Sum(PartOpr.EstProdHours) AS SumOfEstProdHours,
Sum(PartOpr.ProdStandard) AS SumOfProdStandard

FROM Part INNER JOIN PartOpr ON (Part.Company = PartOpr.Company) AND
(Part.PartNum = PartOpr.PartNum)

GROUP BY Part.PartNum, Part.PartDescription, PartOpr.RevisionNum,
Part.TypeCode, Part.InActive

HAVING (((Part.TypeCode)='m') AND ((Part.InActive)=0))

ORDER BY Part.PartNum, PartOpr.RevisionNum;

I can get it to return the data, but it returns all rows..... I just
want the summary for each item/revision. Any thoughts?

Regards,

Bill Turgeon

Director, Information Technology

Coto Technology, Inc.

66 Whitecap Drive

North Kingstown, RI 02852

Ph: 401-583-7223

Cell: 401-368-9938

Fax: 401-942-0920

E-mail: bturgeon@... <mailto:bturgeon%40cotorelay.com>
jbentley@... <mailto:jbentley%40cotorelay.com> >

P Please consider the environment before printing this email.

This e-mail and any attachments are for the sole use of the intended
recipients and may be privileged or confidential. Any distribution,
printing or other use by anyone else is prohibited. If you are not an
intended recipient, please contact the sender immediately, and
permanently delete this e-mail and attachments.

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
4
I purposely left out the PartRev because I need all revisions for every
item.



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Cathy
Sent: Monday, January 28, 2013 11:35 AM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Epicor 9.05.700C - BAQ return summary data





You should add the PartRev table and join it to the Partopr table by
company, partnum, and revisionum to narrow the number of records.

I have logged calls to Epicor when you use the summary tables in BAQ's -
not sure if it is fixed in Epicor 9 but, it does not work like a group
by in
SQL.

From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
[mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On
Behalf Of
Turgeon, Bill
Sent: Monday, January 28, 2013 6:50 AM
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Epicor 9.05.700C - BAQ return summary data

I need to create a BAQ that will only return the summary data as shown
in the SQL statement below:

use epicor905

SELECT Part.PartNum, Part.PartDescription, PartOpr.RevisionNum,
Sum(PartOpr.EstProdHours) AS SumOfEstProdHours,
Sum(PartOpr.ProdStandard) AS SumOfProdStandard

FROM Part INNER JOIN PartOpr ON (Part.Company = PartOpr.Company) AND
(Part.PartNum = PartOpr.PartNum)

GROUP BY Part.PartNum, Part.PartDescription, PartOpr.RevisionNum,
Part.TypeCode, Part.InActive

HAVING (((Part.TypeCode)='m') AND ((Part.InActive)=0))

ORDER BY Part.PartNum, PartOpr.RevisionNum;

I can get it to return the data, but it returns all rows..... I just
want the summary for each item/revision. Any thoughts?

Regards,

Bill Turgeon

Director, Information Technology

Coto Technology, Inc.

66 Whitecap Drive

North Kingstown, RI 02852

Ph: 401-583-7223

Cell: 401-368-9938

Fax: 401-942-0920

E-mail: bturgeon@... <mailto:bturgeon%40cotorelay.com>
jbentley@... <mailto:jbentley%40cotorelay.com> >

P Please consider the environment before printing this email.

This e-mail and any attachments are for the sole use of the intended
recipients and may be privileged or confidential. Any distribution,
printing or other use by anyone else is prohibited. If you are not an
intended recipient, please contact the sender immediately, and
permanently delete this e-mail and attachments.

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
5
Since you are on 9.05 and already have your query written in SQL, you could try creating the query as a SQL view in your database. Then go into the BAQ and select File / New External BAQ to point to the view you created.

I've only tried it 1 other time and had an issue with the way I believed Epicor was running the view. When I called the tools support they told me they would never create a temp table of all records and they themselves didn't support SQL view writing. My view was written properly so I dropped it and created a custom crystal report instead of using BAQ reports. If you try it and it works please let me know - I'm going to have to stick within the software (e.g. BAQ reports rather than SQL/Crystal) more since I may have to support multiple languages now...

Jenn
6
I just made the SQL view and tried to call it in a BAQ as an external
query, but I can't get past eh ODBC connection.

I know the ODBC is installed and working fine as I use it with
Access/Excel all the time.

Maybe because it's a view and Epicor is as for a database name?



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Jennifer
Sent: Monday, January 28, 2013 12:39 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Re: Epicor 9.05.700C - BAQ return summary data





Since you are on 9.05 and already have your query written in SQL, you
could try creating the query as a SQL view in your database. Then go
into the BAQ and select File / New External BAQ to point to the view you
created.

I've only tried it 1 other time and had an issue with the way I believed
Epicor was running the view. When I called the tools support they told
me they would never create a temp table of all records and they
themselves didn't support SQL view writing. My view was written properly
so I dropped it and created a custom crystal report instead of using BAQ
reports. If you try it and it works please let me know - I'm going to
have to stick within the software (e.g. BAQ reports rather than
SQL/Crystal) more since I may have to support multiple languages now...

Jenn





[Non-text portions of this message have been removed]