Use One Click Export to quickly export data to an Excel report. You can also export data and include a linked query in the file. A linked query lets you create a "live" Excel report that can be refreshed dynamically. Because a linked query contains the underlying SQL for a report, each time you refresh the report the query executes and retrieves the latest data. The dynamic nature of linked query reports makes them ideal for distribution to users who need to see changes to data on demand without waiting for new static reports to be distributed.
Click here to view a video of this feature.
Notes:
Before you can export to Excel, the Primary Interop Assemblies (PIAs) for your version of Excel must be installed. Typically, the PIAs are installed automatically when you install Microsoft Office.
Use the Export wizard if you need to specify column delimiters and advanced field options when exporting data.
To export data in one click
Select the range of data to export or click any row in a data grid to export all data.
Select an export option. Review the following for additional information:
Icon |
Option |
Description |
---|---|---|
Excel File |
Export data to an Excel file. The file is automatically saved under a default name in the default export folder. | |
CSV File |
Export data to a CSV file (comma separated value file). The file is automatically saved under a default name in the default export folder. | |
HTML File |
Export data to an HTML file. The file is automatically saved under a default name in the default export folder. | |
Excel Instance |
Export data to an Excel instance. Data is copied to a clipboard, Excel is launched, and a worksheet is populated with data. You can save the worksheet under a name and folder you specify or save it in the default export folder. | |
Excel Instance at active cell |
Export data to an active cell in Excel. First open Excel and select a cell. Then select the Excel Instance at active cell option. | |
|
Excel Pivot Table |
Export data to an Excel pivot grid report. See the Excel documentation for information on creating a Pivot Table report with external data. |
Excel Pivot Table at active cell |
Export data to an active cell for an Excel Pivot Table report. First open Excel and select a cell. Then select the Excel Pivot Table at active cell option. | |
|
Excel Linked Query |
Export the underlying query for data to Excel. |
Excel Linked Query at active cell |
Export the underlying query for data to an active cell in Excel. First open Excel and select a cell. Then select the Excel Linked Query at active cell option. | |
Excel Linked Query with pivot table |
Export the underlying query for data to an Excel Pivot Table report. See the Excel documentation for information on creating a pivot grid report with external data. | |
Excel Linked Query at active cell with pivot table |
Export the underlying query for data to an active cell for an Excel Pivot Table report. First open Excel and select a cell. Then select the Excel Linked Query at active cell with pivot table option. | |
Excel Column Chart | Export data to an Excel chart instance. Data is copied to the clipboard, Excel is launched, and a chart is created with the data. You can then modify the chart's design and save the Excel file. | |
Tip: To refresh data in a linked query report, click in Excel's External Data toolbar. This executes the underlying query for the report. See Share Excel Linked Queries for more information about creating and using linked queries. |
Note: If the window containing data does not have the focus when you select a One Click Export option, the following message displays: "Export did not find any results sets to process."
Tips:
You can use linked queries to create "live" Excel reports that can be refreshed dynamically. Because a linked query contains the underlying SQL for a report, each time you refresh the report the query is executed and the latest data is retrieved. The dynamic nature of linked query reports makes them ideal for distribution to users who need to see changes to data without waiting for scheduled updates or for new reports to be distributed.
Notes:
Any user who opens an Excel linked query must have access to a user name and password for the queried database to refresh the data. If a user does not have access to the database, they can only see the original data in the spreadsheet.
If the Excel linked query was created from an ODBC connection that uses a Data Source Name (DSN), the DSN must also exist on the computer where the Excel file is opened to refresh the data.
Scenario You need to create an Excel linked query so that the Regional Sales Manager, can track whether the Sales Representatives in his region meet their monthly and quarterly goals. By providing a linked query, you only have to create the query once, and then the Regional Sales Manager can refresh the data at any time to view the latest sales information. |
To create an Excel linked query
Select the result set and click one of the following options from the toolbar:
To refresh data in an Excel linked query
If prompted, enter a user name and password to connect to the database and refresh the data.
or
If the following error displays: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified", you must specify the DSN used by the query on your computer. Complete the following steps to resolve this issue:
Use the Data Report Designer to design reports visually. Stored in .tdr files, data reports are "live" reports that can be refreshed dynamically. What makes them dynamic is that they contain the underlying queries for report data sets. Each time a data report is opened, its query runs and the latest data is retrieved. The dynamic nature of data reports makes them ideal for distribution to users who need to see changes to data without waiting for scheduled updates or for new reports to be distributed.
The process of creating a data report has the following steps:
Note: This procedure does not cover all of the possible steps of creating a data report. It only covers the steps required to create a report for the scenario.
Scenario Your company wants to improve its global sales, starting with countries where it has a customer base but averages low sales. You created a query that displays countries with less than $500,000 in sales per fiscal year, but now you need to create a report to help represent and evaluate the results. The data should be grouped per country and show the total and average sales for each country. In addition, you want the report to look similar to the rest of the company's documents, which use specific colors and fonts. Note: This scenario uses the data generated in Visually Build a Query. |
The Toad Data Report wizard creates an initial layout of the report based on options you select, including what columns to include, whether to group data, and the basic style.
To run the wizard
Select COUNTRY_NAME and click , and then click Next. This groups the data by the country name so that each country is listed as a heading and its sales per year display below it.
Tip: You can create a secondary group by selecting another column and clicking again. A secondary group is not appropriate for this scenario, but it would be if the data included regions in each country or fiscal quarters. You would need to make the region or fiscal quarter a secondary group for the data to be organized appropriately.
Select Outline 1 and click Next.
Tip: The Outline and Align Left options are good choices if you grouped the data.
You can move, rename, and update the category of a field.
To update the fields
Click directly above the field.
Field |
Band |
Name |
---|---|---|
FISCAL YEAR | groupHeaderBand2 | Fiscal Year |
SUM(SALES.AMOUNT_SOLD) | groupHeaderBand2 | Sales Amount |
Avg | groupFooterBand1 | Average |
Right-click the COUNTRY NAME field (not the COUNTRY_NAME field on the right) in groupHeaderBand1 and select Delete.
Note: The COUNTRY NAME field is a label that precedes the COUNTRY_NAME field value. If you preview the report before you delete the COUNTRY NAME field, the country displays as 'COUNTRY NAME Argentina'. It is clear that Argentina is the country name, so this field is redundant.
The Data Report Designer uses styles to format different objects and kinds of data. Once you define a style, you can apply it to similar fields to create a consistent and easy-to-update design.
To create, update, and apply styles
Select the Foreground Color field, and then select MidnightBlue in the Web tab.
Note: All colors in this scenario are from the Web tab.
Set the following properties for the new styles:
First Style |
Properties |
Name |
OddRow |
Background Color |
WhiteSmoke |
Border Color |
MidnightBlue |
Borders |
Bottom |
Font |
Tahoma, 10pt |
Second Style |
Properties |
Name |
EvenRow |
Border Color |
MidnightBlue |
Borders |
Bottom |
Font |
Tahoma, 10pt |
Third Style |
Properties |
Name |
TableHeading |
Background Color |
MidnightBlue |
Font |
Tahoma, 10pt, bold |
Foreground Color |
White |
Fourth Style |
Properties |
Name |
CountryName |
Font |
Tahoma, 14pt, bold |
Foreground Color |
Black |
Select TableHeading in the Style field (under Styles) for the Fiscal Year and Sales Amount fields in groupHeaderBand2.
Field |
Property |
---|---|
Even Style | EvenRow |
Odd Style | OddRow |
Bands group the information layout in the report and you can add controls to include additional tables, images, fields, and other items in the report.
Select the groupHeaderBand1 and move it down half an inch.
Tip: It is helpful to expand a band before adding controls to give you plenty of room in which to work.
© ALL RIGHTS RESERVED. Termini di utilizzo Privacy Cookie Preference Center