Dynamic Row-Column structure report for Quote comparison

In Sage X3, one of the client’s requirements was to have a comparison report for Quotes made by the suppliers for the given RFQ products. This report needed to have a dynamic nature of rows-columns design for which we’ve implemented the use of cross-tab option in Crystal report.

New Stuff: Weigh Bridge Integration in Sage X3

We’ll be referencing the following screens from Sage X3 for this:

RFQ: All > Purchasing > RFQs > RFQ (GESPQH)
Responses: All > Purchasing > RFQs > Responses (GESPPD)

Refer the screenshots for reference:

Note: The RFQ screen has two tables, one referencing the Products and other the Suppliers. Each supplier can give quote(s) for the given products which we can track from Response screens.

Step 1: In the Crystal report we need to design the report with a table having RFQ Products as rows and RFQ Suppliers as columns. As the no. of suppliers and products changes on the RFQ screen the report will automatically adjust the row-columns size. For this we require the cross-tab design (Insert > Cross-tab) and a basic design will show as follows:

Fig 1: Basic Cross-tab table design
Fig 1: Basic Cross-tab table design

Step 2: We developed a SQL query which fetches Suppliers, products and other details from RFQ and Response screens as per client’s requirement. Our main rows comprises Products whereas the data corresponding to the product are Summarized rows next to it as shown below:

Fig 2: Structure design for row-column data
Fig 2: Structure design for row-column data

Step 3: Cross-tab report is a matrix style design so the more row-column data we add it’ll maintain a (nxn) design. So we suppress all the remaining duplicate data and delete the redundant columns-row pairs using the Cross-tab Expert menu to change the format to (nxm) design as below:

Fig 3: Suppressed part in cross-tab
Fig 3: Suppressed part in cross-tab

Step 4: Cross-tab table also provides a handful of Valuation options to implement such as row-wise grand totals, column-wise grand totals etc. So we also suppress such redundant totals using the Cross-tab Expert menu from the report as per our requirement.

Refer below a demo implementation of RFQ entry on report.

Fig 4: Test RFQ entry on screen with Responses
Fig 4: Test RFQ entry on screen with Responses

In the above RFQ entry, we’ve two suppliers giving 1 response for the product. So our report will be reflecting the different parameters based on the response data per supplier.

Fig 5: Output of cross-tab implementation
Fig 5: Output of cross-tab implementation

Here we can see the data in report format. If the products are added then the report will reflect that in rows and if more Suppliers are added then it’ll reflect in columns increment all automatically handled by Cross-Tab report.

Step 5: This way we have achieved a dynamic natured report requirement on Crystal report.

About Us

Greytrix – a globally recognized and one of the oldest Sage Development Partner is a one-stop solution provider for Sage ERP and Sage CRM organizational needs. Being acknowledged and rewarded for multi-man years of experience, we bring complete end-to-end assistance for your technical consultations, product customizations, data migration, system integrations, third-party add-on development and implementation competence. 

Greytrix caters to a wide range of Sage X3, a Sage Business Cloud Solution, offerings. Our unique GUMU™ integrations include Sage X3 for Sage CRMSalesforce.com, Dynamics 365 CRM and Magento eCommerce along with Implementation and Technical Support worldwide for Sage X3. Currently we are Sage X3 Implementation Partner in East Africa, Middle East, Australia, Asia, US, UK. We also offer best-in-class Sage X3 customization and development services, integrated applications such as POS | WMS | Payment Gateway | Shipping System | Business Intelligence | eCommerce and have developed add-ons such as Catch – Weight  and Letter of Credit and India Legislation for Sage X3 to Sage business partners, end users and Sage PSG worldwide. Greytrix Sales Commission App for Sage X3 simplifies commission calculations and postings on sales transactions.

The GUMU™ Cloud framework by Greytrix forms the backbone of cloud integrations that are managed in real-time for processing and execution of application programs at the click of a button.

Greytrix is a recognized Sage champion ISV Partner for GUMU™ Sage X3 – Sage CRM integration also listed on Sage Marketplace; GUMU™ integration for Sage X3 – Salesforce is a 5-star rated app listed on Salesforce AppExchange and GUMU™ integration for Dynamics 365 CRM – Sage ERP listed on Microsoft AppSource.

For more information on Sage X3 Integration and Services, please contact us at x3@greytrix.com, We would like to hear from you.