Posts By: dmytro

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()) […]

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 […]

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, […]

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 […]

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.

SQL Anywhere iSQL

Posted by & filed under Administration.

Starting from BusinessObjects XI SP5, SAP Sybase SQL Anywhere is used as default database for BusinessObjects system databases (CMS and Audit). This post describes basics how to connect to the system databases using iSQL.