I've created a layout-table report based on an SQL query that I'd like to programmatically generate and save to an excel file. If you right-click the report and select "Show Xbasic...", the code is generated for various formats, including excel.
The xbasic appears to run an undocumented xbasic function named a5_report_excel_save() that takes a single pointer variable 'def'. The pointer variable has several sub-elements including def.filter. I can set the def.filter element with no issue and the report runs fine.
Unfortunately, the SQL table that I'm running the report against is quite large, and so I'd like to use arguments within the SQL query to filter on the SQL server rather than in the report itself. Adding arguments to the SQL query works fine, HOWEVER the "Specify Argument Values" dialog runs to set value at runtime, which won't work for my purpose- I need to run the report without user input.
The problem is that I don't know where to programmatically set and pass the argument into the def pointer variable. There is a sub-element def.json that appears to be the logical place to look, but I just can't figure out where/how to pass it through.
So I guess what I'm asking is:
A) Is there any documentation for A5_report_excel_save()
-or-
B) does anyone know how to pass SQL arguments to either the def variable or the JSON statement?
Here's the xbasic that is generated:
The xbasic appears to run an undocumented xbasic function named a5_report_excel_save() that takes a single pointer variable 'def'. The pointer variable has several sub-elements including def.filter. I can set the def.filter element with no issue and the report runs fine.
Unfortunately, the SQL table that I'm running the report against is quite large, and so I'd like to use arguments within the SQL query to filter on the SQL server rather than in the report itself. Adding arguments to the SQL query works fine, HOWEVER the "Specify Argument Values" dialog runs to set value at runtime, which won't work for my purpose- I need to run the report without user input.
The problem is that I don't know where to programmatically set and pass the argument into the def pointer variable. There is a sub-element def.json that appears to be the logical place to look, but I just can't figure out where/how to pass it through.
So I guess what I'm asking is:
A) Is there any documentation for A5_report_excel_save()
-or-
B) does anyone know how to pass SQL arguments to either the def variable or the JSON statement?
Here's the xbasic that is generated:
Code:
dim def as p
dim def.filename as c ' optional filename
dim def.filter as c ' optional filter
dim def.excel as c = <<%str%
'-- hundreds of lines of code to define the Excel report--
%str%
dim def.json as c = <<%json%
{ source : 'SQL:::Name::[MY-NAMED-CONNECTION]\nSELECT Statement_Date, Application, Account, Center, Acct_Type, Total_Debit_Trans, Qualifying_Debit_Trans FROM dbo.[MY TABLE] WHERE Month(Statement_Date) = Month(:LastDayOfMonth) AND Year(Statement_Date) = Year(:LastDayOfMonth)' , optimization : 'flat_order' , calcs : '[ALL THE REPORT CALCULATIONS]' , grand : {
header : { '" [TITLE] "' : '" [TITLE] "' }
, children : [
{ group_name : 'acct_type' , break_expression : 'acct_type' , order_expression : 'acct_type'
, header : { style_row_2 : '"font-underline:true;"' ,style_row_3 : '"font-underline:true;"' ,' [CALCULATED FIELD] ' : ' [CALCULATED FIELD] ' ,' [CALCULATED FIELD] ' : ' [CALCULATED FIELD] ' }
,children : [ { group_name : 'detail'
, footer : { statement_date : 'statement_date' ,account : 'account' ,center : 'center' ,total_debit_trans : 'total_debit_trans' ,qualifying_debit_trans : 'qualifying_debit_trans' }
} ]
}
]
, footer : { 'System->Date' : 'System->Date' ,'system->pagexofy' : 'system->pagexofy' }
}
}
%json%
a5_report_excel_save(def)
Comment