I need to sort reports that display totals, by those totals.
(Here is an example: visitors visited open space properties in 2013. One of my reports displays the total number of visitors for each open space property. It needs to be sorted by the total number of visitors, so that one can see easily, which properties were visited the most.)
I have come to the conclusion that this cannot be done, unless I first assemble the data (including the totals), using the crosstab method, in a separate table and then build my report on that table.
The report also needs to be filtered by a date range that the user can provide. I am attaching a screenshot of the report interface.
I can't just filter the report by this date range, as I would normally do, since the totals have to be recalculated first.
Since several users could be running reports for different date ranges, this means, that I need to create a separate temporary table for each user that is running the report.
My plan was, using a solution described by Steve Workings in a different post, to:
- create the crosstab for the default date range
- use the <tbl>.duplicate method to create an empty copy that includes the report in the session folder
- fill that table with the data for the user entered date range
- create the report on the table in the session folder and pop it up for the user to see.
I can do this using session.session_folder, but this has been deprecated and is supposed to be replaced using Session.SaveDataAsFile and Session.FormatFileDataURL.
I managed to do a lot of what I need to do using these functions by looking at the examples on the wiki, but there are still two pieces of code left that I don't know how to rewrite without using session.session_folder: the part that duplicates the table into the session folder and the string that tells a5w_report_saveAs what report to run.
So here is my question: is there a way to do all of this with the new Session.SaveDataAsFile and Session.FormatFileDataURL functions? Also, am I on the right track or is there an alltogether easier way of doing this that I have missed?
Thanks a lot for any help! I have been banging my head against the wall for quite a while...
(Here is an example: visitors visited open space properties in 2013. One of my reports displays the total number of visitors for each open space property. It needs to be sorted by the total number of visitors, so that one can see easily, which properties were visited the most.)
I have come to the conclusion that this cannot be done, unless I first assemble the data (including the totals), using the crosstab method, in a separate table and then build my report on that table.
The report also needs to be filtered by a date range that the user can provide. I am attaching a screenshot of the report interface.
I can't just filter the report by this date range, as I would normally do, since the totals have to be recalculated first.
Since several users could be running reports for different date ranges, this means, that I need to create a separate temporary table for each user that is running the report.
My plan was, using a solution described by Steve Workings in a different post, to:
- create the crosstab for the default date range
- use the <tbl>.duplicate method to create an empty copy that includes the report in the session folder
- fill that table with the data for the user entered date range
- create the report on the table in the session folder and pop it up for the user to see.
I can do this using session.session_folder, but this has been deprecated and is supposed to be replaced using Session.SaveDataAsFile and Session.FormatFileDataURL.
I managed to do a lot of what I need to do using these functions by looking at the examples on the wiki, but there are still two pieces of code left that I don't know how to rewrite without using session.session_folder: the part that duplicates the table into the session folder and the string that tells a5w_report_saveAs what report to run.
Code:
function serverside_067066f1fb34448babec61699aa7c127 as c (e as p)
'Duplicate the relevant table including its layouts into the session folder:
dim t1 as p
t1 = table.open("[PathAlias.ADB_Path]\l_acti_ini.dbf")
t1.duplicate(session.session_folder + "l_acti_ini.dbf",3)
t1.close()
'Create the report on the duplicated table in the session folder and put it also into the session folder:
dim filter as c = ""
dim order as c = ""
dim filename as c = Request.GetRequestTempFileName() 'the filename that the pdf should be saved to
'dim reportname as c = a5w_report_saveAs("test@[PathAlias.ADB_Path]\l_acti_ini.ddd","pdf",filter,order,filename,global_variables())
dim reportname as c = a5w_report_saveAs("test@" + session.session_folder + "l_acti_ini.ddd","pdf",filter,order,filename,global_variables()) 'legacy->bad, how do I replace that?
dim returned as c
if file.exists(reportname)
Session.SaveDataAsFile("test.pdf",file.to_blob(reportname))
returned = Session.FormatFileDataURL("test.pdf")
'Response.Redirect("http://" + request.host + returned) 'This doesn't seem to be working, even though it should
end if
dim js as c = ""
'js = js + "window.location = 'http://" + request.host + "/A5SessionFile/" + "test.pdf" + "';" 'This code works, but I want to open the report in a modal window.
'Regarding the code below:
'I created and action with action javascript to open the fixed url "http://localhost/A5SessionFile/test.pdf"
'I then copied the resulting javascript and adjusted the url to use request.host.
js = <<%jstxt%
window['{grid.componentName}_O_1A90DA050B0C48BFA989CC73A01677BD'] = function(rowNum,rowId,objEle) {
window['{Grid.ComponentName}_1A90DA050B0C48BFA989CC73A01677BD'] = {grid.object}.createWindow('{grid.componentName}_WINDOW_1A90DA050B0C48BFA989CC73A01677BD','modal-url-resizable',{
height: '5in',
width: '8in',
title: {
html: 'Window',
location: 'top'
},
disableMove: false,
theme: '{grid.style}',
body: {
content: {
type: 'url',
url: '__workingMessage.a5w'
}
},
onHide: function() { $(this.getWindowId('body')).src = '__workingMessage.a5w';}
} );
window['{Grid.ComponentName}_1A90DA050B0C48BFA989CC73A01677BD'].show();
%jstxt%
js = js + "$(window['{Grid.ComponentName}_1A90DA050B0C48BFA989CC73A01677BD'].getWindowId('body')).src = 'http://" + request.host + "/A5SessionFile/test.pdf';"
dim js_tail as c = ""
js_tail = <<%jstxt%
}
{grid.componentName}_O_1A90DA050B0C48BFA989CC73A01677BD({grid.rownumber},'{grid.rowId}',this);
%jstxt%
js = js + js_tail
serverside_067066f1fb34448babec61699aa7c127 = js
Thanks a lot for any help! I have been banging my head against the wall for quite a while...
Comment