Cross tabulation table with day of week as header

[TPerson]

Hi,

I've been playing around with your ReportEditor demo, reading online help and watching videos. The component seems great and like what we are looking for.

There's one thing I haven't figured out how to do, below is a simplified example. Fields marked with a * are part of the primary key

Master table
Factory *
InvoiceNumber *
TimeOfSale

Details table
Factory *
InvoiceNumber *
LineNumber *
ItemCode
Quantity

I would like to create a report with headers like this:

Factory, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday

The day of week is taken from the field TimeOfSale which is a TDateTime field.

Detail rows can be sum of Quantity, or count of Quantity, for example. To see how much each Factory has sold, or how many sales has been done, on a certain day of week.


This could also be extended to for each day having sold Quantity per hour of day. Where hour is also taken from TimeOfSale. If working day starts at 7 AM it could look like this.

Day of week | Monday |....
Hour | 7 | 8 | 9 | 10 | ....
Count Quantity | 3 | 11 | 7 | 0 | .....


Hope you understand what I'm trying to achieve. Otherwise, please let me know and I'll try to explain it better.

[Sergey Tkachenko]

RW can create a crosstab where rows correspond to invoices, columns to days of weeks.
Something like this:

invcrosstab.png (2.6 KiB) Viewed 99986 times

Working hours can be added too.

However, it is simple to implement only if you use SQL with DATEPART() function. Otherwise, some modification in RW are needed to allow matching TDateTime with days of week and hour.

---

But I am afraid RW cannot create a crosstab where rows correspond to factories (summarizing invoices for each factory that were paid at the specific day/hour).

Let you try to make (factory x day of week) crosstab.
This means that if two invoices of the same factory are paid at the same day of week, two records are mapped to the same crosstab cell.
In this case, RW just ignores the second record (and OnError even is called with error code rvrgeCrossTabDuplicateRecords, but this error can be ignored).

---

Also, I am not sure what you want to display in crosstab cells (where "100" are shown in my example)

[Sergey Tkachenko]

Well, probably something may be done regarding (factory x days of week) crosstab.

Cell in crosstab may contain sub-reports. So, when we found what some factory has invoices in the specified day of week (that gives us a specific crosstab cell), in this cell we can execute a data query that calculates and displays information about all invoices of this factory in this day.

However, it is possible only if:
- this report is built only for one week (i.e. "Monday" is a specific day, not several Mondays), and
- you use SQL that supports BETWEEN on dates.
or
- you use SQL that supports DATEPART function

[TPerson]

I suppose I will have to create a temporary database table where the DateTime field has been separated into several other fields that can be used for different crosstabs: Year, Month, Day, Hour, Monthname, WeekdayName....
That way values for all Mondays in January can be summed up, correct?

I attach a more realistic example than my previous attempt.

Most often it is separate sums, or counts, of all invoices for one Factory at a time that is of interest.
There could be three factories belonging to region West, and five that belong to region South. But if my temporary database table contains a field Region, it wouldn't be a problem to create a crosstab sum of all sales in a Region instead of per Factory.

Thanks for taking your time.
Have a lovely weekend.

[Sergey Tkachenko]

Do you use SQL database? Does it support DATEPART function? If yes, "fields" can be created directly in a data query in RW.
Something like:

Code: Select all

select DATEPART(hour, TimeOfSale) as HourOfSale, DATEPART(weekday, TimeOfSale) as WeekDayOfSale ...

Otherwise, I can make improvements in RW.
Currently, to match records with crosstab columns, RW compares values of Table.CrossTabulation.Levels[].FieldName in records to values of columns.
It will not be too complicated to extend this functionality, allowing to specify an expression to compare with values of columns.
I.e., instead of a field name, this property may contain an expression like '=GetHour({TimeOfSale})'.

[Sergey Tkachenko]

Well, I remembered that RW has a very similar example, on sakila (film rental) database:
"<TRichView Dir>\ReportWorkshop\Demos\Reports\MySQL\sakila\Crosstab Customers x PG Rating.rw"

It displays a crosstab (customer x film rating), showing the count of films having the given rating ordered by the specific customer in cells.
The report has summary column (total count of films ordered by the given customer):

films-crosstab.png (39.34 KiB) Viewed 99950 times

However, to make this table, a non-trivial SQL select was used that calculates count of films having the given rating ordered by the specific customer.

Code: Select all

SELECT 
  customer.customer_id,
  customer.first_name,
  customer.last_name,
  film.rating, 
  count(film.film_id) as count
FROM <several tables are joined here>
GROUP BY customer.customer_id, film.rating

Source data for this report:

films-crosstab-data.png (34.66 KiB) Viewed 99950 times

So, if you want to build your first crosstab, you need to prepare a temporal table or a queue with fields:
- Factory
- DayOfWeekOfSale
- HourOfSale
- AmountSoldByThisFactoryInThisDayOfWeekAndThisHour

In advanced SQL versions, it's quite trivial to make SQL select like this on your data.
But for less advanced SQL versions, preparations of this table may be more difficult than building a cross-tab on it.

[Sergey Tkachenko]

As for the second table, it looks even more complicated.
It can be built only if you prepare a table or a query with the fields:
- Factory
- Month
- DayOfWeek
- Hour
- AmountSoldByThisFactoryInThisMonthAndThisDayOfWeekAndThisHour.

And even then, it will not look exactly like in your example: month name will be repeated in each row, because in RW rows of crosstab reports do not have hierarchy.
(but you can edit reports after generation)

[TPerson]

Thank you for all the information. I'll take a look at the film demo.

My project uses a Paradox database accessed with BDE, and the SQL for BDE is limited. Instead of DATEPART I can use EXTRACT, like this:
EXTRACT(month from TimeOfSale)
EXTRACT(hour from TimeOfSale)
But there is no EXTRACT(weekday from TimeOfSale), as far as I know. So some preparation of the data is needed. Also the numeric value returned by EXTRACT(month from TimeOfSale) needs to be converted to name of the month.

In your picture (films-crosstab.png) the cell with the word "Customer" takes up two rows. I understand this can be done because it's a header? Would it be hard to add a feature that makes something similar for detail rows containing the same value? Thus seven consecutive cells in a column with the word "January" becomes a single cell with that word? Of course this is not always desired, so it would have to be an option that can be specified for each column when designing the report. Just a thought I had.

Edited for spelling and making it clearer.

[Sergey Tkachenko]

If months will be represented by numbers (from 1 to 12) in DB, it will be enough. I'll add a function to RW expressions that will return the month name (in the system language) by its number.
RW expressions include many functions for date and time, but no functions related to days of weeks. It will be improved.

In the table on the picture, the structure of the table header is predefined.
Here is like a template for that report looks like:

films-crosstab-template.png (16.55 KiB) Viewed 99920 times

(crosstab header is highlighted by purple, data rows are highlighted by light blue)

The idea of combining cells of data rows containing identical content looks good. I can add this option in the next update.