BusinessObjects-Keycod Error For Xcelsius

If ypu got the "The Keycode you entered is not valid. Please try again" error go the to registry and open the "HKEY_LOCAL_MACHINE–> SOFTWARE –> Business Objects –> Suite 12.0" folder.

Under Xcelsius>Keycodes change the DATA value to your keycode and add a semicolon and the date. For example:

XXXXXXXXXXXXXXXXXXXXXXX;20101203

Tweet

BusinessObjects-Xcelsius 2008 Installation On Office 2010

First download the file from

http://download.microsoft.com/download/7/c/4/7c426dfc-46e2-4ded-bab4-3b33600ad7d1/msi45sdk.msi

After you run this file go to the installation folder and run orca.msi (under tools subfolder. usually "C:\Program Files\Windows Installer 4.5 SDK\TOOLS")

This will create the orca.exe file.

Run the orca.exe file and open the xcelsius.msi file (before this action take a copy of your file).

From the left pane select "LaunchCondition" and drop the "EXCEL 10 OR...." line.

Save the file and run.

Tweet

BusinessObjects-Query Builder Examples

Main tables: CI_INFOOBJECTS, CI_APPOBJECTS, CI_SYSTEMOBJECTS

Some scenarios:

Which BusinessObjects Active Directory Groups don't have any member:
• Select SI_NAME from CI_SYSTEMOBJECTS WHERE SI_KIND = 'UserGroup' and SI_GROUP_MEMBERS is null

Which Universes use the Connection 'con1'?
• Select * FROM CI_APPOBJECTS WHERE CHILDREN("SI_NAME = 'dataconnection-universe'", "SI_NAME = 'con1'")

List of all Universes
• Select SI_NAME, SI_ID FROM CI_APPOBJECTS WHERE SI_KIND='Universe'

List of all Universe Connections
• Select SI_ID, SI_NAME FROM CI_APPOBJECTS WHERE SI_KIND = 'metadata.dataconnection'

List of Active Connections
• Select * FROM CI_SYSTEMOBJECTS WHERE SI_KIND='Connection'

Which Reports Use my Univ1 Universe
• SELECT si_id, si_name,si_universe,si_cuid
FROM CI_INFOOBJECTS, CI_SYSTEMOBJECTS, CI_APPOBJECTS
Where PARENTS("SI_NAME='Webi-Universe'","SI_NAME ='Univ1'")
and SI_KIND = 'Webi' and si_instance = 0

List of Named Or Concurrent Users
• SELECT si_name FROM CI_SYSTEMOBJECTS where SI_NAMEDUSER=0 AND SI_KIND='User'
SI_NAMEDUSER=0 is for Concurrent users. Use SI_NAMEDUSER=1 for Nameduser

To bring back all folders and objects in the ‘Report Samples’ folder:
• select * from ci_infoobjects, ci_appobjects, ci_systemobjects where DESCENDENTS("si_name='Folder Hierarchy'","si_name='Report Samples' and si_kind='Folder'")

To bring back only direct children (objects and folders) in the ‘Report Samples’ folder:
• select * from ci_infoobjects, ci_appobjects, ci_systemobjects where CHILDREN("si_name='Folder Hierarchy'","si_name='Report Samples' and si_kind='Folder'")

To list reports and documents those are in public folders including Sub folders. (Excluding instances, personal documents and inbox documents):
• SELECT * FROM CI_INFOOBJECTS WHERE SI_KIND IN ('FullClient', 'Txt', 'Excel', 'Webi', 'Analysis', 'Pdf', 'Word', 'Rtf', 'CrystalReport', 'Agnostic') AND SI_RUNNABLE_OBJECT = 0 AND SI_INSTANCE_OBJECT = 0 AND SI_ANCESTOR = 23

Tweet

BusinessObjects-Universe not Found WIS 00501

Let's try to explain the situation what we faced. You have two BusinessObjects environments. Our goal is export webi reports and universes to a biar file and import them to the target system.

Everything looks fine? Here comes the nightmare. You refresh the reports and



There are many possible reasons. Our root cause was security.

Under Central Management Console look to the "Universes" folder. On the "Rights" tab be sure there is a group which has access to this folder (Universes) and this group belongs to a group that want to view the webi reports.

For the second be sure that the same group has access to the Universe Connections. You can check this in Central Management Console->Universe Connections->Rights.

More about this article can be found here :
http://www.businessobjectstips.com/tips/web-intelligence/what-is-an-unbound-report-and-why-should-i-care/

Tweet

ORACLE-Find the Time Difference Between Two Steps

Suppose that you have a table which has only one time column. This table is used for collect the datetime for each steps of your transactions. (Eg. step started, step in progress, step closed for each transaction) In this example below the Pkey column is the unique key for each transaction and the second column is the start time for it.

pkey	datecreated
10	06/02/2007 10:28:25
10	06/02/2007 11:28:25
10	06/02/2007 12:28:25
20	06/02/2007 10:28:25
20	06/02/2007 09:28:25
20	06/02/2007 10:20:25
30	06/02/2007 10:28:25
30	07/02/2007 10:28:25
30	07/02/2007 01:28:25

solution 1 :


SELECT u.pkey,u.orderno,u.datecreated,g.datecreated,
TRUNC(((((86400*(u.datecreated-g.datecreated))/60)/60)/24)/7) weeks,
TRUNC((((86400*(u.datecreated-g.datecreated))/60)/60)/24) days,
TRUNC(((86400*(u.datecreated-g.datecreated))/60)/60)-24*(TRUNC((((86400*(u.datecreated-g.datecreated))/60)/60)/24)) hours,
TRUNC((86400*(u.datecreated-g.datecreated))/60)-60*(TRUNC(((86400*(u.datecreated-g.datecreated))/60)/60)) minutes,
TRUNC(86400*(u.datecreated-g.datecreated))-60*(TRUNC((86400*(u.datecreated-g.datecreated))/60)) seconds
FROM
(SELECT e.*,row_number() over (PARTITION BY e.pkey ORDER BY e.datecreated) orderno FROM table e) u,
(SELECT e.*,row_number() over (PARTITION BY e.pkey ORDER BY e.datecreated) orderno FROM table e) g
WHERE u.pkey = g.pkey(+) AND u.orderno = g.orderno(+)+1


solution 2 :


WITH std AS
(SELECT e.*,row_number() over (PARTITION BY pkey ORDER BY e.datecreated) orderno FROM table e)
(SELECT
u.pkey,u.orderno,u.datecreated,g.datecreated,
TRUNC(((((86400*(u.datecreated-g.datecreated))/60)/60)/24)/7) weeks,
TRUNC((((86400*(u.datecreated-g.datecreated))/60)/60)/24) days,
TRUNC(((86400*(u.datecreated-g.datecreated))/60)/60)-24*(TRUNC((((86400*(u.datecreated-g.datecreated))/60)/60)/24)) hours,
TRUNC((86400*(u.datecreated-g.datecreated))/60)-60*(TRUNC(((86400*(u.datecreated-g.datecreated))/60)/60)) minutes,
TRUNC(86400*(u.datecreated-g.datecreated))-60*(TRUNC((86400*(u.datecreated-g.datecreated))/60)) seconds
FROM std u , std g
WHERE u.pkey = g.pkey(+) AND u.orderno = g.orderno(+)+1
)


solution 3 :


SELECT pkey, datecreated,
TRUNC(((((86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))/60)/60)/24)/7) weeks,
TRUNC((((86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))/60)/60)/24) days,
TRUNC(((86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))/60)/60)-24*(TRUNC((((86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))/60)/60)/24)) hours,
TRUNC((86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))/60)-60*(TRUNC(((86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))/60)/60)) minutes,
TRUNC(86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))-60*(TRUNC((86400*(datecreated-lag (datecreated,1) over (ORDER BY datecreated)))/60)) seconds
FROM table

ORACLE-Cumulative Sum at the Same Line

You can sum the total of a measure in the same line for each section in your row data. For example:

select deptno,ename,sal,
sum(sal) over (partition by deptno order by sal,ename) CumDeptTot,
sum(sal) over (partition by deptno) SalByDept,
sum(sal) over (order by deptno, sal) CumTot,
sum(sal) over () TotSal
from emp
order by deptno, sal

and the result looks like this:

deptno	ename	sal	CumDeptTot	SalByDept	CumTot	TotSal
10	John	1300	1300	3400	1300	11300
10	Michael	1100	2400	3400	2400	11300
10	Robert	1000	3400	3400	3400	11300
20	Vicky	1200	1200	3550	4600	11300
20	Sally	1250	2450	3550	5850	11300
20	Harry	1100	3550	3550	6950	11300
30	Pavel	1400	1400	4350	8350	11300
30	Dennis	1400	2800	4350	9750	11300
30	Barbie	1550	4350	4350	11300	11300

BusinessObjects-Dont' Ask for Username and Password by Opening Olap Reports

After creating an Olap Report the deafult setting is ask for username and password for each logon. To prevent this and run the report directly is simple.

Go to the Central Management Console. Find your Olap Report from "Objects". Click on to it and go to "Data Source Logon" tab. Select "Logon using specific credentials"

ORACLE-Merge Command

You can use the Merge command to insert and update at the same time with one query. So you don't need to write a procedure which includes insert, exception and update.

Use of the command;

Merge Into Table1 T1

Using (select * from Table2 where ...) T2

On (T1.Field1 = T2.Field1 And ...)

When Matched Then

Update

Set T1.Field2 = T2.Field2

When Not Matched Then

Insert (T1.Field2) Values (T2.Field2)

Table2 is our source table. Table1 is our destination table.

The fields in the "on" clause are the unique fields for T1 and works like a where clause.

ORACLE-Delete Same Multiple Rows Except One

To delete all the rows which have the same data except one.

(In this example we suppose that your table has same data in ID1 and ID2 columns.)

delete from t1 A

where rowid != ( select min(rowid) from t1 B

where B.ID1 = A.ID1

and B.ID2 = A.ID2)

ORACLE-Get the Values from a Long Data Type Column

DECLARE
long_var LONG;
var_var VARCHAR2(2000);
BEGIN
SELECT text_column INTO long_var
FROM table_with_long
WHERE rownum < 2;
var_var := substr(long_var,1,2000);
INSERT INTO table_b
VALUES (var_var);
END;

ORACLE-Find Non-Numeric Rows in a Table

Sometimes you want to alter your table. For example you want to change the data type from varchar to numeric. But many times some rows prevents you to doing this.

Find the non-numeric rows;

select nvl(length(translate(trim(column_name),' +-.0123456789',' ')),0) from table_name

where nvl(length(translate(trim(column_name),' +-.0123456789',' ')),0) > 0

tip: by equal the where clause to 0 (zero) you can find the numeric columns.

ORACLE-Create a Calendar Table

Create a table named X which should include numbers from 0 to 9. Let say vals as the column name.

so;

create table calendar as

select (a.vals + b. vals*10 + c. vals*100 + d. vals*1000 + e. vals*10000 )+

to_date('01.01.1900','dd.mm.yyyy') date from x a, x b, x c, x d, x e

this will create a table from 01.01.1900 to 10^5 days after.

ORACLE-Find Number of Character in a String

For example, find how many "|" is in "abc|123|wer|123|bnm".

select length('abc|123|wer|123|bnm')-length(replace('abc|123|wer|123|bnm','|','')) from dual