Calculated Fields in a List/Pivot Table

[pw]

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

[sir_jeroen]

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 ;)

[bdybldr]

Brilliant, RNA!!!Ã,  Nice going.