SQL Server Reporting Services for Epicor ERP - Spiceworks

Epicor ERP SQL Server Reporting Services for Epicor ERP

10.1

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 its date of publication. However, Epicor Software Corporation makes no guarantee, representations or warranties with regard to the enclosed information and specifically disclaims any applicable implied warranties, such as fitness for a particular purpose, merchantability, satisfactory quality or reasonable skill and care. As each user of Epicor 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 contained 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 Epicor 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 publication 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 2016. 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-583101001

10.1

Revision: March 27, 2016 5:44 p.m.

Total pages: 73

course.ditaval

SQL Server Reporting Services for Epicor ERP

Contents

Contents

SQL Server Reporting Services for Epicor ERP Course.........................................................6 Before You Begin....................................................................................................................7

Audience.........................................................................................................................................................7 Prerequisites....................................................................................................................................................7 Environment Setup..........................................................................................................................................7

Report Types...........................................................................................................................9 Review ERP Schema..............................................................................................................10

Field Help Technical Details............................................................................................................................10 Workshop - Use the Field Help................................................................................................................10

Data Dictionary Viewer..................................................................................................................................11 Tables.....................................................................................................................................................11 Fields......................................................................................................................................................12

Table Linking.................................................................................................................................................12

Modify System Reports........................................................................................................14

Workshop - Add a Logo.................................................................................................................................14 Create Report Style.................................................................................................................................14 Add a Logo Image..................................................................................................................................15 Upload and Test Report..........................................................................................................................15

Workshop - Add a Table/Column...................................................................................................................16 Modify Report Data Definition................................................................................................................16 Create Report Style.................................................................................................................................17 Add the New Field..................................................................................................................................18 Upload and Test the Report....................................................................................................................19

BAQ Reports..........................................................................................................................21

Workshop - Create SSRS BAQ Report.............................................................................................................21 Copy a System BAQ for Report Use.........................................................................................................21 Create the BAQ Report...........................................................................................................................21 Create Report Option.............................................................................................................................22 Add Report Filter....................................................................................................................................22 Design the BAQ Report...........................................................................................................................23 Add Menu Item for BAQ Report..............................................................................................................24

Routing Rules........................................................................................................................26

Rule Elements................................................................................................................................................26 Workshop - Customer Statement E-Mail........................................................................................................27

Create Rule.............................................................................................................................................27 Design Rule............................................................................................................................................28 Test Rule.................................................................................................................................................29 Workshop - Filter by Attribute........................................................................................................................29 Create the Attribute...............................................................................................................................30 Select the Attribute.................................................................................................................................30

Epicor ERP | 10.1

3

Contents

SQL Server Reporting Services for Epicor ERP

Copy System Report...............................................................................................................................31 Modify Report Definition........................................................................................................................31 Define Relationship.................................................................................................................................32 Create Report Style.................................................................................................................................33 Design Routing Rule...............................................................................................................................33 Select Break Column...............................................................................................................................34 Define Condition....................................................................................................................................34 Add User Action.....................................................................................................................................35 Add Send E-mail Action..........................................................................................................................35 Activate Routing Rule/Test Rule...............................................................................................................36 Workshop - Overdue Jobs..............................................................................................................................37 Create Overdue Jobs Style......................................................................................................................37 Create Routing Rule................................................................................................................................38 Select Break Column...............................................................................................................................38 Define Condition....................................................................................................................................38 Add Print Action.....................................................................................................................................39 Activate Routing Rule.............................................................................................................................39

Direct Access to SSRS and SQL Database............................................................................41

Workshop - Manage Reports.........................................................................................................................41 Open Report Manager from Server.........................................................................................................41 Open Report Manager from Client..........................................................................................................42 Launch Report Builder.............................................................................................................................43 Create a Data Source..............................................................................................................................44

Workshop - Parts List Custom Report.............................................................................................................44 Create the Parts List Report.....................................................................................................................44 Select the Data Source.....................................................................................................................44 Design the Query.............................................................................................................................45 Modify the Report...........................................................................................................................46 Add the Report to the Interface.......................................................................................................46 Add Parameters......................................................................................................................................47 Create Parameters...........................................................................................................................47 Create Parameter Filters...................................................................................................................48 Test the New Parameters.................................................................................................................49 Create Row Group..................................................................................................................................49 Add Row Group..............................................................................................................................49 Format Row Group..........................................................................................................................50 Add a Calculated Field............................................................................................................................51 Create the Calculated Field..............................................................................................................51 Add a Column for Calculated Value.................................................................................................51 Test the Finished Report..................................................................................................................52

Workshop - Customer Report with Subreports...............................................................................................53 Create the Customer Main Report..........................................................................................................53 Select the Data Source.....................................................................................................................53 Design the Query.............................................................................................................................54 Modify the Report...........................................................................................................................54

4

Epicor ERP | 10.1

SQL Server Reporting Services for Epicor ERP

Contents

Add Parameters...............................................................................................................................55 Create Dataset Filters.......................................................................................................................56 Test the Report................................................................................................................................57 Adjust the Report Format................................................................................................................57 Create the Call Activity Subreport...........................................................................................................58 Select the Data Source.....................................................................................................................58 Design the Query.............................................................................................................................59 Modify the Report...........................................................................................................................59 Add Parameters...............................................................................................................................60 Create Dataset Filters.......................................................................................................................60 Test the Report................................................................................................................................61 Adjust the Report Format................................................................................................................62 Create the Open Orders Subreport.........................................................................................................63 Select the Data Source.....................................................................................................................63 Design the Query.............................................................................................................................64 Modify the Report...........................................................................................................................64 Add Parameters...............................................................................................................................65 Add Dataset Filters..........................................................................................................................65 Test the Report................................................................................................................................66 Adjust the Report Format................................................................................................................67 Embed the Subreports............................................................................................................................68 Embed the Call Activity Subreport....................................................................................................68 Embed the Open Orders Subreport..................................................................................................69 Add the Main Report to the Interface.....................................................................................................70

Conclusion.............................................................................................................................72

Epicor ERP | 10.1

5

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download