Misc. Dates – 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

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

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

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

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

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

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.