On a large database, calculating a Crosstab could take a long time, and it could affect performance if you are running it on the database server. Note: Be aware that while it is tempting to create Crosstabs to browse and analyse your transaction data, you should be careful to filter out unnecessary transactions. It would look identical to the one above. Select the Region, Customer type, and Period as dimensions.ĥ. Create a Crosstab component in the report layout.ģ. Select the sales data from the databaseĢ. For example, let's assume you wanted to create the Crosstab we just described. The Crosstab component is designed to handle the most common Crosstab requirements with minimal effort on your part. Clarity has a built-in facility for creating Crosstabs. You can see that Crosstabs can express a lot of information in a very small amount of space.
Excel split cells crosstab plus#
This Crosstab shows all of the information of the initial Crosstab, plus more detailed information by Customer Type. Notice the new subtotal columns after each Region. The resulting Crosstab would look like the one below: Customer Type) and another calculation to the values (i.e. We can present this information by adding another dimension to the columns (i.e.
What if we wanted to know the sales by Customer type within the region, as well as the total number of distinct sales per Customer type. The simple Crosstab we've outlined here can be taken a couple of steps further to create a very informative report. The values in the cells are the calculations created when the sales data is summarized and are sometimes referred to as measures. The Year and Region values are called "dimensions" because they orient the data in rows and columns. It is easy to make state-to-state and year-to-year comparisons. This format is easier to read and more compact there is more information in less space.
This format is OK, but it makes a state-to- state comparison difficult. Here is one way you could present the data: Now, assume you want to know the total sales for each month by area. Each row of data contains a customer name, company type, Geographical Area, sale date or period, and sale amount. Each sale is represented by a row of data. In Microsoft Excel™ the term "Pivot Table" is used for a Crosstab.įor example, let's assume you have a set of data that describes the sales for a company.
Crosstab stands for Cross tabulation, a process by which totals and other calculations are performed based on common values found in a set of data. The Crosstab format is one of the most popular. There are many different formats a spreadsheet may take.