2006 IR Open Dicussion Posted August 26, 2006 Share Posted August 26, 2006 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 Link to comment Share on other sites More sharing options...
tel2 Posted January 18, 2008 Share Posted January 18, 2008 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. Link to comment Share on other sites More sharing options...
itchytoes Posted January 19, 2008 Share Posted January 19, 2008 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 Link to comment Share on other sites More sharing options...
broschb Posted May 22, 2008 Share Posted May 22, 2008 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 Link to comment Share on other sites More sharing options...
djmamana Posted June 27, 2008 Share Posted June 27, 2008 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 15500Sally 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 byeDj Link to comment Share on other sites More sharing options...
jimsmith Posted June 13, 2011 Share Posted June 13, 2011 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now