7.18.0690 |
  
|
7.18.0690 |
|
Released on November 5, 2019
It was not easy to understand data delivered by peak curves because, so far, whatever the granularity of the time-frame, the cash-data or the curve, internal calculations were carried out with the most accurate granularity (days). That delivered accurate figures but they were not easy to understand because months for example may have 28, 29, 30 or 31 days resulting in significant number of peaks from one month to another.
We have changed this, stating that as soon as the granularity of the time-frame or the cash-data is in months or greater (quarters, quadrimesters, semesters, years or decades) and also unit of the peak curve applied is also expressed as months or greater, granularity used for internal calculation is always in months.
This results in much more understandable figures because we don’t have to take into account the real length of each month.
Below is a screen shot which helps understanding this:
The two first rows use peak curves in months and cash-data is also expressed as months. So, we calculate using months. It delivers clear figures, one peak every two months. The second row using the same peak curve except that we start after one period.
For the other rows, we use weeks or days as the unit of the curve so we use days instead of months for internal calculations. We see that, despite it delivers accurate figures it’s not so easy to understand. Looking at the third row, the period being 30 days, we have two peaks in the first month and one peak in the other months.
When you have complex reports (having lot of pivot tables and a huge amount of data), you may want to simplify them at run-time to save processing time and render only the data you need.
For this purpose, we have added a new event called On_Before_Create_Analytic_Report which is invoked each time you call the View function to create and display a report:
We provide below a piece of code which could be invoked by the event to modify and Excel report before it creates the data-sources and before it renders on screen.
Notice that we use VBA instead of SpreadsheetGear to modify the Excel workbook in a copy of the report because SpreadsheetGear doesn't support all Excel features and would remove extended features such as pivot tables.
Public Shared Sub EntryMethod(ByVal Es As Qdv.UserApi.IEstimate, ByVal Context As Qdv.UserApi.ICallingContext)
Try 'This is recommended to catch all errors to deliver proper error messages
'This is the entry point of your macro
'You can add your code here below...
Dim reportName As String = Context.CallingContextAnalyticReporting.CallingReportName
If reportName.ToLower.EndsWith("xlsx") Then
Dim theFileNameOnly As String = System.IO.Path.GetFileName(reportName)
If theFileNameOnly.ToLower = "Test_Report.xlsx".ToLower Then
'this is an Excel report
Dim response = MsgBox("Do you want to write 'REPLACED BY THE MACRO!' in cell A2 or the MATTER Sheet?", MsgBoxStyle.YesNoCancel, "Alter report template?")
If response = vbNo Then
'Just alter nothing, keep going
ElseIf response = vbyes Then
'Get a temporary file name
Dim tempFile As String = System.IO.Path.GetTempFileName & ".xlsx"
Try
System.IO.File.Copy(reportName, tempFile, True)
Catch ex1 As Exception
MsgBox("Cannot copy report file to temp file!" & vbCrLf & vbCrLf & ex1.Message, MsgBoxStyle.Critical, "")
Context.MustCancel = True
Exit Sub
End Try
'We must use Excel VBA to rename the sheet because it we use SpreadsheetGear we would loose dynamic arrays
'A reference to Microsoft.Interop.Excel is required to use Excel Here. See references in the Build menu of this editor
'The reference must be taken from the QDV folder as shown in this macro
Dim xlApp As New Microsoft.Office.Interop.Excel.Application
Dim wbk As Microsoft.Office.Interop.Excel.Workbook = Nothing
Try
wbk = xlApp.Workbooks.Open(tempFile)
Dim wsh As Microsoft.Office.Interop.Excel.Worksheet = wbk.Worksheets("MATTER")
wsh.Range("A2").Value = "REPLACED BY THE MACRO!"
wbk.Save
Context.CallingContextAnalyticReporting.CallingReportName = tempFile 'Continue with modified file
Catch ex2 As Exception
MsgBox("Cannot alter sheet MATTER in the report!" & vbCrLf & vbCrLf & ex2.Message, MsgBoxStyle.Critical, "")
Context.MustCancel = True
Exit Sub
Finally
If Not IsNothing(wbk) Then
wbk.Close 'Make sure we always close it
End If
xlApp = Nothing
End Try
Else
Context.CallingContextAnalyticReporting.CallingReportName = ""
Context.MustCancel = True
Exit Sub
End If
End If
End If
Catch GeneralError As Exception 'Catches all error to get proper message
MessageBox.Show(GeneralError.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error)
Context.MustCancel = True 'Cancel the event if it is called through an event
End Try
End Sub
With above code, we modify the report in a copy so that we can render a modified report and use only sheets and columns we need. This can drastically reduce the time needed to build the data source which feeds the report.
The principle is as follows:
1. we copy the incoming report to a temporary file
2. we bring request changes to this temporary file
3. we tell the application that this temporary file becomes the report to render with this line: Context.CallingContextAnalyticReporting.CallingReportName = tempFile