Reporting using Cognos

[PreetiGupta]

Hello,

We have started learning some reporting techniques using Cognos. I have few concerns about how it works with Windchill. I am not sure if we are missing something here?


* Adding new columns to existing report in the query builder report does get passed into Cognos report until you restart Cognos service and Windchill. This seems like so much burden on the admin side where you have restart Windchill production now and then to update the Cognos reports to show the data in newly added column.

* Creating hyperlinks to the Windchill objects: We need to hard code the URL of the server and URLEncode to get object id. I was so frustrated that the report expression in the hyperlink column was getting corrupted if I start typing the expression. I had to undo all the changes and make a new Cognos report, not reliable.

* Once the Cognos report is built on the development machine: we always have to tweak the URL to get the correct hyperlink on production after deploying the report in production. Or modify the xml itself.

* Vendor specific functions(ex Oracle functions) does not work at all.

Please share your experience. Is anybody using Cognos in production yet with Windchill?

Thanks for your help.
Preeti

[jessh]

> Hello,
>
> We have started learning some reporting techniques using Cognos. I
> have few concerns about how it works with Windchill. I am not sure if
> we are missing something here?
>
> ·Adding new columns to existing report in the query builder report
> does get passed into Cognos report until you restart Cognos service
> and Windchill. This seems like so much burden on the admin side where
> you have restart Windchill production now and then to update the
> Cognos reports to show the data in newly added column.
>
That should not be true -- one should only need to update the Cognos
meta-model and not restart anything.

--
Jess Holle

[MichaelMarshall]

Preeti / Jess,

Unfotunately, I've run in to the exact same problem that Preeti reports, as mentioned in this thread:

http://portal.ptcuser.org/p/fo/st/thread=45739

I've tested this in both our Prod and Dev systems (9.1 M060, running on Red Hat), and it it works the same...after a modelUpdate, the already-established Cognos Reports do not recognize the newly-added Selections...it puts the column header in place, but the column has no data, even though the data is pre-existing in the source document type.

I also confirmed with with Mike Lockwood...his Windows-based system does the same thing as my Linux installs.

Jess,

I have C#10232346 open concerning this...I'm on my 3rd support engineer. The challenge has been finding where the problem actually lies. In my trial-and-error, what I've determined is that this is the process I have to follow:

1) Make a change to add columns (again, pre-existing info) to a Report in Query Builder.

2) Make sure you're logged out of Cognos.

3) Perform the modelUpdate in the windchill shell.

4) Shut down Cognos.

5) windchill stop

6) Tomcat stop.

7) Tomcat start.

😎 windchill start

9) Start Cognos.

It doesn't work for me if I just do the modelUpdate. Just stopping Windchill doesn't work either. I have to go to the point of restarting Tomcat to get this to work. When I do, the data magically appears in the columns where previously I only rec'd empy columns.

I disovered this after setting up demonstration Queries/Reports for PTC during the week...then we had to do a complete restart over a weekend due to some network changes...and when I brought everything back online, I checked my empty-column reports on a whim, and BOOM there was the data. So I went back and repeated the shutdown/startup process repeatedly, adding steps to find out where the problem is...for me it appears to be Tomcat.

[MichaelMarshall]

I forgot to mention that somewhere in there, I go through a process in Cognos to update the existing report in Report Studio, of course.

The newly-added columns (from the Select tab) in Query Builder don't just magically appear in the Cognos reports, of course!

Right now I do a modelUpdate, go to Cognos and see that the Report Templates are updated, get the Data Items in place in the Page, and confirm that it runs, albeit with empty columns...then save.

Then I go through my shutdown/startup process (to the Tomcat level) to have the newly-added columns properly populated with data when I come back to Cognos.

[jessh]

I'm stating how it's /supposed/ to work.

If there's a bug, then by all means file an SPR and chase the team
responsible -- which isn't me (unfortunately for you and fortunately for
me).

--
Jess Holle

[PreetiGupta]

Yes this problem is repeatable many many times. I do not see us restarting production(including each service) every week just to see data in the newly added column in the existing report. This is a nuisance.

[jessh]

On 3/31/2011 11:41 AM, Gupta,Preeti,IRVINE,R&D wrote:
>
> Yes this problem is repeatable many many times. I do not see us
> restarting production(including each service) every week just to see
> data in the newly added column in the existing report. This is a
> nuisance.
>
> Michael: How far you in the Cognos testing, would like to compare the
> results offline.
>
> Somehow the Cognos database does NOT refresh for the existing report
> until you restart the service.
>
> Jess: Is there any config settings we are missing here, which will
> address this issue?
>
It should just work. If it doesn't, then I have no idea why -- as I
said, not my area of responsibility.

--
Jess Holle

[MichaelMarshall]

Steve,

What exactly was the solution mentioned by Pierre at PTC?

Also, when you say "It's a well known issue by anyone who uses Cognos regularly," are you referring to the Windchill Business Reporting-version of Cognos, or Cognos installations in general?

I ask because, when I first encountered this problem, I consulted with a group of Cognos power-users here at the company. I thought that I must simply be doing something wrong in Cognos when I would add my newly-appearing Data Items from the Report Template. They told me that I was doing it exactly they way they would after they update their packages, so it must a "a problem on the Windchill side".

Granted, they're working with standard Cognos 8.4.1 against SQL, whereas I'm using WBR against Oracle, but I didn't think that would make a difference. And after exchanging info with Mike and Preeti, I'll stick with that theory.

In Reply to Steve Vinyard:

I was told about this problem and solution by Pierre at PTC. It's a well known issue by anyone who uses Cognos regularly. I think last time we brought it up with TS, we were told that to contact Cognos directly....though must TS guys have very little knowledge of Cognos.