Knowledgebase: Setup
[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 the 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 the date value in the parameter. So, I can execute reports for a 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 the below steps for using Prompted Date Ranges in MS Excel with sp_reports.

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

Please refer to how to create sp_reports using Microsoft Excel 2007 to export reports in MS Excel.

I have already exported the BalanceSheetStandard report to MS Excel.

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

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

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

In the Excel command text, You need to replace the actual date value with the question mark for the parameter you want & save it by clicking the "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 the below format for providing the date value.

YYYY-MM-DD

Please enter both parameter values 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).