Filename Based on Variable / Calculation

JHDCC

Forums

Hi everyone.

I'm sure this must be a relatively straightforward, but wonder if someone could help with the below.

I have an ODBC import script, which performs various pieces of analysis, then exports the results.

The source ODBC imports data from the financial year to the end of the previous month, for the financial year of the previous month ... ie
in March 2020, it should return April19-Feb20 for financial year 19/20
in April 2020 it should return April19-Mar20 for financial year 19/20
in May 2020 it should return April20-April20 for financial year 20/21

The export is currently named Analysis "Current FY".xlsx ... however, this isn't always strictly speaking true, and I would prefer to use the calculated financial year in the file name if posslbe ... ie Analysis "CALCULATED FY".xlsx

Thanks for any assistance.

Simplified script below

Sub Main
IgnoreWarning(True)
Call ODBCImport1()
Call ExportDatabaseXLSX1()
End Sub

Function ODBCImport1
dbName = "ODBC Import Current FY.IMD"
Client.ImportODBC where payment_date between '01-APR-20' || substr(EXTRACT (YEAR FROM ADD_MONTHS (SYSDATE, -4)),-2) and LAST_DAY (ADD_MONTHS (TRUNC (SYSDATE), -1)) and FINANCIAL_YEAR = substr(EXTRACT (YEAR FROM ADD_MONTHS (SYSDATE, -4)),-2) || '/' || substr(EXTRACT (YEAR FROM ADD_MONTHS (SYSDATE, +8)),-2) "
Client.OpenDatabase (dbName)
End Function

Function ExportDatabaseXLSX1
Set db = Client.OpenDatabase("Analysis Current FY.IMD")
Set task = db.ExportDatabase
task.IncludeAllFields
eqn = ""
task.PerformTask "Server Path\Analysis Current FY.XLSX", "Database", "XLSX", 1, db.Count, eqn
Set db = Nothing
Set task = Nothing
End Function