We will first see what I am trying to achieve and then explain you the steps .
The Below report is being sourced from a Data Source Name(DSN)= 'ORCL' .

Now if you would like to source the same report from a different Data Source let's Say 'ORCL2' we can do so by adding just one Prefix statment by going to the Reports>Advance tab.
SET VARIABLE OLAP_DSN = 'ORCL2'

After applying the above statment the Report now sources from a DSN=ORCL2 as you can see below .

Let us now look into the steps to achieve this
1. I created three Different Data Sources and ran the script to create two tables Department and Employees.You can download the script and the RPD from the link below.
ORCL>DWH: Data Source(Database)=ORCL,Schema(User)=DWH
ORCL>DWH2:Data Source(Database)=ORCL,Schema(User)=DWH2
ORCL2>DWH:Data Source(Database)=ORCL2,Schema(User)=DWH
Make sure that you keep the Same Password for all the three different schemas above.
2. As you can see in the RPD I just imported the two Tables established the Join in the Physical Layer and Dragged/Dropped the Folder all the way to the Presentation Layer.Then I set the Aggregation as Sum For Employees>Salary.
3.We then need to create a Session Initialization Block as below with two variables.
SELECT 'ORCL' OLAP_DSN , 'DWH' OLAP_USER FROM DUAL

4.Variable OLAP_DSN . Make sure that 'Enable any user to set the value' is checked.

5.Variable OLAP_USER.

6.Now let us Modify the Connection Pool to use the Session Variables.
VALUEOF(NQ_SESSION.OLAP_DSN)
VALUEOF(NQ_SESSION.OLAP_USER)

7. Let us now create three Reports.
Report1_ORCL_DWH

Report2_ORCL_DWH2
Go to the Advanced Tab>Prefix and add the below script
SET VARIABLE OLAP_USER = 'DWH2';

Report3_ORCL2_DWH
Go to the Advanced Tab>Prefix and add the below script
SET VARIABLE OLAP_DSN = 'ORCL2';

7.As you can see below the reports are coming from three different Data Sources in a Single Dashboard Page.

8. The Administrator can now Navigate to Administration>Manage Privileges>Answers:Access Advanced Tab and grant privileges to the group who is allowed to add the script in the Reports Advanced Tab.

We will first see what I am trying to achieve and then explain you the steps .
The Below report is being sourced from a Data Source Name(DSN)= 'ORCL' .

Now if you would like to source the same report from a different Data Source let's Say 'ORCL2' we can do so by adding just one Prefix statment by going to the Reports>Advance tab.
SET VARIABLE OLAP_DSN = 'ORCL2'

After applying the above statment the Report now sources from a DSN=ORCL2 as you can see below .

Let us now look into the steps to achieve this
1. I created three Different Data Sources and ran the script to create two tables Department and Employees.You can download the script and the RPD from the link below.
ORCL>DWH: Data Source(Database)=ORCL,Schema(User)=DWH
ORCL>DWH2:Data Source(Database)=ORCL,Schema(User)=DWH2
ORCL2>DWH:Data Source(Database)=ORCL2,Schema(User)=DWH
Make sure that you keep the Same Password for all the three different schemas above.
2. As you can see in the RPD I just imported the two Tables established the Join in the Physical Layer and Dragged/Dropped the Folder all the way to the Presentation Layer.Then I set the Aggregation as Sum For Employees>Salary.
3.We then need to create a Session Initialization Block as below with two variables.
SELECT 'ORCL' OLAP_DSN , 'DWH' OLAP_USER FROM DUAL

4.Variable OLAP_DSN . Make sure that 'Enable any user to set the value' is checked.

5.Variable OLAP_USER.

6.Now let us Modify the Connection Pool to use the Session Variables.
VALUEOF(NQ_SESSION.OLAP_DSN)
VALUEOF(NQ_SESSION.OLAP_USER)

7. Let us now create three Reports.
Report1_ORCL_DWH

Report2_ORCL_DWH2
Go to the Advanced Tab>Prefix and add the below script
SET VARIABLE OLAP_USER = 'DWH2';

Report3_ORCL2_DWH
Go to the Advanced Tab>Prefix and add the below script
SET VARIABLE OLAP_DSN = 'ORCL2';

7.As you can see below the reports are coming from three different Data Sources in a Single Dashboard Page.

8. The Administrator can now Navigate to Administration>Manage Privileges>Answers:Access Advanced Tab and grant privileges to the group who is allowed to add the script in the Reports Advanced Tab.

We will first see what I am trying to achieve and then explain you the steps .
The Below report is being sourced from a Data Source Name(DSN)= 'ORCL' .

Now if you would like to source the same report from a different Data Source let's Say 'ORCL2' we can do so by adding just one Prefix statment by going to the Reports>Advance tab.
SET VARIABLE OLAP_DSN = 'ORCL2'

After applying the above statment the Report now sources from a DSN=ORCL2 as you can see below .

Let us now look into the steps to achieve this
1. I created three Different Data Sources and ran the script to create two tables Department and Employees.You can download the script and the RPD from the link below.
ORCL>DWH: Data Source(Database)=ORCL,Schema(User)=DWH
ORCL>DWH2:Data Source(Database)=ORCL,Schema(User)=DWH2
ORCL2>DWH:Data Source(Database)=ORCL2,Schema(User)=DWH
Make sure that you keep the Same Password for all the three different schemas above.
2. As you can see in the RPD I just imported the two Tables established the Join in the Physical Layer and Dragged/Dropped the Folder all the way to the Presentation Layer.Then I set the Aggregation as Sum For Employees>Salary.
3.We then need to create a Session Initialization Block as below with two variables.
SELECT 'ORCL' OLAP_DSN , 'DWH' OLAP_USER FROM DUAL

4.Variable OLAP_DSN . Make sure that 'Enable any user to set the value' is checked.

5.Variable OLAP_USER.

6.Now let us Modify the Connection Pool to use the Session Variables.
VALUEOF(NQ_SESSION.OLAP_DSN)
VALUEOF(NQ_SESSION.OLAP_USER)

7. Let us now create three Reports.
Report1_ORCL_DWH

Report2_ORCL_DWH2
Go to the Advanced Tab>Prefix and add the below script
SET VARIABLE OLAP_USER = 'DWH2';

Report3_ORCL2_DWH
Go to the Advanced Tab>Prefix and add the below script
SET VARIABLE OLAP_DSN = 'ORCL2';

7.As you can see below the reports are coming from three different Data Sources in a Single Dashboard Page.

8. The Administrator can now Navigate to Administration>Manage Privileges>Answers:Access Advanced Tab and grant privileges to the group who is allowed to add the script in the Reports Advanced Tab.

Nice Post...Very useful
ReplyDelete