Is it possible to use the fieldname reference created in a filters (select section) in a custom function of a field (manipulate section)?
Ralph, no, reference in select section is used for unions (select over multiple modules). There is for sure an intention behind your question. What would you like to do?
Hi,
In a report I have a filter by a date field to select a variable period (year, month, quarter … or any two dates).
Then I need the start and end dates of the period selected in the filter to make a calculation through a custom function in one of the columns of the report (for example to determine the number of days that the period indicated in the filter includes).
As I understand it, I could use the variables {p1} and {p2} to refer to the values used in the filter, but I can not get it to work and I do not know if I’m interpreting it badly.
Specifically, I’m trying to use the filter values in a simple custom function … DATEDIFF ({p1}, {p2})
I do not see how to use the parameters passed in the filters ({p1} and {p2} if I’m not mistaken)
ok Ralph, if I understood you correctly following example should help you.
My report lists opportunities name, amount, creation date, closed date and the number of days between creation and closed date. The selection only contains opportunities created in 2016.
Now let me explain the content of customFunction:
I dragged the field Date Created from Opportunities. I renamed it to DateDiff so that the column title has the same meaning as its value. Then I entered the SQL customFunction.
{t} represents the name of the table from where the field is from.
{f} represents the name of the field that you dragged.
{t}.{f} is therefore the date_entered field (creation date). That’s the one I dragged.
{t}.date_closed is the second field in the same table. The one we need to make calculation.
Packed in the SQL CustomFunction we get DATEDIFF({t}.date_closed,{t}.{f})
Hi Maretval,
Thanks for your answers.
I will try to explain my needs with the example you give me:
- In the example that you mention you tries making a calculation with datediff, starting from a date that appears in a column returned in the own report. In the case of the example you want to calculate the date from another that you have (creation_date) then on custom function in the column creation_date you apply the formula Datediff ({t}. {F}, {t} .date_closed) (or vice versa) and you get the result.
In the case I try to solve (putting an example with the same data as your example) would be like asking (and show it in the same report). How many opportunities win and lost of each client were there before the report date (2016-01-01)? For example, to show a comparative of opportunities of each client.
I’m try to use {p1} and {p2} variables in a custom funcion. My report includes all the oportunities between two dates {p1} {p2} (filter - selection tab), and I need to know the days between the first day of the period {p1} and the opportunity date_entered field.
I try the custom function datediff({p1},{t}.{f}) in the date_entered column, but don’t works.
Thanks
Ralph, {p1} and {p2} are reserved names corresponding to Equals From / To when you define the selection operators in a custom kreporter field. They are not available in manipulate tab.
You would like the Equals/From value to be used as a constant in manipulate tab, it is not possible.
Regarding the count of Won & Lost Opportunities per Account, you can use SUM combined with IF in custom Function.
I grouped the report by Account ID and by sales stage.
I dragged opportunities sales stage 3 times: 1 to apply group by, 1 to count when value is Closed Won, 1 to count when value is Closed Lost.
SUM(IF({t}.{f} = ‘Closed Won’,1,0))
Hello, Good afternoon!
can you give me an exemplo how I use {p1} and {p2} in custom formula?
best regard,
Caio Carvalho
{p1} and {p2} can’t be used in custom formula.
They can be used for custom kreporter fields.
check documentation https://spicecrm.gitbooks.io/kreporter-4-x/content/advanced/kreporter-field.html
I will check. thanks so much maretval…
What is the function to filter date to display records of next month?
For example, I have date filed in the module and I want to display only records from the next month.
