pre-defined conditions in an OLAP universe

[Former Member]

Hi,

I need to schedule my report and pass date parameter as yesterday.

so for that i was trying predefined condition in universe, but i am not able to pass day/year dynamically.

the OLAP cube's hierarchy is like

A. YEAR>Month>Day

so please help me to achieve this

i have used a conditional filter and the condition is

<FILTER KEY="[A].[Day]">

<CONDITION OPERATORCONDITION="InList">

<CONSTANT CAPTION="19]"/>

</CONDITION>

</FILTER>

<OPERATOR VALUE=u201DANDu201D><FILTER KEY="[A].[Month]">

<CONDITION OPERATORCONDITION="InList">

<CONSTANT CAPTION="1"/>

</CONDITION>

</FILTER></OPERATOR><OPERATOR VALUE=u201DANDu201D><FILTER KEY="[A].[Year]">

<CONDITION OPERATORCONDITION="InList">

<CONSTANT CAPTION="2009"/>

</CONDITION>

Regards,

Vishhu

[Former Member · Accepted solution]

Hi,

If you are using Microsfot Analysis Services, it is possible to pass yesterday as parameter but if you are using SAP BW it is not possible because of SAP MDX restrictions. For SAP it is recommended to cretae an SAP Exit on a BEx query to do what you need.

For MSAS, here is a sample based on AdwentureWorks. This sample returns 1 if the current member from Date.Calendar is lower than Yesterday:

<FILTER EXPRESSION="IIF ( CDate([Date].[Calendar].CurrentMember.MemberValue) < Now()-1,1,0)">
<CONDITION OPERATORCONDITION="Equal">
<CONSTANT CAPTION="1"/>
</CONDITION>
</FILTER>

Didier

[Former Member · Accepted solution]

Thanks Didier,

i need to go in this direction but the problem is i dont have a date object.

the date is divided into three objects Year, Month and Day.

so can you please suggest how i can filter ony year or month from now() function which you have suggested.

Regards,

Vishvanath

[Former Member · Accepted solution]

Hi,

You can try this:

<FILTER EXPRESSION="IIF (CDate(CSTR([Year].CurrentMember.MemberValue)+ "/" + CSTR([Month].CurrentMember.MemberValue)  + "/" + CSTR([Day].CurrentMember.MemberValue)  ) < Now()-1, 1, 0)">
<CONDITION OPERATORCONDITION="Equal">
<CONSTANT CAPTION="1"/>
</CONDITION>
</FILTER>

Didier

[Former Member · Accepted solution]

Hi Didier,

Thanks for your reply, we are almost there to solve the problem.

but there is something which is missing so its not working as expected, i will explain you the data as well so you can co relate the problem.

I have data in Year Object as (2009,2008)

in Month (7,8)

in Day (1-11)

so now i have created a filter as suggested by you and marked as use filter as mandotary in query. and then exported the universe.

and tried to create new report where i am taking year, month, day and volume as measure.

but the result is it shows all days data, 1-11 for 7th and 8th month for year 2009 and 2008.

so i think the filter is not applying properly.

the filter i have used is like this suggested by you.

<FILTER EXPRESSION="IIF (CDate(CSTR([GPRS DATE].[Year].CurrentMember.MemberValue)+ "/" + CSTR([GPRS DATE].[Month].CurrentMember.MemberValue) + "/" + CSTR([GPRS DATE].[Day].CurrentMember.MemberValue) ) < Now()-1, 1, 0)">

<CONDITION OPERATORCONDITION="Equal">

<CONSTANT CAPTION="1"/>

</CONDITION>

</FILTER>

so pelase suggest, and i am using MSAS 2008. and BOXI 3.1

Regards,

Vishvanath

[Former Member · Accepted solution]

Hi,

I suggest that you create a calculated measure in order to see the content of the expression used in the filter.

So create a calculated measure in the universe like this:

<EXPRESSION>CDate(CSTR([GPRS DATE].[Year].CurrentMember.MemberValue)+ "/" + CSTR([GPRS DATE].[Month].CurrentMember.MemberValue) + "/" + CSTR([GPRS DATE].[Day].CurrentMember.MemberValue) ) </EXPRESSION>

Set the new object as dimension with character data type and use it in your WebI report.

Check if the calculated expression rtuens what you expected.

Didier

[Former Member · Accepted solution]

Hi Didier,

you are too good.now i am able to see the output of both the conditions.

Now i am not able to use it, i mean according to the condition if date will match then it will return 1 else 0.

now please tell me how to use this filter. in report if i drag this condition in query filter the report window closes immediately

and if i mark it as mandatory it return all the dates.

so please suggest next step,

right now condition is like this

<FILTER EXPRESSION="IIF (CDate(CSTR([GPRS DATE].[Year].CurrentMember.MemberValue)+ "/" + CSTR([GPRS DATE].[Month].CurrentMember.MemberValue) + "/" + CSTR([GPRS DATE].[Day].CurrentMember.MemberValue) ) = CDate(Format(now()-1,"MM/dd/yyyy")), 1, 0)">

<CONDITION OPERATORCONDITION="Equal">

<CONSTANT CAPTION="1"/>

</CONDITION>

</FILTER>

so what to do next.

Regards,

Vishvanath

[Former Member · Accepted solution]

Hi Vishvanath,

I am not sure to understand what happened in the query panel.

Do you mean that when you drag and drop the filter in WebI query panel, the query panel automatically closes?

What do you mean by mark it as mandatory? Is it in the universe?

By the way I noticed that the format for the current date (Now) is "MM/DD/YYYY" whereas the format for the GPRS members is "YYYY/MM/DD". I think that you need to have the same format for all 2 operands to have a correct result.

Regards,

Didier

[Former Member · Accepted solution]

Hi Didier,

Yes when i drag the filter in query panel and run the report Web Intellegence Rich Client itself closes.

so i tried to make that filter mandatory as we have option when we write a filter, "Use filter as mandatory in query"

Also i have changed the format of GPRS date.

so how this filter will work now.

<FILTER EXPRESSION="IIF (CDate(CSTR([GPRS DATE].[Month].CurrentMember.MemberValue)+ "/" + CSTR([GPRS DATE].[Day].CurrentMember.MemberValue) + "/" + CSTR([GPRS DATE].[Year].CurrentMember.MemberValue) ) = CDate(Format(now()-1,"MM/dd/yyyy")),CDate(Format(now()-1,"MM/dd/yyyy")) , 0)">

<CONDITION OPERATORCONDITION="Equal">

<CONSTANT CAPTION="1"/>

</CONDITION>

</FILTER>

Now-1 will return 08/12/2009 and i have 08/12/2009 in my Tables so this condition will return 1 as it is true.

so it should not show any data other then 08/12/2009, but it is still showing other dates also.

so please help me to find where i am doing something wrong.

Regards,

Vishvanath

[Former Member · Accepted solution]

Hi Vishvanath;

Concerning the first issue it sounds like a bug in Web Intelligence so I suggest that you submit a support case and provide as mush as possible any information that can help: screenshot, WebI trace, MDX trace (see attached zip file on how to activate traces).

Concerning the issue where alll dates are displayed I cannot explian that without detailed information.

So please, can you attach any file that can help: screenshot, universe (save it for all users with a shared connection), webi report, traces, etc.

Regards,

Didier

[Former Member · Accepted solution]

Hi Didier,

i am not able to attach any file,

can you guide me how i can attach universe and screenshot here.

Regards,

Vishvanath