hello all gurus,
I have the following report which is fairly straight forward the Diff column is a running-difference from year to year. I'd like to be able to show the %diff of that column in a column next to it. How can this be done?
Thanks for any advice/help!
Dana
INQUIRER
Academic Year College/School # of Students Diff %Diff
2006 School of Business 386
2007 400 4
2008 350 -50
2009 450 100
PROSPECTS
Academic Year College/School # of Students Diff %Diff
2006 School of Business 410
2007 470 60
2008 350 -120
2009 650 300
APPLICANTS
Academic Year College/School # of Students Diff %Diff
2006 School of Business 700
2007 600 -100
2008 750 150
2009 850 100
Take the applicants data set as an example
Quote from: danasellers@udayton.edu on 08 Jan 2010 04:03:49 PM
APPLICANTS
Academic Year College/School # of Students Diff %Diff
2006 School of Business 700
2007 600 -100
2008 750 150
2009 850 100
Try this
Students/(Students-Diff)The results should be this
2006 386 0
2007 600 -100 0.857142857
2008 750 150 1.25
2009 850 100 1.133333333
if you are looking for the percent of increase/decrease then you will have to take the percent minus 100
(Students/(Students-Diff)) - 100
Thanks so much for getting back to me. I had tried this in the past and it renders really odd percents - like in the 1,000's. It seems pretty straight forward to me, but it appears Cognos has a different idea. I'm wondering if it's because the Diff field is a 'running-difference'.
You might try using the data item for the running-difference field rather that the source field from your Cognos package. I believe this makes a difference in how Cognos generates the SQL.
I have the # of students defined as
count(distinct [ID] for [ACADEMIC_YEAR], [Admission Status Sort], [Admission Status], [COLLEGE_DESC])
which is pulling from the data item.
The diff is defined as
running-difference([# of Students] for [Admission Status Sort], [Admission Status], [COLLEGE_DESC])
which is also the data item. Is that what you mean?
Quote from: danasellers@udayton.edu on 11 Jan 2010 11:07:13 AM
I have the # of students defined as
count(distinct [ID] for [ACADEMIC_YEAR], [Admission Status Sort], [Admission Status], [COLLEGE_DESC])
which is pulling from the data item.
The diff is defined as
running-difference([# of Students] for [Admission Status Sort], [Admission Status], [COLLEGE_DESC])
which is also the data item. Is that what you mean?
Right, so then when you calculate the percentage use the data item for both the count & running-difference. If you know SQL you can view the SQL Cognos generates and see what it is actually doing.
well, i do understand sql, however, i've never been too great at deciphring cognos/native sql - doesn't make sense to me. when i check it it doesn't look like it's taking the query item - looks like it's still inturpreting it as the data source item. i can post the sql if it will help to figure this running-diff thing, but it's ugly, as there are several case statements which makes the sql long.
I think i figured this out. It's really conveluded but it seems to work. thanks for all the input on this!