Home
Analytics
Report using CVS file
new member
Hi,
I have a requirement to create the BIRT report using CSV file and I get that CSV file unformatted.
Please let me know how to do this.
Thanks in advance,
Find more posts tagged with
Comments
new member
Hi,
We have reports using Actuate Report tool and converting those reports into BIRT reports and I have a scenario where I have use Unformatted CSV file as a datasource and produce the report in BIRT.
I figured out how to use CSV file using flat file but I don't know how to use the unformatted CSV and convert it to formatted way and use that as a datasource.
I really need to solve this ASAP Please help me solve it.
Thank you so much in advance
new member
Hi Jason,
Could you take a peek at my request and suggest me something.
Thank you
mwilliams
Can you explain a little more about your unformatted file that needs to be formatted? If you know the layout of the unformatted file, you could use a scripted dataSet to bring the data in.
new member
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="75109" data-time="1301331130" data-date="28 March 2011 - 09:52 AM"><p>
Can you explain a little more about your unformatted file that needs to be formatted? If you know the layout of the unformatted file, you could use a scripted dataSet to bring the data in.<br /></p></blockquote>
<br />
<br />
Hi William,<br />
<br />
Thanks for the responce,<br />
<br />
Here is my scenario<br />
<br />
1: I get the csv file which is ";"(Semicolon) separeted(I can make it comma seperated too)<br />
2: I need to use that file as a datasource and create the report and in that report first row should be the heading and remianing ones are the result.<br />
3: csv file is dynamicaly gererated file and it has max number of rows but i get the result in that file based on the search cretiria.<br />
<br />
I have attched example .txt(I could not upload the csv file) file.<br />
Please help me.<br />
<br />
Thank you in advance
mwilliams
What do you mean by unformatted? No line breaks?
new member
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="75121" data-time="1301340774" data-date="28 March 2011 - 12:32 PM"><p>
What do you mean by unformatted? No line breaks?<br /></p></blockquote>
<br />
Hi William,<br />
<br />
Thanks for the responce,<br />
<br />
Here is my scenario<br />
<br />
1: I get the csv file which is ";"(Semicolon) separeted(I can make it comma seperated too)<br />
2: I need to use that file as a datasource and create the report and in that report first row should be the heading and remianing ones are the result.<br />
3: csv file is dynamicaly gererated file and it has max number of rows but i get the result in that file based on the search cretiria.<br />
<br />
Please help me.<br />
<br />
Thank you in advance
mwilliams
So, the issue is that there are more spaces in the title row than in the other rows? Or am I still missing something? What do you want the report to look like coming from the attached ssv files?
new member
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="75134" data-time="1301347353" data-date="28 March 2011 - 02:22 PM"><p>
So, the issue is that there are more spaces in the title row than in the other rows? Or am I still missing something? What do you want the report to look like coming from the attached ssv files?<br /></p></blockquote>
<br />
I Think I am not explainig it properly.<br />
<br />
Here is my scenario<br />
<br />
I get the dynamically genareted csv file(abc<currenttimestamp>.csv) which contains the data based on the search criteria.<br />
<br />
Ex: <br />
1:If user selects "ALL" for Region and "ALL" for airport I get the csv file with all the Regions and all the airports with data with Region and Airport headings(Which also dynamically generated)<br />
<br />
2: If user select a particular Region and particular Airport i get the csv which has only that region and that airport other fields will be "null"<br />
<br />
I have to use that csv file and create a report like first row as a header and remaining result as a detail.<br />
<br />
I hope i explained it well
<br />
<br />
Thank you
mwilliams
So, if "all" was selected, you'd get something like:
Field;NorthRegion;SouthRegion;EastRegion;WestRegion
Average;1;2;3;4
Total;2;4;6;8
and if just "north" was selected, you wouldn't get the last 3 columns? Is this correct? Or would the last 3 columns just be represented by blank spaces and semicolons?
Then, are the regions the only variable columns? Any other column is the same no matter what the selection?
If there are blank spaces left for column names, you might run into an issue with using a flat file datasource, if the column name row is always there, you might be able to just bring in the whole flat file, then hide the unneeded columns of the table based on the parameter that was used to create the file, which could be passed into BIRT in a hidden parameter.
If there are no blank spaces, you could potentially use the hidden parameter with the airport selections in it to build you table in script and edit the "queryText" for your dataSet to only expect columns for the airports in the hidden parameter.
The last option I see is that you'd be able to use a scripted dataSource to step through your file to arrange your dataSet how you'd like.
new member
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="75163" data-time="1301409116" data-date="29 March 2011 - 07:31 AM"><p>
So, if "all" was selected, you'd get something like:<br />
<br />
Field;NorthRegion;SouthRegion;EastRegion;WestRegion<br />
Average;1;2;3;4<br />
Total;2;4;6;8<br />
<br />
and if just "north" was selected, you wouldn't get the last 3 columns? Is this correct? Or would the last 3 columns just be represented by blank spaces and semicolons?<br />
<br />
Then, are the regions the only variable columns? Any other column is the same no matter what the selection?<br />
<br />
If there are blank spaces left for column names, you might run into an issue with using a flat file datasource, if the column name row is always there, you might be able to just bring in the whole flat file, then hide the unneeded columns of the table based on the parameter that was used to create the file, which could be passed into BIRT in a hidden parameter.<br />
<br />
If there are no blank spaces, you could potentially use the hidden parameter with the airport selections in it to build you table in script and edit the "queryText" for your dataSet to only expect columns for the airports in the hidden parameter.<br />
<br />
The last option I see is that you'd be able to use a scripted dataSource to step through your file to arrange your dataSet how you'd like.<br /></p></blockquote>
<br />
<br />
Yes,If i select "ALL" I get all the records and If i select only north i get only that field with the remaining one as "null" and I need to show the report how i get the resumt in csv file.<br />
<br />
Ex: <br />
<br />
1:Selecting "ALL"<br />
<br />
Result:<br />
<br />
;North;South;East;West<br />
Average;1;2;3;4<br />
Total;2;4;6;8<br />
<br />
My report should be like this:<br />
<br />
North South East West<br />
Average 1 2 3 4<br />
Total 2 4 6 8<br />
<br />
2: Selcting "North"<br />
<br />
Result:<br />
<br />
;North;null;null;null<br />
Average;1;;;;<br />
Total;2;;;;<br />
<br />
My report should be like this:<br />
<br />
North <br />
Average 1 <br />
Total 2 <br />
<br />
And I need make the first column in different color(As a heading) and the remanining columns would be in another color.<br />
<br />
I really appriciate your help on this.<br />
<br />
Thank you in advance
mwilliams
Do you or can you have access to the value selected to create the flat file? Whether it's "all" or "north"?
new member
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="75185" data-time="1301416378" data-date="29 March 2011 - 09:32 AM"><p>
Do you or can you have access to the value selected to create the flat file? Whether it's "all" or "north"?<br /></p></blockquote>
<br />
<br />
Yes, I do have access.<br />
<br />
Thanks
mwilliams
You couldn't bring in the file with the checkbox for the first row being the column name as is, you'd have to rework the file somehow before passing it into BIRT if you want to do this. BIRT doesn't handle having multiple columns with the same name. In your case, multiple "null" names One simple way around this would be when you create your ssv file to put the header row completely and just leave nulls for the actual values, then you'd just have to build your table in script based on the user parameter.
You could unselect that option and just bring in the data as is and assume the first row as the headers and work with it that way. You'd just use aggregations in the table header to display the "FIRST" row to make it the header row, then you'd just hide the first actual row of the table. You could create the table and bindings based on the parameter that shows the user selections.
Another way like I said before would be to use a scripted dataSet since you know how many columns you'll need to grab from the flat file based on the user parameter.
new member
Hi Michele,
Thank you for your support.
I am able to build the report using csv file which will be created dynamically from the parameter as long as I have selected "ALL" and getting problme when I select the particular region( EX: North) bec's I get any that column in my csv file.
I need to show the columns based on the result from csv.
Can you suggest me how to handle this scenario.
Again, Thanks a lot for your support.
mwilliams
If you don't define the entire "columnName" row, you will run into issues if you don't use "all" because you will have errors with multiple null named columns. The way you'll have to handle this is by changing your CSV file creation to always fill the columnName row or to run your file through a function to clean your file. Or do one of the other options I listed above.
new member
Hi Michel,
I am facing a different problme and not able to solve it. It's really great help if you could let me know the solution.
I am able to upload the csv file and created the table but I am getting error if the first column is blank in the cvs file and i tried to add some test in that column while creating the csv file but still I am getting the same problme.
Can you let me know why I am getting the error
Thank you in advance,
mwilliams
It's probably because in turn, the column name is "", which might not be acceptable by BIRT. Are you getting an error? Can you post the error stack in here?
new member
Thanks for the quick responce Micheal,
This is the error I am getting. If I open that csv and add some text in that column It's working properly.
Table (id = 484):
- Can not load the report query: 484. Errors occurred when generating the report document for the report element with ID 484.
Error.ReportQueryLoadingError2 ( 1 time(s) )
detail : org.eclipse.birt.report.engine.api.EngineException: Can not load the report query: 484. Errors occurred when generating the report document for the report element with ID 484.
at org.eclipse.birt.report.engine.data.dte.DataPresentationEngine.doExecuteQuery(DataPresentationEngine.java:160)
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:265)
at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1876)
at org.eclipse.birt.report.engine.internal.document.v4.ReportItemExecutor.executeQuery(ReportItemExecutor.java:412)
at org.eclipse.birt.report.engine.internal.document.v4.TableItemExecutor.doExecute(TableItemExecutor.java:72)
at org.eclipse.birt.report.engine.internal.document.v4.ReportItemExecutor.execute(ReportItemExecutor.java:294)
at org.eclipse.birt.report.engine.internal.document.v4.ContainerExecutor.prepareChildExecutor(ContainerExecutor.java:226)
at org.eclipse.birt.report.engine.internal.document.v4.ContainerExecutor.hasNextChild(ContainerExecutor.java:110)
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.hasNextChild(WrappedReportItemExecutor.java:86)
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.hasNextChild(WrappedReportItemExecutor.java:86)
at org.eclipse.birt.report.engine.api.impl.RenderTask$ReportExecutorWrapper.hasNextChild(RenderTask.java:782)
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM$1.hasNextChild(HTMLPageLM.java:68)
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:62)
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:90)
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:99)
at org.eclipse.birt.report.engine.api.impl.RenderTask$PageRangeRender.render(RenderTask.java:662)
at org.eclipse.birt.report.engine.api.impl.RenderTask.render(RenderTask.java:284)
at org.eclipse.birt.report.service.ReportEngineService.renderReport(ReportEngineService.java:1537)
at org.eclipse.birt.report.service.BirtViewerReportService.getPage(BirtViewerReportService.java:204)
at org.eclipse.birt.report.service.actionhandler.AbstractGetPageActionHandler.doExecution(AbstractGetPageActionHandler.java:238)
at org.eclipse.birt.report.service.actionhandler.AbstractGetPageActionHandler.__execute(AbstractGetPageActionHandler.java:105)
at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:90)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseDocumentProcessor.__executeAction(AbstractBaseDocumentProcessor.java:47)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.executeAction(AbstractBaseComponentProcessor.java:143)
at org.eclipse.birt.report.soapengine.processor.BirtDocumentProcessor.handleGetPage(BirtDocumentProcessor.java:87)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.process(AbstractBaseComponentProcessor.java:112)
at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingImpl.getUpdatedObjects(BirtSoapBindingImpl.java:66)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397)
at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186)
at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323)
at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)
at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)
at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)
at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454)
at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281)
at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doPost(BirtSoapMessageDispatcherServlet.java:265)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:816)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:917)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:122)
at org.apache.catalina.core.ApplicationFilterChain.servletService(ApplicationFilterChain.java:398)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:185)
at org.eclipse.birt.report.filter.ViewerFilter.doFilter(ViewerFilter.java:68)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:217)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:185)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:275)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:255)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:188)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:586)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:556)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:187)
at org.apache.catalina.core.StandardPipeline.doInvoke(StandardPipeline.java:586)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:556)
at com.sun.webserver.connector.nsapi.NSAPIProcessor.service(NSAPIProcessor.java:160)
mwilliams
Well, it looks like the issue is with the table and not with the dataSet, so the data must be making it in, but the table is unable to bind it with no name for the first column. Try creating an "alias" for the unnamed column in your dataSet and create the binding for that column calling the alias rather than trying to use the blank column name.
new member
Hi Micheal,
I have created the query like below but it didn't work. I am still getting the error.Could you let me know what i am missing that would be great help for me.
I am getting the problme with COLUMN_0. First cell is empty in the csv and I have tried adding some text from my java code which did not work and I have created the query like below with "alias" but still not working.
this.queryText = "SELECT "
this.queryText = this.queryText + "COLUMN_0 as COLUMN_0, "
this.queryText = this.queryText + "COLUMN_1 as COLUMN_1, "
this.queryText = this.queryText + "COLUMN_2 as COLUMN_2, "
this.queryText = this.queryText + "COLUMN_3 as COLUMN_3, "
this.queryText = this.queryText + "COLUMN_4 as COLUMN_4, "
this.queryText = this.queryText + "COLUMN_5 as COLUMN_5, "
this.queryText = this.queryText + "COLUMN_6 as COLUMN_6 "
+ " FROM " + params["csvFile"].value
Thank you in advance
mwilliams
When I said set the alias, I meant that there's a setting under "output columns" in your dataSet editor to set an alias for each column. I haven't found the way to set this in script yet, though, but you might be able to do it from the editor and the query change you make may not matter. The other thing I see, is when I bring in a column without a name, it is named automatically "UNNAMED_1". Maybe this will help for when you go to call your column in your table.
If none of this helps. Please create a SSV file and a report that uses it the way you're using it and attach it in here so I can run it and see what you're doing exactly. You'll probably have to zip them up to attach a ssv file.
new member
<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="75485" data-time="1302023488" data-date="05 April 2011 - 10:11 AM"><p>
When I said set the alias, I meant that there's a setting under "output columns" in your dataSet editor to set an alias for each column. I haven't found the way to set this in script yet, though, but you might be able to do it from the editor and the query change you make may not matter. The other thing I see, is when I bring in a column without a name, it is named automatically "UNNAMED_1". Maybe this will help for when you go to call your column in your table.<br />
<br />
If none of this helps. Please create a SSV file and a report that uses it the way you're using it and attach it in here so I can run it and see what you're doing exactly. You'll probably have to zip them up to attach a ssv file.<br /></p></blockquote>
<br />
Hi Micheal,<br />
<br />
I tried that too but it didn't work.<br />
I have attached the csv file and sample report <br />
<br />
Thank you
mwilliams
From this design, I'm not sure of what you're trying to do. You're not building the table from scratch based on an input parameter like I thought you were going to do. Also, you have a column name for every column of your csv file. I thought that at least the first one was going to come in blank. Is this how your file will now come to you? Please clear up my confusion to what direction you're heading. Thanks!
new member
Hi Micheal,
Here is what I am doing:
Since I get the first row dynamically I have created a static row with "COLUMN_0,COLUMN_1...." and using those I created DataSet and Once I get the result from that csv I make that first row invisible and the next row as a header.
test,MW,NE,SE,SW,WEST,TOTAL is dynamically genarated text it won't be the same all the time.
And I am having issue with first column from this row(test). When I try to modify that and save it manullay It's working fine but when it generates dynamically It's not recognizing.
mwilliams
So, if "test" is not there, you have an issue?
new member
If I run it from my web page(Which creates the csv dynamically) even if i have that text("test" or blank) or not it's not working and when I open that csv and modify that column in the csv itself manually and save it and use that csv it's working.
mwilliams
So, what does the CSV file that is created by your application look like? Does it still have the "COLUMN_0", "COLUMN_1", etc row? If so, I'm guessing the error is actually coming with the "query" you're trying to use. Are you using the query text that's in your beforeOpen in the report you gave me? "select * from " params[paramName]? I think your query text needs to be in the following format:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
select "COLUMN_0", "COLUMN_1", "COLUMN_2", "COLUMN_3", "COLUMN_4", "COLUMN_5", "COLUMN_6", "COLUMN_7", "COLUMN_8", "COLUMN_9", "COLUMN_10", "COLUMN_11", "COLUMN_12", "COLUMN_13", "COLUMN_14", "COLUMN_15", "COLUMN_16", "COLUMN_17", "COLUMN_18", "COLUMN_19", "COLUMN_20", "COLUMN_21", "COLUMN_22", "COLUMN_23", "COLUMN_24", "COLUMN_25", "COLUMN_26", "COLUMN_27", "COLUMN_28", "COLUMN_29" from "sample.txt" : {"COLUMN_0","COLUMN_0",STRING;"COLUMN_1","COLUMN_1",STRING;"COLUMN_2","COLUMN_2",STRING;"COLUMN_3","COLUMN_3",STRING;"COLUMN_4","COLUMN_4",STRING;"COLUMN_5","COLUMN_5",STRING;"COLUMN_6","COLUMN_6",STRING;"COLUMN_7","COLUMN_7",STRING;"COLUMN_8","COLUMN_8",STRING;"COLUMN_9","COLUMN_9",STRING;"COLUMN_10","COLUMN_10",STRING;"COLUMN_11","COLUMN_11",STRING;"COLUMN_12","COLUMN_12",STRING;"COLUMN_13","COLUMN_13",STRING;"COLUMN_14","COLUMN_14",STRING;"COLUMN_15","COLUMN_15",STRING;"COLUMN_16","COLUMN_16",STRING;"COLUMN_17","COLUMN_17",STRING;"COLUMN_18","COLUMN_18",STRING;"COLUMN_19","COLUMN_19",STRING;"COLUMN_20","COLUMN_20",STRING;"COLUMN_21","COLUMN_21",STRING;"COLUMN_22","COLUMN_22",STRING;"COLUMN_23","COLUMN_23",STRING;"COLUMN_24","COLUMN_24",STRING;"COLUMN_25","COLUMN_25",STRING;"COLUMN_26","COLUMN_26",STRING;"COLUMN_27","COLUMN_27",STRING;"COLUMN_28","COLUMN_28",STRING;"COLUMN_29","COLUMN_29",STRING}
</pre>
This is the query from the XML Source if I just use the CSV file you provided to run the report.
new member
This is the query I wrote in beforeOpen
this.queryText = "SELECT "
this.queryText = this.queryText + "COLUMN_0 as COLUMN_0, "
this.queryText = this.queryText + "COLUMN_1 as COLUMN_1, "
this.queryText = this.queryText + "COLUMN_2 as COLUMN_2, "
this.queryText = this.queryText + "COLUMN_3 as COLUMN_3, "
this.queryText = this.queryText + "COLUMN_4 as COLUMN_4, "
this.queryText = this.queryText + "COLUMN_5 as COLUMN_5, "
this.queryText = this.queryText + "COLUMN_6 as COLUMN_6, "
this.queryText = this.queryText + "COLUMN_7 as COLUMN_7, "
this.queryText = this.queryText + "COLUMN_8 as COLUMN_8, "
this.queryText = this.queryText + "COLUMN_9 as COLUMN_9, "
this.queryText = this.queryText + "COLUMN_10 as COLUMN_10, "
this.queryText = this.queryText + "COLUMN_11 as COLUMN_11, "
this.queryText = this.queryText + "COLUMN_12 as COLUMN_12, "
this.queryText = this.queryText + "COLUMN_13 as COLUMN_13, "
this.queryText = this.queryText + "COLUMN_14 as COLUMN_14, "
this.queryText = this.queryText + "COLUMN_15 as COLUMN_15, "
this.queryText = this.queryText + "COLUMN_16 as COLUMN_16, "
this.queryText = this.queryText + "COLUMN_17 as COLUMN_17, "
this.queryText = this.queryText + "COLUMN_18 as COLUMN_18, "
this.queryText = this.queryText + "COLUMN_19 as COLUMN_19, "
this.queryText = this.queryText + "COLUMN_20 as COLUMN_20, "
this.queryText = this.queryText + "COLUMN_21 as COLUMN_21, "
this.queryText = this.queryText + "COLUMN_22 as COLUMN_22, "
this.queryText = this.queryText + "COLUMN_23 as COLUMN_23, "
this.queryText = this.queryText + "COLUMN_24 as COLUMN_24, "
this.queryText = this.queryText + "COLUMN_25 as COLUMN_25, "
this.queryText = this.queryText + "COLUMN_26 as COLUMN_26, "
this.queryText = this.queryText + "COLUMN_27 as COLUMN_27, "
this.queryText = this.queryText + "COLUMN_28 as COLUMN_28, "
this.queryText = this.queryText + "COLUMN_29 as COLUMN_29"
+ " FROM " + params["csvFile"].value
This is what i see in xml
select "COLUMN_0", "COLUMN_1", "COLUMN_2", "COLUMN_3", "COLUMN_4", "COLUMN_5", "COLUMN_6", "COLUMN_7", "COLUMN_8", "COLUMN_9",
"COLUMN_10", "COLUMN_11", "COLUMN_12", "COLUMN_13", "COLUMN_14", "COLUMN_15", "COLUMN_16", "COLUMN_17", "COLUMN_18", "COLUMN_19",
"COLUMN_20", "COLUMN_21", "COLUMN_22", "COLUMN_23", "COLUMN_24", "COLUMN_25", "COLUMN_26", "COLUMN_27", "COLUMN_28", "COLUMN_29"
from "sample.csv" : {"COLUMN_0","COLUMN_0",STRING;"COLUMN_1","COLUMN_1",STRING;"COLUMN_2","COLUMN_2",STRING;"COLUMN_3",
"COLUMN_3",STRING;"COLUMN_4","COLUMN_4",STRING;"COLUMN_5","COLUMN_5",STRING;"COLUMN_6","COLUMN_6",STRING;"COLUMN_7","COLUMN_7",STRING;
"COLUMN_8","COLUMN_8",STRING;"COLUMN_9","COLUMN_9",STRING;"COLUMN_10","COLUMN_10",STRING;"COLUMN_11","COLUMN_11",STRING;"COLUMN_12",
"COLUMN_12",STRING;"COLUMN_13","COLUMN_13",STRING;"COLUMN_14","COLUMN_14",STRING;"COLUMN_15","COLUMN_15",STRING;"COLUMN_16","COLUMN_16",
STRING;"COLUMN_17","COLUMN_17",STRING;"COLUMN_18","COLUMN_18",STRING;"COLUMN_19","COLUMN_19",STRING;"COLUMN_20","COLUMN_20",
STRING;"COLUMN_21","COLUMN_21",STRING;"COLUMN_22","COLUMN_22",STRING;"COLUMN_23","COLUMN_23",STRING;"COLUMN_24","COLUMN_24",
STRING;"COLUMN_25","COLUMN_25",STRING;"COLUMN_26","COLUMN_26",STRING;"COLUMN_27","COLUMN_27",STRING;"COLUMN_28","COLUMN_28",
STRING;"COLUMN_29","COLUMN_29",STRING}
I have tried this in Expression Builder for "COLUMN_0" too
dataSetRow["COLUMN_0"] = (dataSetRow["COLUMN_0"] !="" || dataSetRow["COLUMN_0"] !=null || dataSetRow["COLUMN_0"] !="UNNAMED_1" || dataSetRow["COLUMN_0"] !=" ")
dataSetRow["COLUMN_0"]
I have no more idea how to fix this
new member
This is the error I am getting right now.
Error.ReportQueryLoadingError2 ( 1 time(s) )
detail : org.eclipse.birt.report.engine.api.EngineException: Can not load the report query: 248. Errors occurred when generating the report document for the report element with ID 248.
at org.eclipse.birt.report.engine.data.dte.DataPresentationEngine.doExecuteQuery(DataPresentationEngine.java:164)
at org.eclipse.birt.report.engine.data.dte.AbstractDataEngine.execute(AbstractDataEngine.java:265)
at org.eclipse.birt.report.engine.executor.ExecutionContext.executeQuery(ExecutionContext.java:1890)
at org.eclipse.birt.report.engine.internal.document.v4.ReportItemExecutor.executeQuery(ReportItemExecutor.java:412)
at org.eclipse.birt.report.engine.internal.document.v4.TableItemExecutor.doExecute(TableItemExecutor.java:72)
at org.eclipse.birt.report.engine.internal.document.v4.ReportItemExecutor.execute(ReportItemExecutor.java:294)
at org.eclipse.birt.report.engine.internal.document.v4.ContainerExecutor.prepareChildExecutor(ContainerExecutor.java:226)
at org.eclipse.birt.report.engine.internal.document.v4.ContainerExecutor.hasNextChild(ContainerExecutor.java:110)
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.hasNextChild(WrappedReportItemExecutor.java:86)
at org.eclipse.birt.report.engine.internal.executor.wrap.WrappedReportItemExecutor.hasNextChild(WrappedReportItemExecutor.java:86)
at org.eclipse.birt.report.engine.api.impl.RenderTask$ReportExecutorWrapper.hasNextChild(RenderTask.java:780)
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM$1.hasNextChild(HTMLPageLM.java:68)
at org.eclipse.birt.report.engine.layout.html.HTMLBlockStackingLM.layoutNodes(HTMLBlockStackingLM.java:62)
at org.eclipse.birt.report.engine.layout.html.HTMLPageLM.layout(HTMLPageLM.java:90)
at org.eclipse.birt.report.engine.layout.html.HTMLReportLayoutEngine.layout(HTMLReportLayoutEngine.java:99)
at org.eclipse.birt.report.engine.api.impl.RenderTask$PageRangeRender.render(RenderTask.java:661)
at org.eclipse.birt.report.engine.api.impl.RenderTask.render(RenderTask.java:284)
at org.eclipse.birt.report.service.ReportEngineService.renderReport(ReportEngineService.java:1545)
at org.eclipse.birt.report.service.BirtViewerReportService.getPage(BirtViewerReportService.java:204)
at org.eclipse.birt.report.service.actionhandler.BirtChangeParameterActionHandler.doRenderPage(BirtChangeParameterActionHandler.java:82)
at org.eclipse.birt.report.service.actionhandler.AbstractChangeParameterActionHandler.__execute(AbstractChangeParameterActionHandler.java:103)
at org.eclipse.birt.report.service.actionhandler.AbstractBaseActionHandler.execute(AbstractBaseActionHandler.java:90)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseDocumentProcessor.__executeAction(AbstractBaseDocumentProcessor.java:47)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.executeAction(AbstractBaseComponentProcessor.java:143)
at org.eclipse.birt.report.soapengine.processor.BirtDocumentProcessor.handleChangeParameter(BirtDocumentProcessor.java:100)
at sun.reflect.GeneratedMethodAccessor104.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseComponentProcessor.process(AbstractBaseComponentProcessor.java:112)
at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingImpl.getUpdatedObjects(BirtSoapBindingImpl.java:66)
at sun.reflect.GeneratedMethodAccessor38.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCProvider.java:397)
at org.apache.axis.providers.java.RPCProvider.processMessage(RPCProvider.java:186)
at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvider.java:323)
at org.apache.axis.strategies.InvocationStrategy.visit(InvocationStrategy.java:32)
at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)
at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)
at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService.java:454)
at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281)
at org.apache.axis.transport.http.AxisServlet.doPost(AxisServlet.java:699)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.doPost(BirtSoapMessageDispatcherServlet.java:265)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
at org.apache.axis.transport.http.AxisServletBase.service(AxisServletBase.java:327)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherServlet.service(BirtSoapMessageDispatcherServlet.java:122)
at org.eclipse.equinox.http.registry.internal.ServletManager$ServletWrapper.service(ServletManager.java:180)
at org.eclipse.equinox.http.servlet.internal.ServletRegistration.service(ServletRegistration.java:61)
at org.eclipse.equinox.http.servlet.internal.ProxyServlet.processAlias(ProxyServlet.java:126)
at org.eclipse.equinox.http.servlet.internal.ProxyServlet.service(ProxyServlet.java:60)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at org.eclipse.equinox.http.jetty.internal.HttpServerManager$InternalHttpServiceServlet.service(HttpServerManager.java:318)
at org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:511)
at org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:390)
at org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:182)
at org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:765)
at org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:152)
at org.mortbay.jetty.Server.handle(Server.java:326)
at org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:542)
at org.mortbay.jetty.HttpConnection$RequestHandler.content(HttpConnection.java:939)
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:756)
at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:212)
at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:404)
at org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:409)
at org.mortbay.thread.QueuedThreadPool$PoolThread.run(QueuedThreadPool.java:582)
mwilliams
I don't think you're necessarily having a problem with just the first column. It must be that when you're trying to dynamically call your CSV file with your "query", you're running into an issue with the first column and it's crashing. That's why it seems that it's with the first column. That is just a guess though. I don't think you're doing the query thing right. In your initialize method of you report, if you put QT = "" and in your beforeOpen method of your dataSet, set the value of QT to be what your query text ends up being, and then in a dynamic textbox in your report, put QT; and see if you can see what the query text you end up with looks like. It should look similar to the query text that I posted above and that you posted below your beforeOpen code above. If it doesn't, that's where we should start, with your query.
Quick question. If you're making your table have 29 columns, why do you need to have a dynamic query of your file? If you're going to have the header column, it should work out without having a query. You should be able to just call the CSV and hide the columns you don't need. Let me know.
