Can you create a BAQ that gives you the information you want? If you can,
follow the directions that Rob Bucek posted on 5-22-09. I have included
them in this e-mail.
PROBLEM: How to create a Crystal Report from a Business Activity Query.
OR How to create a CR form from a BAQ.
XA803
FURTHER INFORMATION:
The BAQ Report Designer lets you turn a Business Activity Query (BAQ)
into a Crystal Report. You create these personalized queries through the
Business Activity Query program; you design each query to pull in a
customized set of information from your database.
You then use the BAQ Report Designer to select this BAQ as the base for
a report -- and also define the option fields, filters, and sort by
options that will appear on the report interface. To finish the report,
you use the Crystal Reports application to define its layout.
In order to effectively use the BAQ Report Designer, you should have
solid knowledge of both Business Activity Queries and Crystal Reports.
The reports you create through this tool are flat reports -- they are
restricted to only pulling data from the table defined on their selected
BAQs.
RESOLUTION: Before you create the BAQ report, you must first create the
Business Activity Query. This BAQ must contain the Company column within
its table; if the Company column is not present, the BAQ report will not
work properly. After you finish creating the BAQ, you will then select
it on your report.
A) CREATE THE QUERY:
Before you begin the BAQ, you may wish to create a blank spread sheet in
excel and name it what you would name your report. Save it and exit.
Next, look in your System Management> Data Dictionary Viewer and write
down the tables and fields you wish to use in your query.
1. From the main menu, select System Management -> Utilities -> Business
Activity Query. This will open the Business Activity Query main screen.
From here you can either display a list of existing queries, or create a
new one.
2. To add a new query, select File --> New. This will automatically fill
in the Query ID with the Company ID followed by a query number. The
number is incremented for each query created against the current
company. The Query ID can be used as is, or changed if desired.
3. Enter a Description for the new Query.
4. If this query is to be incorporated on the mainmenu and shared with
other users, check the "Shared" box. At this time, press the [Save]
button to save the base query.
5. Click on the [Phrase Builder] button in the lower right hand corner.
This will activate the query wizard, which is designed to create the
query as you select the tables and criterion of interest without
requiring you to know the query syntax structure. Once you are familiar
with the query language, you could alternatively click on the individual
Tabs and key in the query manually.
6. From the Phrase Builder - Available Tables browser, select the
OrderHed table which contains the sales order header information, by
highlighting it and clicking on the Right Arrow button.
7. After selecting the OrderHed table, check the "Filter Tables" option
below the Available Tables browse. This will limit the display to only
the tables which have a relationship to the OrderHed table.
8. Select the Customer table by highlighting it in the Available Tables
browse and again click on the Right Arrow button moving it to the
Selected Tables.
9. Click the [Next] button. Notice that the Business Activity Query
joins the OrderHed table to the Customer table and displays the key
fields it uses to link the Parent and child table. Click on the Next
button to continue.
10. The next screen allows you to add filter information, since we are
interested in sales orders that are on hold, we need to add that as a
filter. Using the table drop-down list, select the Customer table. Next,
press the Criteria button in the lower left corner.
11. Hold the mouse over the Field Name field and the dropdown will
appear. Click on it and select CreditHold.
12. Click on the Equals button and since the field is a logical field
(Yes/No), enter YES in the Constant box, then click on the Finished
button. Notice the Query Phrase box will not display the syntax for this
query. This is a good time to press the [Save] button and save the
query.
13. Click the Display tab to select the desired fields you want to
display on the Dashboard. (i.e. CustNum, Name, City, State, Zip,
OrderNumber, etc)
14. Click on the Headings Tab. Here you can change the display names of
fields you have selected.
15. Click on the Sort Tab to select the field or fields you wish to sort
this query by.
16. Again, at this time press the [Save] button.
17. Finally, click on the Analyze Tab. First press [Analyze] button. If
the syntax is correct you will get a message saying "Syntax OK". If not,
you will need to revisit the query and correct the phase. Next, click on
the [Test] button and the query will run, displaying all customers on
Credit Hold.
18. Correct any problems, and save the updates.
B) DEFINE THE REPORT OPTIONS: You also need to define the local paths
that will be used to save the report files. You must define three paths
-- the Crystal Reports Executable, the Sample Data Directory, and the
Local Reports Directory. When you first create a report, the BAQ report
designer will automatically place the .rpt report file within the Local
Reports Directory.
1. Go to Actions->BAQ Report Options.
2. Set the location of the Crystal Report Executable to the location of
the CRW32.exe, under the Crystal Report XI R2 installation.
3. Set the Sample Data Directory to the location of the \mfgsysdata
folder.
4. Set the Local Reports Directory to the location where the .rpt files
will be saved.
5. Check the Copy Report Locally check box, if you want a copy of the
.rpt file saved locally. This would serve as a backup to the .rpt file
in the Local Reports Directory.
6. Click Apply to set these settings.
C) CREATE A NEW BAQ REPORT: After you save the Business Activity Query,
you can create the BAQ Report. This BAQ Report will allow you to design
and deploy the Crystal Report form to your users.
1. Go to File->New->New Report Data Definition.
2. Click the BAQ ID button, and search for the BAQ that will be the
source of the Crystal Report.
3. Give the report a Report ID. This will be the default Crystal Report
filename as well.
4. Give the report a Description and a Form Title.
5. Go to File->Save to create the .rpt file named in the Crystal Report
field.
D) DESIGN THE PRINT OPTIONS WINDOW: Use the Option Fields, Filters, and
Sorts sheets to define the various options that will appear on the
report. See Help for details on setting up this window.
E) TEST THE REPORT FORM: Use the Test Report Form command to display the
interface for the current BAQ report. You can then test the option
fields, filters, and sort orders you defined for the report.
1. Go to Actions->Test Report Form.
2. The current BAQ report's interface appears.
3. Confirm that all options, filters, and sort orders are included on
the Print Options Window.
F) GENERATE SAMPLE DATA: The Generate Sample Data command lets you
create data that you can use to test the current report. It runs the BAQ
selected on the current report to generate the data. This data is then
saved to the Sample Data Directory. You can then use this data to help
you design the final layout of the report within Crystal Reports. You
will not be able to run the report with data until section I is
finished. Only two records are generated for testing.
1. Go to Actions->Generate Sample Data.
2. When the data is generated, a "Sample data was successfully created."
message will appear.
3. This message shows you both the path and the name of the data file
(an .xml file).
4. Note this location. It will be used when designing the Crystal
Report.
G) DESIGN CRYSTAL REPORT: The Design Crystal Report command lets you
launch the current BAQ report within the Crystal Report application.
This command causes the program to search for the Crystal Report .exe in
the Crystal Report Executable path. When it finds this file, Crystal
Reports is launched. NOTE: A familiarity with Crystal Reports is
recommended for the next steps, as you will be designing your Crystal
Report.
1. Go to Actions->Design Crystal Report.
2. Crystal Reports opens, with the blank template report.
3. Go to Database->Set Datasource Location.
4. In the Replace With section, double-click Create New
Connection->ADO.NET (XML).
5. In the ADO.NET (XML) window, in the File Path field, browse to the
.xml file created in F.3 above, highlight the .xml, click Open, and
click Finish.
6. Highlight the ReportDataSet in the Current Data Source and Replace
With sections, and then click the Update button on the right. Click
Close.
7. Go to Database->Database Expert.
8. On the Data tab, copy the BAQReportResults from Available Data
Sources on the left to the Selected Tables on the right. Click OK.
9. On the Link tab, double-click the link line between the Company table
and the BAQReportParameter table. Select Left Outer Join in the Join
Types section. You may accept the remaining default links or create your
own links between tables and fields. Click OK.
10. Go to View->Field Explorer. Expand the Database
Fields->BAQReportResults tree. This will list your tables.fields from
the BAQ. Include and arrange the fields as required.
11. Go to File->Save to save your report layout. Do *NOT* save the
report with data.
12. Close Crystal Report to return to the BAQ Report Designer.
H) DEPLOYING THE BAQ REPORT: Use the Deploy BAQ Report command to move
the current BAQ report to the network. The program will place the report
within the Server Reports Directory out on the server. The program does
this by finding the report within the Local Reports Directory and then
moving it to the Server Reports directory. When the BAQ report is
deployed, you are then ready to add this report to the Main Menu.
1. Go to Actions->Deploy BAQ Report.
2. The .rpt file will be placed in the Server Reports Directory on the
server.
3. This report can be added to a menu, and used by other users.
4. Exit out of BAQ Report Designer.
I) CREATING THE BAQ REPORT MENU OPTION: After deploying the .rpt file,
you can add the custom BAQ Report to any menu option desired. This is
done from the Menu Maintenance menu.
1. Go to System Management->Utilities->Menu Maintnenance.
2. Choose File->New.
3. Add a Menu ID - UD has to be part of this menu ID.
4. Choose a Parent Menu ID (IE OMMN4000 for a report under Order
Management->Reports).
5. Choose an Order Sequence - the lower the number, the closer to the
top the menu item will appear, something like 25, will put the report
second in the list.
6. Check the "Enabled" box
7. Choose a Security ID, usually the Security ID of the actual Report
Menu this will fall under, unless you want to set specific security
levels on just this one report.
8. Click the BAQ Report option from the Program Type drop down. Also
choose the Report under the Icon drop down.
9. From Program, it should default to the Epicor.Mfg.UIRpt.BAQReport.dll
file. This should NOT contain a path.
10. Click the Report button and select APInvOpen in the Report field.
11. Save the changes.
12. Log off, and log back into Vantage. Your Custom Report Link will now
be visible.
Rob Bucek
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Sreejith Jaganathan
Sent: Wednesday, May 27, 2009 11:06 AM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Creating a report for parts the qty in hand of which
is lower than the safety stock
Dear Friends,
Kindly help me to get a solution for the below mentioned problem.
Regards
Sreejith J
________________________________
From: Sreejith Jaganathan <sreesvantagedoubts@...
<mailto:sreesvantagedoubts%40yahoo.com> >
To: Vantage Group <vantage@yahoogroups.com
<mailto:vantage%40yahoogroups.com> >
Sent: Tuesday, May 26, 2009 12:59:20 PM
Subject: [Vantage] Creating a report for parts the qty of which is lower
than the safety stock
Dear Friends,
Kindly help me to prepare a report showing the parts of which the qty in
hand is less than or equal to the safety stock.
Regards
Sreejith J
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
follow the directions that Rob Bucek posted on 5-22-09. I have included
them in this e-mail.
PROBLEM: How to create a Crystal Report from a Business Activity Query.
OR How to create a CR form from a BAQ.
XA803
FURTHER INFORMATION:
The BAQ Report Designer lets you turn a Business Activity Query (BAQ)
into a Crystal Report. You create these personalized queries through the
Business Activity Query program; you design each query to pull in a
customized set of information from your database.
You then use the BAQ Report Designer to select this BAQ as the base for
a report -- and also define the option fields, filters, and sort by
options that will appear on the report interface. To finish the report,
you use the Crystal Reports application to define its layout.
In order to effectively use the BAQ Report Designer, you should have
solid knowledge of both Business Activity Queries and Crystal Reports.
The reports you create through this tool are flat reports -- they are
restricted to only pulling data from the table defined on their selected
BAQs.
RESOLUTION: Before you create the BAQ report, you must first create the
Business Activity Query. This BAQ must contain the Company column within
its table; if the Company column is not present, the BAQ report will not
work properly. After you finish creating the BAQ, you will then select
it on your report.
A) CREATE THE QUERY:
Before you begin the BAQ, you may wish to create a blank spread sheet in
excel and name it what you would name your report. Save it and exit.
Next, look in your System Management> Data Dictionary Viewer and write
down the tables and fields you wish to use in your query.
1. From the main menu, select System Management -> Utilities -> Business
Activity Query. This will open the Business Activity Query main screen.
From here you can either display a list of existing queries, or create a
new one.
2. To add a new query, select File --> New. This will automatically fill
in the Query ID with the Company ID followed by a query number. The
number is incremented for each query created against the current
company. The Query ID can be used as is, or changed if desired.
3. Enter a Description for the new Query.
4. If this query is to be incorporated on the mainmenu and shared with
other users, check the "Shared" box. At this time, press the [Save]
button to save the base query.
5. Click on the [Phrase Builder] button in the lower right hand corner.
This will activate the query wizard, which is designed to create the
query as you select the tables and criterion of interest without
requiring you to know the query syntax structure. Once you are familiar
with the query language, you could alternatively click on the individual
Tabs and key in the query manually.
6. From the Phrase Builder - Available Tables browser, select the
OrderHed table which contains the sales order header information, by
highlighting it and clicking on the Right Arrow button.
7. After selecting the OrderHed table, check the "Filter Tables" option
below the Available Tables browse. This will limit the display to only
the tables which have a relationship to the OrderHed table.
8. Select the Customer table by highlighting it in the Available Tables
browse and again click on the Right Arrow button moving it to the
Selected Tables.
9. Click the [Next] button. Notice that the Business Activity Query
joins the OrderHed table to the Customer table and displays the key
fields it uses to link the Parent and child table. Click on the Next
button to continue.
10. The next screen allows you to add filter information, since we are
interested in sales orders that are on hold, we need to add that as a
filter. Using the table drop-down list, select the Customer table. Next,
press the Criteria button in the lower left corner.
11. Hold the mouse over the Field Name field and the dropdown will
appear. Click on it and select CreditHold.
12. Click on the Equals button and since the field is a logical field
(Yes/No), enter YES in the Constant box, then click on the Finished
button. Notice the Query Phrase box will not display the syntax for this
query. This is a good time to press the [Save] button and save the
query.
13. Click the Display tab to select the desired fields you want to
display on the Dashboard. (i.e. CustNum, Name, City, State, Zip,
OrderNumber, etc)
14. Click on the Headings Tab. Here you can change the display names of
fields you have selected.
15. Click on the Sort Tab to select the field or fields you wish to sort
this query by.
16. Again, at this time press the [Save] button.
17. Finally, click on the Analyze Tab. First press [Analyze] button. If
the syntax is correct you will get a message saying "Syntax OK". If not,
you will need to revisit the query and correct the phase. Next, click on
the [Test] button and the query will run, displaying all customers on
Credit Hold.
18. Correct any problems, and save the updates.
B) DEFINE THE REPORT OPTIONS: You also need to define the local paths
that will be used to save the report files. You must define three paths
-- the Crystal Reports Executable, the Sample Data Directory, and the
Local Reports Directory. When you first create a report, the BAQ report
designer will automatically place the .rpt report file within the Local
Reports Directory.
1. Go to Actions->BAQ Report Options.
2. Set the location of the Crystal Report Executable to the location of
the CRW32.exe, under the Crystal Report XI R2 installation.
3. Set the Sample Data Directory to the location of the \mfgsysdata
folder.
4. Set the Local Reports Directory to the location where the .rpt files
will be saved.
5. Check the Copy Report Locally check box, if you want a copy of the
.rpt file saved locally. This would serve as a backup to the .rpt file
in the Local Reports Directory.
6. Click Apply to set these settings.
C) CREATE A NEW BAQ REPORT: After you save the Business Activity Query,
you can create the BAQ Report. This BAQ Report will allow you to design
and deploy the Crystal Report form to your users.
1. Go to File->New->New Report Data Definition.
2. Click the BAQ ID button, and search for the BAQ that will be the
source of the Crystal Report.
3. Give the report a Report ID. This will be the default Crystal Report
filename as well.
4. Give the report a Description and a Form Title.
5. Go to File->Save to create the .rpt file named in the Crystal Report
field.
D) DESIGN THE PRINT OPTIONS WINDOW: Use the Option Fields, Filters, and
Sorts sheets to define the various options that will appear on the
report. See Help for details on setting up this window.
E) TEST THE REPORT FORM: Use the Test Report Form command to display the
interface for the current BAQ report. You can then test the option
fields, filters, and sort orders you defined for the report.
1. Go to Actions->Test Report Form.
2. The current BAQ report's interface appears.
3. Confirm that all options, filters, and sort orders are included on
the Print Options Window.
F) GENERATE SAMPLE DATA: The Generate Sample Data command lets you
create data that you can use to test the current report. It runs the BAQ
selected on the current report to generate the data. This data is then
saved to the Sample Data Directory. You can then use this data to help
you design the final layout of the report within Crystal Reports. You
will not be able to run the report with data until section I is
finished. Only two records are generated for testing.
1. Go to Actions->Generate Sample Data.
2. When the data is generated, a "Sample data was successfully created."
message will appear.
3. This message shows you both the path and the name of the data file
(an .xml file).
4. Note this location. It will be used when designing the Crystal
Report.
G) DESIGN CRYSTAL REPORT: The Design Crystal Report command lets you
launch the current BAQ report within the Crystal Report application.
This command causes the program to search for the Crystal Report .exe in
the Crystal Report Executable path. When it finds this file, Crystal
Reports is launched. NOTE: A familiarity with Crystal Reports is
recommended for the next steps, as you will be designing your Crystal
Report.
1. Go to Actions->Design Crystal Report.
2. Crystal Reports opens, with the blank template report.
3. Go to Database->Set Datasource Location.
4. In the Replace With section, double-click Create New
Connection->ADO.NET (XML).
5. In the ADO.NET (XML) window, in the File Path field, browse to the
.xml file created in F.3 above, highlight the .xml, click Open, and
click Finish.
6. Highlight the ReportDataSet in the Current Data Source and Replace
With sections, and then click the Update button on the right. Click
Close.
7. Go to Database->Database Expert.
8. On the Data tab, copy the BAQReportResults from Available Data
Sources on the left to the Selected Tables on the right. Click OK.
9. On the Link tab, double-click the link line between the Company table
and the BAQReportParameter table. Select Left Outer Join in the Join
Types section. You may accept the remaining default links or create your
own links between tables and fields. Click OK.
10. Go to View->Field Explorer. Expand the Database
Fields->BAQReportResults tree. This will list your tables.fields from
the BAQ. Include and arrange the fields as required.
11. Go to File->Save to save your report layout. Do *NOT* save the
report with data.
12. Close Crystal Report to return to the BAQ Report Designer.
H) DEPLOYING THE BAQ REPORT: Use the Deploy BAQ Report command to move
the current BAQ report to the network. The program will place the report
within the Server Reports Directory out on the server. The program does
this by finding the report within the Local Reports Directory and then
moving it to the Server Reports directory. When the BAQ report is
deployed, you are then ready to add this report to the Main Menu.
1. Go to Actions->Deploy BAQ Report.
2. The .rpt file will be placed in the Server Reports Directory on the
server.
3. This report can be added to a menu, and used by other users.
4. Exit out of BAQ Report Designer.
I) CREATING THE BAQ REPORT MENU OPTION: After deploying the .rpt file,
you can add the custom BAQ Report to any menu option desired. This is
done from the Menu Maintenance menu.
1. Go to System Management->Utilities->Menu Maintnenance.
2. Choose File->New.
3. Add a Menu ID - UD has to be part of this menu ID.
4. Choose a Parent Menu ID (IE OMMN4000 for a report under Order
Management->Reports).
5. Choose an Order Sequence - the lower the number, the closer to the
top the menu item will appear, something like 25, will put the report
second in the list.
6. Check the "Enabled" box
7. Choose a Security ID, usually the Security ID of the actual Report
Menu this will fall under, unless you want to set specific security
levels on just this one report.
8. Click the BAQ Report option from the Program Type drop down. Also
choose the Report under the Icon drop down.
9. From Program, it should default to the Epicor.Mfg.UIRpt.BAQReport.dll
file. This should NOT contain a path.
10. Click the Report button and select APInvOpen in the Report field.
11. Save the changes.
12. Log off, and log back into Vantage. Your Custom Report Link will now
be visible.
Rob Bucek
From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Sreejith Jaganathan
Sent: Wednesday, May 27, 2009 11:06 AM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Creating a report for parts the qty in hand of which
is lower than the safety stock
Dear Friends,
Kindly help me to get a solution for the below mentioned problem.
Regards
Sreejith J
________________________________
From: Sreejith Jaganathan <sreesvantagedoubts@...
<mailto:sreesvantagedoubts%40yahoo.com> >
To: Vantage Group <vantage@yahoogroups.com
<mailto:vantage%40yahoogroups.com> >
Sent: Tuesday, May 26, 2009 12:59:20 PM
Subject: [Vantage] Creating a report for parts the qty of which is lower
than the safety stock
Dear Friends,
Kindly help me to prepare a report showing the parts of which the qty in
hand is less than or equal to the safety stock.
Regards
Sreejith J
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]