SQL Query for Top 10 Product Sales

Here is the Oracle Query to find the Top 5 Products sales amounts from the table

Synopsis:

SELECT * FROM
(
  SELECT PROD_ID, SUM(AMOUNT_SOLD) AS "AMOUNT SOLD"
    FROM SALES
GROUP BY PROD_ID
ORDER BY SUM(AMOUNT_SOLD) DESC
       , PROD_ID ASC
) A
WHERE rownum <= 10;

Cheers!!!
Bose

Oracle / SQL Server Query for MTD (Month to Date), YTD (Year to Date), Rolling 12 Months, & Prior Year Sales

Recently I came across an interview and I had been asked for write an SQL Query from the table for Month To Date and Year To Date for sales amount from sales table.

Here is the synopsis:
If using Oracle use the trunc and numtoyminterval functions; if using SQL Server use the datediff function.
 
Oracle Month to Date:

SELECT sum(SALES_AMOUNT) FROM SALES WHERE DATEOFSALES BETWEEN trunc(sysdate, 'MONTH') and sysdate

Oracle Year to Date:

SELECT sum(SALES_AMOUNT) FROM SALES WHERE DATEOFSALES BETWEEN trunc(sysdate, 'YEAR') and sysdate

 
Oracle Rolling 12 Months:

select sum(SALES_AMOUNT) FROM SALES WHERE DATEOFSALES between trunc(sysdate - numtoyminterval(12, 'MONTH'),  'MONTH') and sysdate

 
Oracle Prior Year:

SELECT sum(SALES_AMOUNT) FROM SALES WHERE trunc(DATEOFSALES, 'YEAR') = trunc(sysdate - numtoyminterval (1, 'YEAR'), 'YEAR')

SQL Server Month to Date:

SELECT sum(SALES_AMOUNT) FROM SALES WHERE datediff(month, DATEOFSALES, getdate()) = 0 AND DATEOFSALES <= getdate()

 
SQL Server Year to Date:

SELECT sum(SALES_AMOUNT) FROM SALES WHERE datediff(year, DATEOFSALES, getdate()) = 0 AND DATEOFSALES <= getdate()

 
SQL Server Rolling 12 Months:

SELECT sum(SALES_AMOUNT) FROM SALES WHERE datediff(month, DATEOFSALES, getdate()) <= 12 AND DATEOFSALES <= getdate()

 
SQL Server Prior Year:

SELECT sum(SALES_AMOUNT) FROM SALES WHERE datediff(year, DATEOFSALES, getdate()) = 1

Hope it is useful.
Cheers!!!
Bose

Creating Briefing Book in OBIEE 11g

Briefing Book:

A briefing book is a collection of static or updatable snapshots of dashboard pages, individual analyses, and BI Publisher reports.

If your organization licensed Oracle BI Briefing Books, you can store a static snapshot of dashboard pages or individual requests in one or more briefing books. You can then download and share briefing books for viewing offline. Briefing books and their content can also be updated, scheduled, and delivered using Oracle BI Delivers.

To create an Oracle BI Briefing Book

1. Navigate to a dashboard in Oracle BI Interactive Dashboards and then perform one of the following actions:
• Click Page Option and then click the Add to Briefing Book button, available as 4^th option of Page Option in dashboard page.   

NOTE:  This button is not available on an empty dashboard page. Click the Add to Briefing Book link that appears with an individual request on the dashboard.

2. Once we are clicked with ‘Add To Briefing Book’ New pop up window will open like below;

 For Content Type, choose one of the following options:

• Snapshot. This adds the content in its current state. Snapshot content preserves the original data and is not updated when the briefing book is rerun. Snapshot content will not be updated using Oracle BI Delivers.
• Updatable. The content is refreshed whenever the briefing book is downloaded, or when it is specified as the delivery content for an iBot in Oracle BI Delivers.

 For Follow Briefing Book Navigation Links, choose one of the following options:

• No. Briefing book navigation links will not be followed.
• Yes. Briefing book navigation links will be followed.

3.  Then click ‘Browse’ with above screen to add this Dashboard page to Briefing Book. If Briefing Book is already created then Select that book and add this page otherwise browse to the location where you want to save the briefing book and give the new name for briefing book and click ‘OK’ to save.

4.            And then click ‘OK’ with next Screen

We will be getting a confirmation popup window like below after successful creation of Briefing Book

5.   Click the Cancel button to return to Oracle BI Interactive Dashboards.

 This creates an empty briefing book. The briefing book folder appears in the selection    pane in Oracle BI Answers and Oracle BI Delivers.

To add additional Dashboard pages add the same briefing book, Click the Add to Briefing Book link or button, and then select the Briefing Book which we have created or create new Briefing Book using the preceding steps.

6. If you wish to add only particular saved request from the dashboard not the entire page then Follow the below steps. Click the Page Option from the Dashboard and then click ‘Edit Dashboard’ Option

7. Once you are edited the dashboards then go to the section where the saved requests or analysis is available which you want to add to the briefing book. Click Section Properties and then Click ‘Report Links’ Options

From the ‘Report Links’ Option, select customize  and then select ‘Add to Briefing Book’ option and then Click ‘OK’.

Now Save and Run the dashboard.

8. Now if you are looking below the saved requests or analysis in dashboard you could able to see ‘Add To Briefing Book’ option. Click that option and follow the   same steps to add that report to Briefing Book

Congratulations !!! With the above steps you have successfully created Briefing Book in OBIEE 11g.

Adding Briefing Book in Dashboard

1. After successful creation of Briefing Book, Edit the dashboard page where you want to add the Briefing Book. Drag ‘Folder’ object from Dashboard Objects to the Column or Section or new Page where you want to add the Briefing Book

2. Once we are clicked ‘Folder’ Properties ‘Folder Properties’ pop up will open

 

Browse the folder where we have saved the briefing book with the ‘Folder Properties’ Window and select ‘Expand’ Option.

3. Now Save and Run the dashboard and check the changes in the dashboard. You will be able to see the newly added Briefing Book in the dashboard.

4. OBIEE 11g is supporting only .mht and PDF format to download the Briefing book. Right click on the Briefing book from dashboard and select further option to edit or download the briefing book.

 Happy Blogging!!!

Bose