SQL Server Reporting Services SSRS

Epicor ERP SQL Server Reporting Services for Epicor ERP 10.0.700

Disclaimer This document is for informational purposes only and is subject to change without notice. This document and its contents, including the viewpoints, dates and functional content expressed herein are believed to be accurate as of i ts date of publication. However, Epicor Software Corporation makes no guarantee, representations or warranties with regard to the enclosed information and specifically di sclaims any applicable implied warranties, such as fitness for a particular purpose, merchantability, satisfactory quality or reasonable skill and care. As each user of Epicor Epi cor software is likely to be unique in their requirements in the use of such software and their business processes, users of this document are always advised to discuss the content of this document with their Epicor account manager. All information contai ned herein is subject to change without notice and changes to this document since printing and other important information about the software product are made or published in release notes, and you are urged to obtain the current release notes for the software product. We welcome user comments and reserve the right to revise this publication and/or make improvements or changes to the products or programs described in this publication at any time, without notice. The usage of any Epicor software shall be pursuant to an Epicor end user license agreement and the performance of any consulting services by Epicor personnel shall be pursuant to Epicor's standard services terms and conditions. Usage of the solution(s) described in this document with other Epicor software or third party products may require the purchase of licenses for such other products. Where any software is expressed to be compliant with local laws or requirements in this document, such compliance is not a warranty and is based solely on Epicor's current understanding of such laws and requirements. All laws and requirements are subject to varying interpretations as well as to change and accordingly Epicor cannot guarantee that the software will be compliant and up to date with such changes. All statements of platform and product compatibility in this document shall be considered individually in relation to the products referred to in the relevant statement, i.e., where any Epicor software is stated to be compatible with one product and also stated to be compatible with another product, it should not be interpreted that such Epicor software is compatible with both of the products running at the same time on the same platform or environment. Additionally platform or product compatibility may require the application of Epi cor or third-party updates, patches and/or service packs and Epicor has no responsibility for compatibility issues which may be caused by updates, patches and/or service packs released by third parties after the date of publ ication of this document. Epicor ® is a registered trademark and/or trademark of Epicor Software Corporation in the United States, certain other countries and/or the EU. All other trademarks mentioned are the property of their respective owners. Copyright © Epicor Software Corporation 2014. All rights reserved. No part of this publication may be reproduced in any form without the prior written consent of Epicor Software Corporation.

ED907905 90521-10-1606-58310700 10.0.700 Revision: June 08, 2014 6:10 p.m. Total pages: 64 course.ditaval

SQL Server Reporting Services for Epicor ERP

Contents

Contents SQL Server Reporting Services for Epicor ERP Course......................................... Course....................................................... ................5 ..5 Before You Begin............................ Begin.......................................... ............................. ............................. ............................ ............................. ............................. ................6 ..6 Audience Audi ence.......... ...................... ........................ ....................... ....................... ........................ ....................... ....................... ........................ ....................... ....................... ........................ ........................ ..............6 ..6 Prerequisi Prere quisites.................. tes............................. ....................... ........................ ....................... ....................... ........................ ....................... ....................... ........................ ....................... ....................... ..............6 ..6 Environment Setup...................... Setup.................................. ....................... ....................... ........................ ........................ ....................... ....................... ........................ ....................... ......................6 ...........6

Finding the Data.......................... Data......................................... ............................. ............................. ............................. ............................ ............................. ...................9 ....9 Field Help Technical Details...................... Details................................. ....................... ........................ ........................ ....................... ....................... ........................ ....................... ......................9 ...........9 Workshop - Use the Field Help....................... Help................................... ........................ ....................... ....................... ........................ ....................... ....................... .....................9 .........9 Data Dictionary Viewer.................... Viewer................................ ....................... ....................... ........................ ....................... ....................... ........................ ....................... ....................... .................10 .....10 Tables................................. Tables............................................. ........................ ....................... ....................... ........................ ........................ ....................... ....................... ........................ ......................10 ..........10 Fields................. Fiel ds............................ ....................... ........................ ........................ ....................... ....................... ........................ ....................... ....................... ........................ ....................... ................11 .....11 Table Linking....................... Linking................................... ........................ ....................... ....................... ........................ ....................... ....................... ........................ ....................... ....................... .................11 .....11

Accessing SSRS SS RS Report Manager and Report Builder................................... Builder................................................. .....................13 .......13 Workshop - Open SSRS Report Manager from Server.................... Server............................... ....................... ........................ ........................ ....................... ..................13 .......13 Workshop - Open SSRS Report Manager from Client........................ Client................................... ....................... ........................ ....................... ....................... ...............14 ...14 Workshop - Open Report Builder from Report Manager.................... Manager............................... ....................... ........................ ....................... ....................... ...............15 ...15

Developing a Custom SSRS Report - Parts List.................................... List.................................................. ............................. .................16 ..16 Workshop - Create the Parts List Report.................... Report................................ ........................ ....................... ....................... ........................ ....................... ....................... ...............16 ...16 Create the Parts List Report in Report Builder....................... Builder................................... ....................... ....................... ........................ ....................... ....................16 .........16 Add the Parts List Report to the Epicor Application....................... Application.................................. ....................... ........................ ....................... ....................... .............18 .18 Workshop - Add Parameters to the Parts List Report.................... Report............................... ....................... ........................ ........................ ....................... ....................19 .........19 Create Parameters for the Parts List Report.................... Report................................ ....................... ....................... ........................ ....................... ....................... ...............19 ...19 Create Parameter Filters for the Parts List Report.................... Report............................... ....................... ........................ ....................... ....................... ...................20 .......20 Test the Parts List Report with the New Parameters........................... Parameters....................................... ........................ ........................ ....................... ..................21 .......21 Workshop - Apply Conditional Formatting to the Parts List Report.............................. Report.......................................... ....................... ....................... .............21 .21 Add Alternating Row Coloring to the Parts List Report.............................. Report.......................................... ........................ ....................... ......................21 ...........21 Add Value-Based Row Coloring to the Parts List Report..................... Report................................. ........................ ....................... ....................... ...................22 .......22 Workshop - Add a Calculated Field to the Parts List Report..................... Report................................. ........................ ........................ ....................... ....................23 .........23 Create a Calculated Field in the Parts List Report.................... Report............................... ....................... ........................ ........................ ....................... ..................23 .......23 Add a Column for Calculated Value to the Parts List Report.................... Report................................ ........................ ....................... ....................... .............24 .24 Test the Finished Parts List Report in ERP...................... ERP.................................. ....................... ....................... ........................ ....................... ....................... .................24 .....24

Developing a Custom SSRS Report - Customer Information with Embedded Subrepor Subr eports...................... ts..................................... ............................. ............................. ............................. ............................. ............................. .........................26 ...........26 Workshop - Create Supporting Epicor CRM Call Records..................... Records................................ ....................... ........................ ....................... ....................... .............26 .26 Workshop - Create the Customer Main Report..................... Report................................. ....................... ....................... ........................ ....................... ....................... ...............28 ...28 Create the Customer Main Report in i n Report Builder....................... Builder.................................. ....................... ........................ ....................... ......................28 ...........28 Add Parameters to the Customer Main Report..................... Report................................ ....................... ........................ ........................ ....................... ....................30 .........30 Adjust the Customer Main Report Format.................. Format.............................. ....................... ....................... ........................ ....................... ....................... ...................32 .......32 Workshop - Create the Call Activity Subreport.................... Subreport................................ ....................... ....................... ........................ ....................... ....................... .................33 .....33 Create the Call C all Activity Subreport in Report Builder.......... Bui lder...................... ....................... ....................... ........................ ....................... ....................... .............33 .33

Epicor ERP | 10.0.700

3

Contents


Add Parameters and Filters to the Call Activity Subreport........................................................................35 Adjust the Call Activity Subreport Format................................................................................................37 Workshop - Create the Open Orders Subreport..............................................................................................39 Create the Open Orders Subreport in Report Builder...............................................................................39 Add Parameters and Filters to the Open Orders Subreport.......................................................................41 Adjust the Open Orders Subreport Format..............................................................................................42 Workshop - Embed the Subreports in the Main Report...................................................................................44 Workshop - Add the Main Report to the Epicor Application...........................................................................46

Working with Epicor Shipped SSRS Reports.......................................................................48 Workshop - Creating a Modified Version of an Epicor Shipped SSRS Report...................................................48 Copy and Edit the Shipped Report Data Definition..................................................................................48 Add a Report Style..................................................................................................................................49 Create a Copy of the Shipped Sales Order Pick List Report......................................................................50 Add a Table Field to the Sales Order Pick List Copy..................................................................................51 Add a Field Placeholder to the Sales Order Pick List Copy........................................................................52 Test the Sales Order Pick List Copy in Epicor ERP.....................................................................................53

Working with BAQ Reports.................................................................................................54 Workshop - Create a BAQ Report with SSRS Report Layout............................................................................54 Add the New BAQ Report.......................................................................................................................54 Perform an Initial Test of the New BAQ Report........................................................................................55 Design the BAQ Report Layout in Report Builder.....................................................................................56 Add the BAQ Report to the Epicor ERP Application.................................................................................57

Printing an SSRS Report.......................................................................................................59 Add an SSRS Printer.......................................................................................................................................59 Print a Report.................................................................................................................................................59

Working with Report Security.............................................................................................61 Security Features in Menu Maintenance.........................................................................................................61

Conclusion.............................................................................................................................63

4



SQL Server Reporting Services for Epicor ERP Course

SQL Server Reporting Services for Epicor ERP Course This course introduces Microsoft SQL Server Reporting Services (SSRS) reports and their implementation in Epicor ERP. Microsoft SQL Server Reporting Services (SSRS) is a server-based reporting platform that provides comprehensive reporting functionality for generating reports from SQL Server databases. For creating custom reports, the workshops in this course uses Microsoft SQL Server Report Builder, which is a report authoring tool that allows the user to get started quickly in a Microsoft Office-like environment. Upon successful completion of this course, you will be able to: •

Use the SSRS Report Manager management tool and the Report Builder authoring tool to create custom reports.

•

Add custom SSRS reports to the Epicor ERP application menu system and retrieve them for viewing.

•

Edit an Epicor ERP shipped SSRS report.

•

Create a BAQ report and then use the Report Builder authoring tool to design an SSRS report layout for the report.

•

Add an SSRS printer in the Epicor Client and print SSRS reports.

•

Understand the report security resources available when working with custom and shipped SSRS reports in Epicor.


5

Before You Begin


Before You Begin Read this topic for information you should know in order to successfully complete this course.

Audience Specific audiences will benefit from this course. •

System Administrator

•

IT/Technical Staff

•

Report Writers

•

Business Analysts

Prerequisites To complete the workshops in this course, the necessary modules must be licensed and operating i n your training environment. For more information on the modules available, contact your Epicor Customer Account Manager at [email protected]. It is also important you understand the prerequisite knowledge contained i n other valuable courses. •

Navigation Course - This course introduces navigational aspects of the Epicor application's user interface. Designed for a hands-on environment, general navigation principles and techniques are available at each of the interface levels in the Epicor appl ication - system, module, and program. Workshops focus on each of these levels and guide you through each navigational principle introduced.

•

Recommended Industry Knowledge •

Exposure to other reporting programs.

•

Knowledge of relational database concepts such as table relationships, records, and field types.

•

Understanding of the current release application functionality.

Environment Setup The environment setup steps and potential workshop constraints must be reviewed in order to successfully complete the workshops in this course. Your Epicor training environment, in which the Epicor demonstration database is found, enables you to experience Epicor functionality in action but does not affect data in your live, production environment. The following steps must be taken to successfully complete the workshops in this course.

6



1.

Before You Begin

Verify the following or ask your system administrator to verify for you: •

Your Epicor training icon (or web address if you are using Epicor Web Access) points to your Epicor training environment with the Epicor demonstration database installed. Do not complete the course workshops in your live, production environment. Note It is recommended that multiple Epicor demonstration databases are installed. Contact Support or Systems Consulting for billable assistance.

2.

•

The Epicor demonstration database is at the same service pack and patch as the Epicor application. Epicor's education team updates the Epicor demonstration database for each service pack and patch. If your system administrator upgrades your Epicor application to a new service pack or patch, he or she must also download the corresponding Epicor demonstration database from EPICweb > Support > Epicor > Downloads and install it. If this is not performed, unexpected results can occur when completing the course workshops.

•

Your system administrator restored (refreshed) the Epicor demonstration database prior to starting this course. The Epicor demonstration database comes standard with parts, customers, sales orders, and so on, already defined. If the Epicor demonstration database is shared with multiple users (that is, the database is located on a server and users access the same data, much like your live, production environment) and is not periodically refreshed, unexpected results can occur. For example, if a course workshop requires you to ship a sales order that came standard in the Epicor demonstration database, but a different user already completed this workshop and the Epicor demonstration database was not restored (refreshed), then you will not be able to ship the sales order. Epicor's education team has written the course workshops to minimize situations like this from occurring, but Epicor cannot prevent users from manipulating the data in your installation of the Epicor demonstration database.

Verify, or ask your system administrator to verify, that you have access to the SSRS Reporting Services Configuration Manager, Report Manager, and Report Builder for your SQL Server report server. You access to these tools must include sufficient user role assignments to create folders and save report files on the report server. The administrator sets user role assignments in the Report Manager Folder Settings page. It is recommended that your user account have all roles selected. This includes Browser, Content Manager, My Repots, Publisher, and Report Builder. Ensure that security for the root Reports folder is inherited from Parent Security or is otherwise appropriately defined to allow user access. The course workshops for developing custom reports and modifying an Epicor shipped SSRS report use Report Builder starting from Report Manager. The course workshop for developing a BAQ report uses Report Builder starting from within the Epicor ERP application. In each scenario, SSRS will download Report Builder automatically if it is not found on the system where you are working.

3.

Verify, or ask your system administrator to verify, that the security configuration of the SQL Server instance in which your training database is located includes a login with sufficient permissions to access the data used in the workshop.

4.

In the Windows Services control panel, verify, or ask your administrator to verify, that Epicor SQL Report Monitor Service is running and the Show Report Parameters option is enabled in the report monitor's configuration.

5.

Log in to the training environment using the credentials manager/manager. If you are logged into your training environment as a different user, from the application Home Page click Settings to open the Settings Page, and then choose Change User under General Options.

6.

From the Menu, select the company Epicor Education (EPIC06).


7

Before You Begin

8


7.

From the Menu, select the Main site.

8.

In Company Maintenance verify, or ask your system administrator to verify, that SSRS Printer Option on the Email and Printing tab is set to either Client and Server Printing or Server Printing Only.



Finding the Data

Finding the Data This section of the course reviews the table and field name identification process in the Epicor ERP application, using Field Help, and the Data Dictionary Viewer functionality. It also describes table linking procedures and requirements, join type definitions and specifications. A fundamental requirement when modifying or authoring custom reports or queries is to identify which tables within the database contain the needed data, and which field names are used to store that data.

Field Help Technical Details The Field Help feature is a quick reference tool that provides a brief field description and the technical property reference for selected fields. To enable Field Help, from the Help menu, select Field Help and click a field in the interface. The Field Help sheet contains two menu items: •

Field Level Help - The Field Level Help is a text description of the field from the Application Help. You can use Field Help as a learning tool, as it allows you to access documentation for each field.

•

Technical Details - The technical details include the data dictionary information for the field. Use technical specifications for a field when building business activity querie s (BAQs) and using Business Process Management (BPM) methods and other advanced functions of the Epicor application. Note To view technical details, you must have permission to access the corresponding business object. This permission is set in Process Security Maintenance for the bo.DataDict business object.

The technical details of the Field Help are valuable in understanding the table structure of the database. Keep in mind that data can reside in multiple tables. The query topic displays where the data comes from. You can also use the Customization feature to locate tables and fields.

Workshop - Use the Field Help In this workshop, use the Field Help to find table and field names. The Field Help provides a Technical Details sheet to display the data dictionary information for a selected field. The properties that display are helpful when you create a Business Activity Query and discuss the Data Dictionary Viewer. •

Field Name - This displays the field name as defined by the Epicor application. This name is used in all expressions.

•

EpiBinding - This displays the table name and the field name. For example, the field displays Part.PartNum, which means the table to use is the Part table and the field to pull into the query is i s the Part field. The syntax is always .

•

DB Field - This displays the database field property which also displays as .

•

Format - This is the database format for this field and the number of characters to which that field is limited.

•

Like - Use this field to validate a BAQ search and generate Foreign Key Views to indicate the common field between two tables or datasets.

Navigate to Part Maintenance. Epicor ERP | 10.0.700

9

Finding the Data


Menu Path: Material Management > Inventory Management > Setup > Part 1. From the Help menu, select Field Help.

In the tree view pane, the Field Help sheet displays. It automatically opens in an undocked position. 2. Place the cursor in the Field Help sheet header and click the push pin icon to dock the Field Help window. 3. Place the cursor in the Description field. 4. In the Field Help sheet, click Technical Details and view the information.

You can adjust the Field Help width to see the fields properly. 5. Navigate to the Part > Sites > Warehouses > Primary Bin sheet and place the cursor in the Bin field.

The properties populate with the data in this field. The table and field combination display in the EpiBinding field of the Technical Details in the format of .. 6. Navigate to the Part > Sites > Warehouses > Bin Information > Detail sheet and place the cursor in the Bin field.

Notice the database field is different. di fferent. Note Data is often stored in multiple places, and table selection depends on the purpose of the query. This example presented a part's listing with a part bin location.

7. Exit Part Maintenance.

Data Dictionary Viewer Use the Data Dictionary Viewer to find and review details of each field and table within the database. It helps you better understand the purpose and data values of each field. The Data Dictionary Viewer helps you to identify the fields and tables you want for a customization or a custom report. This program is also an aid during upgrades, as you can view the current database structure to compare it against a previous database version. Important

In Epicor ERP version 10, user-defined columns are placed within separate UD tables. For example, the Part table may have a corresponding Part_UD table. Navigate to this program from the Main Menu: •

System Setup > System Maintenance > Data Dictionary Viewer

Tables Use the Tables sheet to find and select the database table to review. The Data Dictionary Viewer is organized by tables. A table is a set of fields that contain related information. Example •

10

Use the Customer table to store all your customer records.



•

Finding the Data

The OrderHed table records your order header records.

Data is often stored in multiple tables, each with a specific purpose. Use the Search option to display tables in a grid format. The grid includes a brief overview of the tables and helps determine which table to use when a field is located in more than one table.

Fields Use the Fields sheet to display specific information about each selected field. All values for a selected field, such as format, label, and description, display on this sheet. Each table contains various fields. These items are individual pi eces of information associated with the table. Each field has a unique name. Example

The Customer table includes the identifying code for the customer (CustNum), name (Name), and other specific details about the customer. Tip

The Data Dictionary Viewer properties are also available in the Technical Details area of the Field Help. When accessed through the Field Help, the details are specific to the selected field.

Table Linking In the table selection process, it is important to link tables together. Use one or more common fields to aid in reporting by selecting field data from multiple tables to create links. Table Links

Link tables so the records from one table match related records from another. This linking process involves using a common field between the selected tables. For example, i f you activate an Orders table and a Customers table, link the tables so each order from the Orders table can be matched with the customer from the Customer table that made the order. Reporting or querying tools use this connection to identify related records between tables. In the above example, the linking process ensures the data in each line of the report refers to the same order. Table Link Requirements

Table link requirements involve linking from one table to another. The from table is the primary table, while the to table is the lookup table where records are looked up by the primary table. For example, if the Quote is the primary table, the querying or reporting tool chronologically goes field by field through the Quote table and searches for matching records in the second table, which can be customers, until all fields are evaluated. Link Types

The main link types are one table to another or one table to multiple others. •

One table to another - The primary table and the lookup table have a one-to-one relationship between their records.

•

One table to multiple others - The primary table and the lookup table have a one-to-many relationship between their records.


11

Finding the Data


Indexed Fields

An index field points to a data location which is a named path designed within the database in order to quickly locate and store data or to easily retrieve data in a table. The index name and path, or index order, are identified in the Data Dictionary Viewer and on the Field Help > Technical Details sheet. Note Always use the Company field as it is the first indexed field and it is located at the top of the Epicor application table hierarchy. The data of each company is stored and separated from other company data using the Company ID field. Since every table uses the Company field, it is an indexed field and you must select it as one of the fields to use in a join.

There are no exceptions to this rule. You must select the Company field as a join field pair when linking tables because company is at the top of the table hierarchy in the Epicor application. Your report will not provide the correct results if this field is not used in the join.

12



Accessing SSRS Report Manager and Report Builder

Accessing SSRS Report Manager and Report Builder SSRS Report Manager and Report Builder are the SSRS tools that you will be using to manage and create reports. Installed as one of the components of SSRS, Report Manager is a web-based report access and management tool that you use to manage reports on the SSRS report server. Report Manager is a good starting point for creating and editing reports; you can select a report and open it for editing in Report Builder and you can launch Report Builder by itself to begin creating a new report. SSRS Report Builder is a report authoring tool that features a Microsoft Office-like authoring environment. Report Builder is well integrated with Report Manager. When you select an action in Report Manager that requires Report Builder, the application will open or it will be automatically downloaded if it is not already available. Report Manager and Report Builder can be accesses from either the server where your SQL Server installation is located or from your Epicor ERP client machine. In this section, you can choose the workshop for opening Report Manager that matches your situation.

Workshop - Open SSRS Report Manager from Server Use this workshop if you are planning on working with Report Manager and Report Builder on the server where your SQL Server installation is located. Report Manager is easy to find and launch from the Reporting Services Configuration Manager. 1. On the server where your SQL Server is installed, locate Reporting Services Configuration Manager on the Apps page or Start menu. 2. Start the Reporting Services Configuration Manager and click Connect to complete the connection to the report server. 3. On the left pane, click Web Service URL.

The URL listed in the right pane under Report Server Web Service URLs is where SSRS web services are hosted. Later on, if you need to identify the target report server while working with reports, the value needed is shown here. 4. Click Report Manager URL on the left pane and click the URL displayed in the right pane under Report Manager Site Identification.

Report Manager opens to its Home page, from which you can navigate the directory structure of the report server. Note that buttons and links at the top of the page provide access to important functions, including a start button for Report Builder. Also note the help link on the top right, which takes you to the Microsoft web site for online help and supporting information resources. 5. Click New Folder on the Report Manager Home page. 6. On the New Folder page, create a folder to support the course workshops in which you will create new custom reports:

a. For Name, enter XXXCustomReports, where XXX are your initials. b. For Description, enter SSRS course workshop custom reports .


13



c. Click OK to return to the Home page. In the workshops, you will save your custom reports to this folder from Report Builder, and point to the folder to access reports from Epicor ERP. 7. Leave Reporting Services Configuration Manager and Report Manager running so that you can get back to them easily during the upcoming workshops.

Workshop - Open SSRS Report Manager from Client Use this workshop if you are planning on working with Report Manager and Report Builder on your Epicor ERP client machine. You can connect to Report Manager through your Internet Explorer browser, where you will have access to all Report Manager functionality, including access to Report Builder. Important To ensure access to all Report Manager functionality, use Microsoft Internet Explorer running in compatibility mode, as explained in this procedure. 1. If you do not already have it, obtain the Report Manager URL from your administrator.

As noted in the previous workshop, the URL can be obtained from the Reporting Services Configuration Manager on the server, if you have access to the server. 2. Use the URL to open Report Manager in a browser.

Report Manager opens to its Home page, from which you can navigate the directory structure of the report server. Note that buttons and links at the top of the page provide access to important functions, including a start button for Report Builder. Also note the help link on the top right, which takes you to the Microsoft web site for online help and supporting information resources. 3. Do the following to ensure that Internet Explorer is running in compatibility mode and Report Manager functionality is enabled.

a. Verify that Internet Explorer is running in compatibility mode. For example, with Internet Explorer 10 open in Windows 7, open the Tools menu and verify that Compatibility View is selected. You also can click the Compatibility icon in the browser's address bar. b. Hover your mouse over the Reports folder and click the arrow on the right to verify that the drop-down menu displays. Inability to access the drop-down menus for folders and individual reports is the most common problem if your browser is not in compatibility mode. 4. Click New Folder on the Report Manager Home page. 5. On the New Folder page, create a folder to support the course workshops in which you will create new custom reports:

a. For Name, enter XXXCustomReports, where XXX are your initials. b. For Description, enter SSRS course workshop custom reports . c. Click OK to return to the Home page. In the workshops, you will save your custom reports to this folder from Report Builder, and point to the folder to access reports from Epicor ERP.

14




6. Leave the browser running with Report Manager displayed so that you can get back to it easily during the upcoming workshops.

Workshop - Open Report Builder from Report Manager Open the Report Builder authoring tool and verify the connection to the SQL Server report server. 1. Ensure the Report Manager tool is open, as described in the previous workshops. 2. Click Report Builder at the top of the Report Manager Home page.

If you get a Do you Want to Run App message, click Run. Report Builder is downloaded automatically if it is not already available. If a download is initiated, accept any prompts to continue. 3. If a Getting Started page displays, select the Don't show this dialog box at startup check box and then close the dialog box. 4. Click the Report Builder button (upper left corner of window) and then click Options.

The Report Builder Options window displays. 5. In Use this report server or SharePoint site by default , verify that the web service url is set for your SQL Server report server.

In your work environment, the report server URL on this screen must always match the URL that is set for your SQL Server report server in the Reporting Services Configuration Manager. Since you started Report Builder from Report Manager, this URL should be set automatically and should not require adjustment. Important If there is no value in the field or when in doubt about the value shown, open Reporting Services Configuration Manager on the server and select Web Service URL on the left pane. Your Report Builder options must match the URL displayed under Report Server Web Service URL . If you are working from your Epicor ERP client and do not have access to server, contact your administrator to obtain the correct URL.

6. Click OK to close the Report Builder Options window. Important When you edit an existing URL, the change will not be enabled until you exit and restart Report Builder. If you made a change, exit and restart before attempting the next step.

7. To verify that you have a functioning connection to your report server, click the Report Builder button (upper left corner of window) again and click Open.

The Open Report dialog box displays. The web service URL for your report server should be displayed in the Look in field and the folder XXXCustomReports that you created in the previous workshops should be available in the selection pane. If not, click Recent Sites and Servers on the left and then click your report server URL in the selection pane. 8. Click Cancel to close the Open Report dialog box. 9. Exit Report Builder.


15

Developing a Custom SSRS Report - Parts List


Developing a Custom SSRS Report - Parts List In these workshops, you will create a parts list report, save the report on the SSRS report server, and enable access to the report through the menu system in the Epicor ERP application.

Workshop - Create the Parts List Report In this workshop, you will create a report that displays a parts list. You will then deploy the report and add it to the menu system in your Epicor application.

Create the Parts List Report in Report Builder Create the parts list report in Report Builder. 1. Verify that Report Manager is open.

Accessing Report Manager either from the server or from a client machine is explained in the workshops under Accessing SSRS Report Manager and Report Builder. 2. To start Report Builder, click Report Builder at the top of the Report Manager Home page. 3. In Report Builder, click the Report Builder button (upper left corner of window) and then click New.

The New Report or Dataset window displays. 4. Verify that New Report is selected in the left pane and click Table or Matrix Wizard on the right. 5. If you are presented with the Choose a dataset screen, verify that Create a dataset is selected and click Next.

Continue with the next step if this screen does not display. 6. On the Choose a connection to a data source screen, click New.

The Data Source Properties window displays. 7. Do the following on the General screen:

a. In Name, type PartsDS. b. In Select connection type, select Microsoft SQL Server . c. Click Build to open the Connection Properties window. d. In the Server Name drop down menu, select your SQL Server i nstance. e. Under Log on to the server, select Use SQL Server Authentication, enter a valid SQL Server User Name and Password, and select the Save my password check box. f. In the Select or enter a database name drop down menu, select your training database. g. Click Test Connection to verify the connection to the SQL server instance.

16




h. Click OK to return to the Data Source Properties window. 8. For this exercise, no adjustment is required on the Credentials screen so skip selecting it. 9. Click OK to close the Data Source Properties window. 10. Click Next. 11. In the Design a Query screen, expand the folder for your training database, expand Tables, locate the Part table, and expand the table to view its fields. 12. Select the following fields: PartNum, PartDescription, ClassID, TypeCode, and UnitPrice. As they are selected, the fields display in the Selected Fields pane. 13. Click Run Query to verify data retrieval. 14. Click Next. 15. Make the following adjustments in the Arrange fields screen:

a. In the Available Fields pane, select PartNum, PartDescription, ClassID, TypeCode, and drag them to the Row Groups box. b. Again from Available Fields, select UnitPrice and drag it to the Values pane. At least one field is required in this pane. The default Sum function will work for this exercise. c. Click Next. 16. Make the following adjustments in the Choose the layout screen:

a. Clear the Show subtotals and grand totals check box. b. Clear the Expand/Collapse groups check box. c. Click Next. 17. In the Choose a style screen, accept the default or choose a style. 18. Click Finish.

The wizard closes and the table you have created displays in the Report Builder design view. At this point you have a tabular report with defined data source and data set. 19. Above the table, click on Click to add title and type Parts Report. 20. On the Home tab, select Run to preview what the report will look like. 21. Click the Report Builder button (upper left corner of window) and select Save As.

The directory structure of your report server displays. If not, click Recent Sites and Servers on the left and then click your report server URL in the selection pane. 22. Navigate to XXXCustomReports and save the report as MyPartsListReport. 23. Exit Report Builder.


17



24. In Report Manager, navigate to XXXCustomReports and click MyPartsListReport.

The report displays, verifying that you have successfully saved the report to your report server. 25. Go to the next exercise.

Add the Parts List Report to the Epicor Application Add your report to the menu system in your Epicor application. 1. Start the Epicor ERP application.

If prompted, enter manager for both user name and password. 2. Open the Menu Maintenance program. Menu Path: System Setup > Security Maintenance > Menu Maintenance Important This program is not available in the Epicor Web Access.

3. On the Menu Maintenance tree, navigate to: Main Menu > Material Management > Inventory Management > Reports . Verify Reports is highlighted. 4. Select File > New > New Menu. 5. In the Menu ID field, enter UDXXX01 (where XXX are your initials). Important It is an important standard practice to use a UD prefix when adding a menu ID. UD stands for User Defined.

6. In the Name field, enter XXX My Parts List Report (where XXX are your initials). 7. In the Order Sequence field, enter 997. 8. Click the Program Type drop-down menu and select SSRS Custom Report Link. 9. In the Report field, enter \XXXCustomReports\MyPartsListReport (where XXX are your initials).

The Epicor installation is configured to connect to the SQL Server reporting server via the reporting server's URL. Make sure that the report path and report name you enter here match the folder structure and name that you have set up on the report server. 10. Click Save and click OK if you get a security update message. 11. Log out and log back into the Epicor application.

This ensures you do not have to wait for an automatic update. 12. Open the XXX My Parts List Report report program window (where XXX are your initials). Menu Path: Material Management > Inventory Management > Reports > XXX My Parts List Report 13. Now define how you want the report to display in the print preview. To do this, click the Output Format drop-down list and select the PDF option.

18




Tip Notice you can preview the report through several options, including Excel, Word, XML, and EMF. If you select the EMF option, you display the report using the Altec Advanced Print Management ™ tool.

14. To display the report, click the Print Preview icon. The report displays in Adobe Reader ™. 15. Close the print preview and report program windows. You can either exit the application or leave it running.

Workshop - Add Parameters to the Parts List Report In this workshop, you will create and test a parameters for your report that filters the report data based on minimum and maximum values for unit price. Parameters can be added to your custom report as a filter option to show/hide data before printing. The main thing to remember when adding a parameter is to be aware of the data you are filtering. The types of data you can filter when adding a parameter consists of text, boolean (true/false), date/time, integer and float. In this workshop, you will be working in the parts list report you just created.

Create Parameters for the Parts List Report Create two parameters for filtering the report on minimum and maximum values. 1. In Report Manager, navigate to XXXCustomReports, hover your mouse over MyPartsListReport, click the arrow on the right, and then choose Edit in Report Builder.

Report Builder opens with your report displayed. 2. To create the minimum value parameter, right-click on Parameters in the Report Data pane and select Add Parameter. The Report Parameter Properties window displays. 3. Select General and make the following adjustments:

a. In the Name field, enter UnitPriceFilter1. This name will not be visible on the report, just visible under the Report Data pane. b. In the Prompt field, enter Unit Price Sort Minimum. This name will be visible to the report user. c. Click the Data Type drop-down menu and click Integer. 4. Select Default Values and make the following adjustments:

a. Select Specify Values. b. Click Add and replace [Null] with 20. 5. Click OK. You can now see your parameter in the Parameters tree in the Report Data pane.


19



6. To create the maximum value parameter, right-click Parameters in the Report Data pane and select Add Parameter. 7. Select General and make the following adjustments:

a. In the Name field, enter UnitPriceFilter2. b. In the Prompt field, enter Unit Price Sort Maximum. c. Click the Data Type drop-down menu and click Integer. 8. Select Default Values and make the following adjustments:

a. Select Specify Values. b. Click Add and replace [Null] with 400. 9. Click OK. You can now see your parameter in the Parameters tree in the Report Data pane. 10. Click the Report Builder button and select Save. 11. Remain in Report Builder with MyPartsListReport displayed and go to the next exercise.

Create Parameter Filters for the Parts List Report Create the filters for your new parameters. 1. In Report Builder, verify that you are on the Home tab and MyPartsListReport is displayed. 2. In the Report Data pane, expand Datasets, right-click DataSet1 and select Dataset Properties. 3. Select Filters. 4. Click the Add button. 5. Click the Expression drop-down menu and select [UnitPrice]. 6. Click the Operator drop-down menu and select >=. 7. In the Value field, click the

fx button.

8. In Category, select Parameters. 9. In Values, double-click UnitPriceFilter1. 10. Click OK. 11. Back in the Dataset Properties dialog box, click Add. 12. Verify your cursor is in the new filter you just created, click the Expression drop-down menu and select [UnitPrice]. 13. Click the Operator drop-down menu and select <=.

20



14. In the Value field, click the


fx button.

15. In Category, select Parameters. 16. In Values, double-click UnitPriceFilter2. 17. Click OK. 18. Click OK again. 19. Click the Report Builder button and select Save. 20. Remain in Report Builder with MyPartsListReport displayed and go to the next exercise.

Test the Parts List Report with the New Parameters Generate the report and test the parameters you just created. 1. In Report Builder, verify that you are on the Home tab and MyPartsListReport is displayed. 2. On the Home tab, click Run.

The report displays with your new parameters at the top. The report displays all data greater than or equal to 20 unit price, and less than or equal to 400 unit price, matching the default values that you included in the parameter setup. 3. You can experiment with entering other values into the Unit Sort Price Minimum and Unit Sort Price Maximum fields, clicking View Report to update the displayed data based on the new values. 4. On the Run tab, click Design and remain in Report Builder with the MyPartsListReport report displayed for the next exercise.

Workshop - Apply Conditional Formatting to the Parts List Report In this workshop, you will learn to apply conditional formatting to the parts list report, first creating an expression that applies color to every other row, and then creating an expression that applies color based on the current value in a column. Important

Add Alternating Row Coloring to the Parts List Report Apply alternate row coloring to your report. 1. In Report Builder, verify that you are on the Home tab and MyPartsListReport is displayed. 2. On the ribbon, click the View tab and select the Properties check box.

The Properties pane, which is hidden by default, displays on the right-hand side. This pane is a good resource for quickly accessing and editing the properties of your current selection in the design area.


21



3. Click on the report table (the header and placeholder rows) so the gray bars appear. 4. On the left side of the table (in the gray fields), click the gray box in the first line of data to highlight the entire row. 5. Go to the Properties pane on the right hand side. 6. Navigate to Fill > BackgroundColor. 7. Click the BackgroundColor drop-down menu and select Expression. 8. In the Set Expression for: BackgroundColor field, enter the following: =iif(RowNumber(Nothing) Mod 2, "Silver", "White")

9. Click OK. 10. Click the Report Builder button and select Save. 11. Click the Home tab and then click Run to view your results.

Notice every other row is colored gray. This format could provide contrast that could help the user when reading the report. 12. Click Design to return to the Home tab. 13. Remain in Report Builder with the MyPartsListReport report displayed for the next exercise.

Add Value-Based Row Coloring to the Parts List Report Apply color to report rows based on the current value in a column. 1. In Report Builder, verify that you are on the Home tab and MyPartsListReport is displayed. 2. Click on the report table so the gray bars appear. 3. On the left side of the table (in the gray fields), click the left gray box in the first line of data to highlight the entire row. 4. Go to the Properties pane on the right hand side. 5. Scroll until you find Fill > BackgroundColor. 6. Click the drop-down list and select Expression. 7. Clear the existing code from the previous exercise. 8. In the Set Expression for: BackgroundColor field, enter the following: =iif(Fields!TypeCode.Value = "M", "Yellow", iif(Fields!TypeCode.Value = "P", "Turquoise", "White"))

9. Click OK. 10. Click the Report Builder button and select Save.

22




11. Click the Home tab and then click Run to view your results.

Notice that all Type Code values labeled P are turquoise, M are yellow, and everything else is white. 12. Click Design to return to the Home tab. 13. Remain in Report Builder with the MyPartsListReport report displayed for the next exercise.

Workshop - Add a Calculated Field to the Parts List Report In this workshop, you will create a new calculated field that multiplies the Uni t Price by an exchange rate, and you will add a new column to the report to display the calculated foreign currency values. When creating reports, users may want information that is not directly available from the database. It often is possible to make the required information available by adding calculated fields to the report.

Create a Calculated Field in the Parts List Report Add a calculated field to your report. 1. In Report Builder, verify that you are on the Home tab and MyPartsListReport is displayed. 2. Right-click on DataSet1 and select Add Calculated Field.

The Dataset Properties dialog box opens with Fields selected. 3. Working with the new blank row at the bottom of the list, enter UnitPriceForeign in the Field Name column. 4. In the Field Source column, click the

fx button.

5. In the Fields (DataSet1) category, double-click UnitPrice. 6. Enter a space after the new string. 7. Expand Operators, select Arithmetic, and double-click the * multiplication symbol. 8. Enter another space. 9. Enter an exchange rate of 0.74. 10. Click OK. 11. Click OK again to close the Dataset Properties dialog box. 12. Click the Report Builder button and select Save. 13. Remain in Report Builder with MyPartsListReport displayed and go to the next exercise.


23



Add a Column for Calculated Value to the Parts List Report Add a report column to hold your new calculation. 1. In Report Builder, verify that you are on the Home tab and MyPartsListReport is displayed. 2. Click on the report table so the gray bars appear. 3. Right-click on the gray box above the UnitPrice column. 4. Navigate to Insert Column and select Right. 5. Hover over the bottom row of the new column. 6. Click the icon that displays on the right side of the field to view the field chooser. 7. Select UnitPriceForeign.

The new column with calculated value i s now part of the report. 8. Adjust the Unit Price and Unit Price Foreign column placeholders to display values to two decimal places:

a. In the Unit Price column, select the placeholder (text) in the second row, right click and choose Text Box Properties. b. In the Text Box Properties dialog box, choose Number on the left. c. In the Category list, choose Number and accept the default configuration of 12345.00. d. Click OK to close the dialog box. In the Unit Price Foreign column, repeat steps a through c. 9. Click Run.

Notice the new column with a conversion calculation displayed for each part. 10. Click the Report Builder button and select Save. 11. Exit Report Builder.

Test the Finished Parts List Report in ERP Optionally, take a few minutes to view the parts list report with its added features in the Epicor ERP application. 1. If you are not already logged in, start the Epicor ERP application.

If prompted, enter manager for both user name and password. 2. Open the XXX My Parts List Report report program window (where XXX are your initials). Menu Path: Material Management > Inventory Management > Reports > XXX My Parts List Report 3. To display the report, click the Print Preview icon. 4. Note that the report includes the column with calculated field.

24




5. Experiment with changing the parameter values at the top of the window and clicking View Report. Important For parameters to display in the report, the Show Report Parameters option must be enabled in the Epicor SQL Report Monitor configuration.

6. Close the print preview and report program windows. You can leave the application running for later exercises.


25

Developing a Custom SSRS Report - Customer Information with Embedded Subreports


Developing a Custom SSRS Report - Customer Information with Embedded Subreports In these workshops, you create an SSRS report that combines three separate reports to display a single report. The first is the main report that displays general information about a user-selected customer. The second and third reports are subreports that are embedded in the main report. Based on values passed from the main report into subreport parameters, the subreports display detailed information about the selected customer's call activity and open orders. Important The reports in this workshop are based on the availability of records for four customers in the EPIC06 Epicor Education company. If you are not working in the EPIC06 company and demonstration database, you will need to ensure that you have valid customer, call, and order records to support creating and viewing the reports.

Workshop - Create Supporting Epicor CRM Call Records In this workshop, add an Epicor CRM call record to each of the Epicor customers that are used in the upcoming workshops. Creating these records ensures that data is available to populate the Call Activity subreport. 1. To open the Epicor application, double-click Epicor on your desktop.

If prompted, enter manager for both user name and password. 2. Open the Call Log program. Menu Path: Sales Management > Customer Relationship Management > General Operations > CRM Call Entry Tip The CRM menu path is: Customer Relationship Management > Sales and Marketing Management > General Operations > CRM Call Entry

3. Create a new call for Addison, Inc.:

a. From the File menu, select New > New Call. b. On the Detail sheet, under Call: •

For Description, type Weekly follow-up.

•

For Text, type Discussed deliveries and near-term plans.

•

For Call Type, choose Call - Sales.

•

For Owner, choose Brian Howard.

c. On the Contacts sub-sheet, type ADDISON in the Customer field and press Tab. d. From the File menu, select Save. 4. Create a new call for Barriston Engineering:

a. From the File menu, select New > New Call. 26




b. On the Detail sheet, under Call: •

For Description, type Participants list.

•

For Text, type Sent background info on service analysts who will be at conference.

•

For Call Type, choose E-Mail.

•

For Owner, choose Chuck Williams.

c. On the Contacts sub-sheet, type BARRISTON in the Customer field and press Tab. d. From the File menu, select Save. 5. Create a new call for Clarke Construction Co:


For Description, type Credit increase.

•

For Text, type Let accounting know that line-of-credit increase has been approved.

•

For Call Type, choose Call - Credit/Collection.

•

For Owner, choose Frank Wright.

c. On the Contacts sub-sheet, type CLARKE in the Customer field and press Tab. d. From the File menu, select Save. 6. Create a new call for Dalton Manufacturing:


For Description, type Demo appointment.

•

For Text, type Made appointment to demo new control options.

•

For Call Type, choose Call - Sales.

•

For Owner, choose Beverly Joseph.

c. On the Contacts sub-sheet, type DALTON in the Customer field and press Tab. d. From the File menu, select Save. 7. Close the Call Log program window. You can either exit the Epicor application or leave it running for later exercises.


27



Workshop - Create the Customer Main Report In this workshop, you will work in Report Builder to create the main customer report, add report parameters, and adjust format.

Create the Customer Main Report in Report Builder Working in Report Builder, create the main report . 1. Verify that Report Manager is open.

Accessing Report Manager either from the server or from a client machine is explained in the workshops under Accessing SSRS Report Manager and Report Builder. 2. To start Report Builder, click Report Builder at the top of the Report Manager Home page. 3. In Report Builder, ensure that the Properties pane is displayed on the right hand side of the window.

If not, go to the View tab, select Properties, and then go back to the Home tab. 4. Click the Report Builder button (upper left corner of window) and then click New.




a. In Name, enter Customer. b. In Select connection type, select Microsoft SQL Server . c. Click Build to open the Connection Properties window. d. In the Server Name drop down menu, select your SQL Server i nstance. e. Under Log on to the server, select Use SQL Server Authentication, enter a valid SQL Server User Name and Password, and select the Save my password check box. f. In the Select or enter a database name drop down menu, select your training database. g. Click Test Connection to verify the connection to the SQL server instance. h. Click OK to return to the Data Source Properties window. 9. For this exercise, no adjustment is required on the Credentials screen so skip selecting it.

28




10. Click OK to close the Data Source Properties window. 11. Click Next. 12. In the Design a Query screen, expand the folder for your training database, expand Tables, locate the Customer table, and expand the table to view its fields. 13. Select the following fields: Company, CustID, CustNum, Name, Email, Address1, City, State, and Zip. As they are selected, the fields display in the Selected Fields pane. 14. Click Run Query to verify data retrieval. 15. Click Next. 16. Make the following adjustments in the Arrange fields screen:

a. In the Available Fields pane, select Company, CustID, Name, Email, Address1, City, State, and Zip, and then drag them to the Row Groups box. The order of these fields can be adjusted in the Row Groups box to determine the initial left-to-right ordering of the report columns. For this exercise, the order is ok as-is and does not need adjustment. b. Again from Available Fields, select CustNum and drag it to the Values pane. At least one field is required in this pane. The default Sum function will work for this exercise. c. Click Next. 17. Make the following adjustments in the Choose the layout screen:


The wizard closes and the report displays on the Report Bui lder Home tab. At this point you have a tabular report with defined data source and data set. 20. In the Report Data pane, adjust the name that has been assigned automatically to the new dataset:

a. Under Datasets, right-click the new dataset (the default name is DataSet1) and select Dataset Properties. b. Select Query and change the value in Name to CustomerDS. c. Click OK to close the Dataset Properties dialog box. 21. Verify that the report table is updated to use the new dataset name:

a. Click the table to display the grey bars. b. Right-click the top left corner of the grey area and choose Tablix Properties.


29



c. For Dataset name, verify that CustomerDS is selected and click OK to close the dialog box. 22. Click Run to verify that the report displays without error.

At this point, the report is retrieving an unfiltered list of matching records in the data source . 23. Click Design to return to the Home tab. 24. Click the Report Builder button and select Save As.

The directory structure of your report server displays. If not, click Recent Sites and Servers on the left and then click your report server URL in the selection pane. 25. Navigate to XXXCustomReports and save the report as CustomerInfo. 26. Remain in Report Builder with the CustomerInfo report displayed and go to the next exercise.

Add Parameters to the Customer Main Report Add parameters and supporting dataset filters that enable the report user to select the Epicor company and customer on which the report is based. 1. In Report Builder, verify that you are on the Home tab and the CustomerInfo report is displayed. 2. To create the Company parameter, do the following:

a. Right-click Parameters in the Report Data pane and select Add Parameter. b. In the Report Parameter Properties dialog box, select General and make the following adjustments: •

For Name, enter Company.

•

For Prompt, enter Epicor Company ID.

•

For Data Type, verify that Text is selected.

c. Select Default Values and make the following adjustments: •

Select Specify Values.

•

Click Add and replace [Null] with EPIC03.

Setting default values will save you having to repeatedly enter values as you create and test the reports in this workshop. d. Click OK. 3. To create the CustID parameter, do the following:

a. Right-click Parameters in the Report Data pane and select Add Parameter. b. Select General and do the following:

30

•

For Name, enter CustID.

•

For Prompt, enter Customer ID.

•





c. Select Available Values and do the following: •

Select Specify values.

•

Click Add and, for both Label and Value, enter ADDISON .

•

Click Add and, for both Label and Value, enter BARRISTON.

•

Click Add and, for both Label and Value, enter CLARKE.

•

Click Add and, for both Label and Value, enter DALTON.

d. Select Default Values and do the following: •

Select Specify values.

•

Click Add and replace [Null] with DALTON.

e. Click OK. 4. Click the Report Builder button and select Save. 5. To create the dataset filters for the new parameters, do the following:

a. In the Report Data pane, right-click CustomerDS under Datasets and select Dataset Properties. b. Select Filters. c. Click Add. d. For Expression, click the drop-down menu and select [Company]. e. For Operator, verify that = is selected. f. For Value, click the fx button and make the following adjustments in the Expression dialog box: •

In Category, select Parameters.

•

In Values, double-click Company.

•

Click OK to close the Expression dialog box.

g. Back at the Dataset Properties window, verify that you are still on Filters and click Add. h. For Expression, click the drop-down menu and select [CustID]. i. For Operator, verify that = is selected. j. In the Value field, click the box:

fx button

and make the following adjustments in the Expression dialog

•


•

In Values, double-click CustID.

•


k. Click OK to close the Dataset Properties dialog box. 6. Click the Report Builder button and select Save. 7. Click Run to test the report.


31



The report is generated based on the default parameter values. You may see some formatting issues. Format adjustments are the subject of the next exercise. 8. Optionally, to display a different customer, select it from the Customer ID drop-down menu and click View Report. To change the company, manually replace the default value. 9. Click Design to return to the Home tab. 10. Remain in Report Builder with the CustomerInfo report displayed and go to the next exercise.

Adjust the Customer Main Report Format Adjust format details on the new customer main report. 1. In Report Builder, verify that you are on the Home tab and the CustomerInfo report is displayed. 2. Hover over the bottom edge of the report body (initially aligned with the table bottom) to display the drag icon then drag the report body bottom edge down to about 6 inches, as indicated on the vertical ruler.

This will give you some room when you later add the subreports. 3. Make the following adjustments at the top of the report: •

Type Customer in the report title text box at the top of the report. Remove some of the unneeded width from the text box by dragging the right side of the text box to the left.

•

Select the [&ExecutionTime] text box that was added at the bottom of the report by default when the report was created. Drag the text box and place it just to the right of the report title text box.

4. Adjust the column headings. In the header row, select and change heading text as follows: •

CustID to Customer ID

•

Name to Customer

•

Address1 to Address

5. Configure the CustNum column to be hidden in the generated report.

The value in CustNum is needed for generating the subreports but i t does not need to be displayed. Do the following: a. Click the table to display the grey bars. b. Right-click the bar above the CustNum column and select Column Visibility. c. In the Column Visibility dialog box, choose Hide and click OK. 6. Click Run to test the report.

The CustNum column does not display. Also the report body height adjustment you made is not seen because you have not yet added anything to that area. Look for column headers that wrap due to the name changes you just made. 7. Click Design to return to the Home tab. 8. If needed to correct line wrap in column headers, select columns and drag the sides to adjust their width.

32




Retest and readjust as needed. 9. Optionally, do the following to familiarize yourself with the properties for controlling table cell automatic sizing:

a. Right click any cell and choose text box properties to display the Text Box Properties dialog box (cells are text box objects by default). b. In the General properties, under Sizing Options, note that Allow height to increase is selected by default. This property, which controls the element in the report's .rdl file, enables the text box to grow vertically to display its entire contents when the report is viewed. The companion Allow height to decrease option controls the property. These properties also can be seen and adjusted in the Properties pane when you have a text box selected. c. Click Cancel to close the window without making any changes. 10. Click the Report Builder button and select Save. 11. Remain in Report Builder with the CustomerInfo report displayed and go to the next exercise.

Workshop - Create the Call Activity Subreport In this workshop, you will work in Report Builder to create a report that displays Epicor CRM call details for the customer currently displayed in the main report. In a later workshop, this report will be embedded as a subreport in the main report.

Create the Call Activity Subreport in Report Builder Working in Report Builder, create the report that later will become the call activity subreport. 1. Verify that you are in Report Builder.

Assuming you are doing the workshops in sequence, you are coming from the last exercise in the main report workshop and the CustomerInfo main report is still displayed. 2. Click the Report Builder button and then click New.

When you open a report or begin creating a new report in Report Builder, any currently opened report is closed automatically. If the current report has not been saved you will be prompted to save. The New Report or Dataset window displays. 3. Verify that New Report is selected in the left pane and click Table or Matrix Wizard on the right. 4. If you are presented with the Choose a dataset screen, verify that Create a dataset is selected and click Next.


The Data Source Properties window displays.


33



6. Do the following on the General screen:

a. In Name, enter CRMActivity. b. In Select connection type, select Microsoft SQL Server . c. Click Build to open the Connection Properties window. d. In the Server Name drop down menu, select your SQL Server i nstance. e. Under Log on to the server, select Use SQL Server Authentication, enter a valid SQL Server User Name and Password, and select the Save my password check box. f. In the Select or enter a database name drop down menu, select your training database. g. Click Test Connection to verify the connection to the SQL server instance. h. Click OK to return to the Data Source Properties window. 7. For this exercise, no adjustment is required on the Credentials screen so skip selecting it. 8. Click OK to close the Data Source Properties window. 9. Click Next. 10. In the Design a Query screen, expand the folder for your training database, expand Tables, locate the CRMCall table, and expand the table to view its fields. 11. Select the following fields: Company, CallDesc, CallText, SalesRepCode, CallTypeCode,CallCustNum, and LastDate. As they are selected, the fields display in the Selected Fields pane. 12. Click Run Query to verify data retrieval. 13. Click Next. 14. Make the following adjustments in the Arrange fields screen:

a. In the Available Fields pane, select CallDesc, CallText, SalesRepCode, CallTypeCode, and LastDate, and then drag them to the Row Groups box. The order of these fields can be adjusted in the Row Groups box to determine the initial left-to-right ordering of the report columns. For this exercise, the order is ok as-is and does not need adjustment. b. Again from Available Fields, select Company, and CallCustNum and drag them to the Values pane. At least one field is required in this pane. In the main-sub report relationship, these fields will support passing of report parameters and will be hidden from view in a later exercise. Putting them under Values keeps them independent of row groups and makes it easier to hide them. The default functions that these fields assume will work for this exercise. c. Click Next. 15. Make the following adjustments in the Choose the layout screen:

a. Clear the Show subtotals and grand totals check box. b. Clear the Expand/Collapse groups check box. c. Click Next. 34




16. In the Choose a style screen, accept the default or choose a style. 17. Click Finish.


a. Under Datasets, right-click the new dataset (the default name is DataSet1) and select Dataset Properties. b. Select Query and change the value in Name to CRMActivityDS. c. Click OK to close the Dataset Properties dialog box. 19. Click Run to verify that the report displays without error.

At this point, the report is retrieving an unfiltered list of matching records in the data source . 20. Click Design to return to the Home tab. 21. Click the Report Builder button and select Save As.

The directory structure of your report server displays. If not, click Recent Sites and Servers on the left and then click your report server URL in the selection pane. 22. Navigate to XXXCustomReports and save the report as CRMActivity. 23. Remain in Report Builder with the CRMActivity report displayed and go to the next exercise.

Add Parameters and Filters to the Call Activity Subreport Add parameters and supporting dataset filters that will receive, from the main report, the company and customer number on which the subreport data display is based. 1. In Report Builder, verify that you are on the Home tab and the CRMActivity report is displayed. 2. To create the parameters, do the following:

a. Right-click Parameters in the Report Data pane and select Add Parameter. b. Set up the customer parameter. In the Report Parameter Properties dialog box, select General and make the following adjustments: •

For Name, enter CRMActivityCompany.

•


•


•

For Select parameter visibility, select Hidden.

c. Click OK. d. Right-click Parameters in the Report Data pane and select Add Parameter.


35



e. Set up the customer number parameter, Select General and do the following: •

For Name, enter CRMActivityCustNum.

•

For Prompt, enter Customer Number.

•

For Data Type, verify that Integer is selected.

•

For Select parameter visibility, select Hidden.

f. Click OK. The parameters are set to Hidden because passing of parameters from the main report into the subreport is automatic and the parameters do not need to be displayed. In hidden mode, there is no prompting for user input and it will not be possible to display the report on its own as there are no default parameter values. When the report is added as a subreport to the main report in a later exercise, you will configure the parameter relationships between the main report and subreport. Note that the values you set for Prompt are there only to support making the parameters visible for test purposes. This is demonstrated later in the exercise when you switch the parameters to visible so that you can test the report. 3. Click the Report Builder button and select Save. 4. To create the dataset filters for the new parameters, do the following:

a. In the Report Data pane, right-click CRMActivityDS under Datasets and select Dataset Properties. b. Select Filters. c. Click Add. d. For Expression, click the drop-down menu and select [Company]. e. For Operator, verify that = is selected. f. For Value, click the fx button and make the following adjustments in the Expression dialog box: •


•

In Values, double-click CRMActivityCompany.

•


g. Back at the Dataset Properties window, verify that you are still on Filters and click Add. h. For Expression, click the drop-down menu and select [CallCustNum]. i. For Operator, verify that = is selected. j. In the Value field, click the box:

fx button


•


•

In Values, double-click CRMActivityCustNum.

•


k. Click OK to close the Dataset Properties dialog box. 5. Click the Report Builder button and select Save.

36




6. So that the report can be tested for this and the next exercise, go back to the parameter properties and switch them to visible.

a. Right-click CRMActivityCompany in the Report Data pane and select Parameter Properties . b. In the Report Parameter Properties dialog box, verify that General is selected and select Visible under Select parameter visibility. c. Click OK. d. Repeat steps a-c for the CRMActivityCustNum parameter. 7. Click the Report Builder button and select Save. 8. Test the report:

a. Click Run. b. For Epicor Company ID enter EPIC06 (the Epicor Education company) c. For Customer Number, enter 9 (the customer number for Dalton) d. Click View Report. The report displays with the CRM call record that you created for the customer Dalton in an earlier exercise. In upcoming exercises, the parameters will be switched back to hidden and the subreport will be embedded in the main report. As noted in step 2, the hidden parameters will have values passed to them automatically from the main report. 9. Click Design to return to the Home tab. 10. Remain in Report Builder with the CRMActivity report displayed and go to the next exercise.

Adjust the Call Activity Subreport Format Adjust format details on the new CRM call activity subreport. 1. In Report Builder, verify that you are on the Home tab and the CRMActivity report is displayed. 2. Select and delete the [&ExecutionTime] text box that was added by default when the report was created. 3. Make the following adjustments to the report title:

a. Select the report title text box (not the text), right-click and choose Text Box Properties. b. In the Text Box Properties window, select Font, select 14pt under Size, and click OK. c. Select the title text and replace with Call Activity. 4. Configure the Company and CallCustNum columns to be hidden in the generated report.

These columns support the report parameters but do not need to be displayed. Do the following: a. Click the table to display the grey bars. b. Right-click the bar above the Company column and select Column Visibility.


37



c. In the Column Visibility window, choose Hide and click OK. d. Repeat steps a-c for the CallCustNum column. 5. Adjust the column headings. In the header row, select and change heading text as follows: •

CallDesc to Subject

•

CallText to Summary

•

SalesRepCode to Sales Rep

•

CallTypeCode to Call Type

•

LastDate to Date

6. Verify the format in which call dates will display:

a. In the Date column, select [LastDate], right-click, and select Placeholder Properties. b. In the Placeholder Properties dialog box, select Number, select Date, and select *1/31/2000. c. Click OK. 7. Working in the grey bar at the top of the table, drag the right side of the Summary column to provide more room for several sentences of written comments, which is typical for the data in this field.

Remember that the Company and CallCustNum columns will be hidden in the generated report, making more room for the wider column. 8. Test the report:

a. Click Run. b. For Epicor Company ID enter EPIC06 (the Epicor Education company) c. For Customer Number, enter 9 (the customer number for Dalton) d. Click View Report. The report displays with your format adjustments. 9. Click Design to return to the Home tab. 10. You are now done testing this subreport, so go back to the parameter properties and switch them to hidden.

a. Right-click the CRMActivityCompany parameter in the Report Data pane and select Parameter Properties. b. In the Report Parameter Properties dialog box, verify that General is selected and select Hidden under Select parameter visibility. c. Click OK. d. Repeat steps a-c for the CRMActivityCustNum parameter. 11. Click the Report Builder button and select Save. 12. Remain in Report Builder with the CRMActivity report displayed and go to the next exercise.

38




Workshop - Create the Open Orders Subreport In this workshop, you will work in Report Builder to create a report that displays open order for the customer currently displayed in the main report. In a later workshop, this report will be embedded as a subreport in the main report.

Create the Open Orders Subreport in Report Builder Working in Report Builder, create the report that later will become the open orders subreport. 1. Verify that you are in Report Builder.

Assuming you are doing the workshops in sequence, you are coming from the last exercise in the call activity report workshop and the CRMActivity report is still displayed. 2. Click the Report Builder button and then click New.




a. In Name, type OpenOrder. b. In Select connection type, select Microsoft SQL Server . c. Click Build to open the Connection Properties window. d. In the Server Name drop down menu, select your SQL Server i nstance. e. Under Log on to the server, select Use SQL Server Authentication, enter a valid SQL Server User Name and Password, and select the Save my password check box. f. In the Select or enter a database name drop down menu, select your training database. g. Click Test Connection to verify the connection to the SQL server instance. h. Click OK to return to the Data Source Properties window. 7. For this exercise, no adjustment is required on the Credentials screen so skip selecting it. 8. Click OK to close the Data Source Properties window. 9. Click Next.


39



10. In the Design a Query screen, expand the folder for your training database, expand Tables, locate the OrderHed table, and expand the table to view its fields. 11. Select the following fields: OpenOrder, Company, CustNum, OrderDate, TotalLines, and OrderAmt. As they are selected, the fields display in the Selected Fields pane. 12. Click Run Query to verify data retrieval. 13. Click Next. 14. Make the following adjustments in the Arrange fields screen:

a. In the Available Fields pane, select OrderDate, TotalLines, and OrderAmt, and then drag them to the Row Groups box. For this exercise, the list ordering is ok as-is and does not need adjustment. b. Again from Available Fields, select OpenOrder, Company, and CustNum, and then drag them to the Values pane. At least one field is required in this pane. In the main-sub report relationship, these fields will support data filtering and passing of report parameters, and they will be hidden from view in a later exercise. Putting them under Values keeps them independent of row groups and makes it easier to hide them. The default functions that these fields assume will work for this exercise. c. Click Next. 15. Make the following adjustments in the Choose the layout screen:



a. Under Datasets, right-click the new dataset (the default name is DataSet1) and select Dataset Properties. b. Select Query and change the value in Name to OpenOrderDS. c. Click OK to close the Dataset Properties dialog box. 19. Click Run to verify that the report displays without error.

At this point, the report is retrieving an unfiltered list of matching records in the data source . 20. Click Design to return to the Home tab.

40




21. Click the Report Builder button and select Save As.

The directory structure of your report server displays. If not, click Recent Sites and Servers on the left and then click your report server URL in the selection pane. 22. Navigate to XXXCustomReports and save the report as OpenOrder. 23. Remain in Report Builder with the OpenOrder report displayed and go to the next exercise.

Add Parameters and Filters to the Open Orders Subreport Add parameters and supporting dataset filters that will receive, from the main report, the company and customer number on which the subreport data display is based. Add a data filter that will limit the report to open orders. 1. In Report Builder, verify that you are on the Home tab and the OpenOrder report is displayed. 2. To create the parameters:

a. Right-click Parameters in the Report Data pane and select Add Parameter. b. Set up the customer parameter. In the Report Parameter Properties dialog box, select General and make the following adjustments: •

For Name, enter OpenOrderCompany.

•


•


•

For Select parameter visibility, leave Visible selected. As with the other subreport, having the parameters visible during development allows you to test the report.

c. Click OK. d. Right-click Parameters in the Report Data pane and select Add Parameter. e. Set up the customer number parameter, Select General and do the following: •

For Name, enter CRMActivityCustNum.

•

For Prompt, enter Customer Number.

•

For Data Type, verify that Integer is selected.

•

For Select parameter visibility, leave Visible. Again, this is to allow testing.

f. Click OK. 3. Click the Report Builder button and select Save. 4. To create the dataset filters needed for this report, do the following:

a. In the Report Data pane, right-click OpenOrderDS under Datasets and select Dataset Properties. b. Select Filters. c. Click Add. d. For Expression, click the drop-down menu and select [Company]. e. For Operator, verify that = is selected.


41



f. For Value, click the fx button and make the following adjustments in the Expression dialog box: •


•

In Values, double-click OpenOrderCompany.

•


g. Back at the Dataset Properties window, verify that you are still on Filters and click Add. h. For Expression, click the drop-down menu and select [CustNum]. i. For Operator, verify that = is selected. j. In the Value field, click the box:

fx button


•


•

In Values, double-click OpenOrderCustNum.

•


k. Back at the Dataset Properties window, verify that you are still on Filters and click Add. l. For Expression, click the drop-down menu and select [OpenOrder]. m. For Operator, verify that = is selected. n. In the Value field, enter true. o. Click OK to close the Dataset Properties dialog box. 5. Click the Report Builder button and select Save. 6. Test the report:

a. Click Run. b. For Epicor Company ID enter EPIC06 (the Epicor Education company) c. For Customer Number, enter 9 (the customer number for Dalton) d. Click View Report. The report displays with the open orders for the customer Dalton. As with the other subreport, in upcoming exercises, the parameters will be switched to hidden and the subreport will be embedded in the main report. Values will be passed to the hidden parameters automatically from the main report. Since the data filter on OpenOrder has no associated parameter, it will always li mit the report to orders with an open status. 7. Click Design to return to the Home tab. 8. Remain in Report Builder with the OpenOrder report displayed and go to the next exercise.

Adjust the Open Orders Subreport Format Adjust format details on the new open orders subreport. 1. In Report Builder, verify that you are on the Home tab and the OpenOrder report is displayed.

42




2. Select and delete the [&ExecutionTime] text box that was added by default when the report was created. 3. Make the following adjustments to the report title:

a. Select the report title text box (not the text), right-click and choose Text Box Properties. b. In the Text Box Properties window, select Font, select 14pt under Size, and click OK. c. Select the title text and replace with Open Orders. 4. Configure the OpenOrder, Company and CustNum columns to be hidden in the generated report.

These columns support the report parameters and filtering but do not need to be displayed. Do the following: a. Click the table to display the grey bars. b. Right-click the bar above the OpenOrder column and select Column Visibility. c. In the Column Visibility window, choose Hide and click OK. d. Repeat steps a-c for the Company and CallCustNum columns. 5. Adjust the column headings. In the header row, select and change heading text as follows: •

OrderDate to Date

•

TotalLines to Total Lines

•

OrderAmt to Amount (Adjust the column width to show the heading on one line.)

6. Adjust the format in which order dates will display:

a. In the Date column, select [OrderDate], right-click, and select Placeholder Properties. b. In the Placeholder Properties dialog box, select Number, select Date, and select *1/31/2000. c. Click OK. 7. Test the report:

a. Click Run. b. For Epicor Company ID enter EPIC06 (the Epicor Education company) c. For Customer Number, enter 9 (the customer number for Dalton) d. Click View Report. The report displays with your format adjustments. 8. Click Design to return to the Home tab. 9. You are now done testing this subreport, so go back to the parameter properties and switch them to hidden.

a. Right-click the OpenOrderCompany parameter in the Report Data pane and select Parameter Properties. b. In the Report Parameter Properties dialog box, verify that General is selected and select Hidden under Select parameter visibility.


43



c. Click OK. d. Repeat steps a-c for the OpenOrderCustNum parameter. 10. Click the Report Builder button and select Save. 11. Remain in Report Builder with the OpenOrder report displayed and go to the next exercise.

Workshop - Embed the Subreports in the Main Report In this workshop, embed the subreports and configure the report parameters that will enable passing, from the main report, values on which the data displayed in the subreports is based. 1. Verify that you are in Report Builder.

Assuming you are doing the workshops in sequence, you are coming from the last exercise in the open orders subreport workshop and the OpenOrder report is still displayed. 2. Click the Report Builder button and then click Open.

The Open Report dialog box displays. 3. Navigate to the XXXCustomReports folder and open the CustomerInfo report.

The CustomerInfo report displays on the Home tab. 4. Embed the call activity report:

a. Select the Insert tab, click Subreport, and then click in the report, below the report table. A new subreport placeholder displays. b. Ensure that the subreport placeholder is selected, hover to enable the move icon, and drag the subreport placeholder to the left edge of the report body and slightly below the table. 5. Right-click the subreport placeholder and select Subreport Properties. 6. In the Subreport Properties window, ensure that General is selected and do the following:

a. In the Name field, type CallActivitySubreport. b. In the Use this report as a subreport field, click Browse, and then navigate to and select your CRMActivity report. 7. Select Parameters and do the following to configure the subreport parameters that are the links for passing values from the main report into the subreport:

a. Click Add. b. For Name, select CRMActivityCompany (the subreport parameter). c. For Value, from the drop down list, select =First(Fields!Company.Value, "CustomerDS") (the main report field holding the required value).

44




In all of the parameter value selections in this exercise, the listed expressions are based on the fields in the report dataset. You also could click fx and manually enter or edit the expression. d. Click Add. e. For Name, select CRMActivityCustNum (the subreport parameter). f. For Value, from the drop down list, select =First(Fields!CustNum.Value, "CustomerDS") (the main report field holding the required value). 8. Click OK to close the Subreport Properties window.

Note that the subreport item is only a placeholder and has not changed other than it now displays the name of the report that you specified in the properties configuration. 9. Create the second subreport item, which is for the open orders report:

a. Select the Insert tab, click Subreport, and then click in the report, below the report table and to the right of the first subreport. A new subreport item displays. b. Ensure that the subreport item is selected, hover to enable the move icon, and drag the report to place it slightly to the right of the other subreport, with the tops of the two reports aligned. You also can select both subreport items, right-click, and choose Layout > Align Tops.

10. Right-click the new subreport item and select Subreport Properties. 11. In the Subreport Properties window, ensure that General is selected and do the following:

a. In the Name field, type OpenOrdersSubreport. b. In the Use this report as a subreport field, select OpenOrder. 12. Select Parameters and do the following to configure the subreport parameters that are the links for passing values from the main report into the subreport:

a. Click Add. b. For Name, select OpenOrderCompany (the subreport parameter). c. For Value, from the drop down list, select =First(Fields!Company.Value, "CustomerDS") (the main report field holding the required value). d. Click Add. e. For Name, select OpenOrderCustNum (the subreport parameter). f. For Value, from the drop down list, select =First(Fields!CustNum.Value, "CustomerDS") (the main report field holding the required value). 13. Click OK to close the Subreport Properties window. 14. Click the Report Builder button and select Save. 15. Click Run to test the combined reports.


45



The main report and both subreports display. The subreports are populated with data for the company and customer values passed to them from the main report. The report is initially based on the default parameter values that you assigned to the main report configuration. You can experiment with entering other company identifiers and choosing other customers. Also keep in mind that if a selected customer does not include records that support a subreport, the subreport header will display with no rows underneath it. 16. Click Design to return to the Home tab. 17. Exit Report Builder.

Workshop - Add the Main Report to the Epicor Application Add the main report to the menu system in your Epicor application. 1. To open the Epicor application, double-click Epicor on your desktop.

If prompted, enter manager for both user name and password. 2. From the Main Menu, navigate to Epicor Education > Main Plant > System Management > Security Maintenance > Menu Maintenance. 3. From Menu Maintenance, navigate to: Main Menu > Material Management > Inventory Management > Reports. Make sure Reports is highlighted. 4. Select File > New > New Menu. 5. In Menu ID, enter UDXXX04 where XXX are your initials. 6. In Name, enter XXX My Customer Information Report where XXX are your initials. 7. In Order Sequence, enter 996. 8. In Program Type, select the drop down menu and chose SSRS Custom Report Link. 9. In Report, enter \XXXCustomReports\CustomerInfo.

The Epicor installation is configured to connect to the SQL Server report server via the report server's URL. Make sure that the report path and report name you enter here match those in your report deployment. Important All slashes need to be a backslash "\".

10. Click Save. 11. Log out, and log back into the Epicor application. 12. From the Main Menu, navigate to Epicor Education > Main Plant > Material Management > Inventory Management > Reports . 13. Double-click the XXX My Customer Information Report report (where XXX are your initials) on the right hand side to open the report's program window. 14. To display the report, click the Print Preview icon.

46




15. After reviewing the report, close the print preview and report program windows. You can leave the application running for later exercises.


47

Working with Epicor Shipped SSRS Reports


Working with Epicor Shipped SSRS Reports The SSRS reports that are shipped with the Epicor ERP application are built on dynamic database tables. This structure has bee implemented to support good report performance and report usage in SaaS cloud hosting implementations. In the following workshop, you will explore how to work in this report structure to create an edited version of a shipped report.

Workshop - Creating a Modified Version of an Epicor Shipped SSRS Report In this workshop, you create a copy of the Sales Order Pick List report that ships with the Epicor ERP application and then edit the copy to create a new report that includes an additional column.

Copy and Edit the Shipped Report Data Definition Working in the Epicor ERP application, copy a shipped report data definition and edit the copy to add a column to it. A report data definition is a structure of tables and fields that will be used by a reporting engine (like Crystal, EDI, SSRS or Bartender) to lay out data on a report. The Report Data Definition program can be used by customers to define their own customized reports, extracting data from an Epicor database. 1. Start the Epicor ERP application.

If prompted, enter manager for both user name and password. 2. Open the Report Data Maintenance program. Menu Path: System Management > Reporting > Report Data Definition Important This program is not available in the Epicor Web Access.

3. For Code, type SOPick and press Tab. 4. From the Actions menu, select Duplicate Report.

The Duplicate Report Definition dialog box opens. 5. For Report Def ID, enter SOPickXXX where XXX are your initials. 6. For Description, enter My Sales Order Pick List. 7. Click OK.

The newly-created report definition is displayed. 8. From the File menu, select Save. 9. Leave Report Type set to Base Definition.

This setting was picked up from SOPick.

48




10. Click the Report Table sheet. 11. Click the Exclusions sub sheet. 12. In the Report Data tree, navigate to Report Table and click OrderHed. 13. Back on the Exclusions sheet, locate OrderDate and uncheck the ExcludeColumn and ExcludeLabel check boxes. 14. From the File menu, select Save. 15. Close Report Data Maintenance and remain in the application for the next exercise.

Add a Report Style Working in the Epicor ERP application, add the report style used to manage the new report. A report style defines a variation for a report in the Epicor ERP application. The variation is reflected mainly on the report type, the report template being used, the report data output location, report options passed to the reporting engine, and the companies in which the style can be used. 1. Open the Report Maintenance program. Menu Path: System Management > Reporting > Report Style Important This program is not available in the Epicor Web Access.

2. For Report ID, enter SOPick and press Tab key. 3. Click File > New > New Report Style.

The new report you are creating in this workshop is a variation on the report SO Pick List, based on the report style you are creating now and the report data definition you created in the previous exercise. 4. In the Reports tree, click the newly-created empty node under Report Styles . 5. On the Styles > Style Detail sheet:

a. For Description, enter My Pick List Style. b. For Report Type, select SQL Server Reporting. c. For Data Definition, select SOPickXXX where XXX are your initials. d. For Report Location, enter reports/SalesOrderPickListNew/SOPickL. You will be creating, in SSRS Report Manager, a folder that matches this location in the next exercise.

6. From the File menu, select Save. 7. Close Report Maintenance and leave the application running for use later in the workshop.


49



Create a Copy of the Shipped Sales Order Pick List Report Working in Report Manager and Report Builder, create a new folder on the SSRS Report Server and copy the shipped report rdl files into the new folder. In the upcoming exercises, you will edit the copy. The shipped report will remain as-provided. 1. Verify that Report Manager is open.

Accessing Report Manager either from the server or from a client machine is explained in the workshops under Accessing SSRS Report Manager and Report Builder. 2. From the Report Manager Home page, navigate to the Epicor reports folder on the report server.

This is the folder where the Epicor shipped SSRS reports were deployed when the Epicor ERP application was installed and configured. For example, from the report server root: reports. 3. Click New Folder. 4. On the New Folder page, create a folder:

a. For Name, enter SalesOrderPickListNew. b. For Description, enter My Sales Order Pick List. c. Click OK. 5. Use Report Builder to copy the shipped SOPickL.rdl file and supporting subreport .rdl files into the new folder:

a. In Report Manager, navigate to the SalesOrderPickList folder. This is the location for the shipped report files. b. Hover your mouse over SOPickL, click the arrow on the right, and then choose Edit in Report Builder. c. After the report opens in Report Builder, click the Report Builder button and select Save As. d. In the Save As Report dialog box, navigate to the SalesOrderPickListNew folder, and click Save without changing the file name. 6. Continuing in Report Builder, copy the supporting subreport .rdl files into the new folder:

a. Click the Report Builder button, and select Open. b. In the Open Report dialog box, navigate back to the SalesOrderPickList folder, choose SOPickL_KitSubReport, and choose Open. c. Click the Report Builder button and select Save As. d. In the Save As Report dialog box, navigate to the SalesOrderPickListNew folder, and click Save without changing the file name. e. Repeat steps a through d for the SOPickL_QtyOnhand and SOPickL_QtyOnHandKits reports. Copying all .rdl files will prevent getting a subreport unavailable error when you test your new report later in the workshop. 7. Remain in Report Builder for the next exercise.

50




Add a Table Field to the Sales Order Pick List Copy Add, to the OrderHed dataset in the report copy, the OrderDate field from the OrderHed table. This change is accomplished in two main actions. First, working in Report Builder, OrderDate is added to the OrderHed dataset's query expression and field list. Next, the field's XML element, which was added to the dataset structure in SoPickL.rdl as a user defined type by default, is adjusted manually in a text editor to identify it correctly as a string type. 1. In Report Builder, click the Report Builder button, click Open, and then navigate to the SalesOrderPickListNew folder. 2. Select SOPickL and click Open. 3. In the Report Data pane, expand Datasets, right-click the OrderHed dataset and select Dataset Properties. 4. In the Dataset Properties dialog box, verify that Query is selected on the left, and then click the under Query on the right.

fx button

The Expression dialog box opens. 5. Add the OrderDate field to the expression:

a. Resize the dialog box to make the expression (SQL query) in Set expression for: Query.Command.Text easier for you to read. b. In the expression, find T1.Calc_HeadComment, and immediately following that field type T1.OrderDate, (be sure to include the ending comma). In this expression, T1 is the alias for OrderHed table. c. Click OK to save your change and close the Expression dialog box. 6. Back at the Dataset Properties dialog box, add OrderDate to the dataset:

a. Select Fields on the left. b. On the right, click Add to create a new row at the bottom of the field list. c. In the new row, type OrderDate for both Field Name and Field Source. d. Click OK to add the new field and close the Dataset Properties dialog box. In the Report Data pane, OrderDate is added to the list of fields under the OrderHed dataset. The field, by default, has been added as a user defined type. This detail of the field definition is not accessible from this location. Next, you will edit SOPickL.rdl to change the field definition to a string type. 7. Click the Report Builder button and select Save. 8. Exit Report Builder. 9. In Report Manager, download SOPickL.rdl to your local folder structure:

a. Verify that you are in the SalesOrderPickListNew folder. b. Hover your mouse over SOPickL, click the arrow on the right, and then choose Download.


51



c. Use the buttons at the bottom of the Report Manager window to download and save the file to a default folder (use Save) or a folder of your choice (expand Save and use Save as), and then choose Open folder to display the chosen download folder. 10. From the download folder, open SOPickL.rdl in a text editor. Tip Open Notepad (Start > Run, enter Notepad) and drag SOPickL.rdl onto the Notepad window.

11. With SOPickL.rdl open in the text editor, search for the OrderDate field element.

The following XML was added to the report structure when you added the OrderDate field to the OrderHed dataset earlier in the exercise. OrderDate true

12. Edit the OrderData field element to change the element as shown in bold below. You can edit it manually or copy an element that is already set to System.String from another field element. OrderDate

System.String

13. Save the file and exit the text editor. 14. In Report Manager, upload the modified file:

a. Verify that you are in the SalesOrderPickListNew folder. b. At the top of the page, click Upload file. c. On the Upload File page, click Browse and use the Choose File to Upload dialog box to locate and select the modified SOPickL.rdl file in your local folder structure. d. Back on the Upload File page, select Overwrite Item if it exists and click OK. The modified SOPickL.rdl file is uploaded and overwrites the existing file on the Report Server. 15. Remain in Report Manager for the next exercise.

Add a Field Placeholder to the Sales Order Pick List Copy Add, to the layout of the report copy, a placeholder for the new OrderDate field. Adjust the placeholder's format. 1. In Report Manager, verify you are in the SalesOrderPickListNew folder.

This is the folder into which you uploaded the modified SOPickL.rdl file at the end of the previous exercise. 2. Hover your mouse over SOPickL, click the arrow on the right, and then choose Edit in Report Builder. 3. In the Report Data pane, expand the OrderHed dataset and drag OrderDate to the design area and drop it to the right of Calc_CustName.

52




A new text box containing the placeholder [First(OrderDate)] is created. 4. Select [First(OrderDate)], right-click, and choose Placeholder Properties. 5. In the Placeholder Properties window, do the following:

a. Select General, and for Value, click fx to open the Expression window. b. Under Set expression for: Value, change =First(Fields!OrderDate.Value) to =Fields!OrderDate.Value and click OK to close the Expression window. c. Select Number, then Date and leave at the topmost (already selected) format option. d. Select Font, and for Size select 8pt. e. Click OK to close the Placeholder Properties window. The placeholder now appears as [OrderDate]. 6. Inside the text box, use the arrow keys to get the cursor to the left of [OrderDate] and type Order Date: (with an ending space). 7. Click the text box and use the handles to resize and move the text box to be on one line at the level of Calc_CustName. 8. Click the Report Builder button, and select Save.

The modified report is saved back to the folder SalesOrderPickListNew. 9. Exit Report Builder.

Test the Sales Order Pick List Copy in Epicor ERP Test the new report from the Epicor ERP application. 1. If it not already open, start the Epicor ERP application.

If prompted, enter manager for both user name and password. 2. Open the Sales Order Pick List report program window. Menu Path: Sales Management > Order Management > Reports > Sales Order Pick List Tip The CRM menu path is: Customer Relationship Management > Order Management > Reports > Sales Order Pick List

3. Under Report Options, change the From date to be one year back of the To date (to get a year of data). 4. For Report Style, select My Pick List style. 5. Select Print Preview. The report preview displays. You should see the added Order Date: on each report record. 6. Close the print preview and report program windows. You can leave the application running for later exercises.


53

Working with BAQ Reports


Working with BAQ Reports In the Epicor ERP application, the BAQ Report Designer enables you to design a SQL Server Reporting Services (SSRS) report that displays the output from a Business Activity Query (BAQ). You can create a report based on a system BAQ, an existing custom BAQ, or you can create a custom BAQ in the Business Activity Query Designer that is the companion of the BAQ Report Designer. Working in the BAQ Report Designer you can open the report in SSRS Report Builder, where you can control the report layout and select the data that is displayed in the report.

Workshop - Create a BAQ Report with SSRS Report Layout In this workshop, you add a BAQ report based on an existing BAQ, design the report layout in Microsoft SQL Server Report Builder, save the report (.rdl) on the SQL Server Report Server, and add the report to the application menu. The workshop procedures use simple examples to demonstrate how create a report and then automatically connect to Report Builder to define a layout that presents the data you need from the BAQ results. Advanced users will be able to design both BAQs and SSRS reports that address complex data reporting requirements.

Add the New BAQ Report In BAQ Report Designer, add and save a new report. 1. Navigate to BAQ Report Designer Menu Path: System Management > Business Activity Queries > BAQ Report Designer Important This program is not available in the Epicor Web Access.

2. Click the down arrow next to New on the Standard toolbar and select New Report Definition. 3. On the Details sheet, enter or verify the following for the new report: •

For Report ID, enter aa_CustomerTest.

•

For Description, enter SSRS BAQ Report Demonstration.

•

For BAQ ID, locate and choose zES_Customers, which is an existing system BAQ.

•

For Form Title, enter Customers SSRS Report Demonstration .

•

For SSRS Report, note that aa_CustomerTest.rdl has been added automatically based on the value in Report ID. Tip Once you enter and save a Report ID, you cannot change this identifier. You can, however, make a copy of the report to create a new identifier.

4. Click Save. 5. Leave the BAQ Report Designer open and navigate to Report Maintenance.

54




Menu Path: System Management > Reporting > Report Style Important This program is not available in the Epicor Web Access.

6. Retrieve your new report, and review the report style that has been created automatically for your new BAQ Report.

a. Click ReportID and then locate and retrieve aa_CustomerTest. b. Click the Styles tab and note the following about the new report style: •

Description and Report Type both indicate an SSRS report.

•

Data Definition identifies the report data definition that has been created automatically for your new report.

•

Report Location is the report's location on the SSRS report server relative to the report server settings in your Epicor ERP application server configuration.

7. Close Report Maintenance and remain in BA Q Report Designer for the next exercise.

Perform an Initial Test of the New BAQ Report In BAQ Report Designer, use the Test Report Form action to perform an initial test of the new report. This verifies the report form and print preview functionality for the new report and enables you to obtain, from the System Monitor, the report's TableGuid identifier that will be needed for testing the report while you are working on the report design in Report Builder. 1. Verify that BAQ Report Designer is open and you new aa_CustomerTest report is displayed on the Details sheet. 2. Choose Actions > Test Report Form to display the report's form. 3. On the report form, choose Print Preview to display the report.

The report displays in a layout based on a template that has been set up for new SSRS reports. Later in the workshop, you will be working in Report Builder to add your design to that starting point . 4. Close the report and report form and launch the application's System Monitor. 5. In the System Monitor, navigate to the Reports sheet and locate the line for your report.

The TableGuid identifier is in the FileName column found at the end of the Reports grid. The value will be similar to this example: REPORT DATABASE: 2ca4ef91d3884e1db980f45e37ac42e6

Record , for later use, the actual value you are seeing. You will not need the REPORT DATABASE: portion. 6. Close or minimize the System Monitor and remain in the BAQ Report Designer for the next exercise.


55



Design the BAQ Report Layout in Report Builder Create a new BAQ Report and use SSRS Report Builder to design the report layout. This procedure is a simple example that demonstrates how to take advantage of the BAQ Report Designer progra m features that allow you to create a report and then automatically connect to Report Builder to define a layout the presents the data you need from the BAQ results. Advanced users will be able to leverage both BAQs and SSRS to build more complex reports. 1. Verify that BAQ Report Designer is open and you new aa_CustomerTest report is displayed on the Details sheet. 2. Choose Actions > Design SSRS Report.

Report Builder opens and displays the report layout for editing and design. For a new report, the report automatically displays using the layout stored in the template file BAQReport.rdl. Important You may be prompted for valid credentials for accessing the SSRS report server before Report Builder can run.

Design SSRS Report launches Report Builder from the SSRS report server. By default, the Report Builder URL is pulled from your Epicor application server configuration, so Report Builder should l aunch automatically. However if the configured path is a "localhost" URL and you are on a different computer, you need to enter the complete URL path in BAQ Report Options. To do this, click Actions > BAQ Report Options and enter the URL in the SSRS Web Service URL field. Obtain, or ask your system administrator to obtain, the correct URL from the SSRS Configuration Manager on the server.

3. In Report Builder, select the Insert tab and then select Table > Table Wizard to run the New Table or Matrix wizard. 4. On the Choose a dataset screen, choose BAQReportResult and click Next. 5. From the Available Fields on the Arrange Fields screen:

a. Move Customer_Name and SalesRep_Name to Row Groups. b. Move Customer_CustID, Customer_CustNum, and Customer CreditHold to Values. c. Click Next. 6. On the Choose the Layout screen, clear all options and click Next. 7. On the Choose a Style screen, accept the default and click Finish to close the wizard. 8. Click the Report Builder button and then click Save. Tip Optionally, you can click the Report Builder button again and choose Open to review the report's folder location on the SSRS report server.

9. Test your design.

a. Verify that you are on the Home tab and click Run. b. At the top of the report page, enter the TableGuid identifier obtained in the previous exercise and click View Report.

56




The report runs in HTML format, as it would if it was run from SQL Server Report Manager. c. When done, click Design to return to the Home tab. Important To be able to run the report in Report Builder, you must have sufficient permissions on both the SQL Server Report Server and the SQL Server Database. See the requirements in the topic Environment Setup if you are blocked from running the report.

10. In Report Builder, you can continue to make adjustments to the report design. As you work, be sure to save and retest the report as described in steps 8 and 9. 11. When you are satisfied with the report, click the Report Builder button and choose Exit Report Builder. 12. Back in BAQ Report Designer, test the report to verify it in the application.

a. Click Actions > Test Report Form to open the report's form. b. Click Print Preview to display the report with your design work. The report displays in PDF format, which is the default for SSRS reports. c. When done, close the report, and close the report form. 13. Remain in the application with the BAQ Report Designer open for the next exercise.

Add the BAQ Report to the Epicor ERP Application Add the BAQ report to the menu system in the Epicor ERP application. For this example, the BAQ report is added to the application menu structure under Sales Management. You can use locations and values that match the report you are working with. 1. Open the Menu Maintenance program. Menu Path: System Setup > Security Maintenance > Menu Maintenance Important This program is not available in the Epicor Web Access.

2. On the Menu Maintenance tree, navigate to: Main Menu > Sales Management > Order Management > Reports. Verify Reports is highlighted. 3. Select File > New > New Menu. 4. In the Menu ID field, enter UDXXX01 (where XXX are your initials). Important It is an important standard practice to use a UD prefix when adding a menu ID. UD stands for User Defined.

5. In the Name field, enter Customers SSRS Report Demonstration . 6. In the Order Sequence field, enter 997. 7. Click the Program Type drop-down menu and select BAQ Report.


57



8. Click Report and then search for and choose the BAQ report CustomerTest. 9. Click Save and click OK if you get a security update message. 10. Log out and log back into the Epicor application.

This ensures you do not have to wait for an automatic update. 11. Open the Customers SSRS Report Demonstration report form. Menu Path: Sales Management > Order Management > Reports > Customers SSRS Report Demonstration 12. To display the report, click Print Preview. 13. When done, close the print preview and form windows. You can leave the application running for later exercises.

58



Printing an SSRS Report

Printing an SSRS Report In this workshop, you learn the basics for printing and monitoring an already existing Epicor report, and how to add an SSRS printer.

Add an SSRS Printer Add an SSRS printer to your Epicor application. 1. Navigate to Printer Maintenance. Menu Path: System Management > Reporting > Printer Maintenance Important This program is not available in the Epicor Web Access.

2. Select File > New. 3. Enter a value in Printer ID. 4. Enter a value in Description. 5. In the Network Path field, enter the path for a printer that is shared on your network. 6. Click the SSRS Printer check box. 7. Click Save. 8. Close Printer Maintenance. 9. Remain in the Epicor application and proceed to the next exercise.

Print a Report Select and Print an SSRS report from your Epicor application. 1. Navigate to the General Ledger report. Menu Path: Financial Management > General Ledger > Reports > General Ledger 2. In Report Styles, ensure that Standard - SSRS is selected. 3. Click Print on the Standard toolbar.


59

Printing an SSRS Report


The resulting action depends on the current setting for SSRS Printer Option on the Email and Reporting tab in Company Maintenance: •

SSRS Printer Option is Client and Server Printing - A menu provides the choices of Server Printer or Client Printer. Choose Server Printer to display the SSRS Printer dialog box.

•

SSRS Printer Option is Server Printing Only - The SSRS Printer dialog box displays.

•

SSRS Printer Option is Client Printing Only - The Print dialog box displays for the system where you are running your Epicor ERP client You do not have access to the SSRS Printer dialog box. For this course, the instructions in the topic Environment Setup specify that SSRS Printer Option in Company Maintenance must be set to either Client and Server Printing or Server Printing Only. You will not be able to complete this exercise until SSRS Printer Option in Company Maintenance is changed to allow Server Printing. Tip In an Epicor ERP deployment, enabling client printing and choosing a client printer can work well for immediate printing needs. It is however dependent on your Epicor ERP client being available when a print action is initiated, so it is not a good choice if you are setting up printing events such as scheduled printing or auto printing.

4. In the SSRS Printer dialog box, verify that the printer you set up i n the previous exercise is the printer selected and click OK. Tip The printer list in the SSRS Printer dialog box includes all SSRS printers defined in Printer Maintenance.

5. Monitor the print job in the System Monitor.

In the system tray, double-click the Epicor System Monitor Icon and click the Reports tab. Your job appears on the list. 6. Close the System Monitor. You can leave the application running for later exercises.

60



Working with Report Security

Working with Report Security This section describes the security features that are available for controlling access to SSRS reports from within the Epicor application.

Security Features in Menu Maintenance This topic provides a walk through of the Menu Maintenance security features in the Epicor Client. You use Menu Maintenance for two security functions. You first use it to create Security Identifier (ID) records that define which users and security groups do and do not have access through this security identifier. You then select the security identifier (or level) you want for each program icon on the Menu. 1. Navigate to Menu Maintenance. Menu Path: System Setup > System Maintenance > Menu Maintenance Important This program is not available in the Epicor Web Access.

2. Click the down arrow next to New on the Standard toolbar; select New Security. 3. Enter the Security ID that you want for this security level. 4. Enter a Description that briefly details the purpose for this security level. 5. Select the Security Manager Access Only check box to indicate that only security managers can access programs assigned to this security level. No other users are able to see these programs. Note You should select this option only when you want this ID to override all other security settings. This option is useful when you are first setting up security, as it blocks all access until you create a security plan. You indicate which users have security manager rights within User Account Maintenance.

6. Select the Current Company Only check box to apply this security code against the Menu structure for the current company. Only users with access through this security code can display and launch the programs within this company node, but this security code is not applied against other companies. If the Current Company Only check box is not selected (check box is clear), this security code is applied against all companies within your Epicor application.

For example, if security code SEC545 is applied against Company A and it has its Current Company Only check box selected, this level of security i s in place and only users with access can expand this company node and display its programs. This security code does not affect other areas of the Menu. 7. The Disconnected check box indicates whether this Security ID is available to use with Mobile Connect, enabling you to connect to your database through a mobile device. Only specific security IDs created by Epicor permit this feature, so you are not abl e to select this check box on your new security ID. 8. To prevent users within this security group from l aunching the Epicor application within an internet browser, select the Exclude Epicor Web Access check box.


61

Working with Report Security


9. If you have Localization Developer rights, the Country/Country Group drop-down list appears. Use this list to select a Country/Country Group code for the security group. All country and country group codes assigned to the current company display on this drop-down list. If you select a code from this list, users assigned to this security group will see all localized Menu programs linked to this CGC code, as well as any Menu programs which do not have a CGC code assigned (null) to them. 10. The Menu Options field displays all the programs that currently use this security level. Because this record is a new security ID, this field is blank. As you select this security ID for specific programs, this field di splays the selected programs that use this identifier. 11. Select the Allow Access to All Groups/Users check box to give everyone in the company access to programs that use this security level. 12. To limit this program to specific users and/or groups, you first must clear the Allow Access to All Groups/Users check box. The Groups/Users and Selected Groups/Users lists become active. Important Until you add users and/or groups to the Selected Groups/Users list, nobody has access through this security level. Be sure you are ready to assign security before you clear this check box.

13. In the Groups/Users list, highlight the specific group or user for which you want to give security access and click the arrow button to move them onto the Selected Groups/Users list. Note Only groups or users in this list will have access to programs assigned to this security level. Any groups or users that remain in the Groups/Users list do not have access.

14. To prevent specific users/groups from accessing this security level, click on the Disallow Access tab. 15. Select the Disallow Access to All Groups/Users check box to prevent everyone in the company from accessing programs assigned to this security level. 16. To disallow security access to specific users and/or groups, you first must clear the Disallow Access to All Groups/Users check box. The Groups/Users and Selected Groups/Users lists both become active. Note that until you add users and/or groups to the Selected Groups/Users list, everyone now has access through this security level. Be sure you are ready to assign security before you clear this check box. Note Any groups or users that remain in the Groups/Users list have access to the programs assigned to this security level.

17. Use the arrow buttons to add and remove users from the Selected Groups/Users list. 18. When you finish, click Save and close Menu Maintenance.

62



Conclusion

Conclusion Congratulations! You have completed the course.


63

Report "SQL Server Reporting Services SSRS"

Your name

Email

Reason

Description

Copyright © 2024 IDOC.TIPS. All rights reserved.
About Us | Privacy Policy | Terms of Service | Copyright | Contact Us | Cookie Policy

Sign In

Email

Password

Remember me Forgot password?

Our partners will collect data and use cookies for ad personalization and measurement. Learn how we and our ad partner Google, collect and use data. Agree & close

SQL Server Reporting Services SSRS

Recommend Documents