cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Your Friends List is a way to easily have access to the community members that you interact with the most! X

WBR (Cognos) Issue w/ Updated Queries

MichaelMarshall
1-Newbie

WBR (Cognos) Issue w/ Updated Queries

Let's see if I can succinctly state the problem I'm experiencing...


I begin with Report Manager/Query Builder, and create a report that contains, say, 10 Attributes from a particular Document softtype.


After a modelUpdate, I go to WBR/Cognos, open Report Studio and start a New Report (blank). I find my newly-created Report Template. I drag the whole thing in to the window, and format the heck out of it. Its beautiful.


A week later, someone asks to add a new column to the report. No problem, its an existing Attribute in the Document softtype. I go to Report Manager/Query Builder, open that existin Report, and add the Attribute under the Select tab. Save.


I do another modelUpdate.


Upon opening my previosly-save and beautifully-formattedreport in Report Studio, I'm informed that the package has been updated and my report will be updated also. Great, just what I expected.


But when I go to the Data Items tab in Insertable Objects, the recently-added Attribute isn't there under the Query. So I go back to the Source tab in Insertable Objects, find my Report Template, and sure enough, the recently-added Attribute is there.


So, I just drag it out in to the report, and place it where I want it amongst the columns. And that's when things *really* go bad!



For a test, I created a single-column report. Worked fine. Added a 2nd column of info, and following the above procedure, and Validate Report will give several errors, such as "The column "Group" was not found in the table or view...", and *every* Attribute in the report shows an error of "Unable to find query information for the item *****".


And if you're obstinate enough to run it anyway, it says "An error occurred while performing operation 'sqlPrepareWithOptions' status='-56'."



So, I've tried this numerous times with different reports. I've tried bouncing Cognos after a modelUpdate, I've tried bouncing it before, and on both sides of a modelUpdate. No dice. It doesn't appear to be a system- or performance-related issue, as other unmodified reports will run as expected.



Anyone else seeing this behaviour? It occurs to me that I'm clearly doing something wrong...my process must be off. What process do others follow when making a change to the Report at the Report Manager/Query Builder level? I have a hard time believing that in order to add an Attribute/Column, you basically have to start over...but in the last few days, that's really the only way I've been able to add an Attribute to an existing report.



I'm currently running 9.1 M060, with the matching Cognos 8.4.1




TIA!

9 REPLIES 9

A colleague sitting next to me says that he's definitely seen this before, as he's been messing with a lot of cognos recently, and that for him it has been a variable or something in the query that was invalid now.

Can you create a quick test report (ugly) and see if your query can come in?

I read this with great interest. We now have Cognos in production but haven't used much yet. Hoping to add Cognos elements to most of our existing query builder reports. We haven't seen what you mention that I know of - will try asap.

Let's compare notes on a web session if you like.

I responded to Steve's question privately. If I followed it correctly, the answer is Yes, if I start with a blank report in Report Studio, and drop in my updated Report Template (updated via modelUpdate after a new Select added within Query Builder) then that new report will run just fine. So its not a case that I just recently added a bad variable or something...my installation just doesn't like me adding new columns in the Report Template and trying to make this work in an existing report.



Mike - as of right now, my schedule is fairly open Tues/Wed/Fri next week. I'm in the Central time zone. Let me know what works for you and we can link screens and chat.



Steve - we can include your colleague if he's interested. May be able to confirm that we're seeing the same issue - or highlight where I'm making a misstep in the process.




In Reply to Mike Lockwood:


I read this with great interest. We now have Cognos in production but haven't used much yet. Hoping to add Cognos elements to most of our existing query builder reports. We haven't seen what you mention that I know of - will try asap.

Let's compare notes on a web session if you like.

I sent some steps to Mike M. this weekend as far as the process I used to clear up this issue. I am not sure it is the same problem that everyone else is experiencing but when you update a column/attribute and then run modelUpdate you must add the data items to your query in Cognos - by draging them from theInsertable Objects Source tab into the Data Items section for the query (Query Explorer) then into the report(for a column) from the Insertable Objects DataItems tab. If you don't add them to the query in Query Explorer before adding the columns to your report it will not bereferening them correctly and result in parsing errors- you can see thisby viewing the Expression for the data item. Hope this helps.


Thanks,


Dezja Schultz

Mike and I spent some time on this yesterday. Mike's system didn't like it either. So there's one Windows installation that won't allow updates in Query Builder to pass through to Cognos, and I've tested on both my production and dev instances with the same end result.


Speaking with one of our Cognos experts, he believes that something is going wrong with the modelUpdate process corrupting thespec of the newly-added items in the package. Unfortunately, we cannot confirm this as the WBR version of Cognos doesn't appear to update/store information exactly the same way a standard Cognos installation does.


I will be submitting a support call to PTC yet this afternoon. Will reply back to list with what I learn.

Discovered something interesting - had a need to perform a complete restart of Windchill over the weekend. For our dev system it was all the way down to shutting down VMs, production wasjust all WC-related services.


Anyway,when I brought WC back online, my modified/updated report that previously would show empty columns for newly-added Attributes, now show the proper data in the columns. For both production and dev systems. Surprised me.


So I tested this on my dev system this morning. Creating all new Queries in QB and Reports in Cognos. If I restart just Cognos, it doesn't work. If I restart Cognos and just Windchill, it doesn't work. But if I stop Cognos, Windchill, Tomcat, Apache, and WindchillDS, then restart everything, when I come back to that report in Cognos that previously displayed blank columns, the proper data shows.


If I have time this afternoon (likely tomorrow), I'll repeat the experiment by going Cognos, Windchill, Tomcat (leaving Apache and WindchillDS). Then if that doesn't work, add Apache. If that doesn't work, then we know where the problem lies.


Still not an idea solution, as I can't restart all Windchill-related services in production whenever I want to update a report, but interesting - may point to the problem? Updated my case with PTC with this info.

An update:


Working with our dev system, the update process works when employing a shutdown-startup routing of Cognos-Windchill-Tomcat in the appropriate orders for startup/shutdown. I don't have to take it down to Apache or WindchillDS (which makes sense).


So its at least something in Tomcat, or the combination of the three. I'm updating my C# with PTC.


In a separate e-mail message, Dezja confirmed that when going the Info*Engine route, a shutdown to the Tomcat level is sometimes required there as well to allow the reports in Report Studio to run properly with the updated info. Thanks Dezja! Sounds like our same issue, though arrived at via different means, is indeed being caused at the same point.

Michael,



Do you clear your tomcat cache every time you restart? If so You might
try setting tomcat to development mode and seeing if that forces the
change every time. It is a bit of a shot in the dark.

--Bob



Some additional info...Bob is correct it does help to havetomcat set to development mode my development enviroment is and it helps -there are also times that clearing the compiled tasks directoryhelps when data is seemingly'stuck' and not being updated for a report.

Top Tags