{"id":31419,"date":"2025-10-30T06:53:30","date_gmt":"2025-10-30T06:53:30","guid":{"rendered":"https:\/\/www.greytrix.com\/blogs\/sagex3\/?p=31419"},"modified":"2025-10-30T06:53:30","modified_gmt":"2025-10-30T06:53:30","slug":"crafting-dynamic-date-pivoted-reports-in-crystal-reports-turning-sql-joins-into-excel-style-cross-tabs","status":"publish","type":"post","link":"https:\/\/www.greytrix.com\/blogs\/sagex3\/2025\/10\/30\/crafting-dynamic-date-pivoted-reports-in-crystal-reports-turning-sql-joins-into-excel-style-cross-tabs\/","title":{"rendered":"Crafting Dynamic Date-Pivoted Reports in Crystal Reports: Turning SQL Joins into Excel-Style Cross-Tabs"},"content":{"rendered":"\n<p>Cross-tab reports in <a href=\"https:\/\/www.greytrix.com\/sage-x3-erp\/development-services\/\" target=\"_blank\" data-type=\"link\" data-id=\"https:\/\/www.greytrix.com\/sage-x3-erp\/development-services\/\" rel=\"noreferrer noopener\">Sage X3<\/a> are a versatile feature for summarizing and analyzing multidimensional data in a compact, matrix-like format. Similar to pivot tables in spreadsheet tools, they organize data with rows and columns representing different categories or variables, while the intersecting cells display aggregated values such as sums, averages, counts, or other calculations. In Sage X3, crosstab reports are powered by Crystal Reports, the integrated reporting engine that allows for dynamic, customizable outputs. This integration enables users to pull data directly from Sage X3&#8217;s database tables, apply business logic, and generate professional-looking reports that can be exported to formats like PDF or Excel. Key benefits include real-time visibility into processes (e.g., comparing supplier quotes against RFQ products in a dynamic row-column structure) and advanced features like formulas, conditional formatting, and sub-reports for deeper insights.<\/p>\n\n\n\n<p>Recently, we received a requirement from one of our clients regarding a custom screen, EIR on Borrowing. On this screen, they want a report on the Effective Interest Rate and Closing Balance in a dynamic row-and-column format with date filters.<\/p>\n\n\n\n<p>On the <em>EIR on Borrowing <\/em>screen in Sage X3 (as shown below), data is available in a detailed format for each borrowing entry:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Fields: Date, Opening Balance, Principal Transaction, Interest Accrual<\/li>\n\n\n\n<li>Data Flow: Each line records periodic financial movement for a loan account.<\/li>\n<\/ul>\n\n\n\n<p><strong>New Stuff:<\/strong> <a href=\"https:\/\/www.greytrix.com\/blogs\/sagex3\/2025\/10\/30\/restricting-user-access-to-specific-sites-in-sage-x3\/\" target=\"_blank\" data-type=\"link\" data-id=\"https:\/\/www.greytrix.com\/blogs\/sagex3\/2025\/10\/30\/restricting-user-access-to-specific-sites-in-sage-x3\/\" rel=\"noreferrer noopener\">Restricting User Access to Specific Sites in Sage X3<\/a><\/p>\n\n\n\n<center><a href=\"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-content\/uploads\/2025\/10\/Figure-01-\u2013-EIR-on-Borrowing-Screen-1024x435.png\" target=\"_blank\" rel=\"noreferrer noopener\"><img decoding=\"async\" class=\"size-full\" style=\"border: 1px solid #A9A9A9; padding: 2px; margin: 2px; \nalign: center;\" src=\"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-content\/uploads\/2025\/10\/Figure-01-\u2013-EIR-on-Borrowing-Screen-1024x435.png\" alt=\"Figure 01 \u2013 EIR on Borrowing Screen\"><\/a><\/center>\n<font size=\"2\"><center>Figure 01 \u2013 EIR on Borrowing Screen<\/center><\/font>\n\n\n\n<p>However, the finance team often needs to see this same data in a summarized, Excel-style matrix format, where each month appears as a column and each borrowing ID appears as a row \u2014 showing clear picture of interest accruals over time.<\/p>\n\n\n\n<p>To implement the above requirement, please follow the steps outlined below:<\/p>\n\n\n\n<p>Step 01 &#8211; Create the SQL query that stores the values based on your WHERE condition. Here, we are filtering the data based on the date. Please find the attached screenshot for your reference.<\/p>\n\n\n\n<center><a href=\"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-content\/uploads\/2025\/10\/Figure-02-\u2013-SQL-Query-For-Report-1024x438.png\" target=\"_blank\" rel=\"noreferrer noopener\"><img decoding=\"async\" class=\"size-full\" style=\"border: 1px solid #A9A9A9; padding: 2px; margin: 2px; \nalign: center;\" src=\"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-content\/uploads\/2025\/10\/Figure-02-\u2013-SQL-Query-For-Report-1024x438.png\" alt=\"Figure 02 \u2013 SQL Query For Report\"><\/a><\/center>\n<font size=\"2\"><center>Figure 02 \u2013 SQL Query For Report<\/center><\/font>\n\n\n\n<p>Step 02 &#8211; Open Crystal Reports, go to the File menu, select New, and choose Cross-Tab Report. As shown in the screenshot below.<\/p>\n\n\n\n<center><a href=\"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-content\/uploads\/2025\/10\/Figure-03-\u2013-Cross-Tab-Report-1024x478.png\" target=\"_blank\" rel=\"noreferrer noopener\"><img decoding=\"async\" class=\"size-full\" style=\"border: 1px solid #A9A9A9; padding: 2px; margin: 2px; \nalign: center;\" src=\"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-content\/uploads\/2025\/10\/Figure-03-\u2013-Cross-Tab-Report-1024x478.png\" alt=\"Figure 03 \u2013 Cross-Tab Report\"><\/a><\/center>\n<font size=\"2\"><center>Figure 03 \u2013 Cross-Tab Report<\/center><\/font>\n\n\n\n<p>Step 03 &#8211; Since we have already fetched the fields as per the client&#8217;s requirements in the SQL query from the first step, our main aim is to display the Borrowing ID row-wise, corresponding to the date filters. As we are preparing the report below for the Effective Interest Rate, we will arrange the fields as shown in the screenshot below. Here, we have added some extra fields as per the client&#8217;s requirements.<\/p>\n\n\n\n<center><a href=\"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-content\/uploads\/2025\/10\/Figure-04-\u2013-Cross-Tab-Expert-1024x599.png\" target=\"_blank\" rel=\"noreferrer noopener\"><img decoding=\"async\" class=\"size-full\" style=\"border: 1px solid #A9A9A9; padding: 2px; margin: 2px; \nalign: center;\" src=\"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-content\/uploads\/2025\/10\/Figure-04-\u2013-Cross-Tab-Expert-1024x599.png\" alt=\"Figure 04 \u2013 Cross-Tab Expert\"><\/a><\/center>\n<font size=\"2\"><center>Figure 04 \u2013 Cross-Tab Expert<\/center><\/font>\n\n\n\n<p>Step 04 \u2013 Cross-tab report is a matrix style design so the more row-column data we add it\u2019ll 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:<\/p>\n\n\n\n<center><a href=\"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-content\/uploads\/2025\/10\/Figure-05-Suppressed-Part-in-Cross-tab-1024x395.png\" target=\"_blank\" rel=\"noreferrer noopener\"><img decoding=\"async\" class=\"size-full\" style=\"border: 1px solid #A9A9A9; padding: 2px; margin: 2px; \nalign: center;\" src=\"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-content\/uploads\/2025\/10\/Figure-05-Suppressed-Part-in-Cross-tab-1024x395.png\" alt=\"Figure 05 - Suppressed Part in Cross-tab\"><\/a><\/center>\n<font size=\"2\"><center>Figure 05 &#8211; Suppressed Part in Cross-tab<\/center><\/font>\n\n\n\n<p>Step 05 &#8211; In below screenshot you will get desired output.<\/p>\n\n\n\n<center><a href=\"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-content\/uploads\/2025\/10\/Figure-06-Output-1024x240.png\" target=\"_blank\" rel=\"noreferrer noopener\"><img decoding=\"async\" class=\"size-full\" style=\"border: 1px solid #A9A9A9; padding: 2px; margin: 2px; \nalign: center;\" src=\"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-content\/uploads\/2025\/10\/Figure-06-Output-1024x240.png\" alt=\"Figure 06 - Output\"><\/a><\/center>\n<font size=\"2\"><center>Figure 06 &#8211; Output<\/center><\/font>\n\n\n\n<p>By using the above simple steps, we can easily create a cross-tab report with a dynamic row and column structure.<\/p>\n\n\n[about_us_blog_common]\n","protected":false},"excerpt":{"rendered":"<p>Cross-tab reports in Sage X3 are a versatile feature for summarizing and analyzing multidimensional data in a compact, matrix-like format. Similar to pivot tables in spreadsheet tools, they organize data with rows and columns representing different categories or variables, while the intersecting cells display aggregated values such as sums, averages, counts, or other calculations. In\u2026 <span class=\"read-more\"><a href=\"https:\/\/www.greytrix.com\/blogs\/sagex3\/2025\/10\/30\/crafting-dynamic-date-pivoted-reports-in-crystal-reports-turning-sql-joins-into-excel-style-cross-tabs\/\">Read More &raquo;<\/a><\/span><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13,8],"tags":[4445,461,4446,1822,4447],"class_list":["post-31419","post","type-post","status-publish","format-standard","hentry","category-integration","category-sage-erp-x3","tag-cross-tab-report","tag-crystal-report","tag-formulas","tag-sage-x3","tag-summary"],"_links":{"self":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-json\/wp\/v2\/posts\/31419","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-json\/wp\/v2\/comments?post=31419"}],"version-history":[{"count":5,"href":"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-json\/wp\/v2\/posts\/31419\/revisions"}],"predecessor-version":[{"id":31424,"href":"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-json\/wp\/v2\/posts\/31419\/revisions\/31424"}],"wp:attachment":[{"href":"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-json\/wp\/v2\/media?parent=31419"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-json\/wp\/v2\/categories?post=31419"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.greytrix.com\/blogs\/sagex3\/wp-json\/wp\/v2\/tags?post=31419"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}