Report not rendering

[Marvin Falentin]

Hey everyone,

I got an active report, in it one list containing several columns, three of them are 1) datacollectionname 2) messagetext 3) logtypename. Whenever I try to render the report with all three columns in the list it just stops along the line, however if I only have "messagetext" and "logtypename" in the list or "datacollectionname" alone it will render the report without any problem.Trying different combinations like "messagetext" and "datacollectionname" or "logtypename" and "datacollectionname" aren't working either.

My customer already agreed on simply not displaying the "datacollectionname" in that list but I want to understand what's the problem.

We're using Cognos 11.0.6, IE as a browser (Firefox won't work too). The "usage" of the "datacollectionname" is Attribute, die "Date Type" is "nVarChar (I tried casting it to varchar but that didn't work).

As always I'm very glad for any insights on this issue!

Kind regards,

Marvin

[MFGF]

Hey everyone,

I got an active report, in it one list containing several columns, three of them are 1) datacollectionname 2) messagetext 3) logtypename. Whenever I try to render the report with all three columns in the list it just stops along the line, however if I only have "messagetext" and "logtypename" in the list or "datacollectionname" alone it will render the report without any problem.Trying different combinations like "messagetext" and "datacollectionname" or "logtypename" and "datacollectionname" aren't working either.

My customer already agreed on simply not displaying the "datacollectionname" in that list but I want to understand what's the problem.

We're using Cognos 11.0.6, IE as a browser (Firefox won't work too). The "usage" of the "datacollectionname" is Attribute, die "Date Type" is "nVarChar (I tried casting it to varchar but that didn't work).

As always I'm very glad for any insights on this issue!

Kind regards,

Marvin

Hi,

Are you hitting the "Maximum Query Rows" limit for your report? Just wondering if a combination of all the columns is causing the query to try to retrieve more than your allowed max?

You can find this setting in the "Active Reports" property in the Properties pane for the report. The default limit is 5000 rows.

Cheers!

MF.

[Marvin Falentin]

Hey MFGF,

thank you very much for your quick Response!

I thought of that too and set the limit (just to make sure) to 500000 and after that to 5000000 but it didn't change a thing.

Kind regards,

Marvin

[MFGF]

Hey MFGF,

thank you very much for your quick Response!

I thought of that too and set the limit (just to make sure) to 500000 and after that to 5000000 but it didn't change a thing.

Kind regards,

Marvin

That rules out the easy fix, then :)

Can you explain what "it just stops along the line" means? I'm trying to picture what you see. Have you tried enabling debug mode when viewing the rendered report (<ctrl><shift>D) and seeing what values the active report variables are set to? Is it possible you're filtering the list on something you don't realise?

MF.

[Marvin Falentin]

Attached you can find two images, the "stopping along the line" and the filters I currently use (I use them for most part of the report and they work fine for the rest so I kinda rule them out).

What I ment is when I try to render the report in html format it just stops "loading" at some point. I'm german and we're using the german client so what you see on the screenshot is "loading application data" or something close to that.

It certainly looks like "max. rows retrieved" or something due to the query working with either of the three columns but not all of them together... I don't know, it's really weird.

I didn't use the debug mode yet (didn't know it exists) but I don't think I can due to the report not rendering at all, right?

Thanks for your help so far!

Kind regards,

Marvin

[Marvin Falentin]

Here the second image

[MFGF]

Attached you can find two images, the "stopping along the line" and the filters I currently use (I use them for most part of the report and they work fine for the rest so I kinda rule them out).

What I ment is when I try to render the report in html format it just stops "loading" at some point. I'm german and we're using the german client so what you see on the screenshot is "loading application data" or something close to that.

It certainly looks like "max. rows retrieved" or something due to the query working with either of the three columns but not all of them together... I don't know, it's really weird.

I didn't use the debug mode yet (didn't know it exists) but I don't think I can due to the report not rendering at all, right?

Thanks for your help so far!

Kind regards,

Marvin

So are you trying to run the report interactively? What happens if you press the run button and choose "download active report"? Does the mht file render and download? If so, how big is it?

MF.

[Marvin Falentin]

Okay, here's what I got:

I downloaded the file as suggested, when I tried to open it still won't render, the file size seems to me to be really big, it's 18.375kb but the report's kinda filled with data.

A friend suggested to try and use SQL rather than the FM and it magically works, simply using "select d.datacollectionname from [datasource]", does that give you a hint? Sounds kinda related to my FM model, right?

Kind regards,

Marvin

[MFGF]

Okay, here's what I got:

I downloaded the file as suggested, when I tried to open it still won't render, the file size seems to me to be really big, it's 18.375kb but the report's kinda filled with data.

A friend suggested to try and use SQL rather than the FM and it magically works, simply using "select d.datacollectionname from [datasource]", does that give you a hint? Sounds kinda related to my FM model, right?

Kind regards,

Marvin

It does sound like you might have some issues in your model, yes. Having said that, 18Mb is quite large for an Active Report output. I'd guess some devices might struggle to render it. Why is it so big? Are you using lots of charts on data decks?

MF.

[Marvin Falentin]

We're using the Active Report from Cognos as a Dashboard due to our customers need of having multiple lists displayed together with two charts and several images (traffic light logic).

I just took a look at the model (diagram view, not explorer) and the tables are joined with 4 relations between them, that could be the reason why all of them together is just too challenging.. still why would it work if you do it with SQL rather than the FM?

Anway thank you very much MFGF for your time & help!

Kind regards,

Marvin

[MFGF]

We're using the Active Report from Cognos as a Dashboard due to our customers need of having multiple lists displayed together with two charts and several images (traffic light logic).

I just took a look at the model (diagram view, not explorer) and the tables are joined with 4 relations between them, that could be the reason why all of them together is just too challenging.. still why would it work if you do it with SQL rather than the FM?

Anway thank you very much MFGF for your time & help!

Kind regards,

Marvin

Hold on! Are you saying there are four separate relationship links between the two query subjects? Or do you mean there is one relationship based on four items?

If the former, then that means your model needs careful scrutiny. Only one relationship between tow objects will be used - the query engine has to pick one. It uses the first alphabetic relationship (based on the relationship name). This is probably not what you want... :)

MF.

[Marvin Falentin]

What I mean is from one table containing "datacollectionname" to the other table containing "messagetext" and "logtypename" there are three tables between. We're using a database originally not designed for reporting, no star schema and the like.

If you want I can send you an image of what our model looks like (diagram view).

I'm fairly new to Cognos and working on an already existing project from a former student of the company I'm working for, so maybe I can improve the design.

Kind regards,

Marvin

[MFGF]

What I mean is from one table containing "datacollectionname" to the other table containing "messagetext" and "logtypename" there are three tables between. We're using a database originally not designed for reporting, no star schema and the like.

If you want I can send you an image of what our model looks like (diagram view).

I'm fairly new to Cognos and working on an already existing project from a former student of the company I'm working for, so maybe I can improve the design.

Kind regards,

Marvin

Hi,

It's entirely possible the model is inappropriately built. When you write reports in Cognos, the query engine creates queries based on the assumption that the structures in your model are star or snowflake schemas (ie either fact tables or dimension tables). It uses the relationships and their cardinalities to determine which is which. If your data isn't held in this form, you need to make it look and behave as though it is by creating structures in your model that look like star schemas, and publishing these objects into your packages. It doesn't sound like this has been done in your model? If so, that's a fundamental problem - you will most likely get inaccurate, inefficient, unpredictable results from the queries your reports generate. One of the most important things to do when building a model in Framework Manager is to create structures that will return accurate, predictable results, and it's one of the most fundamental of best practices to model as star/snowflake schemas. If you haven't done any formal training for Framework Manager, I'd really advise you try to get your employer to send you on the course - it covers lots of useful material, including this very topic.

Cheers!

MF.

[Marvin Falentin]

Okay, fun fact: I just read a bit about star schemas in Cognos FM and created one with the tables I need now it's rendering really smooth what I couldn't do before .. Guess I have to rework the model quite a bit then!

Kind regards,

Marvin