Conditional Expression throwing Error

[cognos05]

Hi All,
I am trying to check the below condition and it throws an error function name not found and CLR Exception.

I am trying to compare the caption of a day variable with the last completed date as below .

if (Caption( tail ( [DailySales].[Day].[Day] , 1 ) ) ='19')
then(1)
Else(2)

Thanks,
Nithya

[BigChris]

Could you post the error messages by any chance?

[BigChris]

And didn't you ask the same question just the other day?

[cognos05]

RSV-VAL -0034 Failed to validate the variable Boolean1. CRX-API-0011. The FUnction name was not found at or near the position '11' in expression ;
This is the error. Yes, still waiting for a fix.

[cognos05]

Hi,
So I think the condtional variable doesnt like the expression.so what I did is created a dataitem in the list query like

DataItem - if(cast(caption(tail (children([DailySales].[Day].[Day].[Day]->:[PC].[@MEMBER].[Day]),1)),integer) =#timestampMask(  _add_days ($current_timestamp, 0 )  , 'dd' ) #)
Then(1)
Else(0)


And then tried to use this in the conditional variable. And if its 0 show cross tab with data otherwise show cross tab with error.

But the issue is I am getting the below error :

The provider query function is not supported in the context it is used
Function realdatamode is not supported in NoDataModeProvider.


My list query has the region name and cam id from a relational source which i use for bursting , now I am adding the above dataitem to that query and using it in the conditional variable, is this causing the issue.

Please suggest some workarounds.

Thanks,
Nithya

[cognos05]

yes, I tried adding the dataitem to relaational query which is the burst query and it doesnt like the expression

caption(tail (children([DailySales].[Day].[Day].[Day]->:[PC].[@MEMBER].[Day]),1)) or any members from the cube.

so what would be the workaround here to check for previous day sale error.

Thanks,
Nithya

[Lynn]

And didn't you ask the same question just the other day?

Yes, still waiting for a fix.

You've got two posts addressing the same thing, plus a previous post that appears to be related. I've pretty much lost the plot entirely.

http://www.cognoise.com/index.php/topic,29840.msg96826.html#msg96826
http://www.cognoise.com/index.php/topic,29894.msg97047.html#msg97047

You've got a dimensional source, clearly, since you are referencing MUNs, but then you say you've got a relational source for CAM ID and region to do bursting. So which is it? Relational or Dimensional?

I think your overall approach is flawed. Set up a relative time dimension in your cube and you won't need to construct a MUN using the current timestamp to find the latest day sales. You would just reference the appropriate element in the relative time dimension.

As Big Chris suggested in an earlier post, you can use Event Studio to decide whether to run the report or not. A query that gets the caption of the latest member and compares it to the latest day would do the trick.

Using the sample package I created a data item to find the last day member that has revenue:

Code Select Expand


caption (
  tail (
    filter (
      members ( [Sales (analysis)].[Time].[Time].[Day] )
      , [Revenue] > 0 )
  )
)


I then created another data item to create a string, matching to the caption format, based on today's date:

Code Select Expand


substring ( cast (  current_date, VARCHAR(10) ), 1, 4 )
||
substring ( cast ( current_date, VARCHAR(10) ), 6, 2 )
||
substring ( cast ( current_date, VARCHAR(10) ), 9, 2 )


I can now compare these two query items to determine if the dates are matching or not.

[cognos05]

Lynn,

Mine is Dimensional Reporting. But you need to have a relational table to burst a dimensional report.

Relational table will have a Burst Key and Email ID column and on bursting this will key will have a masterdetail relationship with the query item in the model.

Now I have issues with comparing the caption of previous day member in the condtional variable.

Add a new string or boolean variable and try using this below condition and it will not work.

if(cast(caption(tail (children([DailySales].[Day].[Day].[Day]->:[PC].[@MEMBER].[Day]),1)),integer) =#timestampMask(  _add_days ($current_timestamp, 0 )  , 'dd' ) #)
Then(1)
Else(0)

On a conditional variable it doesnot allow me to use these functions,this works when created on a seperate query item.

But I then go and use this query item on the conditonal variable say Query3. DataItem1  and have a conditonal block, I get a error message the query item  is not used in layout so add it to property list.

When I add it to the property list of the page, then it would say burst query cannot be nested.
So I have to add this to my relational query along with burst key and email ID and this will give me query function not supported error.

Thanks,
Nithya



Thanks,
Nithya

[Lynn]

Lynn,

Mine is Dimensional Reporting. But you need to have a relational table to burst a dimensional report.

Relational table will have a Burst Key and Email ID column and on bursting this will key will have a masterdetail relationship with the query item in the model.

Now I have issues with comparing the caption of previous day member in the condtional variable.

Add a new string or boolean variable and try using this below condition and it will not work.

if(cast(caption(tail (children([DailySales].[Day].[Day].[Day]->:[PC].[@MEMBER].[Day]),1)),integer) =#timestampMask(  _add_days ($current_timestamp, 0 )  , 'dd' ) #)
Then(1)
Else(0)

On a conditional variable it doesnot allow me to use these functions,this works when created on a seperate query item.

But I then go and use this query item on the conditonal variable say Query3. DataItem1  and have a conditonal block, I get a error message the query item  is not used in layout so add it to property list.

When I add it to the property list of the page, then it would say burst query cannot be nested.
So I have to add this to my relational query along with burst key and email ID and this will give me query function not supported error.

Thanks,
Nithya

I created the two data items I showed in my previous post and was able to use them in a variable to conditionally render an object. You might consider reading up on how to create variables. Your if-then-else doesn't make sense for either a Boolean or a string expression. A Boolean expression should resolve to true or false, not 1 or 0. For example, [X] = [Y] is a Boolean expression. It will return true when X and Y are equal or false if they are not. String expressions ought to return strings, not numbers. The link below gives some examples.

https://www-01.ibm.com/support/knowledgecenter/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.ug_cr_rptstd.10.2.2.doc/t_cr_rptstd_modrep_adding_variable.html

Secondly, you should notice the available functions in the dialog when creating your variable. You will see they are all report expression functions so it isn't surprising that cast, caption, tail, and children are causing problems when used in a variable expression definition.

Did you look at the query expressions I provided in my previous post or did you stop reading after the relational/dimensional question? I created a query with those expressions and then referenced those query items in a variable I had associated to a layout so that I could conditionally render it.

However, as I said in my last post, I don't think you should be fabricating a MUN using the time stamp to figure out the latest day sales. You should use a relative time member in the power cube. In this situation at least the report wouldn't fail, it would simply send older figures.

A simple Event Studio agent, using query items similar to what I already posted, could get the caption of the latest member and then fire the report (or not) depending on if it matches the expected label based on the current date.

[cognos05]

Thanks, it makes sense . I appreciate your help.

Thanks,
Nithya