SSRS Report In Epicor: How to Modify an Out of the Box Report

Home  /  Solutions and Services  /  Epicor and ERP  /  SSRS Report In Epicor: How to Modify an Out of the Box Report

How to Modify an Out of the Box SSRS Report In Epicor 10

SQL reporting systems is a reporting platform integrated into Epicor version 10. Users may need to modify an out of the box report for one of their many business needs. Here, the Datix Epicor consulting experts show you “how to” modify an out of the box SSRS Report; no matter the complexity of the report:

(SSRS Report – code at bottom of page)

For further SSRS report help contact the Datix team today. We are currently helping several organizations with SSRS report and crystal report processes in Epicor version 10.

  1. Load Epicor; click on the System Management> Reporting> Report Data Definition.
  2. Click the “Code” button to search for the report you are modifying:

SSRS Report 1

3. Click Actions> Duplicate Report
4. The left side bar will display a cascading tree. Right click on Report Table and Add New Table
5. Name it and add a Description.

 SSRS report 2

  • Click Schema Table and search for the table you want to add.
  • Double click to add, then add a sequence number (optional) to change the order of the table in the list.

6.  Add Table Relationship

  • Rick click Report Relationship in the tree on the left and add new
    • Change description from this table to that that table>
    • Do not choose a key>
    • Child table>
    • Choose: Output (definition only will not output anything)>
    • Add relationship fields
      • The field must always show company and always the field linking them together (i.e., part number).

SSRS report 3

7.  After adding the relationships, click on the table in the tree on the left. Click the Report Table, then Exclusions tabs to “un-exclude” or include the fields from the tables needing to be included:

Modify SSRS report 4

8.  Save and close this window.

9.  Choose Report Style

SSRS report change 1

10.  Click the Report ID button to search for your report and double click to open.

11. Click the new icon and choose New Report Style> Style Detail:

SSRS report create

12.  Fill in the following fields:

  • Description (This is what users will see).
  • Type: SQL Server Reporting
  • Choose the data definition (i.e. Job Traveler)
  • Report location. (This is a path to the report)
  • Output: Database

SSRS report creation

13. Navigate to the Report Manager URL

  • SQL Servername/Reports (Not the application server)>
  • Windows authentication enables (User may have issues trying to do this through a VPN)>
  • Choose Main Report> Click Down Arrow to Right of Report> Edit in Report Builder

Create SSRS report 8

14.  Make a copy of the Main Report> Save As

  • Save it to the custom reports folder.

15. Run the Report in Epicor.

  • Make sure to select the report style.
  • Click the Print Preview icon located at the top of the screen.

buildign SSRS report 1

16.  Retrieve the GUID from the System Monitor located in on the bottom right of the screen. Double click to open.

  • The GUID is the last field on the reports tab under filename and will say REPORTDATABASE: fjaldskfjds;lkfjslkfjds;lkfjds

create sirs report 3

  • Copy the contents of the field and paste somewhere to save it. Omit “REPORTDATABASE,” to simply save the GUID number.

17.  Go back to Report Builder: Datasets> Right Click on Report Labels>Dataset Properties

create ssrs reporting

  • Use dataset embedded in form.
  • Click on the fx (functions) button.

Creating SSRS report 1

18.  Edit the Query:

  • Join on company and company: Po Num and Po Num
    1. Below is the table user is joining and the fields user is joining on.
  • Copy from LEFT OUTER JOIN; right before next LEFT OUTER JOIN statement (see below example).
  • After adding the join-statement, add fields (i.e. T5.NewField).

Example (Code):

Original Query

="SELECT
T1.RptLanguageID,
T1.CommentText,
T1.Company,
T1.FOB,
T1.FreightPP,
T1.OrderDate,
T1.PONum,
T1.PrintAs,
T1.ShipToConName,
T1.ShipViaCode,
T1.Calc_AccountRef,
T1.Calc_BillToAddrList,
T1.Calc_Buyer,
T1.Calc_CurDesc,
T1.Calc_CurSymb,
CAST(T1.Calc_ExistingDropShipReleases AS nvarchar) AS Calc_ExistingDropShipReleases,
T1.Calc_FOBDesc,
CAST(T1.Calc_MultiShiptoAddresses AS nvarchar) AS Calc_MultiShiptoAddresses,
T1.Calc_PurTerms,
T1.Calc_ShipToAddrList,
T1.Calc_ShipVia,
T1.Calc_VendorContact,
T1.Calc_VendPhone,
T1.Calc_VendFax,
T1.Calc_EMail,
T1.BuyerID_EMailAddress,
T1.Vendor_EMailAddress,
T1.Vendor_FaxNum,
T1.VendorCnt_EmailAddress,
T1.Calc_TotDocMiscAmt,
T1.VendorCnt_FaxNum,
T2.CommentText AS PODetail_CommentText,
T2.Company AS PODetail_Company,
T2.DocUnitCost,
T2.MfgPartNum,
T2.MfgPartOpts,
T2.PartNum,
T2.POLine,
T2.PONUM AS PODetail_PONum,
T2.PUM,
T2.RevisionNum,
T2.SubPartNum,
T2.SubPartOpts,
T2.SubPartType,
T2.Taxable,
T2.VendorPartOpts,
T2.VenPartNum,
T2.Calc_CostPer,
T2.Calc_DtLineDesc,
T2.Calc_ExtCost,
T2.Calc_UOMDescription,
T2.Calc_MfgID,
T3.[Description],
T3.DocMiscAmt,
T3.POLine AS POMisc_POLine,
T3.PONum AS POMisc_PONum,
T3.Taxable AS POMisc_Taxable,
T4.Calc_AccountRef AS RptLiteralsCalc_AccountRef,
T4.RptLiteralsLFax,
T4.FreightPP AS RptLiteralsFreightPP,
T4.RptLiteralsLAuthBy,
T4.RptLiteralsLChangeOrder,
T4.RptLiteralsLClosed,
T4.RptLiteralsLDueDt,
T4.RptLiteralsLExtPrice,
T4.RptLiteralsLFOB,
T4.RptLiteralsLHdng,
T4.RptLiteralsLJobNum,
T4.RptLiteralsLLine,
T4.RptLiteralsLLineChargeSubtotal,
T4.RptLiteralsLMfgPartNumber,
T4.RptLiteralsLMiscChargeSubtotal,
T4.RptLiteralsLMslshS,
T4.RptLiteralsLOrderDate,
T4.RptLiteralsLOrderQty,
T4.RptLiteralsLOurPartNumber,
T4.RptLiteralsLPg,
T4.RptLiteralsLPhone,
T4.RptLiteralsLPONum,
T4.RptLiteralsLPrtRvDes,
T4.RptLiteralsLQty,
T4.RptLiteralsLResalNo,
T4.RptLiteralsLRevised,
T4.RptLiteralsLSeeBelow,
T4.RptLiteralsLSeq,
T4.RptLiteralsLShipTo,
T4.RptLiteralsLShipVia,
T4.RptLiteralsLShRelReq,
T4.RptLiteralsLSubstitute,
T4.RptLiteralsLSubstitutedFor,
T4.RptLiteralsLSupplierPartNumber,
T4.RptLiteralsLTax,
T4.RptLiteralsLTerms,
T4.RptLiteralsLTotal,
T4.RptLiteralsLUnitPric,
T4.RptLiteralsLVend,
T4.RptLiteralsLLnMsChrg,
T4.RptLiteralsLDesc,
T4.PONum AS RptLiteralsPONum,
T4.RptLiteralsLAmt
 FROM POHeader_" + Parameters!TableGuid.Value + " T1
 LEFT OUTER JOIN PODetail_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.PONum = T2.PONUM
 LEFT OUTER JOIN POMisc_" + Parameters!TableGuid.Value + " T3
ON T1.Company = T3.Company AND T1.PONum = T3.PONum
 LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T4
ON T1.RptLanguageID = T4.RptLanguageID"

 

 New Query

New Query
="SELECT
T1.RptLanguageID,
T1.CommentText,
T1.Company,
T1.FOB,
T1.FreightPP,
T1.OrderDate,
T1.PONum,
T1.PrintAs,
T1.ShipToConName,
T1.ShipViaCode,
T1.Calc_AccountRef,
T1.Calc_BillToAddrList,
T1.Calc_Buyer,
T1.Calc_CurDesc,
T1.Calc_CurSymb,
CAST(T1.Calc_ExistingDropShipReleases AS nvarchar) AS Calc_ExistingDropShipReleases,
T1.Calc_FOBDesc,
CAST(T1.Calc_MultiShiptoAddresses AS nvarchar) AS Calc_MultiShiptoAddresses,
T1.Calc_PurTerms,
T1.Calc_ShipToAddrList,
T1.Calc_ShipVia,
T1.Calc_VendorContact,
T1.Calc_VendPhone,
T1.Calc_VendFax,
T1.Calc_EMail,
T1.BuyerID_EMailAddress,
T1.Vendor_EMailAddress,
T1.Vendor_FaxNum,
T1.VendorCnt_EmailAddress,
T1.Calc_TotDocMiscAmt,
T1.VendorCnt_FaxNum,
T2.CommentText AS PODetail_CommentText,
T2.Company AS PODetail_Company,
T2.DocUnitCost,
T2.MfgPartNum,
T2.MfgPartOpts,
T2.PartNum,
T2.POLine,
T2.PONUM AS PODetail_PONum,
T2.PUM,
T2.RevisionNum,
T2.SubPartNum,
T2.SubPartOpts,
T2.SubPartType,
T2.Taxable,
T2.VendorPartOpts,
T2.VenPartNum,
T2.Calc_CostPer,
T2.Calc_DtLineDesc,
T2.Calc_ExtCost,
T2.Calc_UOMDescription,
T2.Calc_MfgID,
T3.[Description],
T3.DocMiscAmt,
T3.POLine AS POMisc_POLine,
T3.PONum AS POMisc_PONum,
T3.Taxable AS POMisc_Taxable,
T4.Calc_AccountRef AS RptLiteralsCalc_AccountRef,
T4.RptLiteralsLFax,
T4.FreightPP AS RptLiteralsFreightPP,
T4.RptLiteralsLAuthBy,
T4.RptLiteralsLChangeOrder,
T4.RptLiteralsLClosed,
T4.RptLiteralsLDueDt,
T4.RptLiteralsLExtPrice,
T4.RptLiteralsLFOB,
T4.RptLiteralsLHdng,
T4.RptLiteralsLJobNum,
T4.RptLiteralsLLine,
T4.RptLiteralsLLineChargeSubtotal,
T4.RptLiteralsLMfgPartNumber,
T4.RptLiteralsLMiscChargeSubtotal,
T4.RptLiteralsLMslshS,
T4.RptLiteralsLOrderDate,
T4.RptLiteralsLOrderQty,
T4.RptLiteralsLOurPartNumber,
T4.RptLiteralsLPg,
T4.RptLiteralsLPhone,
T4.RptLiteralsLPONum,
T4.RptLiteralsLPrtRvDes,
T4.RptLiteralsLQty,
T4.RptLiteralsLResalNo,
T4.RptLiteralsLRevised,
T4.RptLiteralsLSeeBelow,
T4.RptLiteralsLSeq,
T4.RptLiteralsLShipTo,
T4.RptLiteralsLShipVia,
T4.RptLiteralsLShRelReq,
T4.RptLiteralsLSubstitute,
T4.RptLiteralsLSubstitutedFor,
T4.RptLiteralsLSupplierPartNumber,
T4.RptLiteralsLTax,
T4.RptLiteralsLTerms,
T4.RptLiteralsLTotal,
T4.RptLiteralsLUnitPric,
T4.RptLiteralsLVend,
T4.RptLiteralsLLnMsChrg,
T4.RptLiteralsLDesc,
T4.PONum AS RptLiteralsPONum,
T4.RptLiteralsLAmt
T5.PartNum
 FROM POHeader_" + Parameters!TableGuid.Value + " T1
 LEFT OUTER JOIN PODetail_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.PONum = T2.PONUM
 LEFT OUTER JOIN POMisc_" + Parameters!TableGuid.Value + " T3
ON T1.Company = T3.Company AND T1.PONum = T3.PONum
 LEFT OUTER JOIN Part_" + Parameters!TableGuid.Value + " T5
ON T2.Company = T5.Company AND T2.PartNum = T5.PartNum
 LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T4
ON T1.RptLanguageID = T4.RptLanguageID"

 

19.  Add Field

  • Add Query Field> Name and Source Field
  • Run to Check Syntax Error
  • Add Field to Form
    • Rid the word “first,” if user is drag and dropping.

SSRS report complete

 

20.  Run the report with your saved GUID by right clicking on Parameters in the tree on the left of the screen and Add Parameter:

SSRS reports generate

21.  Click Default Values> Specify Values and paste the GUID from earlier into the expression box.

The user has successfully modified an out of the box SSRS report!


For more help…

We hope this tutorial was helpful. Our experts specialize in helping Epicor users get the most out of their software by bending the technology to fit best practices and proper business processes for each organization we work with. If you’d like more help with Epicor 10 reports, please contact us today.

6 Comments so far:

  1. Crystal Jagmohan says:

    Great article. Very detailed. Thank you for the information. Would you please inform me where can I locate a calc_field? How is it created? I saw the field is added to the list of calculated fields in the Data Definition however how does it know which table it is referencing from?

    • Miguel says:

      This sample tutorial is very confusing!!!!
      You start with the job Traveler and later looks like you are modifying something else….maybe PO ??? could you please be more specific when you put some sample to follow ?????

  2. […] learn how to modify an out of the box SSRS report click here. For additional Epicor reporting help, contact our experts […]

  3. William says:

    How can I know which alias is assigned when adding a new table to the RDD definition?

    Thanks

    • Alejandra says:

      Hi William,

      You would normally just assign the next one in line, if you have 3 tables they would be T1, T2, T3, you can just add T4 to follow the logic.

      Regards,
      Alejandra

  4. hiram Lott says:

    como puedo agregar una tabla y un campo a un ssrs ya creado para que lo ligue ala consulta y se vea reflejado en el Cristal report??

Leave a Reply

Your email address will not be published. Required fields are marked *