Receive an error when using data item defined in query in filter

[danasellers@udayton.edu]

Hi gurus,

I have created 4 data items that I'm trying to use in my filter and I'm receiving UDA-SQL-0327 An invalid object name was detected in the SQL request.  UDA-SQL-0328 Unable to find the column "Academic_Study.ACADEMIC_PERIOD".
error.  This particular column is defined in the report.  If I don't add in the filter the report validates fine and runs fine:

My data items are as follows:

Find Registered for Current Term is defined:
If ([ACADEMIC_PERIOD] = '200980') Then ('Y') Else ('N')

Registered for Current Term is defined:
maximum([Find Registered for Current Term] for [NAME], [ID])

Find Registered for Next Term is defined:
If ([ACADEMIC_PERIOD] = '201080') Then ('Y') Else ('N')

Registered for Next Term is defined:
maximum([Find Registered for Next Term] for [NAME], [ID])

My filter is:
Registered for Current Term = 'Y' and Registered for Next Term = 'N'

Can anyone give me some ideas on how to accomplish this?

Thanks so much for your time on this,
Dana

[MFGF]

Hi Dana,

Is the filter defined as before or after aggregation?

One thing you could try is to drag the ACADEMIC_PERIOD item into your query, then go to the properties of the List object, and in the Properties property, check the ACADEMIC_PERIOD item.

Regards,

MF.

[danasellers@udayton.edu]

Hi MF,

Thanks for getting back with me. 

In the filter I have tried it both before and after aggregation each receiving the same error.

I'm not quite sure I understand your recommendation and I'd like to give it a shot.  I have ACADEMIC_PERIOD in my query already - it's not showing as a column on the report, however.  what do you mean by list object?  When I look at the properties for ACADEMIC_PERIOD in my query I don't see a Properties property.  All I can see is Data Item properties.

thanks a bunch!
Dana

[MFGF]

Hi Dana,

Sorry - I should have been a bit clearer.  On your report page, select any column in your list object, then use the 'Select Ancestor' button at the top of the Properties pane, and choose 'List' from the displayed list of objects.

Once the List object is in focus, scroll through the properties in the Properties pane until you find the 'Properties' property, and press the elipsis to open this up.  Put a check next to the ACADEMIC_PERIOD item, then try running your report again.

To explain the theory behind this; In Cognos 8, if items are brought into the query but not used in a display object (list, crosstab, chart etc) they are ignored by the query as it assumes they are not required.  Adding one of these items as a property of a display object means you are then forcing the query to retrieve it.

I'm not sure whether this is the underlying cause of the error you are seeing, but it's worth trying.  Let us know how you get on!

Regards,

MF.

[danasellers@udayton.edu]

Hi MF,

Thanks for the very clear directions!  I tried this with just checking the ACADEMIC_PERIOD item and got the same results.  I then selected all in that list - same results!  SHUCKS!!!!  Why is Cognos8 so darn picky!   ;)  If you have any other suggestions I would GREATLY appreciate it!

Thanks for your help and time on this!

Dana

[MFGF]

Hi Dana,

Thanks for getting back with the results.

Looking a bit more closely at your original post, I see you are using a maximum() function to determine 'Registered for Current Term' and 'Registered for Next Term', based on ID.

You will probably need to define your filter as a Summary Filter rather than a Detail Filter in this case, based on the ID level of grouping.  it should be easy to make the change - just copy the syntax from your existing Detail Filter, delete the filter and define a new Summary Filter and paste into there.  Don't forget to set the applicable grouping level on the right.

Regards,

MF.

[danasellers@udayton.edu]

Hi MF,

Sorry for this delay.  I understand to make it a summary filter and I don't mean to sound like a total dope, but I'm not quite sure what you mean by setting the applicable grouping level on the right?  When I created the summary filter I set the scope in the properties to the grouping levels and it still received the same validation error.  Just when I think I got a handle on Cognos - WHAM!!!! 

Thanks so much for all your help on this!

Dana

[MFGF]

Hi Dana,

Yes - I meant the scope.  The only dope here is me for not being clear in my posts! :)  One other thing to check - the item you define as the scope needs to be a grouped item within your report.

MF.

[danasellers@udayton.edu]

Hi MF,

I so appreciate all your help on this!  The items I chose in the scope where the items that the report is grouped on and they show as columns on the report. This one is a tough one.  Do you think it's possible something got corrupted in my report and I should try to recreate it from scratch again? 

Dana

[MFGF]

Hi Dana,

Try setting the scope of the summary filter to just the ID.

I just tried your report technique using the GO Sales package, and I managed to get it to work as expected.

My report was a List report, with columns Product Line (grouped), Product Type (grouped) Order Method, and four calculations:
1. Email - if ([Order method] = 'E-mail') then ('Y') else ('N')
2. Fax - if ([Order method] = 'Fax') then ('Y') else ('N')
3. Any Email - maximum([Email] for [Product line],[Product type])
4. Any Fax - maximum([Fax] for [Product line],[Product type])

I added a Summary filter - [Any Email] = 'Y' and [Any Fax] = 'N' - with Scope set to Product type

This behaved exactly as I would have expected.

If you get no joy with the scope, try a simple re-creation of the basic report, just to see igf you can get the approach to work.  It took me only five minutes to prove this with GO Sales, so hopefully you can quickly see if a new report fixes the issue.

Best regards,

MF.

[danasellers@udayton.edu]

Hey MF,

Sorry again for the delay.  Currently, this is appearing to be something on the report server causing issues.  When I find out for sure, I will post so if anyone else has a similar issue it may help.

Thanks for all your time and effort on this. 
Dana

[MFGF]

Thanks for the update.  Let us know if you find anything, or post back if you have further questions.

Good luck!!

MF.