Crosstab with static column

1

how can i make a static column/row in crosstab. For example below, can I have a fixed jan, feb, march, ... column instead of it generating dynamically?

location        jan        feb       mar      apr       may      jun      jul        aug      sep       oct      nov      dec 

london         500        62        200      50        100        60     100      46          89       200    150     210

paris            50          26         20       500        50        70       40       200        70       40       250     50

I want the column (jan, feb,  mar,apr,....)  to always show up regardless of their measures zero or have values. Like they are fixed.

 

Omorak_Taybeh's picture
Joined: Oct 4 2015 - 6:31am
Last seen: 4 years 1 month ago

3 Answers:

0

Hi Omorak,

Crosstabs create rows and columns based on the data. Ideally you would prepare your data so that you can do the minimum amount of effort in the report, e.g. via Jaspersoft ETL. A standard Table might suit you better if you want a predetermined design (although you'd have to calculate aggregated values via Variables, etc.).

In alternative you could create a database table with all the months in your Data Source and UNION it with your actual data. This would guarantee that at least one row is returned for each month. See this example (only for 3 months):

foodmart=# CREATE TABLE sales_months (month_id INT, MONTH VARCHAR);
CREATE TABLE
foodmart=# CREATE TABLE sales (location VARCHAR, month_id INT, amount INT);
CREATE TABLE
foodmart=#
foodmart=# INSERT INTO sales_months VALUES (1, 'jan');
INSERT 0 1
foodmart=# INSERT INTO sales_months VALUES (2, 'feb');
INSERT 0 1
foodmart=# INSERT INTO sales_months VALUES (3, 'mar');
INSERT 0 1
foodmart=# INSERT INTO sales VALUES ('london', 1, 500);
INSERT 0 1
foodmart=# INSERT INTO sales VALUES ('london', 3, 200);
INSERT 0 1
foodmart=# INSERT INTO sales VALUES ('paris', 1, 50);
INSERT 0 1
foodmart=#
foodmart=# SELECT NULL AS location, month_id, MONTH, NULL AS amount FROM sales_months
foodmart-# UNION ALL
foodmart-# (SELECT s.location, s.month_id, sm.month, s.amount FROM sales s
foodmart(# JOIN sales_months sm ON s.month_id = sm.month_id
foodmart(# ORDER BY s.location, s.month_id);
location | month_id | MONTH | amount
----------+----------+-------+--------
          |        1 | jan   |      
          |        2 | feb   |      
          |        3 | mar   |      
 london   |        1 | jan   |    500
 london   |        3 | mar   |    200
 paris    |        1 | jan   |     50

Unfortunately this also creates an empty row in the Crosstab, which I "hid" via "Print When Expression" equal to "$V{location1} != null", "Remove Line When Blank" and "Blank When NULL" on all the elements of the row, but it stil leaves an empty space which I've been unable to remove. I also enabled the "Data Pre Sorted" flag in the Crosstab Dataset properties.

This will not occur if you use a value that you know is always returned by your query, e.g. location 'paris'. Then you would use SELECT 'paris' AS location at the beginning of the query above.

This is the end result:

kkumlien's picture
2866
Joined: Jan 22 2015 - 4:36am
Last seen: 3 days 12 hours ago
0

As far as I know there is no way to do that on editor.Because it processes what is coming from your query as result.But you can achieve that by editing your query. you can add a cross join a table, which holds the month's names, in your query.

zh3ntil's picture
541
Joined: Nov 19 2014 - 11:02pm
Last seen: 3 years 4 months ago
0

To make a fixed header for a crosstab doing sums, you need to supply the values that are missing in the source data. The solutions is this: Perform a difference operation between [1,2,3…12] and distinct(month) to find the missing months (for instance, they are, say, 3 and 5). Then fill up the values of those missing months in the source data.

Here I use esProc to do the job (code is simpler). Here’s the esProc code:

A1=$select month,location,value from tb

A2=to(12)\A7.id(month)

A3=A1|A2.new(~:month,A1.location:location,null:value)

Jasper can access the esProc code via JDBC. This is similar to its calling of a database.

calculate.machine's picture
Joined: Jan 13 2015 - 11:40pm
Last seen: 3 years 11 months ago
Feedback
randomness