Toad for Oracle – FastReports Q&A

Q: What is FastReports?

A: FastReports is the report engine in Toad that allows for custom and dynamic reporting against live data sets. FastReports is included with the standard edition of Toad for Oracle. To use Fast Reports, select a dataset in Toad, then do Grid – Report.

Q: Can I export a report to PDF or HTML or some other format?

A: Yes, in the preview window, the 4th button on the top toolbar says “Export” when you hover your mouse over it.  Click it.  Several formats are available.

Q: How can I add my own style to the report wizard?

A: Save the following code into a file called “xmlstyles.xml”, and place it in the same directory as toad.exe. You can then add or edit “Style” nodes to your liking.

<?xml version="1.0" encoding="utf-8"?>
<stylesheet>
<style Name="FastReport">
<item Name="Title" Color="8388608" Font.Color="16777215" Font.Height="-16" Font.Style="1"/>
<item Name="Header" Color="536870911" Font.Color="128" Font.Style="1"/>
<item Name="Group header" Color="15790320" Font.Style="1"/>
<item Name="Data" Color="536870911"/>
<item Name="Group footer" Color="536870911" Font.Style="1"/>
<item Name="Header line" Color="536870911" Frame.Typ="8" Frame.Width="2"/>
</style>
<style Name="Standard">
<item Name="Title" Color="536870911" Font.Height="-16" Font.Style="1"/>
<item Name="Header" Color="536870911" Font.Style="1"/>
<item Name="Group header" Color="536870911" Frame.Typ="8"/>
<item Name="Data" Color="536870911"/>
<item Name="Group footer" Color="536870911" Frame.Typ="4"/>
<item Name="Header line" Color="536870911" Frame.Typ="8" Frame.Width="2"/>
</style>
<style Name="Soft gray">
<item Name="Title" Color="14211288" Font.Height="-16" Font.Style="1"/>
<item Name="Header" Color="15790320" Font.Style="1"/>
<item Name="Group header" Color="15790320" Font.Style="1"/>
<item Name="Data" Color="536870911" Font.Style="0"/>
<item Name="Group footer" Color="536870911" Frame.Typ="4"/>
<item Name="Header line" Color="536870911" Frame.Width="2"/>
</style>
<style Name="Corporate">
<item Name="Title" Color="0" Font.Color="16777215" Font.Height="-16" Font.Style="1"/>
<item Name="Header" Color="0" Font.Color="16777215" Font.Style="1"/>
<item Name="Group header" Color="52479" Font.Style="1"/>
<item Name="Data" Color="536870911"/>
<item Name="Group footer" Color="536870911" Font.Style="1"/>
<item Name="Header line" Color="536870911" Frame.Width="2"/>
</style>
</stylesheet>

You can pull the style out of an existing report by going into the report designer, then off the main menu click “Report” → “Styles”. Save the style to a file, then edit the file with notepad. You’ll see it’s an XML file with the same format as “xmlstyles.xml”. Copy the “style” node out of it, give it a name, and add it to your “xmlstyles.xml” file.

Q: How can I make a calculated field in a report?

A: Yes, create a calculated field with the report writer – it’s easy once you know what to do.

Drop BuffGetsPerExec.fr3 in your disk.

Example

1. Let’s use the SGA Trace screen to create a report on BufferGets by execution.
2. Open the SGA Trace screen under the Tools menu.Then do Grid → Report and close the wizard. when the report writer comes up, Do a File→Open and open your file you just downloaded.
3. To see how I calculated buffer gets / execution, click the field under “BG/Exec”, then click the “Events” tab.
4. Now click where it says “Memo6OnBeforePrint” and you’ll see the code that calculates the field.
5. To get out of the “code” mode and back to the report designer, click the “Page1” tab up near the top left.

Next time you open Grid→report in SGA-Trace, it should bring this report up automatically.

Q: How can I use FastReports to make a pie chart, bar chart, line chart,or some other kind of chart?

A: 

SELECT owner, COUNT(*) table_count
FROM dba_tables
GROUP BY owner

1. Enter the query in the SQL Editor and run it.
2. click the grid, then go to the main menu and choose Grid, Report.
3. cust click “Finish” in the report wizard.
4. In the report editor, delete the Master Data band, and the Page header and footer bands.
5. Click the last button on the left toolbar. The hint on it is “other objects”. It will give some choices, choose “Chart Object”, and then click the main report area to drop the chart. A chart editor should appear.
6. In the chart editor, click the “Add Series” button at the top (it has a plus sign on it). It will give you some choices, choose “Bar”.
7. In the right-hand side, select “Dataset” in the “DataSet” drop-down.
8. Choose Dataset.”OWNER” for the X-Values.
9. Choose Dataset.”TABLE_COUNT” for the Y-Values.
10. Hit OK to close the chart designer.
11. right-click somewhere on the report and choose “Edit” to change the page layout to Landscape.
12. resize your chart to fill the whole page.
13. preview the report. woo-woo!
14. Now to add the next chart. close the preview window, right-click the chart and choose “Edit” to get back to the chart designer. Click “Series1” to edit the series. (Now, you see how you can add another series if there is another column in your dataset that you’d like to graph)
15. some things to play with – down at the bottom where it says “other options” – you can sort or show just the top values.
16. You can add a new page if you want and make a 2nd chart on the same data like this: Close the chart editor, then right-click on the top left of the report designer where it says “Page1” and choose “New page”. Now repeat steps 6 – 13 again except choose a pie chart instead.

If you save the report layout, your saved report will come up automatically next time you run the same query (or, one with the same columns) in the sql editor and choose “Grid, Reports”

Here’s an example of a FastReports graph with the table names obfuscated to protect the innocent:

Q: I was working on a report and clicked off Toad, now I can’t find my report! Where did it go?

A: Minimize the Toad application and see if the FastReports design window is there.

Q: Where can I get more information documentation for FastReports?

A: Click here http://www.fast-report.com/en/documentation/frx3user/index.htm or hit F1 once you’ve entered the FastReports interface.

Q: Where can I use FastReports?

A: The most ovbious places are:

• Data Grid in the SQL Editor
• Data Tabs in the Schema Browser.

The cool thing is that the FastReports engine can read ANY grid in Toad to create its report(s). Here are a few other examples:

• SGA Trace – Single Record Grid and All Records Grids
• Schema Browser – Tables, LHS Columns, Info, Indexes, Auditing, etc Tabs
• DBA – NLS Parameters

The Master-Detail window has a special connection to Fastreports. There, you can set up your master-detail relationship (with as many datasets as you like, connected in whatever way you like), and send all of the datasets, all master-detailed together, to FastReport to create report just the same way you set up your master-detail window. And once you save your FastReport defintion file and your Master-Detail relationship, they will be paired together so that next time you come into the master-detail window you can open your master-detail relationship and the report will come with it.

Hint: Anywhere you see a grid, select the grid and do Grid – Reports to see if the FastReports engine is available.