I am creating a report in Report Studio, accessing a SQL Server 2008 data source.
I need to create a filter that returns only the last period of the field Month_Ref. The problem is that the field is of type VARCHAR, ex.: 06/2013.
How do I convert this field to date?
After converting, which function I can use to return the most recent month?
Thank you so much!
Hi,
If I'm well understanding your question, you can try this:
cast('01/' + Month_Ref;date)
It couldn't work because the presence of /. So, you could replace / with -.
Tell me if it works, please ;-)
Hi Friend, thanks for the reply.
It did not work ... I tried to do so and did not work:
convert(date, [Camada Lógica].[Dim2 Calculo Modelos].[Month Ref] , 111)
The field I am trying to convert has only month and year, in style YYYY / MM.
Any idea?
Tks!!!
If you convert to date type I think you must include the day information
For this reason I added the '01' dummy string ;)
I hope this help you
It worked!
Now I need to use a function that returns always the last sentence, you know what function I can use?
Thank you so much!
;)
Quote from: lorenmario on 16 Jul 2013 12:10:51 PMNow I need to use a function that returns always the last sentence, you know what function I can use?
I'm not sure what you're looking for here. What do you mean by "returns always the last sentence"?
MF.
You have your reformatted date field as an item in a query, right?
One solution is to use Maximum as the Aggregate Function. This will return the most recent month.
Hi friend, is the following:
My date field (YYYY/MM/DD) returns the following dates:
2013/01/01
2013/02/01
2013/03/01
2013/04/01
2013/05/01
2013/06/01
I need a function that returns me only the most recent month, okay? The last month of this field.
Tks!!! :)
As Michael75 suggested... use the maximum() summary in your expression to return the most recent entry.
Cheers!
MF.
Hi friend,
When do I create a query calculation works correctly, but this field that will return the last month should not appear in the report, I just need to create a filter with it.
I tried to insert a filter like this:
maximum ([Year_Month_Ref])
But it did not work ... gives error.
Can you tell me why? The maximum function does not work as a filter?!
Thank you so much!
Quote from: lorenmario on 18 Jul 2013 02:15:17 PM
Hi friend,
When do I create a query calculation works correctly, but this field that will return the last month should not appear in the report, I just need to create a filter with it.
I tried to insert a filter like this:
maximum ([Year_Month_Ref])
But it did not work ... gives error.
Can you tell me why? The maximum function does not work as a filter?!
Thank you so much!
All filters need to evaluate to a TRUE or FALSE result, so the expression you used is not valid for a filter. Try this instead:
[Year_Month_Ref] = maximum([Year_Month_Ref] for report)
Cheers!
MF.
Hello, good morning!
It worked! What a relief!
Thank you for your help!!
:)