Forgive me if I posted this to the wrong board but I couldn't find one specifically for Cube Designer. I am using Cognos BI 10.2.1 FP 1. I have a requirement to set the Current Period in my Time Dimension Day level to an Oracle sys_context. Has anyone been able to successfully accomplish this? I tried entering the SQL as the value in the Current Period property but get a validation error ( XQE-V5-0016 V5 syntax error found, unexpected end of expression "" found ) even though this same exact SQL runs properly in Toad. Any help would be greatly appreciated. Thanks...
It's not something where you enter a SQL statement. Here's some documentation which might help you.
"The current period value should map to the business key value of the member you want to be the current period member. The expression can be static, based on a current date/time value, or based on a value in the relational database typically populated by the ETL process. "
http://pic.dhe.ibm.com/infocenter/cbiima/v1r0m0/index.jsp?topic=%2Fcom.ibm.swg.ba.cognos.ug_cog_rlp.10.2.1.1.doc%2Ft_cog_rlp_time_dim.html
http://www-01.ibm.com/support/docview.wss?uid=swg27040594
http://books.google.ca/books?id=HfPHAgAAQBAJ&pg=PA78&lpg=PA78&dq=cube+designer+cognos+current+period+business+key&source=bl&ots=6eWcxKXmBy&sig=_C6XgErZB1tvQ1vkFvNu_TJmsHg&hl=en&sa=X&ei=mEASVJ6INpKxyAT73oHgDw&ved=0CB0Q6AEwAA#v=onepage&q=cube%20designer%20cognos%20current%20period%20business%20key&f=false
Have you tried something like wrapping the working sql in a tiny, little view and referencing that? It's definitely not a great idea, but might work as a work-around if that's all you're looking for....
Thanks for you help. I opened an IBM ticket and they informed me that I cannot use the sys_contexts as part of my current period expression. They have put in a request for it as an enhancement. In the meantime, I have created a view using the sys_contexts and am using the view to set my current period.