Combination Chart with Bar and Line based on Description Column

[seeRed]

I've got a chart that is so amazingly easy in Excel but can't seem to get it in Report Studio. 

I essentially have 2 rows of data.  The first row I want to be the "bar" portion of the graph and the second row I need as the "line" portion of the combination chart. 

Each row has a description for the first column (i.e. "This Year" for the first row and "Last Year" for the second row.)  After the description column I have January thru December columns with sales values.  Attached is an example from Excel with the months along the bottom, "This Year" as bars, and "last Year" as a line overlaying the bars.

I feel I've tried everything there's to try in report studio with Series, Categories, Nesting, etc.  Any help would be appreciated.  Thanks.

[Satheesh]

Hi,


Its very easy you can do the same in Cognos Report Studio also.
In charts --> Combination Charts ---> Select Clustered Bar and Clustered Line chart..


In series U can get One for Bar and One For Line
Drag  "This Year" data Item and drop in Bar and
Drag "Last Year" data item and drop in Line...


;)

[seeRed]

Hi,


Its very easy you can do the same in Cognos Report Studio also.
In charts --> Combination Charts ---> Select Clustered Bar and Clustered Line chart..


In series U can get One for Bar and One For Line
Drag  "This Year" data Item and drop in Bar and
Drag "Last Year" data item and drop in Line...


;)

So yeah, that's the thing.  The data item (Description) describes the line I'm working with (whether it's "This Year" or "Last Year.")  So I don't have separate data items to drop in bar and line.  I just have Description. 

[Satheesh]

Hi,


Create 2 data items by using condition One is for Lat Year and other for This Year...

[seeRed]

Hi,


Create 2 data items by using condition One is for Lat Year and other for This Year...

So I've attached an excel layout of what I think you're suggesting.  Bottom line is, the data is in a format: DataPeriodDescription, Month1Total, Month2Total, Month3Total, Month4Total, etc.  But it sounds like that format just doesn't work for Cognos.  Cognos is expecting a format more like MonthName, ThisYearTotal, LastYearTotal.  The way the data is stored, the first method is much easier to obtain.  I'm literally going to have to union 12 queries together (one for each month) to get the second format.

Just disappointing that Cognos can't replicate what Excel is doing from a charting standpoint with a very easy set of data.  The people that request this stuff know how easy it is to do in Excel and are dumfounded when it is so difficult to do in Cognos.

[Lynn]

Well your data isn't organized as a star schema, is it? Typical practice is to model facts surrounded by dimensions.

The month element of your date dimension is baked into the column layout of the table. What if your user wants to see a rolling 12 months or rolling 6 months period and then compare it to the same period last year? Your design is not conducive to supporting what a typical data warehouse design ought to support.

The people who request this stuff also know that they cannot automate their reporting with excel, nor can they handle large volumes of data. Every tool has strengths and limitations, but all need to be used properly.

It is always tempting to blame Cognos, but I'd suggest the person who designed your data warehouse and/or Framework Manager model is more worthy of the blame. When I cook a crappy dinner I try to blame the pots and pans but it never seems to work  ;)

I'd suggest going back to the database or FM layer to see if you can simulate a star schema approach to presenting the data.

[seeRed]

So, truth be told -- I tried to simplify what I was describing for the purpose of this post.  In fact, I'm trying to do a rolling 36 month trailing twelve month (TTM) report.  So, in other words, each of the time buckets (T0, T1, T2,...T12) in my first attachment is 12 months (or a full year) worth of data.  T0 is the most recent fully closed month + the preceding 11 months, T1 is the month before that + the preceding 11 months before that, etc.  Each row is an individual query and the result is "union"ized.  So the rows I'm really dealing with is "Current TTM" and "12 mo. ago TTM" (2 queries unioned.)  The idea is when you overlay them (bar and line) you can tell if the more recent TTM is beating the one from 12 months ago. 

The data is in fact in a BEUTIFUL star schema.   ;D   My BEUTIFUL Time dimension has relative offset month counters (i.e. current month = 0, last month = -1, etc.) makes just about all of my time calcs a breeze.   It allows me to write wonderful queries like (new way to get what I want):

QUERY1:

PERIOD: 'T1'
TTM: case when [Sales].[Accounting Date].[Relative Month] between  -12  and  -1  then [Sales].[Sales Fact].[Revenue] else 0 end
TTM 12 mo. ago: case when [Sales].[Accounting Date].[Relative Month] between  -24 and  -13 then [Sales].[Sales Fact].[Revenue] else 0 end


QUERY2:

PERIOD: 'T2'
TTM:  case when [Sales].[Accounting Date].[Relative Month] between  -13  and  -2  then [Sales].[Sales Fact].[Revenue] else 0 end
TTM 12 mo. ago:  case when [Sales].[Accounting Date].[Relative Month] between  -25 and  -14 then [Sales].[Sales Fact].[Revenue] else 0 end


It's just that I have to now do 36 of them and then union them together.  My original way I had 2 queries!  I was flyin' until the charting then fell flat on my face!

Thanks for taking time to look at this post!