Extending QuickBooks Reporting with the QuickBooks ADO.NET Data Provider
- by dataintegration
The ADO.NET Provider for QuickBooks comes with several reports you may
request from QuickBooks by default. However, there are many more that are not readily available. The ADO.NET Provider for
QuickBooks makes it easy for you to create new reports and customize existing ones. In this article, we will illustrate
how to create your own report and retrieve it from the Server Explorer in Visual Studio. For this example we will show how
to create an Item Profitability Report.
Creating the report script file
Step 1: Download the sample reports available here. Extract them to a folder of your choice.
Step 2: Make a copy of the ReportGeneralSummary.rsd file and rename it to ItemProfitability.rsd. Then open the file in any text editor.
Step 3: Open the installation directory of the ADO.NET Provider for QuickBooks. Under the \db\ folder, locate the ReportJob.rsb file. Open this file in another text editor.
Note: Although we are using ReportJob.rsb for this example, other reports may be contained in other Report*.rsb files. We recommend consulting the included help file and first locating the Report stored procedure and ReportType you are looking for. Otherwise, you may open each Report*.rsb file and look under the "reporttype" input for the report you are attempting to create.
Step 4: First, let's rename the title of ItemProfitability.rsd. Near the top of the file you will see a title and description. Change the title to match the name of the file. Change the description to anything you like. For example:
<rsb:info title="ItemProfitability" description="Executes my custom report.">
Just below the Title, there are a number of columns. The Id represents the row number. The RowType represents the type of data returned by QuickBooks. The ColumnValue* columns represent all of the column data returned by QuickBooks. In some instances, we may need to add additional ColumnValue columns.
Step 5: To add additional ColumnValue columns, simply copy the last column, paste it directly below, and continue increasing the numerical value at end of the attribute name. For example:
<attr name="ColumnValue9" xs:type="string" readonly="true" required="false" desc="Represents a column of data."/>
<attr name="ColumnValue10" xs:type="string" readonly="true" required="false" desc="Represents a column of data."/>
<attr name="ColumnValue11" xs:type="string" readonly="true" required="false" desc="Represents a column of data."/>
<attr name="ColumnValue12" xs:type="string" readonly="true" required="false" desc="Represents a column of data."/>
...
Caution: Do not rename the ColumnValue* definitions themselves. They are generalized so that we can understand each type of report returned by QuickBooks. Renaming them to something other than ColumnValue* will cause your columns to return with null values.
Step 6: Now let's update the available inputs for the table. From the ReportJob.rsb file, copy all of the input elements into ItemProfitability under the "Psuedo-Column definitions" comment. You will be replacing the existing input elements in ItemProfitability with inputs from ReportJob. When you are done, it should look like this:
<!-- Psuedo-Column definitions -->
<input name="reporttype" description="The type of the report." value="ITEMESTIMATESVSACTUALS,ITEMPROFITABILITY,JOBESTIMATESVSACTUALSDETAIL,JOBESTIMATESVSACTUALSSUMMARY,JOBPROFITABILITYDETAIL,JOBPROFITABILITYSUMMARY," default="ITEMESTIMATESVSACTUALS" />
<input name="reportperiod" description="Report date range in the format (fromdate:todate), and either value may be omitted for an open ended range (e.g. 2009-12-25:). Supported date format: yyyy-MM-dd." />
<input name="reportdaterangemacro" description="Use a predefined date range." value="ALL,TODAY,THISWEEK,THISWEEKTODATE,THISMONTH,THISMONTHTODATE,THISQUARTER,THISQUARTERTODATE,THISYEAR,THISYEARTODATE,YESTERDAY,LASTWEEK,LASTWEEKTODATE,LASTMONTH,LASTMONTHTODATE,LASTQUARTER,LASTQUARTERTODATE,LASTYEAR,LASTYEARTODATE,NEXTWEEK,NEXTFOURWEEKS,NEXTMONTH,NEXTQUARTER,NEXTYEAR," default="ALL" />
...
Step 7: Now let's update the operationname attribute. This needs to match the same operationname used by ReportJob. After you have copied the correct value from ReportJob.rsb, the operationname in ItemProfitability should look like so:
<rsb:set attr="operationname" value="qbReportJob"/>
Step 8: There is one more thing we can do to make this a true Item Profitability report. We can remove the reporttype input and hardcode the value. To do this, copy and paste the rsb:set used for operationname. Then rename the attr and value to match the name and value you want to use. For example:
<rsb:set attr="operationname" value="qbReportJob"/>
<rsb:set attr="reporttype" value="ITEMPROFITABILITY"/>
After this you can remove the input for reporttype. Now that you have your own report file, we can move on to displaying the report in the Visual Studio server explorer.
Accessing the report through the Data Provider
Step 1: Open Visual Studio. In the Server Explorer, configure a new connection with the QuickBooks Data Provider.
Step 2: For the Location connection string property, enter the directory where the new report has been saved to.
Step 3: The new report should appear as a new view in the Server Explorer. Let's retrieve data from it.
Step 4: You can specify any inputs in the WHERE clause.
New Report Example Script
To help you get started using this new QuickBooks Data Provider report, you will need to
download the QuickBooks ADO.NET Data Provider
and the fully functional sample script.
