Why isn't this chart rendering data that is returned in the query?

[psrpsrpsr]

Hello all, I have a line chart that is not rendering a portion of the chart, even though data is being returned for the x-axis field.

See below/attached for a snapshot of the line chart object in Report Studio 10.2.2, the chart output, and the query data output showing that the data is not missing from the query supplying the chart.

This is driving me nuts - can anyone let me know why this might be happening and how to remedy the issue? THANKS!

[AnalyticsWithJay]

What's the underlying data source? Cube, DB, etc. CQM or DQM?

Could you test the data in a crosstab instead of a list? It is more functionally similar to a chart in terms of behavior.

[psrpsrpsr]

Hi CognoidJay, the data source is a relational database. What exactly should I be looking for in the crosstab - whether those values missing from the chart are populating the crosstab values? Thanks

[AnalyticsWithJay]

Hi CognoidJay, the data source is a relational database. What exactly should I be looking for in the crosstab - whether those values missing from the chart are populating the crosstab values? Thanks

Yes, exactly. I'm just trying to isolate the cause -- simplify, divide and conquer :).

Is the connection DQM or CQM?

[psrpsrpsr]

Simplify, divide and conquer - I like that! I'm not sure if it is DQM or CQM. I'm an end-user only, without access to Framework Manager. Is there a way I can determine this from some sort of setting in Report Studio?

[AnalyticsWithJay]

Simplify, divide and conquer - I like that! I'm not sure if it is DQM or CQM. I'm an end-user only, without access to Framework Manager. Is there a way I can determine this from some sort of setting in Report Studio?

If you click 'Set Properties' on the package, you should see "Query Mode".

Curious what result you got from the crosstab...

[psrpsrpsr]

Hi Cognoid Jay, the Query Mode is Dynamic.

Here is a snapshot of an excerpt of the crosstab Excel output. The LocationID is columnar, the invoice date is rows. I blurred out the data for security, but as you can see, there are populated cells in the date range where the chart is not rendering that data.

This is driving me nuts!

[rockytopmark]

Seems odd its not working... but if it were my report, I would make one change: Change the X axis to be the month level.  Aggregating (total) up from the date level to the month level, should get rid of those null values. 

For all intense and purposes, the chart is a "by month" chart, yet dates are being used... and some months (happens to be the problematic months) have 2 dates within the month... with value at only 1 of the dates.

[AnalyticsWithJay]

Try changing the expression of Invoice Amount to this temporarily:

Code Select Expand

coalesce([invoice amount], 0)

Does the line at least appear?

If not, could you please filter your query on one problematic date, Run->view tabular data, and post the results? Feel free to mask sensitive data of course...

[psrpsrpsr]

Hi rockytopmark and CognoidJay, so here's the scoop: changing the Invoice Date to a calendar field YearMonth (201405, 201406, etc.) seems to have solved the problem, thanks for the suggestion!

Oddly enough, I noticed that in the Excel chart output, if I add 'markers' to the line, sure enough, they are there. For SOME darn reason, Cognos is deciding not to render those points, even though they are there.

CogJay, using COALESCE(Inovice Amt,0) did not trigger the coalesce function, as there is an invoice amount for each row. The trouble with all this is that there was an institutional change in the particular Invoice Date that I'm looking at, that business users wanted to see. But we can't all get what we want.

Thanks for the help all, this forum is an excellent resource!

Seems odd its not working... but if it were my report, I would make one change: Change the X axis to be the month level.  Aggregating (total) up from the date level to the month level, should get rid of those null values. 

For all intense and purposes, the chart is a "by month" chart, yet dates are being used... and some months (happens to be the problematic months) have 2 dates within the month... with value at only 1 of the dates.

Try changing the expression of Invoice Amount to this temporarily:

Code Select Expand

coalesce([invoice amount], 0)

Does the line at least appear?

If not, could you please filter your query on one problematic date, Run->view tabular data, and post the results? Feel free to mask sensitive data of course...