forfiles -p F:\Logs -s -m *.log /D -30 /C "cmd /c del @path"

To change server name for an Oracle connection in BusinessObjects, we need to update the definition of SID in tnsnames.ora file. You can specify the server name, port and instance in the connection:

We want to validate values of the dimension [Job No.] from Query 1 against values of [Job No.] from query Valid within Webi report.

We can create a detail object [Valid Job Name] for merged object [Job No.] that points to unmerged dimension from the query Valid [Valid].[Job Name] and use it in the following expression to check if the value is valid:

=Not(IsNull([Valid Job Name]))

So the point is to use unmerged object expression like =Not(IsNull([Valid].[Job No.])) will not work.

We want to add hyperlink to an image in Webi document.

Here I am using an image with name UserLogo1.png that has size 165 x 25.

Copy your image to the server to the folder:

C:\BusinessObjects\BusinessObjects Enterprise 12.0\images

Create a cell and set properties to the following.

Text: <a href=’http://google.com‘ style=’width:165px; height:25px;’ target=’_blank’>&nbsp;</a>

Width: 165

Height: 25

Read cell content as: Hyperlink

Background image: boimg://UserLogo1.png

(It does not work in PDF and Excel)

Running queries with performance metrics

SELECT
  S.SQL_ID,
  S.MODULE, 
  S.SQL_TEXT, 
  U.USERNAME, 
  ROUND((S.DISK_READS/DECODE(S.EXECUTIONS,0,1, S.EXECUTIONS)),2) DISK_READS_PER_EXEC, 
  S.DISK_READS, 
  S.BUFFER_GETS, 
  S.PARSE_CALLS, 
  S.SORTS, 
  S.EXECUTIONS, 
  S.ROWS_PROCESSED, 
  100 - ROUND(100 *  S.DISK_READS/GREATEST(S.BUFFER_GETS,1),2) HIT_RATIO, 
  S.FIRST_LOAD_TIME, 
  S.SHARABLE_MEM, 
  S.PERSISTENT_MEM, 
  S.RUNTIME_MEM, 
  S.CPU_TIME, 
  S.ELAPSED_TIME
FROM
  SYS.V_$SQL S, 
  SYS.ALL_USERS U 
WHERE
  S.PARSING_USER_ID=U.USER_ID 
AND 
  UPPER(U.USERNAME) NOT IN ('SYS','SYSTEM') 
ORDER BY 
  5 DESC

SQL query in pieces

SELECT
  *
FROM
  SYS.V_$SQLTEXT
WHERE
  SQL_ID = '68du231hjcykn'
ORDER BY 
  SQL_ID, PIECE;

SQL query concatenated

SELECT 
  SQL_ID, LISTAGG(SQL_TEXT, '') WITHIN GROUP (ORDER BY PIECE)
FROM 
  SYS.V_$SQLTEXT
WHERE
  SQL_ID = '68du231hjcykn'
GROUP BY 
  SQL_ID;

Bind variables

SELECT 
  * 
FROM
  V$SQL_BIND_CAPTURE B 
WHERE
  SQL_ID = '68du231hjcykn';

Configure IE ESC (Internet Explorer’s Enhanced Security Configuration)

Disable for Administrators

(And do not forget to restart IE)

Failed to open the BIAR file. Reason: com.businessobjects.sdk.biar.om.internal.iw.ObjectManagerIWException:Input/Output exception occurred: ‘Couldn’t find entry ‘BusinessObjects.xml’.

Import Wizard could not handle files larger than 500Mb. Use multiple BIARs.

Let’s define two similar queries for 2004 and 2005. The query for 2004 looks like:

The queries are merged by Service:

Now we want to filter table based on a measure from one of the queries.

Let’s define variable v_Filter as

=Abs([2004].[Revenue])<100000

With the following report filter, the table will show 3 rows and it is expected.

But we will get not quite expected results when one of “non-merged” objects is used.

With [2004].[Service], the filter will remove only numbers that does not match restriction.

With [2005].[Service], the filter will be ignored.

Why? It seems it worked more predictable in earlier versions.

• Start cmd.exe as Administrator
• Execute the following command which turns off blocking of outdated ActiveX controls for Internet Explorer

reg add “HKCU\Software\Microsoft\Windows\CurrentVersion\Policies\Ext” /v VersionCheckEnabled /t REG_DWORD /d 0 /f

• Restart IE

PS. To enable blocking again, use command

reg add “HKCU\Software\Microsoft\Windows\CurrentVersion\Policies\Ext” /v VersionCheckEnabled /t REG_DWORD /d 1 /f

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