cognos 10 report showing wrong counts when compared to cognos 8

[PradeepKumarP]

Hi,

we have a report in cognos 8 which shows hour counts for a day. recently we have upgraded to cognos 10 and the same report is copied to cognos10 environment. but the hour counts in cognos 10 are fewer than that of cognos 8 report.

few of the hours are ignored in the cognos 10 report.

any ideas how to resolve this.

thanks in advance.

[RKMI]

Hi,

Have you tried to debug it by making a simple report in Cognos 8 and moving the XML over into 10 and also creating a new simple report of wt you are trying to do in Cognos 10.

Another area you might want to check is SQL sent from Cognos 8 to 10 to see the difference between them and run it on the DB. My guess is you SQL changed as a part of the upgrade since Cognos always tries to optimizes the SQL from previous release.

P.s. please provide more details such the expression you are using to do the hour count, are you doing it against sysdate or data time field from a query subject?

Thanks,
RK

[JosephStreet]

The query engine in Cognos 10 is vastly different to that of Cognos 8. Are you still running the original FM model or has this been upgraded as well? 

[PradeepKumarP]

thanks for the replies,

RKMI, i have tried re building the report from scratch in c8 and copied the xml to c10, and also i have created a report from scratch in c10 also, but still it's the same case. i did not check with the sql as mentioned. i will check it today.

Hi Joseph, i think we have upgraded the FM Model also, but not sure need to check with the admin guys.

will check the sql thing and reply.

thanks again for the replies guys:)

[PradeepKumarP]

Hi, are you talking about the generated SQLs for the reports. the generated SQLs for both the reports are different, does it has something do with this issue?




Thanks

[RKMI]

Hi,

Yes, with the two generated SQL run on the DB and run a execution plan. I bet it not pickin the correct index or doing a full table scan which was not occur with the query which ran on cognos 8.

I think in the long run you will need to redesign the report to make the report the way it used too but, as a temp fix you can take the native SQL from Cognos 8 and open the report in C10 go to query explorer and add the SQL from toolbox next to your base so that it will use the native sql which was used in c8. It should function the same way, if you had prompts you might need to add #prompt marco to reuse working prompts. I hope this makes sense.

Thanks,
RK

[PradeepKumarP]

Hi RKMI,

i tried doing the thing which you mentioned, took native sql from c8 report, opened the report in c10. took the sql from tool box and tried adding in to the main query used for the report but i am getting an error message as below

"The server returned an unrecognizable query framework response."

actually the main query for the report is a joined query of two base queries.

the native sql generated for c10 report has a with clause at the begining, i have changed the use with clause option for the query in properties to no from default but still it is of no use.

is there a way to change the native sql of c10 report to same as that of c8 report without adding an external sql item (like can we change the native sql generated for the joined query without adding a new sql item)?

donno what went wrong with this:(




do i need to look for any other changes?




Thanks in advance.

[blom0344]

Just a thought.  The count function skips null values by default. Is there any chance Cognos 10 encounters different values with regard to null values than Cognos 8?  That  could explain the lower values for the 10 version.

The use of the CTE (common table expression)  'with' can be allowed/disallowed in the model

[PradeepKumarP]

Hi blom0344,

thanks for the reply, sorry i don't know what you said about duplicates. when trying to solve the count mismatch issue, i changed the cardinality of the query once and ran, then i was getting NULLs also.

if the use of with clause as u said is handled at the model level, is there a chance that we could get the exact counts as c8 report?

sorry i don't have much knowledge on this:(



thanks in advance.

[blom0344]

The model uses a set of governor switches among them: 'Use With clause when generating SQL:'   What happended to the C8 model. Was it upgraded to C10 and are you working against a package published from the upgraded model?

[PradeepKumarP]

thanks guys for your time in helpin me,

couldn't do anythin at report design level, but managed to get the needed result by updatin report xml. donno whether this a good practice or not, but last week got a thought of checking the report xml of both the reports, so compared both of them in a text pad and chanegd the c10 xml as that of c8 and opened report. the counts are same this time.




thank you for your help and time again guys:)

[blom0344]

Now, this IS interesting. the report.xml does not store the SQL definitions if you let the package generate them (generated at runtime), so what did you change that resolved the issue??

[PradeepKumarP]

Hi blom0344,

few of the data items in c10 report xml are palced in a different order when compared with c8 report xml, so i just made them placed in same order as in c8 report xml, and when opened the report it the counts are fine and are same as c8 report. this IS really interesting  ::)

[blom0344]

That sounds a bit unsettling. The order in which dataitems are fetched should not make a difference. Did you check whether any different SQL was proposed ?

[PradeepKumarP]

Hi bloom,

as i said the sqls generated for both the reports are a bit different like the one generated in c10 started with a with clause, i am not that good with sqls that's why i think i couldn't understand the exact difference between the two.

about the xmls, in the xmls of the both, the data items were placed like, suppose in c8 if the sequece was data item1, data item2 and they were like data item2, data item1 in c10 report at some places. this  may sound crazy, but i just changed their order same as in c8 and it worked.

[blom0344]

Yes, but what about the influence on the SQL after you altered the report.xml?  Did that change by altering the dataitem order?  Just being curious as you are already satisfied. Trouble is, is the a freak accident (just this one) or a possible Cognos 8 vs 10 issue?

[PradeepKumarP]

Hi bloom,

the sqls generated for both (c8 & c10) reports are different as normal. but the sqls generated for c10 report before altering and for the report after altering are also different.

didn't see the exact diffrences, but when looked at both of them they definitely differed. please let me know if you want to know what exactly are the differences, i will check them and mention in details.




thanks,
pradeep

[MFGF]

Hi bloom,

Hee hee hee. Makes me laugh out loud every time! You should think about changing your name Ties! :D

[blom0344]

Hee hee hee. Makes me laugh out loud every time! You should think about changing your name Ties! :D

Well, I decided to try and ignore it 2013 onwards. One of the many good intentions for this year. Besides, it looks like we are gonna switch from Cognos to MS BI   :o   and I will bid all of this farewell anyhow within the near future   :-[

[MFGF]

Yikes! That's a blow! I hope you can continue to visit us at Cognoise!

[Lynn]

Well, I decided to try and ignore it 2013 onwards. One of the many good intentions for this year. Besides, it looks like we are gonna switch from Cognos to MS BI   :o   and I will bid all of this farewell anyhow within the near future   :-[

:o  :'(

Say it ain't so!!!! You should check the job advertisements board and make a job change!

[CognosPaul]

Why on earth would anyone downgrade from Cognos to MSBI? It's an absolute mess.

[blom0344]

Yes and no   ;)

It is one part 'not being able to appreciate what you got' and one part 'the grass is greener over there'

The only MS BI that as yet is convincing is SSAS.  I am a rational man, using reason , proof and test results. That won't cut it though   :-\

[blom0344]

:o :'(

Say it ain't so!!!! You should check the job advertisements board and make a job change!

Well, this has crossed my mind several times last year.   ;)

It is not all misery, though..  We'll introduce a 'real' ETL tool to replace age old slow code and SSAS looks really impressive. The thing I hate most would be giving up Framework manager. MS BI is lightyears behind when it comes to a sophisticated modelling solution