Line Chart with two Filters

[CognosAdmn]

Hello Everyone,
I am creating a line chart with x-axis being each month in year 2009 and Y axis is a custom calculated field named 'Ratio'.

The calculation is ("NoOfProd" * 1000/"Total_Prod_for_Location")
where "NoOfProd" is the sum(Products)
and "Total_Prod_for_Location" is a field from the database.

I need two lines in the graph one where ProductType is "Int" and the other for "Ext".

I've tried inserting Months into x-axis; ProductType into Series; and Ratio into y-axis. But graph only displays one line for "Int".
I've tried to create a list reports with             | Month | ProductType | Ratio |
and I am seeing values for both "Int" and "Ext".

I've also tried to create two queries one for each "Int" and "Ext" with different filters and joined it with the MonthsKey. When I used to the third Query to build the graph, it is blank.

I am working in Cognos Report Studio 8.2

Any help would be greatly appreciated. Please let me know if I am not clear.

Thank you,

Shiyam

[paddhu]

How are the values for the two product types. If the values for one type is relatively in a small scale than the other, then probably it is being diminished on the scale of the other. Or else it is also possible that the lines overlap each other.

[CognosAdmn]

Thank you for your reply Paddhu.

The second line should be visible with the line chart I've set up. The two lines (ext and int) should cross each other twice. I know this because I have the chart in an excel file.

Thank you for taking the time to think about this Paddhu.

[paddhu]

I now clearly understood your question. Where you are going wrong is with the placement of Product types and Ratio.
Once you put something as the default measure in the y axis, you suggest to use that if the series is not a measure.
What you probably need to do is...

1. Drag months to x-axis.
2. Drag ratio to series, two times as two separate series.
3. Select the first ratio series, and apply the filter Producttype=int
4. Select the second ratio series and apply the filter Producttype=ext

Hope this gets you what you want.

[CognosAdmn]

Thank you for your reply Paddhu.

I gave your solution a try but it didnt quite work.
As per your step 2. and 3. if i drag Ratio twice to Series and apply a filter to both i'd be restricting both producttype(int and ext) and would get null. both filters would be in the same query. I tried two different querries and joined it with the monthKey, and got nothing useful either.

I ended up opening a ticket with cognos and they werent able to come up with a solution either. But the rep did help me pin-point the exact error, which is in the calculation for Ratio calculation for EXT. What we both didnt understand was that both EXT and INT have same calculation but INT gave results and EXT were nulls, when we pulled the Month/ProductType/Ratio into a crosstab.

So I ended up creating the calculation in the database (two fields INTRatio and EXTRatio) and pulling both values into the Series. The reports works fine now. Thank you so much for your time and effort to help me out Paddhu.

[paddhu]

Oh. Thanks for the update. Seems I was a bit too overwhelming when I stated step 2 & 3 without thinking twice, that both will use the same query...  :D