In certain situations, you may find the need to run a SQL Statement with conditions, like a StartDate and EndDate, and import the resulting data into Excel. Additionally, taking it a step further, reading input parameters directly from the Excel sheet can significantly simplify the data refresh process, eliminating the necessity to modify the query each time.
The following article provides a step-by-step guide on how to accomplish this using the CData ODBC Driver for SageIntacct.
-
After installing the driver and setting up the DSN Configuration, in Excel, open the Data tab and choose Get Data -> From Other Sources -> From ODBC
-
Choose the DSN name you have just created from the first step and expand the Advanced options to create the SQL statement.
-
Click OK, then select Load To, and decide on your preferred data visualization method (Table, PivotTable Report, PivotChart). When specifying the data destination (Existing or New Worksheet), be sure to allocate some empty rows for future use with input parameters.
-
Once you've successfully loaded the data into the Excel sheet, the next step is to refresh it with different Start_Date and End_Date values. You'll need to select two cells and rename them as follows:
-
Select the cell that you want to name.
-
Click the Name box at the left end of the formula bar.
-
Type the name (ex. StartDate) you want to use to refer to your selection.
-
Press ENTER.
-
Do the same for the EndDate.
-
Input the updated values for the Date parameters into the cells you've renamed.
-
Open the Power Query Editor, proceed to the Advanced Settings, and make the following adjustments to the query:
-
Click "Done" and proceed while opting to ignore the privacy level checks for this file.
-
Click Close&Load to import the data into the Excel sheet. When you modify the values in the cells you renamed, the data will be filtered based on the specified criteria.
