OLAP filters

[Former Member]

Hi,

I am creating one universe connected to SSAS and need to apply one filter,

the object where i want to apply filter is YEAR object stores data as YYYY format.

now i want to give a probmpt to user and want to display report on the basis of this only.

the filter i am using is like this

<FILTER KEY="[QRY Date].[QRY Date].[YEAR]">

<CONDITION OPERATORCONDITION="InList">

<CONSTANT CAPTION="format(@Prompt('Enter From Date','D',,mono,free,persistent),'YYYY')"/>

</CONDITION>

</FILTER>

but it seems not working,

so can anyne help me in filtering Year from simple prompt,

also please explain about CONSTANT CAPTION,

Regards,

Vishvanath

[Former Member · Accepted solution]

Hi,

Check the syntax of your prompt:

 @Prompt('Enter From Date','D',,mono,free,persistent) 

Use it as:

 @Prompt('Enter From Date','D',,mono,free) 

There is no need to use persistent, it s the default feature of BO to keep the last saved refreshed value in the prompt.

But if you want to use persistent then modify the definition as:

 @Prompt('Enter From Date','D',,mono,free,persistent,) 

Hope this helps you.

Edited by: rohit12 on Aug 25, 2009 7:22 AM

[Former Member · Accepted solution]

Thanks for your answer rohit,

actully what i want is out of this date prompt i want to pass Year only not the whole date,

so how i can achieve that,

I am using this in filter in OLAP universe,

Regards,

Vishvanath

[Former Member · Accepted solution]

try using substring...

Or you can use the object for the LOV of years

Or you can manually define a LOV for the prompt

[Former Member · Accepted solution]

Hi Rohit,

I tried using substring but it does not work.

finally mu filter condition is like

<FILTER KEY="[QRY Date].[QRY Date].[YEAR]">

<CONDITION OPERATORCONDITION="InList">

<CONSTANT TECH_NAME="format(@Prompt('Enter From Date','D',,mono,free)),'YYYY')"/>

</CONDITION>

</FILTER>

can you help on this, i want to show user a full date prompt and out of that i want to subtract Year,

actully in cubes i have data in this format...

YEAR

Month

Day.

so i want to show data for selected date only, that's why i am using filter

Regards,

Vishvanath

[Former Member · Accepted solution]

Hi Vishvanath,

Is this "QRY Date.QRY Date.YEAR" is the technical definition of the object?

as you have used TECH_NAME in the Level Attribute, if not please change this to technical name.

How are you planning to show the complete date in the prompt as you have not used any LOVs while defining the prompt. Can you elaborate on this.

Please post the error message you are getting while parsing the prompt with substring and the syntax you are using

[Former Member · Accepted solution]

Hi,

Actully previously i was trying with CAPTION insted of TECH_NAME, but it was not working so i just tried with that.

you suggest what will be right syntex.

also i am nto getting any error but i am not getting the desired values at all,

In query filter if i pass hard coed value as 2009 then it works but i want to make it prompt driven dynamic condition.

<FILTER KEY="[QRY Date].[QRY Date].[YEAR]">

<CONDITION OPERATORCONDITION="InList">

<CONSTANT TECH_NAME="2009)"/>

</CONDITION>

</FILTER>

so please help how i can replace this 2009 by Date prompt or any other option.

Regards,

Vishvanath

[Former Member · Accepted solution]

Hi

Make the datatype of QRY Date.QRY Date.YEAR to character and then try this....

if this works we can try some-thing else..

 
<FILTER KEY="Tech Name">
<CONDITION OPERATORCONDITION="InList">
<CONSTANT TECH_NAME="@Prompt('Enter From Date','A',{'2008','2009','2010'},mono,free)"/>
</CONDITION>
</FILTER>

[Former Member · Accepted solution]

Hi Rohit,

It doesnot work, gives some parsing error,

The supplied XML is not valid, Tech Namestate N/A

Regards,

Vishvanath

[Former Member · Accepted solution]

I tried this and this works fine

<FILTER KEY="[QRY Date].[QRY Date].[YEAR]">

<CONDITION OPERATORCONDITION="InList">

<CONSTANT TECH_NAME="@Prompt('Enter From Date','A',{'2006','2009','2010'},mono,free)"/>

</CONDITION>

</FILTER>

but we need to remove hardcoding.

[Former Member · Accepted solution]

Hi,

I tried it it does not give any parse error also tried and working in report too...

This is the exact definition I have used.

<FILTER KEY="[0CALYEAR].[LEVEL01]">
<CONDITION OPERATORCONDITION="InList">
<CONSTANT TECH_NAME="@Prompt('Enter From Date','A',{'2008','2009','2010'},mono,free)"/>
</CONDITION>
</FILTER>

By seeing the error, I think you forget to replace the "tech Name" with the Object definition in the first line:

 <FILTER KEY="Tech Name"> 

If this is not the case can you post your exact definition of the prompt?

[Former Member · Accepted solution]

Now use your YEAR object in place of hard coded LOV as:

<FILTER KEY="QRY Date.QRY Date.YEAR">
<CONDITION OPERATORCONDITION="InList">
<CONSTANT TECH_NAME="@Prompt('Enter From Date','A','TIME\YEAR',mono,free)"/>
</CONDITION>
</FILTER>

Hope it works for you..

Edited by: rohit12 on Aug 25, 2009 9:41 AM

[Former Member · Accepted solution]

I replaced this

<FILTER KEY="[QRY Date].[QRY Date].[YEAR]">

<CONDITION OPERATORCONDITION="InList">

<CONSTANT TECH_NAME="@Prompt('Enter From Date','A','QRY Date\YEAR',mono,free)"/>

</CONDITION>

</FILTER>

but it gives error invalid defination (UNV0023)

Year object is like this

[QRY Date].[QRY Date].[YEAR]

Please suggest

[Former Member · Accepted solution]

I don't know why it is throwing error at your end. It is working perfectly fine at my end.

This is what I have used:

<FILTER KEY="[0CALYEAR].[LEVEL01]">
<CONDITION OPERATORCONDITION="InList">
<CONSTANT TECH_NAME="@Prompt('Enter From Date','A','Calendar Year\L01 Calendar Year',mono,free)"/>
</CONDITION>
</FILTER>

Try this syntax once:

<FILTER KEY="@Select(QRY Date.QRY Date\YEAR)">
<CONDITION OPERATORCONDITION="InList">
<CONSTANT CAPTION="@Prompt('Calendar Year(s)','A','QRY Date.QRY Date\YEAR',multi,constrained)"/>
</CONDITION>
</FILTER>

Edited by: rohit12 on Aug 25, 2009 11:33 AM

Edited by: rohit12 on Aug 25, 2009 11:37 AM

[Former Member · Accepted solution]

Thanks Rohit, this works fine.

now after this i have some change in query,

In my Universe i have 3 objects

and their defination is

QRY Date.QRY Date.YEAR

QRY Date.QRY Date.MONTH

QRY Date.QRY Date.DAY

can i create a object where i have the complete defination.

for example if the values in 3 objects are like :

2009

08

23

then can i have a object in universe which contains value like 23-08-2009

if yes then how can i achieve this.

the actual requirement is like i have to prompt user a calander and he will select a date range, so i need to show data for that date range only, how i can achieve the same.

Regards,

Vishvanath

[Former Member · Accepted solution]

Thank God...at last it worked...

I don't know if this will work or not but you can try it:

<EXPRESSION>@Select(QRY Date.QRY Date\YEAR) + @Select(QRY Date.QRY Date\MONTH) + @Select(QRY Date.QRY Date\DAY) </EXPRESSION>

P.S. Year, Month and Day should be the character.

Regards,

Rohit

[Former Member · Accepted solution]

Hi Rohit,

Yes all 3 objects are in Char format, but it gives invalid defination error,

when i removed @select from object defination, it parsed.

but now when i use this in report as prompt it gives some MDX query error.

can we see the query it generates in OLAP Report, as we can see in relation report when we do edit query.

the SQL which is generated.

Regards,

Vishvanath

[Former Member · Accepted solution]

I don't think we can see the generated query but anyway you can see the log genrated giving details of the error...This setting needs to be done at CMC.

I think '+' operator will be giving an error. .Can you concat these objects at the DB level instead of concating it in the universe?

[Former Member · Accepted solution]

the DB i am using is MOLAP cubes, so will not be able to change over there.

how to see log ? can we see query inside logs ?

[Former Member · Accepted solution]

Once the setting is done at CMC the query log is generated with the error message. (I am not aware which settings needs to be done exactly)

If it cannot be done at the DB level, then I think you have to go for search option on how to concate the strings in the SSAS. You can explore on Generate, Descendent and Membertostr or Tupletostr functions of SSAS.