I am relatively new to Cognos having been working with it for less than a year. However, after consulting an entire team of "Cognos Experts", searching to no avail on the interwebs and on blogs like "Cognos Paul" - I still can't find how to do a simple chart without a list or cross tab; just a query feeding it.
The query is getting its data from a sql statement run in native mode:
SELECT CUSTID, BUCKET, SUM(AMOUNT) AS AMOUNT
FROM ARBUCKETS_PIVOT
WHERE SNAPSHOTDATEKEY = 20140701
AND CUSTID IN ('74718','00888','00661')
GROUP BY CUSTID, BUCKET
ORDER BY CUSTID, BUCKET
The data is super simple:
CUSTID, BUCKET, AMOUNT
There are only 11 buckets.
CURRENT,P4,PD01_10,PD11_30,PD31_45,PD46_60,PD61_90,PD91_120,PDOVER120,TOTALAR,TOTALPD
Sample data:
00024,CURRENT,14003.0200
00024,P4,0.0000
00024,PD01_10,0.0000
00024,PD11_30,0.0000
00024,PD31_45,0.0000
00024,PD46_60,0.0000
00024,PD61_90,0.0000
00024,PD91_120,0.0000
00024,PDOVER120,-500.1100
00024,TOTALAR,13502.9100
00024,TOTALPD,-500.1100
I need a bar chart that shows each of the 11 buckets along the x-axis and the range of the amount along the y-axis and that's it, no special cross-tab junk, no fancy formatting, just something that even excel 1.0 could handle and cognos in their infinite "wisdom" had to over complicate.
Currently the chart has the series as bucket with aggregate function as count distinct and the categories as amount with aggregate function Summarize.
Which results in a chart with the different amounts along x and how many times it occurs in one bucket along y. When I flip the chart I get the count of each bucket along the x (which is 1 per bucket) and the number of amounts in each bucket along the y.
I simply need to show a chart of each buckets amount with the buckets along the x and the amounts along the y; I can't believe this is so difficult in Cognos. Please Please Please help.
I attached a jpg of the two charts I've gotten so far and what they should look like.
I'm not sure who the Cognos experts you consulted with are, but they should have pointed out that only your measure (namely, amount) should have an aggregation set. There's no reason to count your bucket. Some might point out that setting an aggregation function on your categories/series is over complicating things.... :)
Also, a Cognos expert might point out that using SQL queries per report is not the way one should work with Cognos, if one wishes to get their money's worth out of the product.
I apologize if I seem to have missed the avice in your reply. Simply putting an aggregate on Amount and putting it in either the measure or the series and then having the Bucket field be default (aggregate function to auto) or none in the x-axis does not produce a chart with any data points. I know that the advice I've gotten from my co-workers is sub-par on this issue, because I'm posting for help here. I've searched ad nauseam on Ibm's sites and others for a plain implementation of a chart and still came here. Is there a kb I can read on how to make a simple x-y chart with flat data from either a table or query? Must I use a cross-tab type of table?
Right, I'm not sure I follow. I took your sample data and uploaded it, and used Amount as measure and Bucket as category. Attached is the chart, which is pretty much what I figured you needed. What precisely are you missing there? Can you upload a screenshot from Excel of how you'd like your data if this is not what you had in mind?