search

OSBI / saiku

Saiku Analytics - The Worlds Greatest Open Source OLAP Browser
http://community.meteorite.bi
Apache License 2.0
1.3k stars 663 forks source link

Exporting reports to EXCEL does not work #598

Open YakupovD opened 6 years ago

YakupovD commented 6 years ago

Exporting reports to EXCEL does not work. Some reports with the merging of cells in the header are not unloaded http://dl3.joxi.net/drive/2017/09/12/0007/2851/482083/83/7239f3a340.jpg

We use the Saiku-3.14 for Pentaho 7.1

vchampion commented 6 years ago

Have you seen Pentaho or Saiku logs? Is this issue repeats in all cases? Can you provide the steps to reproduce this.

YakupovD commented 6 years ago

The following regularity is revealed. If you try to export to the EXCEL report with a header containing the merged cells http://joxi.net/EA46wMWfwlX4om, the error message is http://joxi.ru/82Qq8d0fjJ8X32

baumgaro commented 6 years ago

There is a solution documented for this: https://groups.google.com/a/saiku.meteorite.bi/forum/#!topic/dev/7yINLLuElVM

isaiasanchez commented 6 years ago

I am using saiku 3.15 plugin into pentaho 7.0 using the Quadrant Analysis cube in pentaho.

If I create a simple query for the first time and export it to excel all goes fine, but if I filter some fields in columns then the exported files goes crazy.

I am ataching the two resulting files:

  1. saiku-export.xlsx for the nornal query and how I spect all the files will be exported.

  2. saiku-export(1).xlsx is getting information for only two different positions and the file completely change the format.

saiku-export.xlsx saiku-export (1).xlsx

The solution commented by baumgao don't fix this issue, I guess it's already commited in 3.15.

felixmw commented 6 years ago

Hi all,

@isaiasanchez: From what I see the solution mentioned by @baumgaro has not been added to the development branch of saiku. Compare line 44 of https://github.com/OSBI/saiku/blob/development/saiku-core/saiku-service/src/main/java/org/saiku/service/olap/totals/TotalsListsBuilder.java and line 224 of https://github.com/OSBI/saiku/blob/development/saiku-core/saiku-service/src/main/java/org/saiku/service/util/export/excel/ExcelWorksheetBuilder.java with the suggested changes made here https://groups.google.com/a/saiku.meteorite.bi/d/msg/dev/7yINLLuElVM/IQiG9AS_AQAJ

What do we have to do in order to incorporate these changes to the current development branch of Saiku?

Best Felix

brenopolanski commented 6 years ago

Hey @felixmw send a PR to development branch please!

felixmw commented 6 years ago

Hi @brenopolanski ,

I am sorry but I am not a developer. I don't know how to send a pull request and my java skills got a little bit rusty over the years. I just compared to sets of code ;-)

Best, Felix

robmkennedy commented 6 years ago

Hey is there any progress being made on this? It's been on hold for a while but it's a big issue for us now.

Thanks!

vitich commented 6 years ago

https://stackoverflow.com/questions/45357925/pentaho-7-1-and-saiku-3-11-error-export-to-xls /opt/pentaho/pentaho-server/pentaho-solutions/system/saiku/lib/poi-3.12.jar /opt/pentaho/pentaho-server/pentaho-solutions/system/saiku/lib/poi-ooxml-3.12.jar /opt/pentaho/pentaho-server/pentaho-solutions/system/saiku/lib/poi-ooxml-schemas-3.12.jar /opt/pentaho/pentaho-server/pentaho-solutions/system/saiku/lib/poi-scratchpad-3.12.jar

It works on 7 and 8.1

gleisonsilva commented 5 years ago

Hello! There is some progress in this matter?

vitich commented 5 years ago

Hello! There is some progress in this matter?

Nothing has changed I guess :) It works, but very slowly. Use CSV - it`s fast :) This issue is on hold since Oct10 2017 I do not think that the problem will be solved - it is not beneficial for anyone that you successfully use СE and not ENTERPRISE

OliverFranco commented 5 years ago

Hello, I have been studing the file ExcelWorksheetBuilder.java and there are a bug. With the solution https://groups.google.com/a/saiku.meteorite.bi/forum/#!topic/dev/7yINLLuElVM, the export is ok with only 1 aggregations, but with 2 o more no. It´s necesary modify the way of calculate the rows of total for each aggregations and modify the merge method. I have modifed ExcelWorksheetBuilder.java, I have solved the problem, and I have make many changes of format. If it is util for otrher people, i add the saiku-service-3.90.jar and the code, and the ExcelWorksheetBuilder.java.

I'm not a profesional programmer,but I hope that the solution can help.

ExcelWorksheetBuilder.zip saiku-service.zip

For test this solutions, only is necesary copy the saiku-service files in the directory saiku/lib.

Muchntuchn commented 5 years ago

Did someone test the solution from @OliverFranco with a Pentaho Server 8.1 and Saiku 3.90?

OliverFranco commented 5 years ago

Did someone test the solution from @OliverFranco with a Pentaho Server 8.1 and Saiku 3.90?

I Have test with Pentaho 8.0.0.0-28 and with 8.2.0.0-342, so I think that with the version 8.1 must be ok.

I have make some changes more about the format of the columns, I upload the last version:

saiku-service.zip

jemarroyo commented 5 years ago

@OliverFranco I have tried to implement your service.jar file on my test server Pentaho 8.0.0.0.28 and it didn't work. When I try to open a new saiku view a get this exception: 

13:32:10,210 ERROR [GenericServlet] GenericServlet.ERROR_0004 - Resource /saiku-ui/saikuplugin.properties_es_41.properties not found in plugin saiku
28-Aug-2019 13:32:12.879 SEVERE [http-apr-8080-exec-6] com.sun.jersey.spi.container.ContainerResponse.mapMappableContainerException The exception contained within MappableContainerException could not be mapped to a response, re-throwing to the HTTP container
 java.lang.AbstractMethodError: org.saiku.plugin.PentahoDatasourceManager.getDatasources([Ljava/lang/String;)Ljava/util/Map;
        at org.saiku.datasources.connection.AbstractConnectionManager.getAllConnections(AbstractConnectionManager.java:182)
        at org.saiku.datasources.connection.AbstractConnectionManager.getAllOlapConnections(AbstractConnectionManager.java:206)
        at org.saiku.olap.discover.OlapMetaExplorer.getAllConnections(OlapMetaExplorer.java:119)
        at org.saiku.service.olap.OlapDiscoverService.getAllConnections(OlapDiscoverService.java:58)
        at org.saiku.web.rest.resources.OlapDiscoverResource.getConnections(OlapDiscoverResource.java:60)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at com.sun.jersey.spi.container.JavaMethodInvokerFactory$1.invoke(JavaMethodInvokerFactory.java:60)
        at com.sun.jersey.server.impl.model.method.dispatch.AbstractResourceMethodDispatchProvider$TypeOutInvoker._dispatch(AbstractResourceMethodDispatchProvider.java:185)
        at com.sun.jersey.server.impl.model.method.dispatch.ResourceJavaMethodDispatcher.dispatch(ResourceJavaMethodDispatcher.java:75)
        at com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)
        at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108)
        at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
        at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
        at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1542)
        at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1473)
        at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1419)
        at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1409)
        at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:409)
        at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:558)
        at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:733)
        at org.pentaho.platform.web.servlet.JAXRSPluginServlet.service(JAXRSPluginServlet.java:112)
        at org.saiku.plugin.resources.ExtendedJAXRSPluginServlet.service(ExtendedJAXRSPluginServlet.java:58)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
        at org.pentaho.platform.web.servlet.JAXRSPluginServlet.service(JAXRSPluginServlet.java:117)
        at org.pentaho.platform.web.servlet.PluginDispatchServlet.service(PluginDispatchServlet.java:89)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:292)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.pentaho.platform.web.http.filters.PentahoWebContextFilter.doFilter(PentahoWebContextFilter.java:223)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.pentaho.platform.web.http.filters.PentahoRequestContextFilter.doFilter(PentahoRequestContextFilter.java:87)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317)
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:115)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.pentaho.platform.web.http.security.RequestParameterAuthenticationFilter.doFilter(RequestParameterAuthenticationFilter.java:191)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilterInternal(BasicAuthenticationFilter.java:158)
        at org.pentaho.platform.web.http.security.PentahoBasicProcessingFilter.doFilterInternal(PentahoBasicProcessingFilter.java:125)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.pentaho.platform.web.http.filters.HttpSessionPentahoSessionIntegrationFilter.doFilter(HttpSessionPentahoSessionIntegrationFilter.java:271)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:169)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214)
        at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177)
        at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
        at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.pentaho.platform.web.http.filters.SystemStatusFilter.doFilter(SystemStatusFilter.java:55)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.pentaho.platform.web.http.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:114)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.pentaho.platform.web.http.filters.WebappRootForwardingFilter.doFilter(WebappRootForwardingFilter.java:70)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.pentaho.platform.web.http.filters.PentahoPathDecodingFilter.doFilter(PentahoPathDecodingFilter.java:34)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:212)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:94)
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:504)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79)
        at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:620)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:502)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1132)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:684)
        at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2527)
        at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2516)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:748)
OliverFranco commented 4 years ago

@OliverFranco I have tried to implement your service.jar file on my test server Pentaho 8.0.0.0.28 and it didn't work. When I try to open a new saiku view a get this exception:

Do you copy saiku-service files in the directory saiku/lib? And delete the other versión?

jemarroyo commented 4 years ago

@OliverFranco I have tried to implement your service.jar file on my test server Pentaho 8.0.0.0.28 and it didn't work. When I try to open a new saiku view a get this exception:

Do you copy saiku-service files in the directory saiku/lib? And delete the other versión?

Yes I have! But never matter, I have already install pentaho server 8.3 and works well. Thanks for your replay @OliverFranco

alexssandroos commented 4 years ago

Did someone test the solution from @OliverFranco with a Pentaho Server 8.1 and Saiku 3.90?

Its fine for me ! Server 8.1 + saiku 3.9 (with libs of sparkl), on Settings.yaml change REPOSITORY_LAZY and EXTENDED_REPOSITORY_RESOURCE to true and be Happy ;)

OliverFranco commented 4 years ago

Hello

Did someone test the solution from @OliverFranco with a Pentaho Server 8.1 and Saiku 3.90?

Its fine for me ! Server 8.1 + saiku 3.9 (with libs of sparkl), on Settings.yaml change REPOSITORY_LAZY and EXTENDED_REPOSITORY_RESOURCE to true and be Happy ;)

Hello, I have see another problem with the Drillthrough: https://github.com/OSBI/saiku/issues/645. There are a bug in the function ThinQueryService.drillthroughWithCaptions().

If it is util for otrher people, i add the saiku-service-3.90.jar saiku_service.zip ThinQueryService.zip

artjio commented 4 years ago

Hi, Is it possible to change the xls format same of csv format? saiku-export_1

Because the saiku 3.90 version format not only remove merge column,but also remove the column data ,it is like the attach file: saiku-export

mireyaBLAU commented 3 years ago

Hi, any new about last problem? I have the same issue as @artjio : when I download data to an excel file, column cells are empty, only first label of the dimension is shown. Thanks.

OliverFranco commented 3 years ago

Hi @mireyaBLAU, Sorry, but It´s true that the excel must show all values, but I wanted that functionality and show the data like in Saiku, If i have time the next month upload a new version with all the values.

OliverFranco commented 3 years ago

Hi, Is it possible to change the xls format same of csv format? saiku-export_1

Because the saiku 3.90 version format not only remove merge column,but also remove the column data ,it is like the attach file: saiku-export

I have worked in 2 options, the first where the value of repeats agregations is no empty, and the second where that values aren´t showher like you can see in the Saiku´s view.

saiku-service_(repitvalues).zip

saiku-service.zip

carlosebuenos commented 3 years ago

Hello @OliverFranco, how are you?

Do you have the complete version 3.90 files to make available?

I didn't find any link to download it.

carlosebuenos commented 3 years ago

I found this link

https://sourceforge.net/projects/argum/files/assets/saiku-plugin-p7.1-3.90.zip/download

alexssandroos commented 3 years ago

yes

I found this link

https://sourceforge.net/projects/argum/files/assets/saiku-plugin-p7.1-3.90.zip/download

yep! thats my personal version with some corretions of erros, but not all =(

OliverFranco commented 3 years ago

Hello @OliverFranco, how are you?

Do you have the complete version 3.90 files to make available?

I didn't find any link to download it.

I think the best download option is from Pentaho Marketplace, have you tried that option?

OliverFranco commented 3 years ago

Hi, Is it possible to change the xls format same of csv format? saiku-export_1 Because the saiku 3.90 version format not only remove merge column,but also remove the column data ,it is like the attach file: saiku-export

I have worked in 2 options, the first where the value of repeats agregations is no empty, and the second where that values aren´t showher like you can see in the Saiku´s view.

saiku-service_(repitvalues).zip

saiku-service.zip

Hello everyone, In the tests of the last version, I have detecte some errors in the export, I add the two options again:

Without repit values: saiku-service.zip Repit Values saiku-service_(repitvalues).zip

carlosebuenos commented 3 years ago

Hello @OliverFranco, how are you? Do you have the complete version 3.90 files to make available? I didn't find any link to download it.

I think the best download option is from Pentaho Marketplace, have you tried that option?

My bad ...

Obviously I should try the Marketplace first.

As I couldn't find it on the web, I believed that the link was also broken there.

Download Plugin - Starting job entry Download Plugin - Start of HTTP job entry. Download Plugin - Connecting to URL: http://downloads.meteorite.bi/saiku3/saiku- plugin-p7.1-3.90.zip

Ty

OliverFranco commented 3 years ago

Hello @OliverFranco, how are you? Do you have the complete version 3.90 files to make available? I didn't find any link to download it.

I think the best download option is from Pentaho Marketplace, have you tried that option?

My bad ...

Obviously I should try the Marketplace first.

As I couldn't find it on the web, I believed that the link was also broken there.

Download Plugin - Starting job entry Download Plugin - Start of HTTP job entry. Download Plugin - Connecting to URL: http://downloads.meteorite.bi/saiku3/saiku- plugin-p7.1-3.90.zip

Ty

Here you are the folder with all. You only need to copy to pentaho-server\pentaho-solutions\system and create the license file. saiku_39.zip