posts - 88 , comments - 3 , trackbacks - 0

Extending QuickBooks Reporting with the QuickBooks ADO.NET Data Provider

The ADO.NET Provider for QuickBooks enables you to request almost any report available in QuickBooks Desktop and QuickBooks Online. The ADO.NET Provider for QuickBooks enables you to access reports as views, or read-only tables. This article shows how to customize the included reports and create new reports.

Many QuickBooks Desktop reports are already included as views. However, you may want to extend a report view. For example, if you do not see a column you need in one of the included reports, you can add columns to surface more of the report data returned. You can use the CreateSimpleReportSchema stored procedure to generate a more customized schema, or view definition.

Many QuickBooks Online reports are also available. However, QuickBooks Online reports are not enabled by default. To enable the reports you need, you can use CreateSimpleReportSchema to generate the schema for the report.

Generate Report Schemas

You can call stored procedures to automatically generate view schemas, view definitions, for almost any report available in QuickBooks Desktop and QuickBooks Online.

Generate Report Schemas for QuickBooks Desktop

You can call the CreateSimpleReportSchema stored procedure to generate the schema for reports available in desktop editions of QuickBooks.

Before you call the stored procedure, you will first need to set the Location connection property to the location of the table schemas. If you are using a desktop edition of QuickBooks, the provider will check this folder for a Desktop subfolder. If the folder exists, the stored procedure will output the schema in the Desktop folder.

After setting the Location property, call CreateSimpleReportSchema with the needed inputs. For example, to create an Item Profitability Report, set the ReportType to 'ITEMPROFITABILITY':

EXEC CreateSimpleReportSchema ReportType=ITEMPROFITABILITY

When you reconnect, the provider will pick up the newly created schema. You can then query the report:

SELECT * FROM ReportItemProfitability WHERE Label = Inventory

In Server Explorer, the report will be available in the Views folder:

Generate Report Schemas for QuickBooks Online

You can query many reports in QuickBooks Online as views. To enable a report in QuickBooks Online, call the corresponding stored procedure that will create the report schema.

Before you call the stored procedure, you will first need to set the Location connection property to the location for the table schemas. If you are using QuickBooks Online, the provider will check this folder for an Online subfolder. If the folder exists, the stored procedure will output the schema in the Online folder.

For example, execute the following statement to create a profit and loss summary report:

EXEC CreateProfitAndLossSummaryReport

Extend Report Views

The CreateSimpleReportSchema stored procedure provides default inputs that can be used to create a basic view of the report. The CreateReportSchema stored procedure enables greater control by providing additional inputs. Almost every aspect of the schema can be customized by setting the input parameters of the stored procedure.

See the "Desktop Data Model" chapter of the help documentation for more information on the available input parameters.

Adjust Report Parameters

Instead of calling CreateSimpleReportSchema every time you want to change the parameters of the report, you can edit the schema. Schemas are simple, XML-based .rsd files. In the schema, you can set any of the input parameters that are valid for that report. Reconnect to see the changes.

You can also edit the included schemas. For example, in QuickBooks Desktop, you can modify the included ReportAging report to return details instead of a summary. You can set the report type by modifying the following line in the Table-Specific Information section of the ReportAging.rsd schema:

<!-- Table-Specific Information -->
...
<rsb:set attr="reporttype"            value="APAGINGDETAIL"/>

The default location for the included schemas is the db subfolder in the installation directory.

Add Search Capabilities

After you have created the schema, you can search report data by specifying columns in the WHERE clause of the query. In addition to the columns of the report, you can specify other inputs to be used to build filter criteria.

You can use pseudo columns to add more search capabilities to your reports. Pseudo columns are columns that can only be used in the WHERE clause.

You can follow the steps below to add pseudo columns to a report. The following example shows how to search the included ReportAging report by the report type. You will add a pseudo column to ReportAging.rsb.

  1. Copy the reporttype input element from the ReportJob.rsb file into ReportAging.rsd, under the "Pseudo-Column definitions" section. Below is the input element:

    <!-- Pseudo-Column definitions -->
    ...
    <input name="reporttype" description="The type of the report."value="APAGINGDETAIL,APAGINGSUMMARY,ARAGINGDETAIL,ARAGINGSUMMARY,COLLECTIONSREPORT"default="APAGINGDETAIL" />
  2. Delete the following line from the Table-specific Information section:

    <rsb:set attr="reporttype"            value="APAGINGSUMMARY"/>

    The preceding line sets a default report type.

You can now search ReportAging for all of the available aging report types, in addition to the other inputs. For example,

SELECT * FROM ReportAging WHERE ReportType=ARAGINGDETAIL AND EntityType=CUSTOMER

Print | posted on Friday, September 21, 2012 3:04 AM | Filed Under [ quickbooks cdata quickbooks online ]

Feedback

No comments posted yet.
Post A Comment
Title:
Name:
Email:
Comment:
Verification:
 

Powered by: