In this article, we will learn how to get reports from Quickbooks online in .NET MVC web application using SDK.
Before using any Quickbooks online API we need access token, if you don’t know how to get access token then you can find it here.
Quickbooks online provide many types of reports.like Balance Sheet, Customer Balance Summary, Customer Balance Detail, Profit and Loss, etc.
We have to query the report from the QuickBooks Online Report Service for whatever report we needed in our application.
We can specify query parameters with the query according to our needs. some query parameters available for this report are as below.
| ATTRIBUTES | DESCRIPTION |
|---|---|
| customer | Filters report contents to include information for specified customers. Supported Values: One or more comma-separated customer IDs as returned in the attribute, Customer.Id, of the Customer object response code. |
| vendor | Filters report contents to include information for specified vendors. Supported Values: One or more comma-separated vendor IDs as returned in the attribute, Vendor.Id, of the Vendor object response code. |
| start_date | The start date of the report, in the format YYYY-MM-DD. start_date must be less than end_date. Use if you want the report to cover an explicit date range; otherwise, use date_macro to cover a standard report date range. If a not specified value of date_macro is used |
| end_date | The end date of the report, in the format YYYY-MM-DD. start_date must be less than end_date. Use if you want the report to cover an explicit date range; otherwise, use date_macro to cover a standard report date range. If a not specified value of date_macro is used |
| qzurl | Specifies whether Quick Zoom URL information should be generated for rows in the report. Quick Zoom URL is a hyperlink to another report containing further details about the particular column of data. Supported Values: true, false |
| accounting_method | The accounting method used in the report. Supported Values: Cash, Accrual |
| date_macro | Predefined date range. Use if you want the report to cover a standard report date range; otherwise, use the start_date and end_date to cover an explicit report date range. Supported Values: Today, Yesterday, This Week, Last Week, This Week-to-date, Last Week-to-date, Next Week, Next 4 Weeks, This Month, Last Month, This Month-to-date, Last Month-to-date, Next Month, This Fiscal Quarter, Last Fiscal Quarter, This Fiscal Quarter-to-date, Last Fiscal Quarter-to-date, Next Fiscal Quarter, This Fiscal Year, Last Fiscal Year, This Fiscal Year-to-date, Last Fiscal Year-to-date, Next Fiscal Year |
| adjusted_gain_loss | Specifies whether unrealized gain and losses are included in the report. Supported Values: true, false |
| class | Filters report contents to include information for specified classes if so configured in the company file. Supported Values: One or more comma-separated class IDs as returned in the attribute, Class.Id, of the Class entity response code. |
| item | Filters report contents to include information for specified items. Supported Values: One or more comma-separated item IDs as returned in the attribute, Item.Id, of the Item entity response code. |
| sort_order | The sort order. Supported Values: ascend, descend |
| summarize_column_by | The criteria by which to group the report results. Supported Values: Total, Month, Week, Days, Quarter, Year, Customers, Vendors, Classes, Departments, Employees, ProductsAndServices |
| department | Filters report contents to include information for specified departments if so configured in the company file. Supported Values: One or more comma-separated department IDs as returned in the attribute, Department.Id of the Department object response code. |
Above are some query parameters that we can use according to our requirements.
Below are the few steps for getting a report form Quickbooks online,
- First, we have to create a ServiceContext with Auth tokens and realmId. For that, we need access token and realmId.
- Create an instance of ReportService by passing the ServiceContext object created in the previous step.
- Add all the desired query parameters for the report to the ReportService object created in the previous step. like Customer.ID or Vendor.ID, StartDate, EndDate, etc.
- Execute the report by passing the report name as a string as the attribute. review this Quickbooks link for a list of the attribute.
- The sample code is as below.
public ActionResult GetQuickbooksReport()
{
try
{
OAuth2RequestValidator oauthValidator = new OAuth2RequestValidator(Access_token);
// Create a ServiceContext with Auth tokens and realmId
ServiceContext serviceContext = new ServiceContext(RealmId, IntuitServicesType.QBO, oauthValidator);
serviceContext.IppConfiguration.MinorVersion.Qbo = "23";
serviceContext.IppConfiguration.BaseUrl.Qbo = QboBaseUrl;
//JSON required for QBO Reports API
serviceContext.IppConfiguration.Message.Response.SerializationFormat = Intuit.Ipp.Core.Configuration.SerializationFormat.Json;
//Instantiate ReportService
ReportService reportsService = new ReportService(serviceContext);
//Set properties for Report
//reportsService.start_date = startDate;
//reportsService.end_date = endDate;
String ReportName = "BalanceSheet";
reportsService.customer = "58";
//Execute Report API call
Report report = reportsService.ExecuteReport(ReportName);
}
catch (IdsException ex)
{
}
catch (Exception ex)
{
}
return View();
}
- As we have passed Report Name = “BalanceSheet” and Customer id = “58”, The above code will return the balance sheet report of a customer which id is “58”.
- We will get a Report in “report object”.
- We can pass the Report Name, based on our choice that which report we need. and also other parameters according to our requirements.
- So this way we can fetch reports from Quickbooks Online.
- You can also refer- How To Get Balance Sheet From Quickbooks Online Using C#.
