Hi David,

The crosstab is great, however I found that the columns sort according to the sequence values are found int he data - this can lead to columns out of sequence where values may be missing within a row category.

To workaround this
- I made a cross join table of all possible values, with 0 count
- I then used this table in a UNION select
- wrapped the whole thing in a GROUP BY query with an ORDER BY

The crosstab then shows all rows and columns in order, with 0 values where they were previously missing.

However all this wouldn't be necessary i it was possible to sort the crosstab rows and columns.
Please advise if this is possible.

Thanks.

CommentFileSizeAuthor
#8 crosstab_complete.png37.28 KBMickC
#7 crosstab.png19.22 KBMickC

Comments

metzlerd’s picture

metzlerd commented

I think I might need more information about what you are asking for. It's true that crosstabs are somewhat order sensitive, but what you say you had to accomplish seems extreme. Adding an orderby clause seems normal, but needing a UNION? well that seems a bit extreme, and it's unclear to me how sorting the data will help that problem. Would you mind posting an example of the data and perhaps an example of the crosstab syntax that you're asking for?

Pierre.Vriens’s picture

Pierre.Vriens commented
Status: Active » Postponed (maintainer needs more info)
MickC’s picture

MickC commented

If the data is 'naturally' missing, the cross tab will produce a null - it falls down the sort order - adding a union query with all 0 values means it's 0 and not null so there's a row. Then the cross tab will show 0 and not null. So it will sort all values in order.

Otherwise the problem is like this - say you have to cross tab data:

2011 C 5
2012 A 5
2012 B 5
2013 A 5
2013 B 5
2013 C 5
2014 A 5
2014 B 5
2014 C 5

What would a cross tab look like?

Year C A B
2011 5 null null
2012 null 5 5
2013 5 5 5
2014 5 5 5

Problem is that because C is the first cross tab value within the Year sort order, this column appears first 'naturally'.

By creating a union query of all Year/ABC combinations, then wrapping in a group by - we can create a complete table - then the sort order works.

However it would be good to sort cross tab values without them having to need all values for all labels.

Pierre.Vriens’s picture

Pierre.Vriens commented
Status: Postponed (maintainer needs more info) » Active
metzlerd’s picture

metzlerd commented

OK, I begin to see the use case for this now. You really don't need the whole data sorted, but just the dimensions that come from the crosstab. I can work on this.

FYI: I think one of the issues here is that I would've never thought to use union to get the values that you were talking about. I rather would've used a LEFT JOIN on the group by that created the data. Assuming that there were tables that housed years and the categories A B C. Anyway I'll trust that both are possible ways of getting the same data, but the union confused me.

MickC’s picture

MickC commented

The UNON was simply to create 0 values for every combination of dimensions, so that there were no nulls, therefore the dimensions sorted 'naturally'.
A LEFT JOIN might not work if not every combination existed - e.g. "2011 A" in the example above.
The union query contained "2011 A 0" so the raw table looks like

2011 A 0
2011 B 0
2011 C 5
...

And the crosstab looks like

Year A B C
2011 0 0 5
...

Perhaps a cross join between a query of DISTINCT YEAR and DISTINCT TYPE, then LEFT JOIN to the data - then a coalesce on the value field so it's not null?
Not sure, but I can try that.

Anyway - sorting the dimensions might make this unnecessary.

MickC’s picture

MickC commented
StatusFileSize
new crosstab.png19.22 KB

Hi David - the issue of crosstabs is coming up again for 'Stacked Bar Chart' where not every value exists, e.g.

The series renders, but the colours 'swap' series according to what values appear.
i.e. if only series 3 has a value, it will appear in series 1 colours.

The only workaround I can think of is the sum of a union query to ensure every possible combination exists, albeit with a 0 and not null. Then the cross tab will be complete.

Is there any way to populate a crosstab with 0 and not null?

Thanks

MickC’s picture

MickC commented
StatusFileSize
new crosstab_complete.png37.28 KB

FYI this is what the crosstab data for the stacked column chart looks like with all non null values, based on summary of the union query:

The column colours are all consistent now across all time periods.

metzlerd’s picture

metzlerd commented

Yes... So we'll need to make a new configuration option to do this in the graph. I just got done making sure forena did not do the substitions you need because they create problems with artificial zero points in line graphs. I'll get to this when I can but it probably won't be for a week or more.

MickC’s picture

MickC commented

Sounds good, thanks David

janeks’s picture

janeks commented

This is the same problem for me (https://www.drupal.org/node/2849798) - sorting and zeros instead of null.
Are there any solution from Forena side?

janeks’s picture

janeks commented

The problem is the same for simple crosstab tables (not only charts) like it is shown in pics of posts #7 and #8.
I would like to manage what to see when there is null and when there zero, because in my case, I will have plenty of reports, where total number of all possible combinations of dimension and group values would be rather big. While combinations of meaningful (non null/zero) values will be much less, even multiple times. I can of course write sql that fills all possible null or zero value combinations with whatever I like, but it requires to create additional select queries for joins or unions and requires more data to travel from database. It could make sense in some cases for performance.

I see that this feature (to control nulls and/or zeros) is good to keep sql data blocks simple.

While writing this I started to think about idea, that probably I could create cross tab report by using grouping functions of Forena... and/or that it could be nice if I can define crosstab dimension values from separate datablock...

metzlerd’s picture

metzlerd commented

#12 doesn't really make sense to me in terms of defining crosstab dimentions in another data block. You create a joining problem that is better handled in SQL than XML anyway.

I could see defining it as an array parameter in reports, which would limit the crosstab to specific values and prefill the counts with zero, but that seems to have pretty limited usefulness.

The whole point of crosstabs was to transform SELECT GROUP BY /ROLLUP espressions to a pivoted table. If you want something that is different that a traditiional pivot table a separate control should be built.

Remember that if you are trying to make sure all values are joined and you know what the values are a sql statement like:

I'm sorry to say that I really don't know when I will get to this, as it is a significant undertaking. If someone wants to hire a developer to work on this, I'd recommend contacting me offline so that we can discuss strategy and also making sure the changes get in Drupal 8 which is where all new features are being developed first.