Jump to content

Crosstab with static column


Recommended Posts

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.


Link to comment
Share on other sites

  • Replies 3
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Posted Images

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:


Link to comment
Share on other sites

  • 1 month later...

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



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

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...