Using Text Item value in calculation ?

[Nicodemus]

Hello there,

On my report page I have some Text Items, each containing a number.
I wish to use these numbers within a calculation. I don't know how to refer to these values in my expression.
ex :
text item name - contents
Fee France   -  1.12
Fee Germany -  1.16
Fee UK         -  1.10

case
  when [country_code]='FR' then [amount]*[Fee France]
  when [country_code]='DE' then [amount]*[Fee Germany]
  when [country_code]='UK' then [amount]*[Fee UK]
  else [amount]
end

I know I can type the values directly into the expression, I just want to know if it's possible to refer to the Text Item's name.

Thank you in advance for any help/suggestion

Nicodemus

[morphy]

Hello Nicodemus

I am quite new in Cognos and I have the same problem here.
May I just ask u where to type the expression:

case......then

???

Thank you in advance

Matt

[Nicodemus]

Hi Matt,

your report should be bound to a query (List, CrossTab,...).
With 'Query Explorer', select this query.
Then from the ToolBox you drag a 'Data Item' object into the query.
Double click the newly created 'Data Item'.
The 'Data Item Expression' window opens. You just type your code into the 'Expression Definition' pane, and that's it.

I hope this helps.

Cheers,
Nicodemus

[Suraj]

those fee france and 1.12 are text items that you typed in in report or they come from database columns?
if they are text, it is similar to hard coding anyway.
Also you can't refer to a text item for another calculation.
You can only use parameters and other data items for new calculation.
So instead of text item, you can create a data item for france fee, another for DE fee and so on and use them to calculate...

[Nicodemus]

Hello Suraj,

thx for your reply.
I will then create a query with these Fees and display them into a List on my report.

I tried the Parameters option (Text Box Prompt), but I can't display its 'default selection'. Actually the Text Prompt appears blank, although its value is 1.12. Maybe you know a trick on how to display this default selection instead of the blank ?

Thank you for your support.
Nicodemus

[Suraj]

If you put a value as default selection, it comes in the text box prompt.
Don't know why it's not coming up in your prompt.

[DanSev]

Try to change the data format of the text box prompt to number (default number settings). Without specification a text box prompt will assume it holds a string, even if the values are digits.

[Nicodemus]

thanks a lot to the both of you for your help !

DanSev, excuse my ignorance but I can't find how to change the data format of the Text Box Prompt...
The only related settings I can see are : Default Selections, Hide Text, Numbers Only, Parameter, Use Thousands Separator.
Other settings are not relevant (Background Color, Font, Foreground Color,...)
Can you guide me through ?

[Nicodemus]

thanks a lot to the both of you for your help !

DanSev, excuse my ignorance but I can't find how to change the data format of the Text Box Prompt...
The only related settings I can see are : Default Selections, Hide Text, Numbers Only, Parameter, Use Thousands Separator.
Other settings are not relevant (Background Color, Font, Foreground Color,...)
Can you guide me through ?

Found ! Actually the Parameter name was impacting the display...!
previous name : FRANCE_% --> value displayed = blank
new name : FRANCE_FEE --> value displayed = 1.12

It seems it is not necessary/possible to change the Text Box Prompt data format.

This post can be closed.

Thanks again

[Suraj]

That's strange.
Can you confirm by changing just the parameter name back with all the correct values?
having % in the parameter name should not have that effect if everything else is same.

[Nicodemus]

yes, I've created a new Text Box Prompt parameter :
- unticked 'Create a parameterized filter'
- Default selection = 15
- named it 'FR_FEE'
- run the report and got its default value displayed.

Renaming it to 'FR_%' returns a blank field.

Don't know why this happens...

[morphy]

Hi Matt,

your report should be bound to a query (List, CrossTab,...).
With 'Query Explorer', select this query.
Then from the ToolBox you drag a 'Data Item' object into the query.
Double click the newly created 'Data Item'.
The 'Data Item Expression' window opens. You just type your code into the 'Expression Definition' pane, and that's it.

I hope this helps.

Cheers,
Nicodemus

Hi Nicodemus,

U r great!
I got it now!
Only a further question...
This should be my case clause:

case
  when [country_code]='FR' then [amount]*100/TOTAL AMOUNT
  else [amount]
end

where TOTAL AMOUNT is the sum of the amounts of every country (amount FR + amount UK + amount IT...)

How do I calculate the total amount???
It seems easy but I don't get it.

Thks in advance my friend.
Ciao
Matt