Error on filter function after upgrade

[ToriBurns]

Good day everyone!

I have a report which uses a relational datasource. The report runs perfectly before but it suddenly throws an error after upgrading from 10.2 to 10.2.1. I noticed that what causes the error is the filter on the data items. Whenever I remove the data item that the other data item filters on the crosstab, but remains on the query, the report starts to cause an error. Does anyone know how to fix this?. For now, I just add the data item. And set its property to box type 'None' so that it will not show. But whenever I export the report to excel The row still shows up with no data. Is there a way to remove or hide that? Thanks in advance. :)

[navissar]

Hi Tori,
Every time Cognos is upgraded there's a risk of such behaviours, because improvements to the query engine mean slightly different rendering of a query.
However, from your description it is very difficult to offer a solution. You write that you receive an error, but not what the error is. You write that a data item is causing it, but not what the data item does and how it is calculated. You write that this is relational, but not whether you're using DQM or CQM.
So, in order to try to help, please provide the following information:
1. What is your Crosstab showing?
2. What does the problematic data item does, how is it calculated?
3. How is your filter written?
4. Are you using DQM or CQM?
5. What is the error message?

[ToriBurns]

1.) There are three levels on the vertical part of crosstab node member. And the last data item the is the one that has a filter.
2.) The data item is filtered based on the input on the prompt page. The code is: filter([data item], [data item] = ?parameter1?), aggreate function and rollup aggregate function is set to automatic.
3.) filter([data item], [data item] = ?parameter1?), aggreate function and rollup aggregate function is set to automatic.
4.) How would I know if it's DQM or CQM?
5.) OP-ERR-0217 -  An unexpected type of parameter value was encountered. The specified parameter type is 'String', but the expected type is 'memberUniqueName'.

Sorry for not providing enough information and also if I can hardly explain what the crosstab is showing. Thanks for the reply and thanks in advance. :)

[Francis aka khayman]

the value in your prompts should be Member Unique Names (MUNs).

your prompts uses static values as choices?


anyway base solely on the information you gave, try this one:

filter([data item],caption([data item]) = ?parameter1?)

[ToriBurns]

One of my prompts uses static values but the prompt that I used for filter does not contain any static value.

[navissar]

I don't have an environment to test, but try khayman's proposal. If this doesn't work, use a detail filter - you are working on raltioal so it should work without any issues.

[ToriBurns]

I tried to change the filter to the code or syntax you suggested. But there was an error thrown.

Error 1:QE-DEF-0478 Invalid coercion from 'string' to 'memberSet, member, hierarchy, level' for '[__ns_0].[TO_CHAR(LOGDATE,DD-MON-YYYY)_dim].[TO_CHAR(LOGDATE,DD-MON-YYYY)_dim].[level3].[level3key]' in 'filter([__ns_0].[TO_CHAR(LOGDATE,DD-MON-YYYY)_dim].[TO_CHAR(LOGDATE,DD-MON-YYYY)_dim].[level3].[level3key],rolevalue('_memberCaption',[__ns_0].[TO_CHAR(LOGDATE,DD-MON-YYYY)_dim].[TO_CHAR(LOGDATE,DD-MON-YYYY)_dim].[level3].[level3key]) = ?parameter1?)'.

[Francis aka khayman]

better post a screen shot. if you can include the hierarchy structure so much the better

[ToriBurns]

Sorry Khayman wasn't able to reply immediately. I find it hard to explain the structure. Hope this screenshot will do. For now, we already have a work around. When the date is removed in the first column and placed after the second column which is group1 and group2 on the illustration, the errors is not showing or let's say that it throws no error at all.

I wonder what causes the error when the date is in the first column?And, it works even if the date is in the first column only if I add the column of the data item that is to be filtered before the row of filtered_data_item1 and filterred_data_item2. Thanks for your will to help. GodBless. :)

[Francis aka khayman]

ok you are working on relational but you are using dimensional function. i don't even know if that will work.

in relational usually we use detail filters... just as Nimrod says

[ToriBurns]

Yeah I'm working on relational. I can't use detail filter because I have to filter each data item and not the whole crosstab or that filter is only applicable for two filtered data item and not for the whole query. And, each data item have different condition for filtering. Anyway, thanks for your help. :)

[navissar]

You can use dimensional functions on relational when working with a crosstab, because Cognos renders a small cube on server when creating a crosstab.
Tori, it sounds to me like something in the report got broken during the upgrade. Have you tried re-building the crosstab?

[ToriBurns]

Yes Nimrod. It still throws an error. But, whenever I remove the data item for date, which is placed on the first column of the crosstab on y-axis, it shows no error.

[Francis aka khayman]

you said.. ." it works even if the date is in the first column only if I add the column of the data item that is to be filtered before the row of filtered_data_item1 and filterred_data_item2. "

try this... click on the leftmost column, open the 'Properties' property (sorry need to say that, can't help it) :D, check the data item that is to be filtered. if it does not work for that column, try another column

[ToriBurns]

Thanks for your reply Khayman. I can see that you really want to help and I really appreciate it. :)
I tried your suggestion but the report still throws an error. Thank you very much. :D