Hi Folks,
at first i am sorry to ask a lot of questions for now - when i am into the whole ReportNet stuff, i hope i can give back to the community :-)
Ok, where do i start:
I have a table (sales) with 4 columns (Sales department, Year, Month, Amount), which holds data from over two years.
I want to compare two months in a diagram. letÃ,´s say August 2004 and August 2005.
I want to have the difference of the amount for every sales department.
My data is something like this:
- Sales dept. A | 2004 | 08 | 8000,00EUR
- Sales dept. B | 2004 | 08 | 8000,00EUR
- Sales dept. A | 2005 | 08 | 7500,00EUR
- Sales dept. B | 2005 | 08 | 8200,00EUR
Calculation: Amount of newer Year/Month - Amount of older Year/Month
My result should look something like this:
- Sales dept. A | -500,00EUR
- Sales dept. B | 200,00EUR
The documentation of the Cognos ReportNet ReportStudio and Framework Manager is too little, i couldnÃ,´t find anything concerning this problem.
Thank you,
Cheers,
Philipp
- Sales dept. B | 2005 | 08 | 8200,00EUR
With pivot table you mean CrossTabÃ, :P (Forget Excel... you're playing with the real toys nowÃ, ;) )
Let's do it the difficult way:
1: Add List item to your report;
2: Add the needed columns; ([Department] , [Period]Ã, ) But DON'T ADD [Value] , [Year]
3: Add Calculation: [New Value]
Ã, Ã, Ã, Expression:
Ã, Ã, Ã, Case when ([Year]=2004) then ([Value]= [Value] *-1 )Ã, else ([Value]) end
This should result in the following list if ReportNet doesn't summarize it by default:
[Department] | [Period] | [New Value]
Sales dept. AÃ, |Ã, 08Ã, Ã, Ã, Ã, Ã, | -8000,00EUR
Sales dept. BÃ, |Ã, 08Ã, Ã, Ã, Ã, Ã, | -8000,00EUR
Sales dept. AÃ, |Ã, 08Ã, Ã, Ã, Ã, Ã, | 7500,00EUR
Sales dept. BÃ, |Ã, 08Ã, Ã, Ã, Ã, Ã, | 8200,00EUR
Now group on: [Department] and [Period]
And Summarize:Ã, [New Value]
This will result in:
Sales dept. AÃ, |08|Ã, -500,00EUR
Sales dept. BÃ, |08|Ã, Ã, 200,00EUR
And otherwise do it by using a CROSSTAB ;)
Brilliant, RNA!!!Ã, Nice going.