Concatentation query subject - unable to filter on?

[ry1633]

Hi there,

I've discovered (and my users called me on it too) that if I create a concatenated query subject like [LAST NAME] || ', ' || [FIRST NAME]  --- then in Report Studio or Query Studio, Cognos says that I can't filter on that item.  Why is that and is there anything I can do about it?  -ryan

[raj_aries81]

Hi there,

I've discovered (and my users called me on it too) that if I create a concatenated query subject like [LAST NAME] || ', ' || [FIRST NAME]  --- then in Report Studio or Query Studio, Cognos says that I can't filter on that item.  Why is that and is there anything I can do about it?  -ryan

Hi ry1633,

Can you tell us the exact error that you see, we can use the calculated data items in filters. Did you try running the report by hard-coding the filter using contains/like.

Regards
Raj

[ry1633]

I can run the report with the filter hard-coded in it, but my users don't want it that way.

In Query Studio I get the this error:  A filter cannot be based on the selected report item.     

In Report Studio I get this error:  RQP-DEF-0149 The query specification is incorrect.RQP-DEF-0457 Referenced Query 'Query1' is not defined or its query items contain unresolved references.

[Lynn]

I have concatenated fields and never ran into this problem, although you should consider that filtering on these types of fields may not perform as well compared to filtering directly on something that is indexed in the database. Whenever possible I'd recommend that you have the fields created and populated on the database side so that any tuning that might be needed down the road can be done easily.

Of course that isn't always an option. When I create a derived field like this in the framework model I always refer back to the physical layer rather than using the unqualified names as you indicate. Not sure if that would make any difference or not but simple enough to test I suppose.

[Your Physical namespace].[Your Physical table].[LAST NAME] || ', ' || [Your Physical namespace].[Your Physical table].[FIRST NAME]

vs.

[LAST NAME] || ', ' || [FIRST NAME]

[ry1633]

I should have been more clear and I apologize.  I have been using the entire layer element like this.

[Database Layer].[TABLE_NAME].[LAST_NAME] || ', ' ||  [Database Layer].[TABLE_NAME].[FIRST_NAME]

And it still won't filter on that.

[HalfBloodPrince]

will you be able to provide the filter expression you are trying to use ?

[ry1633]

I can't even make a Filter expression.    As soon as I click on the Filter button in Query Studio - it returns the error 'A filter cannot be based on the selected report item.    '

[Lynn]

In Report Studio I get this error:  RQP-DEF-0149 The query specification is incorrect.RQP-DEF-0457 Referenced Query 'Query1' is not defined or its query items contain unresolved references.

Was there a filter expression you used to get this error?

What is the usage property in the framework model for your concatenated field? How about the aggregation property?

[ry1633]

No filter expression - just clicked the Filter button in Query Studio and get that error.  Here are the properties:

Usage:   Attribute

Regular Aggregate:  Unsupported

Semi-Aggregate:  Unsupported

[Lynn]

You posted two different errors: one in Query Studio and the other in Report Studio. I was asking about the latter as indicated by my quote from you about that specific RQP-DEF-0149 error.

I am on 10.2.2 and find that a concatenated item works fine as a filter in both Query Studio and Report Studio. Both fields are on the same table in my case and the database is DB2.

I don't know what else you may have going on in your model or your environment that causes this problem. You can try looking at the generated SQL for a simple query that includes a different attribute from the same query subject and then compare it with that same query after the problematic field is introduced. There might be some clues in what the database is doing that could help you troubleshoot.

Another thought is that you modify your data source to include the concatenated field on your table, or use a view that creates the concatenated item and model over that.

[MFGF]

No filter expression - just clicked the Filter button in Query Studio and get that error.  Here are the properties:

Usage:   Attribute

Regular Aggregate:  Unsupported

Semi-Aggregate:  Unsupported

I'm wondering if you have query processing set to "Database only" in your FM model?

Just a thought?

MF.

[Lynn]

I'm wondering if you have query processing set to "Database only" in your FM model?

Just a thought?

MF.

Yes, worth checking. My model allows local processing but even if I specify "database only" I can see that the database is handling the concatenated field in a filter expression without issue. Of course I expect this might vary depending on the database vendor.

I think looking carefully at the generated SQL is worthwhile and this is why I asked again about the issue on the report studio side. You can't get generated SQL out of query studio. I'd further suggest looking at the SQL when the field is included in the layout but not used as a filter. There could be something wrong with the way you've modeled that might become clear when viewing the SQL.

[ry1633]

Where do I check for the query processing setting in FM again?   I can't remember.    (Too early and haven't had coffee yet :) )

[Lynn]

Look at the Query Processing property for your data source.

[ry1633]

yeah never mind I found it (brain is slow to get going this morning...)   I checked it and Query Processing is set to 'Limited Local'

[ry1633]

not sure what I should be looking for here is the query SQL from Report Studio.  Doesn't look out of the ordinary to me.

select distinct
       ((Employee_Table1.LAST_NAME || ', ') || Employee_Table1.FIRST_NAME)  as  Employee_Name
from
      Employee_Table1

[ry1633]

Not sure what else to try - I did try to make the concatenation at the Database level in one of the table query subjects there.  That didn't work.

[Lynn]

You posted two different errors: one in Query Studio and the other in Report Studio. I was asking about the latter as indicated by my quote from you about that specific RQP-DEF-0149 error.

I am on 10.2.2 and find that a concatenated item works fine as a filter in both Query Studio and Report Studio. Both fields are on the same table in my case and the database is DB2.

I don't know what else you may have going on in your model or your environment that causes this problem. You can try looking at the generated SQL for a simple query that includes a different attribute from the same query subject and then compare it with that same query after the problematic field is introduced. There might be some clues in what the database is doing that could help you troubleshoot.

Another thought is that you modify your data source to include the concatenated field on your table, or use a view that creates the concatenated item and model over that.

It still isn't clear what filter expression you use to caused the error in Report Studio that you originally reported. Can you please explain?

The generated SQL you posted has no "where" clause which means there is no filtering taking place. Is this because you can't create a filter expression in report studio and are generated the SQL without one? I understand you get a message in Query Studio when you try to filter on the concatenated field but it isn't clear what you are doing in Report Studio.

Not sure what else to try - I did try to make the concatenation at the Database level in one of the table query subjects there.  That didn't work.

There isn't quite enough information here to go on. How did you do the concatenation at the database level? Was it a view with the same || syntax as your model, or was it actually physically storing the result of the concatenation operation such as "Smith, John"?

What do you mean when you say it didn't work? Same errors as before in both QS and RS? Incorrect result? Something else?

[ry1633]

Let me see if I can be clear as possible.  I can't create a filter of any kind on the concatenation - it won't even let me take the very first step.     When I attempt to click/start a filter it won't let me even get that far.  I can't even begin a filter with that field.

1.  In Query Studio - I click the Filter button with the concatenated field selected and QS returns the error:  " A filter cannot be based on the selected report item. "   - and I can get no further.   

2. In Report Studio - I bring the concatenated Field into a new List Report, and create generic Filter which should return all.  Filter is:  [Presentation Layer].[Employee Table].[Employee Name]

And RS returns this error "RQP-DEF-0149 The query specification is incorrect.RQP-DEF-0457 Referenced Query 'Query1' is not defined or its query items contain unresolved references."


***The Query Studio error #1 above is the most important one since my users do not have access to Report Studio.

[MFGF]

Filter is:  [Presentation Layer].[Employee Table].[Employee Name]

Hi,

This doesn't look like a filter expression. Did you omit part of the expression you are using?

MF.

[ry1633]

no that's the entire thing -- if I wanted to filter on the entire thing.  It gives the same error regardless if the filter is more specific or not such as either of these:

[Presentation Layer].[Employee Table].[Employee Name]="Watson"   

OR

[Presentation Layer].[Employee Table].[Employee Name]="Watson"

Anyway, the Query Studio error that I posted earlier is the one that I am most concerned with.  My users don't have access to Report Studio.

[ry1633]

Interesting.... I've found the exact same concatenation I made in another query subject - same exact concat with same table and same field names.   And this other concatenated field works just fine in a filter, though I made it identical to the other one that doesn't work.

The two both have the same Usage (attribute) and Aggregate (unsupported) settings.   But the one that does work in a filter has the Is Nullable: True -- where the one that doesn't work has it set to False.  That's the only difference between the two.  Should that make a difference?

[ry1633]

There seems to be some kind of formatting issue or discrepancy.     I found two differences of the same concatenation in two different query subjects;  one can be filtered and the other cannot.  And they were made the exact same way with the same fields from the table from the same layer.

The instance of it that can be filtered on has the following:
Usage:  Attribute
Data Type:  Character Length 16
Precision: 12
Scale: 0
Size: 206
Is Nullable: True
Display Type: Value

The instance of it that can't be filtered on has the following:
Usage:  Attribute
Data Type:  BLANK (and can't be changed)
Precision: BLANK (and can't be changed)
Scale: 0
Size: BLANK (and can't be changed)
Is Nullable: False
Display Type: Value

[ry1633]

From the "if this works, I'll eat my hat" file,  I think I got it.  Super-obscure fix;  I would've never thought to check this.  Apparently I had a few query subjects that were un-verified, so I went back and re-verified them each one at at a time.  And lo and behold, after doing so they populated the correct properties into the data type and such, and then they worked when Filtered upon in Query Studio.

http://www-01.ibm.com/support/docview.wss?uid=swg21984695&myns=swgimgmt&mynp=OCSSEP7J&mync=R&cm_sp=swgimgmt-_-OCSSEP7J-_-R

Can someone explain this to me a bit more? 

[bdbits]

There is an option when you publish to check the model first. It will validate everything and bring up a list of problems before actually publishing. I would make sure that is enabled next time you go to publish. Otherwise, Cognos will publish the package in whatever the current state.

When it validates an object (e.g. after you change SQL) it ensures everything is in order, and determines things like what the database says the datatype would be. Without this, it will cooperate up to a point but as you saw, functionality is limited.