I'm trying to create a report based on CSV data using multiple CSV files with Jaspersoft Studio 6.3.1. Basically I'd like to join different CSV files with some common keys in their respective data sets and report accordingly.
Firstly, when I create a CSV adapter, I can't see where I can run any SQL for the data set so I can't really see any reasonable way of joining the data set. Am I missing something here?
I think I could create a sub report and link that way, however, what If I have 4 table joins? 3 embedded sub reports? Doesn't look a very good solution to me.
I'm only using the free versions so far, but if I'd like to know If I upgrade to a higher grade products could I set up domains and virutal data sources to join say 4 tables based on CSV data inputs. Is that possible?
Thanks in advance,
Jasper doesn't provide an SQL engine -- SQL queries are passed to the underlying database to be executed there. I wonder if you could use a third-party tool such as csvkit (https://csvkit.readthedocs.io/en/749/index.html) to create a joined file and use that as your data source. (I haven't tried csvkit, so I don't know if this is the right tool.)
The underlying engine for virtual data sources is Teiid. If you can create a Teiid query to join csv files, then you may be able to do that in Jaspersoft (I don't know how much of the Teiid implementation can just pass through, I'm pretty sure this has not been explicitly implemented.) However, domains are relatively heavy-weight and will almost certainly have poorer performance than if you can join the CSV files before bringing them into Jasper.