Hello Cognoise Community !
I would like to do a very similar thing to the person who wrote this thread https://www.cognoise.com/index.php?topic=28816.0 (https://www.cognoise.com/index.php?topic=28816.0): I would like to get all sales from the first date of my cube (so no start date needed) until the last day of the previous month :
so I tried this in the detailed filter section of my report:
[MyCube].[Dim Date].[Dim Date].[Day].[Day - Complete Name] <=_last_of_month (_add_months ( current_date,-1))
But I get several errors (I only have one data item in my report : the sales measure I want to filter):
QE-DEF-0459 CCLException
GEN-ERR-0015 Initially, in date source type(s) ", the function "oneDimension" is not supported
Data source type(s) " - Function "realDataMode" is not supposted
Function "fn:current_date is not supported' in 'OlapQueryProvider'
and so on ....
https://ibb.co/wJMcg93 (https://ibb.co/wJMcg93)
My date hierarchy looks like this :
https://ibb.co/WsWq4Q1 (https://ibb.co/WsWq4Q1)
I don't get it, I have been trying this for days now, and nothing works ! My Date dimension seems pretty basic, I did not develop it because I don't have skills, but I don't get why requesting Cognos to calculate the sales until last month is so difficult ! In a relational model, it is much simplier.
Thank you in advance !
Kind regards
[UPDATE]
I managed to get rid of the errors BUT I get the wrong results ...
I wrote
[Day - Complete Name]<cast(_last_of_month(_add_months(current_date;-1));varchar(12))
instead of
[MyCube].[Dim Date].[Dim Date].[Day].[Day - Complete Name] <=_last_of_month (_add_months ( current_date,-1))
I first inserted the element [Day - Complete Name] in my query. I don't know why it works like this but at least i don't have error messages anymore ...
For info, the data format in the element [Date - Complete Name] is '2020-10-31' ('yyyy-mm-dd'), this is why I have to "cast" to "varchar(12)" (12 caracters in the string).
But the results is completely wrong, it gives me a negative sales number. I checked, it is not the sales we have on or before 2020-10-31, i don't understand...
I created one data item that is equal to cast(_last_of_month(_add_months(current_date;-1));varchar(12)) and it gives 2020-10-31. When I replace in my filter cast(_last_of_month(_add_months(current_date;-1));varchar(12)) by '2020-10-31' it gives me the correct answer.... so why aren't the results equal ?
If anyone has any idea, would help me a lot ! It is quite hard to find as many info and cases on Cognos than excel for instance ! I feel like these forums are the key for Cognos users to develop their skills, so this is why I replied to my own post with a progress I made , but I still can't figure it out ...
Thank you !
Hello,
The issue I see is that you are trying to filter a member in your Date dimension with a string. A string and a Member are not the same thing (common misconception). Instead you should try to filter the Date with a Date member, so we need to manipulate the MUN of a Date member so it represents last day of previous month.
In my example below I have created a data item that returns current_date member from the Date dimension
Data Item (Named it as current_date) : #'[Sales].[Calendar].[Day].[Day]->:[M14].[[Calendar]].[Day]].&['+
timestampMask($current_timestamp,'yyyy-mm-dd')+']]]'#
I drag the Newly created data item to the detailed filter:
[Sales].[Calendar].[Day].[Day] = [current_date]
//Oscar
Hi Oscarca!
First of all, thanks A LOT for your answer!
You are right, I do get a lot confused between members and strings. I tried to create the data item you wrote, but I didn't manage probably because I am not adapting it correctly.
My Date day member looks like this : [MyCube].[Dim Date].[Dim Date].[Day].[Day - Complete Name]
So could you please confirm I have to translate the data item like this ?
[MyCube].[Dim Date].[Dim Date].[Day]->:[M14].[[Calendar]].[Day]].&['+
timestampMask($current_timestamp,'yyyy-mm-dd')+']]]
Without a dynamic setting, my filter usually works like this : [Day - Complete Name]<'2020-10-31' so I thought this meant my Day was actually in a string format ? Actually, the filter works when I convert current date to a string (varchar(12)) as below:
[Day - Complete Name]<cast(_last_of_month(_add_months(current_date;-1));varchar(12))
But it gives me incorrect sales number ...
[UPDATE]: the sales number seems correct with the filter right above, when I create a data item that is equal to my sales measure and I set the aggregate function to "Total" instead of "Automatic". I could not change the aggregate function of my "predefined" measure so this is why I tried to recreate another data item that is equal to it .... I don't know if it makes sense !! Could it be that my sales measure was the issue ? ;D ;D
You were missing the hashtag sign:
#'[MyCube].[Dim Date].[Dim Date].[Day]->:[M14].[[Calendar]].[Day]].&['+
timestampMask($current_timestamp,'yyyy-mm-dd')+']]]'#
And you have to make sure that your Date meneber actually have this format yyyy-mm-dd
Aggregation settings could be the reason for the measure to show wrong values yes.
I also realized that it would be better performance wise to place the newly created data item :#'[Sales].[Calendar].[Day].[Day]->:[M14].[[Calendar]].[Day]].&['+
timestampMask($current_timestamp,'yyyy-mm-dd')+']]]'# in the slicer instead of the detailed filter. So we don't need this at all "[Sales].[Calendar].[Day].[Day] = [current_date]" at all.
Thanks a lot, it works perfectly !! :D :D
Will save me a lot of time !
Also thanks for explaining the difference between member and string and the trick to insert data item into filter.
Have a good day!
No worries my friend, glad to help out.
//Oscar
Sorry to bother you again: may I ask what the "hashtag" sign is doing in Cognos ?
I looked on google but all it says is : cognos on twitter ... ;D
Thanks!
The hashtags are symbols which delimitate macros.
" A macro is delimited by a number sign (#) at the beginning and at the end. Everything between the number signs is treated as a macro expression and is executed at run time."
Great, thanks a lot, I will look for documentation on macros in Cognos to expand my knowledge then.
Thanks again!