AddMonths Function

[Kunal]

Hello,

This is a thread that continues from the following post: https://www.telerik.com/forums/filter-graph-data

To give a default value to a graph, I have used this expression in the report parameter:

= AddMonths(Last(Fields.wDate), -12)

The above displays the past 12 months of data on a graph which has 5 years (60 months) of data.

However, the data that I have does not include weekends and public holidays. So in scenarios such as:

Latest date (Last(Fields.wDate)) is 2nd March 2020, and the 12 month previous data is from 2nd March 2019. 2nd March 2020 is on a Monday so data is recorded, but 2nd March 2019 was on a Saturday hence there is no data on it, which results in an error saying 'Missing or invalid parameter value. Please input valid data for all parameters.'

Is there a way where, if the -12 value is on a weekend or public holiday, it picks the data closest to it, for e.g. 4th March in this case. Or alternatively a function where I just get the latest year's worth of data by default? I have tried = Today().AddMonths(-12) but it also has the same issue.

I have also attached a screenshot of my report parameter.

Thank You

[Todor]

Hello Kunal,

Generally, the Expression used to set the 'Value' of the Report Parameter should be evaluated to a value within its AvailableValues. This causes the error in the described scenario. I managed to come up with a workaround using another Report Parameter as a middleman. I have attached a sample report to demonstrate the approach. Here is the idea.

The StartDate parameter takes its initial value from this middleman parameter (named 'MiddleMan' in the sample). The middleman can be hidden, so that the user may not alter its value as it will play a role only for the initial report rendering with the default parameter values. The AvailableValues of the middleman should be filtered with the following filter:

= Fields.wDate >= = AddMonths(Parameters.EndDate.Value, -12)

This assures that the first available date will be either a year back or the next available in the data source. So, if you set the Value of the middleman to be '= First(Fields.wDate)', it will be a valid value that you would like to use as the StartDate initial value. You can set the Value for StartDate to '= Parameters.MiddleMan.Value'. The AvailableValues of StartDate parameter will remain intact, i.e. no filtering there, and the user will still be able to choose from all dates.

Another approach that you may use is to create a custom user function that returns a valid value that is among the AvailableValues and utilize it in the Expression for the initial StartDate Value.

Regards,
Todor
Progress Telerik

[Kunal]

Hi Todor,

I'm using Telerik Version: 13.1.19.618, hence I was not able to open the report you had created.

However, from your instructions, I created a MiddleMan report parameter. Attached is the screenshot.

When I add a MiddleMan report parameter with the fields as shown in the screenshot, it gives an error 'missing or invalid parameter value. Please input valid data for all parameters.'

Could you advise what I have done wrong in my MiddleMan?

Thank You,

Kunal

 

[Kunal]

Also attaching my updates StartDate and EndDate report parameters

[Kunal]

Edit:

I managed to get this working. While the AvailableValues on the middleman did not work for me, the following code in filters did:

= Fields.wDate >= = Today().AddMonths(-12)

 

Thanks for the guidance.

[John]

Thank you for posting this answer.  Saved me a ton of time as none of the other methods I was trying was working either.  I came from SSRS reporting to Telerik and it's a bit different.