On the BI Answers page, observe the highlighted options in the screenshot below. Click the SH folder in the Shared Folders section (displayed to the left) on the Catalog tabbed page.

This displays all the pre-created requests in the SH folder. Click the Category Sales for last 12 months link from the list of requests (displayed on the right).

You can see that the request is shown on the BI Answers page, which displays the chart. Click Modify to modify this request.

The request is displayed in the Edit mode, showing the Criteria tabbed page.
To add more fields to the request, expand the nodes SH> Measures> Sales Facts and click Gross Profit to add this filed to the request.

Similarly, also add the Amount Sold field to the request. (These measures that you have added should be reflected in the Criteria tab as shown in the screen below):

Click the Save Request icon (found on the top-right corner of the page) to save the changes you have made to the request.
Enter the name of the request as "Category Sales and Profits for last 12 months" and click OK.

In MS Word select Insert > Chart.
(First, you will add a bar chart.)

Note: When you save the Answer request as BI Publisher Report, its loads the XML data definitions into the RTF template file so that you can define the layout for the report data.

In the Chart window that appears, define the graph characteristics by following the instructions listed below carefully:

1. Drag:
   Sales Facts. Gross Profit to the Values field
   Products. Prod Category to the Labels field (These are highlighted in the screenshot below.)
2. Ensure that Bar - Graph Vertical is selected as the graph Type, and select April from the Style
   drop-down list.
3. Type Profits by Category as the Title.
4. Click Preview to preview how the graph looks like, and click OK.

Resize the chart image in MS-Word to fit in the rtf file better.
The bar graph in the template file looks like this:

Similarly, insert another Pie chart for showing the sales percentages by category.
Follow the instructions listed steps below :

1. Drag:
   Sales Facts. Amount Sold to the Values field
   Products. Prod Category to the Labels field (These are highlighted in the screenshot below.)
2. Select Pie Chart as the graph Type, and April as the Style.
3. Type Sales by Category as the Title.
4. Click Preview to preview how the graph looks like, and click OK.

Resize the chart image in MS-Word to fit in the rtf file better.
The pie chart in the template file looks like this: (Observe both the graphs):

Now select Insert > Table Wizard to define a format for the table data in the report.

This displays the Table Wizard. Select Table, and click Next.

Ensure that ROWSET/ROW is selected as the Grouping Field, and click Next.

Select Calendar. Calendar Month Desc, Calendar. Calendar Month Name, Products. Prod Category,
Sales Facts. Amount Sold, and Sales Facts. Gross Profit from the list on the left, and add them to the list on the right. Click Next.
See the screen given below:

Select Calendar. Calendar Month Desc from the Group By drop-down list,
select Calendar. Calendar Month Name from the Then By drop-down list to group the data by calendar month. Accept the defaults for other options, and click Next.

In this step you can select the sort orders for various fields.
Select Products. Prod Category from the Sort By drop-down list, accept the defaults for other options, and then click Next.

Edit the labels of the fields Products. Prod Category as Product Category, Sales Facts. Amount Sold as Sales Revenue, Sales Facts. Gross Profit as Profit.
Then click Finish to complete the creation of the table template.

The template file with the table you created looks like this:

Important Note: The BI Publisher Desktop Template Builder has just created a table for you. Notice the words and letters with the gray background. These are called form fields. Form fields are Word objects that allow you to reference other data (for example, a mail merge letter). BI Publisher uses form fields in two ways:

• First is to reference data fields from the report definition (like YEAR and MONTH). 
• The second use is to embed instructions that control how the data fields will be laid out (like G, F, and E). 

If you are curious to know what these instructions are, double-click the form field and view the Help text. It is important to treat these form fields carefully and not accidentally delete or move them. Doing so will change the layout of the table in your report. Also, you can add or modify your own form fields with XSL commands to do more sophisticated things with the table layout.

Now, you can additionally use the formatting features in MS Word on this template, such as changing the background and text colors, styles, adding an auto layout for the table, aligning the number fields to the right, and so on.
Perform the following steps in MS Word:

1. Select the Sales Revenue and Profit columns in the table and click the Align Right icon in MS Word to align these fields to the right.
2. Select the Calendar. Calendar Month Desc, and Calendar. Calendar Month Name headers (grouping fields) and apply the text color of dark brown (or any other appropriate color of your choice), make it bold, and also apply the Arial font in 12 pt.
3. Also, in the table, make all the column headers dark blue in color and style them in bold. Make sure that the font is Arial 10 pt.

The table looks like this after the above format changes:

Note: You can also use the Auto Format feature in MS Word to define a format for the table. To do this, select the table that you have added, and select Table > Table Auto Format menu option in MS Word. Select an auto format for the table from the list of available formats.

Also, to add an appropriate number format to the Sales Revenue field, double-click the _Sales_Facts.Amount_Sold_ field (highlighted in the screenshot) below the column header Sales Revenue.

This displays the Text Form Field Options for editing the formats.
Select Number from the Type drop-down list.

Enter the Default Number as 99,999,999.99, and select the format that has a $ in the beginning as shown, and click OK.

Similarly, add an appropriate number format to the Profit column too.
Now the table looks like this:

You can also add a form field to calculate the profit totals per month.
Note: Add this total below the table, with an appropriate label such as Total Profit for the month of < Month name> is : <Profit Total>. Also align this entire label to the right so that it appears below the Profit column of the table.

Click Insert> Field. (To insert the total in the place of the Profit Total in the above label)

In the Field window that appears, define the characteristics by following the instructions listed below carefully:

1. Click Sales Facts. Gross Profit, and select Sum from the Calculation drop-down list to add a total on this field.
2. Select On Grouping option to add the total by category. (All these are highlighted in the screenshot below.)
3. Click Insert to insert this total in the template. Click Close.

As this is also a number field, add an appropriate number format to it:

Also add another field in place of <Month name> in the label:

1. Click Insert> Field.
2. Click Calendar. Calendar Month Name.
3. Select the On Grouping option.
4. Click Insert to insert this field. Click Close.

Now the table along with the fields added looks like this:

Add appropriate colors to the Month name and Profit total fields (also add new lines where appropriate) so that the table looks like as shown below:

Save the rtf file in MS Word. You can add your initials to the file when saving.

You can also preview how the data in the report looks like.
In MS Word, select Oracle BI Publisher> Preview Template> PDF to preview the data in PDF format.

The charts and the data are shown in the screens below:

Note: The header, footer, and other format changes you have defined in the template are reflected. You can also view the data in other formats such as HTML, RTF, and so on. Also, do not close the MS Word application or log out from BI Publisher to continue with the next topic.

In MS Word, select the Oracle BI Publisher > Publish Template As option.

Note: If you have not saved the template in RTF format, it may prompt you to save the template in RTF format first before publishing.

The Upload as new dialog box appears.
Enter Template1 as the template name, and click OK.

After the template is uploaded, it displays the following message. Click OK again.

Note: You can also view the data in the BI Publisher report using the template published, by connecting to BI Publisher (Web) as the Administrator.
(Select All Programs > Oracle Business Intelligence > BI Publisher from the Start menu)

Navigate to My Folders > Learn, and click the View link below the Category Sales and Profits for last 12 Months report to view the data.

The report is displayed using the template you have created.

Log in to Oracle BI Presentation Services as Administrator (password Administrator).
(Hint: Refer to the first topic for logging in to Presentation Services.)

When you log in to Presentation Services, the BI Dashboards page is opened displaying the sample dashboard created with the sales trends as shown below:

From the Page Options drop-down list (at the top-right corner of the page), select Edit Dashboard.

The Dashboard Editor page is displayed. Click the Add Dashboard Page icon to add a page to the dashboard.

This displays the Add Dashboard Page screen. Enter My BI Publisher Page as the Page Name, and optionally enter a suitable description. Click OK.

Drag BI Publisher Report from the Dashboard Objects list to the section on the My BI Publisher Page as shown below:

You can see that the BI Publisher Report object is added to the dashboard, click the Properties link on this object (highlighted in the screenshot).

The BI Publisher Report Properties screen is displayed. Click Browse to browse and specify the path for BI Publisher Report.

Select the Category Sales and Profits for last 12 Months report that you created earlier, from the path
My Folders > Learn and click OK.

This brings you back to the BI Publisher Report Properties screen.
In the Display Mode section of the report properties, select View the Latest Version option, accept defaults for other options, and then click OK.

This will take you back to the Dashboard Editor page, click Save to save the changes you made to the dashboard.

Note that the dashboard now has the additional page that you created.

Click My BI Publisher Page tab to view the report that you published on this page:

Scroll down to see the graphs and the entire report data in PDF format:

Log in (if not logged in) to BI Publisher (Web) as Administrator (password Administrator).
Note: You can log in to BI Publisher by logging in to Presentation Services and selecting More Products > BI Publisher option, or you can also log in to BI Publisher directly from:
All Programs > Oracle Business Intelligence > BI Publisher option from the Start menu.

The BI Publisher Welcome page is displayed:

Navigate to My Folders > Learn, and click Create a new report.
Enter From BI Server as the name of the report, and click Create.

The newly created report is displayed in the BI Publisher. Click the Edit link displayed below the report name to edit the properties.

The report is displayed in edit mode.
Click Data Model and click New to define the data source for this report.

On the Data Set page that appears, select SQL Query from the Type drop-down list.

On the Data Set page, select Cache Result. Ensure that Oracle BI EE is selected as the Data Source. (Observe the highlighted options in the screenshot.)
Click Query Builder to create an SQL Query.

The Query Builder is opened displaying the SH subject area (metadata repository) objects displayed on the left.
Drag the Channels, Products, and Sales Facts SH schema objects one by one to the Model canvas on the right.

Hint: Take the help of the screens below:

Drag Channels to the Model canvas.

Similarly, drag Products and Sales Facts tables to the Model canvas.

Select the following columns to be displayed in the query from the Model objects (by selecting the check boxes beside the column names):
-Select Channel Desc from Channels.
-Select Prod Category and Prod Name from Products.
-Select Amount Sold from Sales Facts.

After the column selection, the Query Builder screen should look like this:

Click the Conditions link.
In the Conditions screen, you can define the Aliases, Sorts, Group By fields, Conditions, and so on.
Observe the options, and accept the defaults.

Click the Results link to see if the query results are displayed correctly without any error, and click Save to save the query.

This will take you back to the BI Publisher Data Set page. Observe that the query is displayed in the SQL Query field. Click the Save icon to save the report.

Note: A simple SQL query is created here, but you can create more complex queries by defining Sorts, Aliases, Conditions, Group By fields, and so on in the Conditions screen of the Query Builder.
Also, you can view the data after creating and publishing the template for this report. Now you can view only XML data for the report. (Click View to go to the View mode and click View again, if you want to view the XML data for the report).

Start the MS Word application from the program menu. From the Oracle BI Publisher menu, select Log On.

Enter Administrator as the username and password and click Login.

The Open Template window is displayed. Navigate and select the BI Publisher report that you have created from BI Server metadata from the path: My Folders > Learn > From BI Server, and click Open Report. (This will load the data from the query.)

In the Word document, select Insert > Chart to define a graph format for the SQL query data.

This opens the Chart window.
Define the graph characteristics by following the instructions listed below carefully:

1. Drag:
   Amount Sold to the Values field
   Prod Category to the Labels field
   Channel Desc to the Series field (See the highlighted options in the screenshot below.)
2. Ensure that Bar Graph - Vertical is selected as the graph Type, and select Autumn from the Style
   drop-down list.
3. Enter Product Sales Revenues by Channel in the Title field.
4. Click Preview to preview how the graph looks like.
5. Click OK.

The graph you defined for the SQL query data is displayed in the Word document as shown: (Observe the graph title that is displayed).

You can also add a table to represent the SQL query data.
Select Insert > Table Wizard.
In Table Wizard, select Table, and then click Next.

Ensure that ROWSET/ROW is selected as the Grouping Field, and click Next.

Click to include all the available fields in the table. (See the screenshot below.)
Click Next.

Select Channel Desc from the Group By drop-down list; ensure that the Group above option is selected for this field. Select Prod Category from the Then By drop-down list, and select the Group left option for this field.
Click Next. (Observe the highlighted options in the screenshot.)

Select Amount Sold from the Sort By drop-down list, select the Descending and Number options, and then click Next.

Edit the labels for the fields: Change Prod Name to Product Name and Amount Sold to Sales Revenue. Click Finish.

The template that you have defined looks like this along with the table:

Make the appropriate format changes in MS Word to the table in the template:
Guidelines to make changes:
- Change the text colors to look similar to the colors used in the graph.
- Change column widths of the table as appropriate.
- Change Amount Sold field to a number field which is displayed in dollars ($).
The table in the template looks like the following: (See the table highlighted in the screenshot.)

Save the template in RTF format as ProductSalesbyChannel_Templ.rtf.

You can also preview how the template looks like with the data in MS Word by selecting
Oracle BI Publisher > Preview Template, and then selecting a format such as PDF to view the data.
This helps you to preview, and make any further changes to the template before it is published.

The PDF output in preview looks like this:

In MS Word, select the Oracle BI Publisher > Publish Template As option.

Note: If you have not saved the template in RTF format, it may prompt you to save the template in RTF format first before publishing.

The Upload as new dialog box appears.
Enter Template1 as the template name, and click OK.

After the template is uploaded, it displays the following message. Click OK again.

( If not already connected) Now connect to BI Publisher as Administrator.
(Select All Programs > Oracle Business Intelligence > BI Publisher from the Start menu).

Open the From BI Server report from My Folders > Learn.
Note that the report is displayed using the template that you have created and published as shown in the following screenshot:

Scroll down to see the table data (a portion of the data is shown below):

You can also view the data in other formats such as PDF, RTF, MS Excel, and so on.

Note: If you have time , and want to try more hands-on with BI Publisher, continue with the next topic of creating a report with parameters and list of values.

(If not logged in) Log in to BI publisher as Administrator, and navigate to My Folders> Learn.
Click the report icon beside the From BI Server report.

This displays additional report actions in the Report and Folder Tasks.
Click Copy Report and then click Paste from Clipboard to paste it in the same folder. (See the screens below):

Rename this copied report as From BI Server with Parameters and LOVs.

Note: You can directly modify the From BI Server report to add parameters and LOVs too.

Now click the Edit link below the report to open the report in the Edit mode.

Click New Data Set1 found under the Data Model node in the Report pane on the left to edit the SQL Query for the report. (Observe the screen below):

This displays the SQL Query for the report on the right.
Edit the query to add a bind variable chname in the query as below: (observe the last line)

select Channels."Channel Desc" as "Channel Desc",
Products."Prod Category" as "Prod Category",
Products."Prod Name" as "Prod Name",
"Sales Facts"."Amount Sold" as "Amount Sold"
from SH."Sales Facts" "Sales Facts",
SH.Products Products,
SH.Channels Channels
where Channels."Channel Desc" = :chname

Click Save to save the query.

Now click the Parameters node in the Report pane on the left and click New to create a parameter.

This displays the Parameter screen on the right; define the following:

Enter chname as the name of the parameter, and select String as the Data Type.
Enter Internet in the Default Value field, and select Text as the Parameter Type.
Also, type Enter a Channel Name: as the Display Label. (See the screen below).

Click Save to save the changes to the report.

Click View to view the report data using the existingTemplate1 in html format .
Note that the report data and graph are displayed with the default parameter value, that is for the Internet sales channel.

You can enter the names of other channels to see the data.
For example, in the parameter field enter Partners as the channel name and click View again to see the data and graph for this channel:

Now click Edit to edit the report and define a List of Values to be used with this parameter chname.
In the Report pane displayed on the left, click List of Values and click New to create a new list of values.

In the List of Values screen that appears on the right, ensure that Oracle BI EE is selected as the Data Source, and click Query Builder to define a query for defining a list of values.

In the Query Builder screen that displays the SH subject area on the left, click Channels to add it to the Model canvas on the right, and select Channel Desc column (by selecting the check box beside it).
Click Save.

This takes you back to the List of Values screen again which displays the query.
Click Save to save the changes to report:

Now click chname found under the Parameters node in the Report pane on the left to edit it and associate it with the List of Values that you have created:

In the Parameter screen that appears on the right, now select Menu as the Parameter Type.

In the Menu Setting section that appears below the General Settings section, enter Select a Channel Name: in the Display Label field, and ensure that the New List of Values 1 (that you created) is selected from the List of Values drop-down list.
Also, select Can Select All option to be able to see the data for all the departments.
Click Save to save the report.

Now click View to view the report using Template1 in HTML format.
Observe that the chname parameter is now displayed using the list of values for the channels that you have defined:

Note: You can select any of the channels from the drop-down list to view the data for that channel or select All to view the data for all channels.