Line chart doesn’t display line when there is no data for a particular date in C

[xyz]

Hi All,

We are using Cognos 10.2.2 and database is oracle. We have a requirement where we have to display line chart with marker. User has the option to select the range of days. Days are displayed on X-Axis which is a coming from time dimension or time hierarchy and measure Total demand on series. The problem here is for few days there is no data for Total demand measure in the database and the markers are not connected with line because of no data. User want to see zeroes when there is no data in database. I tried case statement on Total demand measure which is not working and the other option I tried is create one sql query with zeros for Toral demand measure and tried to union it, it is error out saying cannot union on a dimension.

Can you guys please share your thoughts/suggestions to achieve my requirement?

Thanks in advance.

[xyz]

Hi All,

Anyone have any suggestions on this?


Thanks in advance.


Thanks & Regards,
XYZ

[hespora]

run two queries, one with your facts, and one with just the dates from your date dimension. if you'll run tabular data on them, you'll see that the date query should in fact have all dates in the range, regardless of whether fact values are present.

Now, outer join the queries on date, and pull your fact through using coalesce( [fact] , 0 )

[CognosPaul]

Take a look at my post here: http://www.cognoise.com/index.php/topic,33840.msg111407.html#msg111407

[xyz]

Thank you, Hespora and Paul.

I will give a try and let you know. But Paul, when I tried to open the text in Cognos 10.2, I am getting some version error. Any how I will give a try tomorrow and let you know the updates.

Thank you guys, once again for your inputs.



Thanks & Regards,
XYZ

[hespora]

Good luck. Keep in mind, if you're viewing the report in an Excel Export, only mine of the two solutions will work. If you're using any other output format, Paul's is the more efficient solution.