XI 3.1 Issue with Excel Data Provider

Posted by & filed under Administration.

Problem

A Webi document has a query based on Excel (Excel data source). The document can be refreshed in Web Intelligence Rich Client, but it fails in Infoview with WIS 30270 error. The following errors appear in the log:

ExtensionFactoryImpl.cpp:201:void __thiscall WICDZExt::ExtensionFactoryImpl::createRemoteExtension(const 
class WICDZExt::ExtensionDescriptor &,struct WICDZExt::IExtension **): TraceLog message 24650
2014/10/17 09:02:31.474|>>|E| |13916|15036| |||||||||||||||ExtensionFactoryImpl::createRemoteExtension 
has failed : ExtensionFactoryImpl.createExtension has failed : java.lang.Exception: extension creation failed : null object
ExtensionFactoryImpl.cpp:202:void __thiscall WICDZExt::ExtensionFactoryImpl::createRemoteExtension(const 
class WICDZExt::ExtensionDescriptor &,struct WICDZExt::IExtension **): TraceLog message 24651
2014/10/17 09:02:31.474|>>|E| |13916|15036| |||||||||||||||java.lang.Exception: extension creation failed : null object
      at com.businessobjects.cdz_ext.ExtensionFactoryImpl.createExtension(Unknown Source)
      at com.businessobjects.cdz_ext.server.ExtensionFactoryServant.createExtension(Unknown Source)
      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:592)
      at com.businessobjects.framework.servers.platform.adapters.ebus.orb.CommonTransportInterceptor.invoke(CommonTransportInterceptor.java:93)
      at com.businessobjects.framework.servers.common.proxy.cglib.MethodInterceptorChain.intercept(MethodInterceptorChain.java:136)
      at com.crystaldecisions.enterprise.ocaframework.idl.OCA.OCAcdz.WICDZExtensions.ExtensionFactoryPOA$$EnhancerByCGLIB$$9792817d.createExtension(<generated>)
      at com.crystaldecisions.enterprise.ocaframework.idl.OCA.OCAcdz.WICDZExtensions.ExtensionFactoryPOA._OB_op_createExtension(Unknown Source)
      at com.crystaldecisions.enterprise.ocaframework.idl.OCA.OCAcdz.WICDZExtensions.ExtensionFactoryPOA._invoke(Unknown Source)
      at com.crystaldecisions.thirdparty.com.ooc.OBPortableServer.ServantDispatcher.dispatch(ServantDispatcher.java:234)
      at com.crystaldecisions.thirdparty.com.ooc.OBPortableServer.POA_impl._OB_dispatch(POA_impl.java:1917)
      at com.crystaldecisions.thirdparty.com.ooc.OB.DispatchRequest_impl.invoke(DispatchRequest_impl.java:75)
      at com.businessobjects.framework.servers.platform.adapters.ebus.orb.ThreadPoolDispatchStrategy$Dispatcher.run(ThreadPoolDispatchStrategy.java:124)
      at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:417)
      at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:269)
      at java.util.concurrent.FutureTask.run(FutureTask.java:123)
      at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:651)
      at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:676)
      at java.lang.Thread.run(Thread.java:595)
kc3dsxls.cpp:276:long __thiscall tbXlsDS::xtDSExcel::CreateXDS(void): TraceLog message 24652

Solution
AddNode.bat -name LOCALHOST -update -cms LOCALHOST:6400 -user Administrator -password "" -siaport 6410

Maconomy version

Posted by & filed under Maconomy.

SELECT 
  MAINVERSIONNUMBER, 
  PATCHNUMBER 
FROM 
(
  SELECT 
    MAINVERSIONNUMBER, 
    PATCHNUMBER, 
    ROW_NUMBER() OVER (ORDER BY MAINVERSIONNUMBER DESC, PATCHNUMBER DESC) RN 
  FROM 
    SYSTEMMAINTENANCELOG
)A
WHERE RN = 1

Misc. Dates – SQL Server

Posted by & filed under SQL Server.

SELECT

  DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)                    AS FIRST_DAY_PREVIOUS_YEAR,
  DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)                        AS FIRST_DAY_CURRENT_YEAR,
  DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)                    AS FIRST_DAY_NEXT_YEAR,

  DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()) - 1, -1)                  AS LAST_DAY_PREVIOUS_YEAR,
  DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()), -1)                      AS LAST_DAY_CURRENT_YEAR,
  DATEADD(YEAR, DATEDIFF(YEAR, -1, GETDATE()) + 1, -1)                  AS LAST_DAY_NEXT_YEAR,

  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)                  AS FIRST_DAY_PREVIOUS_MONTH,
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)                      AS FIRST_DAY_CURRENT_MONTH,
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)                  AS FIRST_DAY_NEXT_MONTH,
  
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 1, -1)                AS LAST_DAY_PREVIOUS_MONTH,
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()), -1)                    AS LAST_DAY_CURRENT_MONTH,
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) + 1, -1)                AS LAST_DAY_NEXT_MONTH,

  DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0)                      AS YESTERDAY,
  DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)                          AS TODAY,
  DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) + 1, 0)                      AS TOMORROW,

  DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE() - 7), 0)                      AS PREVIOUS_MONDAY,
  DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE()), 0)                          AS CURRENT_MONDAY,
  DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE() + 7), 0)                      AS NEXT_MONDAY,
  
  DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE()), 0) - 1                      AS PREVIOUS_SUNDAY,
  DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE() + 7), 0) - 1                  AS CURRENT_SUNDAY,
  DATEADD(WEEK, DATEDIFF(WEEK,0,GETDATE() + 14), 0) - 1                 AS NEXT_SUNDAY,

  DATEADD(YEAR, 1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))        AS THE_DAY_PLUS_1YEAR,
  DATEADD(YEAR, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))       AS THE_DAY_MINUS_1YEAR,
  
  DATEADD(MONTH, 1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))       AS THE_DAY_1_MONTH_AGO,
  DATEADD(MONTH, 2, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))       AS THE_DAY_2_MONTH_AGO,
  DATEADD(MONTH, 3, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))       AS THE_DAY_3_MONTH_AGO,
  DATEADD(MONTH, 4, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))       AS THE_DAY_4_MONTH_AGO,
  DATEADD(MONTH, 5, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))       AS THE_DAY_5_MONTH_AGO,
  DATEADD(MONTH, 6, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))       AS THE_DAY_6_MONTH_AGO,

  DATEADD(MONTH, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))      AS THE_DAY_1_MONTHS_AHEAD,
  DATEADD(MONTH, -2, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))      AS THE_DAY_2_MONTHS_AHEAD,
  DATEADD(MONTH, -3, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))      AS THE_DAY_3_MONTHS_AHEAD,
  DATEADD(MONTH, -4, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))      AS THE_DAY_4_MONTHS_AHEAD,
  DATEADD(MONTH, -5, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))      AS THE_DAY_5_MONTHS_AHEAD,
  DATEADD(MONTH, -6, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))      AS THE_DAY_6_MONTHS_AHEAD,

  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 2, 0)                  AS FIRST_DAY_2_MONTHS_AGO,
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, 0)                  AS FIRST_DAY_3_MONTHS_AGO,
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 4, 0)                  AS FIRST_DAY_4_MONTHS_AGO,
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 5, 0)                  AS FIRST_DAY_5_MONTHS_AGO,
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 6, 0)                  AS FIRST_DAY_6_MONTHS_AGO,
 
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 2, 0)                  AS FIRST_DAY_2_MONTHS_AHEAD,
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 3, 0)                  AS FIRST_DAY_3_MONTHS_AHEAD,
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 4, 0)                  AS FIRST_DAY_4_MONTHS_AHEAD,
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 5, 0)                  AS FIRST_DAY_5_MONTHS_AHEAD,
  DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 6, 0)                  AS FIRST_DAY_6_MONTHS_AHEAD,
                                                                        
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 2, -1)                AS LAST_DAY_2_MONTHS_AGO,
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 3, -1)                AS LAST_DAY_3_MONTHS_AGO,
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 4, -1)                AS LAST_DAY_4_MONTHS_AGO,
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 5, -1)                AS LAST_DAY_5_MONTHS_AGO,
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) - 6, -1)                AS LAST_DAY_6_MONTHS_AGO,
 
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) + 2, -1)                AS LAST_DAY_2_MONTHS_AHEAD,
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) + 3, -1)                AS LAST_DAY_3_MONTHS_AHEAD,
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) + 4, -1)                AS LAST_DAY_4_MONTHS_AHEAD,
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) + 5, -1)                AS LAST_DAY_5_MONTHS_AHEAD,
  DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE()) + 6, -1)                AS LAST_DAY_6_MONTHS_AHEAD

Weird merging issue in BO XI 3.1 SP5

Posted by & filed under Web Intelligence.

SP3 FP5.3

Create a report that contains 3 objects: Country, Year, Revenue

Add table with expression

=Sum(If([Year] Between ("FY2004";"FY2006"); [Revenue]; 0))

Add section by Country

Everything looks nice

Now add new query with only Year.

Now the expression produces null result. But why???

To see the numbers again, you can either unmerge Year (which is usually not an option)

or enable Extend merged dimension values

It is still not clear why this helps.

Difference between Where and If in Webi

Posted by & filed under Web Intelligence.

Two expressions

=[Revenue] Where ([Country]="US")

and

=Sum(If [Country]="US" Then [Revenue])

might look similar. They produce the same result if Country is not used in the table:

However with Country the result is slightly different

The expression with Where removes Country from the context, i.e. something like

=Sum(If [Country]="US" Then [Revenue]) ForAll([Country])

Misc. Dates – Oracle

Posted by & filed under Uncategorized.

SELECT 
  TRUNC(TRUNC(SYSDATE,'YY')-1,'YY')           AS FIRST_DAY_PREVIOUS_YEAR,
  TRUNC(SYSDATE,'YY')                         AS FIRST_DAY_CURRENT_YEAR,
  ADD_MONTHS(TRUNC(SYSDATE,'YY'),12)          AS FIRST_DAY_NEXT_YEAR,
  
  TRUNC(SYSDATE,'YY')-1                       AS LAST_DAY_PREVIOUS_YEAR,
  ADD_MONTHS(TRUNC(SYSDATE,'YY'),12)-1        AS LAST_DAY_CURRENT_YEAR,
  ADD_MONTHS(TRUNC(SYSDATE,'YY'),24)-1        AS LAST_DAY_NEXT_YEAR,
  
  TRUNC(TRUNC(SYSDATE,'MM')-1,'MM')           AS FIRST_DAY_PREVIOUS_MONTH,
  TRUNC(SYSDATE,'MM')                         AS FIRST_DAY_CURRENT_MONTH,
  ADD_MONTHS(TRUNC(SYSDATE,'MM'),1)           AS FIRST_DAY_NEXT_MONTH,
  
  TRUNC(SYSDATE,'MM')-1                       AS LAST_DAY_PREVIOUS_MONTH,
  ADD_MONTHS(TRUNC(SYSDATE,'MM'),1)-1         AS LAST_DAY_CURRENT_MONTH,
  ADD_MONTHS(TRUNC(SYSDATE,'MM'),2)-1         AS LAST_DAY_NEXT_MONTH,
  
  TRUNC(SYSDATE,'DD')-1                       AS YESTERDAY,
  TRUNC(SYSDATE,'DD')                         AS TODAY,
  TRUNC(SYSDATE,'DD')+1                       AS TOMORROW,
  
  TRUNC(SYSDATE-7,'IW')                       AS PREVIOUS_MONDAY,
  NEXT_DAY(TRUNC(SYSDATE-7,'IW'),'SUNDAY')    AS PREVIOUS_SUNDAY,
  TRUNC(SYSDATE,'IW')                         AS CURRENT_MONDAY,
  NEXT_DAY(TRUNC(SYSDATE,'IW'),'SUNDAY')      AS CURRENT_SUNDAY,
  TRUNC(SYSDATE+7,'IW')                       AS NEXT_MONDAY,
  NEXT_DAY(TRUNC(SYSDATE+7,'IW'),'SUNDAY')    AS NEXT_SUNDAY
FROM 
  DUAL

XI 3.1 OpenDocument URLs for copy-paste

Posted by & filed under Administration.

Here are some simple templates for OpenDocument links

http://localhost:8080/OpenDocument/opendoc/openDocument.jsp?sType=wid&iDocID=00000

http://localhost:8080/OpenDocument/opendoc/openDocument.jsp?sType=wid&sIDType=CUID&iDocID=AAAAAAAAAAAAAAA

http://localhost:8080/OpenDocument/opendoc/openDocument.jsp?sType=wid&sPath=[Folder],[Subfolder]&sDocName=Document

http://localhost:8080/OpenDocument/opendoc/openDocument.jsp?sType=wid&sIDType=CUID&iDocID=AAAAAAAAAAAAAAA&sInstance=Last

="<a target='_blank' href='"
+"../../opendoc/openDocument.jsp?sType=wid&sDocName=" + URLEncode("Document")
+"&lsSPrompt:="+UserResponse("Prompt:")
+"'>"+Text
+"</a>"

 

Infostore Page Query

Posted by & filed under SDK.

Here is some code for paged querying InfoStore (BO XI 3.1):

String uri = "path://InfoObjects/**[si_kind='Webi' and si_instance=0]"; 
PagingQueryOptions options = new PagingQueryOptions();
IPageResult ips = infoStore.getPagingQuery(uri, options);
Iterator<String> pageResultIter = ips.iterator();
while (pageResultIter.hasNext()) {
    String pageQuery = pageResultIter.next();
    IStatelessPageInfo pageInfo = infoStore.getStatelessPageInfo(pageQuery, options);
    String sql = pageInfo.getPageSQL();
    System.out.println(sql);
    IInfoObjects infoobjects = infoStore.query(sql);
    // do something with the infoobjects ...
}

Let's look at it line by line.

String uri = "path://InfoObjects/**[si_kind='Webi' and si_instance=0]";

We will query all Webi documents in the CMS using path query. Double asterisk mean recursive search. Root Folder is displayed as Public Folders in InfoView.

PagingQueryOptions options = new PagingQueryOptions();

PagingQueryOptions determines how the result will be paged. You can provide some parameters to the constructor.

  • Page size – the number of objects returned in the page
  • Whether the query is incremental. If the query is incremental, the page will always be equal to the page size. If the query is not incremental, paging is determined during the initial query, so if some objects are deleted or added, the page size might vary.
  • Options – control if the query will be decoded (e.g. useful if your query contains %), whether to include security parameters to the query, exclude temporary objects

Details can be found in the documentationPagingQueryOptions.

IPageResult ips = infoStore.getPagingQuery(uri, options);
Iterator<String> pageResultIter = ips.iterator();

Initial paging query provides string iterator. The iterator will return the same URI query but with paging parameters.

while (pageResultIter.hasNext()) {
    String pageQuery = pageResultIter.next();
    ...
}

This is the classic way to loop using a Java iterator.

IStatelessPageInfo pageInfo = infoStore.getStatelessPageInfo(pageQuery, options);
String sql = pageInfo.getPageSQL();
IInfoObjects infoobjects = infoStore.query(sql);

These lines builds the SQL queries to retrieve the objects of the page.

Export Webi to MHTML

Posted by & filed under SDK.

To export Webi report to MHTML, the function HTMLView.getContent(Writer,String,String) should be used.

// widoc is an instance of DocumentInstance
Reports reports = widoc.getReports();
HTMLView htmlView = (HTMLView) reports.getItem(0).getView(OutputFormatType.MHTML);
FileWriter fw = new FileWriter("report.mhtml");
htmlView.getContent(fw,"","");
fw.close();

Make sure that libraries xpp3.jar, xpp3_min.jar, xalan.jar and xercesImpl.jar from common\4.0\java\lib\external are included in the class path.