Creating a crosstab

#1 · 8. July 2024, 21:33

Hello, new here but this looks like a cool project, glad Jasper is being continued.

I need some help with a project I am working. I'm not sure this is even possible but I figured I would ask. I'm currently trying to create "stacked" or "double" crosstab (I will explain this later) but I'm not quite sure how to get the data to work. Currently this is what my data looks like, it simply stores instances of an event. I would need to find some way to count these instances and generate fields with them.

The biggest problem is that the names change from match to match and can't really be read statically. I need some way to dynamically read the values and names.

Here's a mock-up of what I want it to look like. If it's not possible to have the X and Y values on the same table, I could also use two different tables though this solution would be less preferred.

Is what I want to do even possible?

#2 · 9. July 2024, 7:57

Moved that to Jasperreports User Doc / General questions

#3 · 9. July 2024, 16:23

@tr4k: your first screenshot was damaged... could you please reupload that?

THX,

Thomas

#4 · 9. July 2024, 17:21

Hi, Thomas.

Here it is:

#5 · 9. July 2024, 20:17

You could have edited your post and repost the screenshot there ... 😉

Indeed, that seems more like an sql task than a jasperreports task to me; something like:

SELECT

"Killer Team" AS 'ROWS',

"Victim Teams" AS 'COLUMNS',

COUNT(*) AS 'KILLS1',

0 AS 'KILLS2'

GROUP BY "Killer Team", "Victim Teams"

UNION ALL

SELECT

"Victim Team" AS 'ROWS',

"Killer Teams" AS 'COLUMNS',

0 AS 'KILLS1',

COUNT(*) AS 'KILLS2'

GROUP BY "Killer Team", "Victim Teams"

(or at least similar to this)

Then set up a crosstab with "ROWS" and "COLUMNS" and create two measures for the count of "ROW -> COLUMN" (KILLS1) and another measure for the count of "COLUMNS -> ROW" (KILLS2)

That should to the job, right?

Have fun!

Thomas

http://jasper-schulungen.de

#6 · 21. July 2024, 10:39

Hi, sorry for the extremely late reply; I kinda forgot that I had posted this here. I managed to solve the issue independently but I'll detail how I did it here in case anyone else wants to do something similar.

I decided to use row number to generate a number for each player then combined them with letter to generate player IDs as A1-5 and B1-5.

Then I used an enormous select statement to count cases when each ID fragged another and summarised them to create fields.

Then I just combined them into the 25 text fields for the crosstab. With _name fields populating the first row / column

And voila! The result is what I intended:

Jasper Forum

Creating a crosstab