Crosstab with static column

[Omorak_Taybeh]

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.

 

[kkumlien]

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 TABLEfoodmart=# CREATE TABLE sales (location VARCHAR, month_id INT, amount INT);CREATE TABLEfoodmart=#foodmart=# INSERT INTO sales_months VALUES (1, 'jan');INSERT 0 1foodmart=# INSERT INTO sales_months VALUES (2, 'feb');INSERT 0 1foodmart=# INSERT INTO sales_months VALUES (3, 'mar');INSERT 0 1foodmart=# INSERT INTO sales VALUES ('london', 1, 500);INSERT 0 1foodmart=# INSERT INTO sales VALUES ('london', 3, 200);INSERT 0 1foodmart=# INSERT INTO sales VALUES ('paris', 1, 50);INSERT 0 1foodmart=#foodmart=# SELECT NULL AS location, month_id, month, NULL AS amount FROM sales_monthsfoodmart-# UNION ALLfoodmart-# (SELECT s.location, s.month_id, sm.month, s.amount FROM sales sfoodmart(# JOIN sales_months sm ON s.month_id = sm.month_idfoodmart(# 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:

[zh3ntil]

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.

[calculate.machine]

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.