Knowledgebase: QODBC
[QODBC-Desktop] Troubleshooting - How to Use Prompted Date Ranges in MS Excel with sp_reports
Posted by Jack - QODBC Support on 19 January 2018 09:14 AM

Troubleshooting - How to Use Prompted Date Ranges in MS Excel with sp_reports

Problem Description:

I am using QODBC for exporting QuickBooks reports in MS Excel. I am using BalanceSheetStandard report for specific date ranges as below.

sp_report BalanceSheetStandard show Text, Label, Amount parameters DateFrom = {d'2020-01-01'},DateTo = {d'2020-06-30'}, SummarizeColumnsBy = 'TotalOnly'

I want to pass date value in the parameter. So, I can execute report for specific date period without changing the query.

Do you know how to pass a value from Excel to the DateFrom and DateTo parameters?

Solution:

Please follow below steps for using Prompted Date Ranges in MS Excel with sp_reports.

First, You need to export the report to the MS Excel.

Please refer: How to create sp_reports using Microsoft Excel 2007 for exporting report in the MS Excel.

I have already exported BalanceSheetStandard report to MS Excel.

In Excel sheet, On the "Data" tab click "Queries & Connections" button.

In the Workbook Connection Window, Right-click on connection name & click "Properties" button to view detailed information:

Navigate to Definition tab, You can view the Connection string and Command text.

In Excel command text, You need to replace actual date value with the question mark for the parameter you want & save it by clicking "OK" button.

For Example: sp_report BalanceSheetStandard show Text, Label, Amount parameters DateFrom = ?,DateTo = ?, SummarizeColumnsBy = 'TotalOnly'

Now, whenever you Refresh Data:

Excel will prompt for the parameter value.

Please use below format for providing date value.

YYYY-MM-DD

Please enter both parameter value one by one & click "OK".

The report data get refreshed with the parameter date value.

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).