You can try this, it convert the timespan to minutes.
cast((-),INTERVAL MINUTE)

I am getting above error when using functions like "locate","substr","to_char" in Report studio.
I have a requirement like I need to get difference between Resolved. Time"(datatype is timestamp) and Assigned. Time (datatype is timestamp)in terms of minutes. I applied below calculation to get the difference between above two dates. -

I got the below value as one of the output "30days0hours25minutes"

I wanted to extract numeric values from the above output so that we can multiply them with respective values .For ex: If I apply "locate and substr "functions to get "30" value from that output so that I can multiply with 24*60,to get value in terms of minutes.I have applied below locate function: locate ('d',) where "Difference" is the column name which holds the "30days0hours25minutes"
It is throwing below error, when I run the report.

UDA-SQL-0219 The function "locate" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.RSV-SRV-0042 Trace back:RSReportService.cpp(779):

How can we come out from this issue? What settings we need to change?
Approach 2:
I have followed second approach where I applied below two calculations individually .So that I will get one value in terms of Days so that I can convert into minutes by multiplying 24 *60 and other calculation in terms of minutes,so that I can add this value to the earlier calculation

Calculation for Days : days ()-days ()

The above function is giving the output in terms of Days. But,we observed the following : If any of the "Assigned. Time and Resolved. Time"have the time value beyond the 12 AM, days function is rounding to next value.
How can I get the correct value?

Calculation for Minutes : (MIDNIGHT_SECONDS ()- MIDNIGHT_SECONDS())/60

The above function is working fine, if the difference is not more than 1 day. And it is showing unexpected negative values if the difference of 2 date columns is more than 1 day.

Is there any function to get difference value in terms of "minutes" apart from above 2 approaches.

Thank you in advance

Swathi N

Hi,
I don't know the other ducument other the document in the Report Studio.The function details can be found in the help document in the Report Studio.

You can try this, it convert the timespan to minutes.
cast((-),INTERVAL MINUTE)

I am getting above error when using functions like "locate","substr","to_char" in Report studio.
I have a requirement like I need to get difference between Resolved. Time"(datatype is timestamp) and Assigned. Time (datatype is timestamp)in terms of minutes. I applied below calculation to get the difference between above two dates. -

I got the below value as one of the output "30days0hours25minutes"

I wanted to extract numeric values from the above output so that we can multiply them with respective values .For ex: If I apply "locate and substr "functions to get "30" value from that output so that I can multiply with 24*60,to get value in terms of minutes.I have applied below locate function: locate ('d',) where "Difference" is the column name which holds the "30days0hours25minutes"
It is throwing below error, when I run the report.

UDA-SQL-0219 The function "locate" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.RSV-SRV-0042 Trace back:RSReportService.cpp(779):

How can we come out from this issue? What settings we need to change?
Approach 2:
I have followed second approach where I applied below two calculations individually .So that I will get one value in terms of Days so that I can convert into minutes by multiplying 24 *60 and other calculation in terms of minutes,so that I can add this value to the earlier calculation

Calculation for Days : days ()-days ()

The above function is giving the output in terms of Days. But,we observed the following : If any of the "Assigned. Time and Resolved. Time"have the time value beyond the 12 AM, days function is rounding to next value.
How can I get the correct value?

Calculation for Minutes : (MIDNIGHT_SECONDS ()- MIDNIGHT_SECONDS())/60

The above function is working fine, if the difference is not more than 1 day. And it is showing unexpected negative values if the difference of 2 date columns is more than 1 day.

Is there any function to get difference value in terms of "minutes" apart from above 2 approaches.

Thank you in advance

Swathi N

Hi Jinpeng,

Thanks a lot.Really it helped me greatly.It is working fine for me.

Is there any documnet or link to refer these kind of functions so that will be helpful to wok with the reports greatly .

Thank You In Advance
Swathi

As I know, no such advanced feature to use iteration in report studio,not very sure about this.
As a workaround, you can try this:
1) Use _day_of_week ( date_expression, integer ), for example, is 'Monday', that is _day_of_week (,1)=1, is 'Saturday', that is _day_of_week (ASSIGNED.TIME,1)=6
_day_of_week ( date_expression, integer ):Returns the day of week (1 to 7), where 1 is the first day of the week as indicated by the second parameter (1 to 7, 1 being Monday and 7 being Sunday). Note that in ISO 8601 standard, a week begins with Monday being day 1.

2) Use (-) to get the total days between them.Since you will not count 'Saturday' and 'Sunday', you can calculate days according to _day_of_week () and _day_of_week ()

I have a requirement like ,I need to get difference between two dates(Assigned.Time and Resolved.Time) and need to exclude the days which falls under Saturday and Sunday from that Difference .

Calculation for Difference column: -

In the above expression it is includes Saturdays and Sundays also.To exclude Saturdays and Sundays I have been using below logic



for(( BETWEEN and ))
{
if(dayname ()= 'Saturday' OR dayname ()= 'Sunday') THEN ('count')
else ('NO'))
(_add_days (,1)
}

means I have assigned...."Assigned.Time" value to that Data Item .It will be getting added with one day from the above logic till it is equal to Resolved time

I am geetting following error while running the report

QE-DEF-0260 Parsing error before or near position: 4 of: "FOR"QE-DEF-0261 QFWP - Parsing text: FOR(<=) { if(dayname ()= 'Saturday' OR dayname ()= 'Sunday') THEN ('count') else ('NO')) (_add_days (,1) }RSV-SRV-0042 Trace back:RSReportService.cpp(779):

Is there any alternative way to work with this logic...

Thank You In Advance
Swathi