crosstab semi dynamic columns with no totals? Â

[2006 IR Open Dicussion]

By: woodchuck - woodchuck5

crosstab semi dynamic columns with no totals? Â

2006-01-05 13:20

hihi,Â

Â

is it possible to create a crosstab report without any measure or total columns or rows?Â

Â

i need to build a crosstab report that looks like the following:Â

Â

Employee Salary Monday Wednesday Thursday FridayÂ

======== ====== ====== ========= ======== ======Â

George 10000 8.0 4.0 6.5Â

Sally 15500 7.5 8.0 5.5Â

Â

but the data looks like the following:Â

Â

Employee Salary Day HoursÂ

======== ====== =========== =====Â

George 10000 Monday 8.0Â

George 10000 Wednesday 4.0Â

George 10000 Friday 6.5Â

Sally 15500 Monday 7.5Â

Sally 15500 Thursday 8.0Â

Sally 15500 Friday 5.5Â

Â

as you can see, the crosstab report i want does not have any totals or aggregate information at all. and not all the columns are dynamic. for example the Employee and the Salary columns are static, while the rest of the columns are dynamic based on available data.Â

Â

i'm having a really hard time trying to figure out how to make a crosstab report since i'm not sure how to define the <crosstabCell> needed for my report. Â

Â

any help is appreciated!Â

Â

please and thanks!Â

woodchuck

 

 

 

 

By: Lucian Chirita - lucianc

RE: crosstab semi dynamic columns with no tot Â

2006-01-06 08:16

hiÂ

Â

You can create a Hours crosstab measure with calculation="Nothing" and Employee and Day groups with totalPosition="None".Â

Â

To get the Salary also, you could use the following trick: create a measure for Salary with calculation="Nothing", set totalPosition="Start" for the Day group and use this total column to display the Salary.Â

Â

Regards,Â

Lucian

 

 

 

 

By: woodchuck - woodchuck5

RE: crosstab semi dynamic columns with no tot Â

2006-01-06 11:38

hihi Lucian!Â

Â

thank you for your help! i will follow your instructions and give it a try. will let you know how it goes.Â

Â

best regards,Â

woodchuck

 

 

 

By: woodchuck - woodchuck5

RE: crosstab semi dynamic columns with no tot Â

2006-01-10 06:49

hihi Lucian,Â

Â

thanks again for your help. but after doing some more testing i believe crosstabs cannot do what i want.Â

Â

the problem is i have many columns that are always present (static) and also potentially many columns that are data-driven (dynamic). crosstabs does not allow this. currently, crosstabs assume all columns are 'grouped' and does not allow the addition of arbitrary non-grouped columns simultaneously. so i guess you can say my table is not a 100% crosstab table, but a sort of hybrid crosstab table.Â

Â

what i'm going to do is to create a table with a number of fixed columns, and populate it with data as it becomes available (so there may be empty cells at times). this is the next best thing i can think of with jasperreports.Â

Â

thanks again,Â

woodchuck

[tel2]

I have a similar problem. The best solution i can come up with is to embed cross tabs in a report, and set the total, and row header cells to have 0 size.

 

However, this puts the column header for the crosstabs on each row, which isn't very pretty. Has anyone come up with a better solution yet?

 

I've tried creating a custom data source to just return the data in the form that is needed. I think it will work in your case (with days of the week), but in my case I don't know the number of columns available (or what to call them).

 

The next best solution i can think of is to put the cross tab in a subreport with a drill down.

 

Here is to hoping someone can come up with a better solution.

[itchytoes]

Hi --

 

I'm the queen of table reports. I do lots and lots of reports with tables in various forms. In some cases, I'm lucky enough that I can get a query result set from the DB in such a way that every column is accounted for, and there may be null values for some columns, and I can deal with that.

 

Sometimes, if you are tricky with the SQL query, you can ensure that this happens. Sometimes, it just does not work and my result set will have rows that don't have data for each column and I cannot fill a crosstab.

 

I've thought about trying to gather that data into some sort of array as I go, and then use a subreport at the end to use the crosstab report, but I haven't done that yet.

 

Instead, I usually fill a HashMap or something as I go through the rows, then at the end of some group, I will output the whole row by displaying the various values in the hashmap. Thus, I don't use a crosstab, but I end up with a table where the HashMap values are the columns, and the keys are the column headers.

 

Sometimes I need a subreport in the title to load up a List with all the possible column headers in the right order.

 

Betty

 

 

Betty

[broschb]

I have run into the same problem, I just created a blog post that goes through solving this issue using a JRBeanCollectionDataSource, and specifying the columns at runtime. Check it out and let me know if you have any questions.

 

http://broschb.blogspot.com/2008/05/dynamic-jasper-report-using-crosstabs.html

[djmamana]

Hi there... A simple way to have such crosstab is to arrange the data in the datasource like this:

 

[Col1] [Col2] [Col3] George Salary 10000 George Monday 8.0 George Wednesday 4.0 George Friday 6.5 Sally Salary 15500 Sally Monday 7.5 Sally Thursday 8.0 Sally Friday 5.5

 

 

And tell the crostab that the dataset is pre-sorted.

 

You may need two queries to achieve that:

for example

 

Select name, 'salary', salary from employees

 

and

 

Select name, weekday, hours from working_days

 

Then make sure you have them sorted properly using as criteria: name and column 2 (the shared among the 'salary' legend and the week days, buuuuut with the special traeatment that the word "salary" must be at the beginin after the sort (we need a special sort)

 

 

Later, in iReport, tell to the crosstab that the data source is pre-sorted

 

That should be it

 

A hint to achieve the special sorting... just make a sub-select (using MySQL)

 

 

select name, SUBSTRING(col2,1), salary from ( Select name, '0salary' col2, salary from employees union Select name, '1' || weekday, hours from working_days ) order by name, col2

 

What we have done there is concatenate a "0" before thw word "salary" and a "1" before the weekdays

 

When sorting, "0salary < 1moday" (and all other "1<what-ever>" days)

In the outer select we just remove the leading "0" and leading "1" geting the clean value.

 

For practice, you can easyly try using DynamicJasper that lets creat crosstabs dynamically

 

Hope I have been of help.

bye bye

Dj

[jimsmith]

Sorry to dig up old topics, but has this changed any over time? I'm trying to find ways to group columns, and just hitting dead-ends. I want the table format that the crosstabs offer, but I can't use the totals. I understand that there are various work arounds, but I was hoping that this use case would have been worked into Jasper at some point.