Pivot like report in Crystal reports.

Question

Hi

How we can creat Month Wise then Location Wise Pivot like report in Crystal reports.

I want Data to be displayed like below  without using Pivot

 	Apr-24	Apr-24	Aug-24	Aug-24	Sep-25	Sep-24
 	Sale	Accounts	Sale	Accounts	Sale	Accounts
1	16531	0	0	212326	0	393994
2	18694	0

Thanks

Muhammad Imran Ansari · Answer

Hello Ramco, In Crystal Reports , you can create a month-wise and location-wise pivot-like report without using Pivot tables by following these steps: Steps to Achieve This Layout 1. Grouping the Data Group the data first by Month-Year (e.g., Apr-24, Aug-24, etc.). Create a sub-group inside each month for category-wise grouping (e.g., Sale, Accounts). 2. Creating Cross-Tab-Like Columns Manually Use Formula Fields to segregate values into different columns. Example formula to show Sales for Apr-24 : IF {Table.MonthYear} = "Apr-24" AND {Table.Category} = "Sale" THEN {Table.Amount} ELSE 0 Similarly, create formulas for Accounts for Apr-24 , Sales for Aug-24 , etc. 3. Placing the Formulas in the Report Place these formula fields inside the Details Section or Group Footer (if summarized). Format the report to ensure correct alignment. 4. Handling Dynamic Months If months are dynamic, consider using Running Totals or Conditional Formatting to handle varying column numbers. Good Luck!

Daniel Wright · Answer

Creating a Month Wise then Location Wise Pivot-like report in Crystal Reports involves organizing your data in a structured manner to display it as per your desired format without using the Pivot functionality directly. To achieve the desired layout as shown in your example output, you can follow these steps: 1. Organize Data Structure : Ensure your data is structured with columns for Month, Location, Sale, and Accounts. You may need to pre-process your raw data to align with this structure. 2. Grouping and Sorting : Use grouping and sorting functionalities in Crystal Reports to first group your data by Month and then by Location. This will allow you to display the data in the required hierarchy. 3. Crosstab Report : Utilize the Crosstab functionality in Crystal Reports to pivot your grouped data. You can place Month in the columns section, Location in the rows section, and Sale/Accounts in the summarized fields section to achieve the pivot-like view. 4. Formatting : Format the Crosstab report to align with your desired layout. You can customize column headers, row headers, and cell data formats to match the structure you provided in your example. By following these steps and leveraging the grouping, sorting, and Crosstab features in Crystal Reports, you can create a report that displays data in a Month Wise then Location Wise format similar to the Pivot-like layout you outlined. Should you require further clarification or additional details on any specific aspect of this process, feel free to ask!

Pivot like report in Crystal reports.

Answers (2)