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()) - 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

Leave a reply

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

Leave a reply

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

Leave a reply 
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

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

Leave a reply

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

3 Replies

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.