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.
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.
6. Add Table Relationship
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:
8. Save and close this window.
9. Choose Report Style
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:
12. Fill in the following fields:
13. Navigate to the Report Manager URL
14. Make a copy of the Main Report> Save As
15. Run the Report in Epicor.
16. Retrieve the GUID from the System Monitor located in on the bottom right of the screen. Double click to open.
17. Go back to Report Builder: Datasets> Right Click on Report Labels>Dataset Properties
18. Edit the Query:
="SELECTT1.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 + " T2ON T1.Company = T2.Company AND T1.PONum = T2.PONUM LEFT OUTER JOIN POMisc_" + Parameters!TableGuid.Value + " T3ON T1.Company = T3.Company AND T1.PONum = T3.PONum LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T4ON T1.RptLanguageID = T4.RptLanguageID" |
New Query="SELECTT1.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.RptLiteralsLAmtT5.PartNum FROM POHeader_" + Parameters!TableGuid.Value + " T1 LEFT OUTER JOIN PODetail_" + Parameters!TableGuid.Value + " T2ON T1.Company = T2.Company AND T1.PONum = T2.PONUM LEFT OUTER JOIN POMisc_" + Parameters!TableGuid.Value + " T3ON T1.Company = T3.Company AND T1.PONum = T3.PONum LEFT OUTER JOIN Part_" + Parameters!TableGuid.Value + " T5ON T2.Company = T5.Company AND T2.PartNum = T5.PartNum LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T4ON T1.RptLanguageID = T4.RptLanguageID" |
19. Add Field
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:
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!
联系客服