How to add a 'stored function' from Oracle in Cognos

[Sunchaser]

Hello,

I've some problems with this subject: 'import' a function from Oracle.
We have created some functions in Oracle that I needed to use in Cognos, so - regarding to the Developer Guide and the subject 'Creating a Custom Function Set' - I made the 'import' of one function(a short test) and finally it's working fine -> I can use it in a 'Data-Item' of a Query object and show the correct results.

The point is that I need to be able to call this function a the 'Variable' level.
It means that, when I'm creating a new variable (string type) I need to be able to see and use my function for the definition of this variable.

I've looked at some other files in the 'cognos_location/configuration/function' (as the 'cogCRXReportFunction' XML file, for example) and try to add my function in it.After the reboot of the cognos service, I can see my function in the list while I'm trying to define the expression of the variable, but in fact I can't use it: the function is not recognised.

Any help or advice is appreciated,
Thanks in advance
;)

[almeids]

Generic advice as I'm not on C8: when I have issues with what's available in a variable expression, I put whatever I need in a data item in my query instead and reference the data item in the variable expression.
HTH

[Sunchaser]

Hello,

Thanks for your interest in my problem.

Several points:
1. In fact I'm trying a 'work around' in order to display chars in a crosstab. I've already found a solution usefull for some issues that I've encountered, but It's not enough flexible for all my subjects.
2. I believed that It was not possible to apply a conditional formatting on a field in a crosstab based on the result/values of another DataItems, but may be I'm wrong of course ...
3. because of the point n°2, I'm trying to call a function (stored function in my Oracle db) in a variable (and so not in a DataItem) ; I will pass to this function (as unique parameter) the numerical value from the field in the crosstab and use this variable to apply my conditional formatting (on the textitem, in order to see special chars instead of numbers).

++
;)

[almeids]

I have a sort of general sense of what you're trying to do, so some more general comments:
Conditional formatting all comes down to "scope" as it was described to me years ago in a Cognos course; as long as the scope of the 2 data items is the same you should be able to use one in the formatting of another.  Unfortunately I can't define "scope" for you as I never fully understood it myself - it has to do with the internal processing of the report engine, whether things were in headers/footers or detail and such.  But I would think if you can treat them both the same in terms of aggregation then cross-formatting would work.  Having said that, conditional formatting in crosstabs is a huge pain and iffy at best, though I'm sure you know that already.
My second and last comment is that if you are not constrained by an unknown number of columns, consider using a list instead.  A lot of folks think a crosstab is the only way to crosstab, but you can do it the hard way with conditional column calculations if the number of columns is limited and the dimension values predictable.  It's more work but sometimes worth doing to circumvent formatting issues or to aggregate non-numeric "measures" as you're trying to do.
Good luck...

[Sunchaser]

Hi,

Many many thanks for your answer,

I'm going to keep in my mind the things you were saying about the 'scope', and will try to see if I could stay in a 'DataItem' level instead of trying to work in a variable (if it was possible, I think that the job will be finished soon).
I'm sure I could have made some mistakes, due to the fact that I don't have any 'real' Cognos course in my short experience.

For the second part, I can't use simply a list. For some reasons that I won't describe here, Crosstab will be the only way for me ...  ;D . I know that formatting over Crosstab is sometimes a little bit difficult, but many things are still possible if you take the time to think about and work on it.

So ... I don't want to be a boring guy, but I 'm still wondering if my first question (use a 'stored function' imported from Oracle in a variable instead of a DataItem) may be possible release or if it's a pure fantasy of my 'twisted' mind ...  ;D

[rockytopmark]

The bigger issue... what is the information you are trying to put into a Crosstab?  Do you know it at the Row level (for instance, your existing row has it in a data item)? 

If so, this is quite possible.  I have recently created Crosstabs that display Text where the number derived from an aggregated measure would normally be.

If this is what you are trying to do, let me know.

[Sunchaser]

Hello rockytopmark,

Yes, we are in the same idea. It's clear that I'm working on text on crosstab, I've done a few things like that (It works fine for some reports but it won't match for every subject), but the result is not enough flexible for all the cases that I could encounter.
To be honest, I don't really/clearly see what you mean by 'know it at the Row level '; the numeric data that I have to 'translate' in some chars are in a field that is a 'Crosstab Intersection'.

Of course, I'm really interested in understanding how you managed to get text in your crosstab, in order to compare to what I've done, and so learn and progress.
I'll understand if you think I want more that It's possible ... ;-)

++
;)

(forgive me for grammatical mistakes in my messages, please)

[rockytopmark]

If you have a Data Item that contains the Text you want in the Crosstab, you can do this using these steps:

1. Insert a Crosstab Space into the Crosstab. (For this example, we will add a Column)
2. Click to select the data cells (not the edge) of the new column
3. In the Properties window, locate the Define Contents property and change to "Yes"
4. Un-lock the report
5. Drag the text item into a cell of the new column
6. Lock the report
7. Click on the Row Edge item to select it.
8. In the properties window, locate the Data --> Properties property, click the ... to edit
9. Select the text data item inserted in step 5, then click OK to close

Run the report and your text should show up in the data area of the crosstab report.

[Sunchaser]

Oki,

You're great.
I've never tried this way, I going to try this with the datas I need.

Sincerely, I thank you for that explanation of your technique.

I'll post later the result of my trial.

++
;)

[Sunchaser]

I'm back ...
(in fact it's very late in France but I wanted to try before going to bed  ;D)

So, I'm worried: I can't get anything in the Crosstab Space; no DataItem value or even not a static text.
I don't know what I'm doing wrong ...  ???

Now I'm remembering that I made some tests with this, It was the same.

In your point 5, when you say: "Drag the text item", you are talking about the DataItem, isn't it ?

++
;)

[rockytopmark]

There is a button in the toolbar that looks like a Lock.  Clicking it will unlock your report, and enable you to drop something in the cell of the Crosstab Space.

[Sunchaser]

Yes, yes, I know that, It's not the problem.
The fact is that whatever I try with Crosstab Space, there is nothing appearing inside when I run the report.

[rockytopmark]

I am afraid to say that you have probably skipped one of the steps I outlined above.  Setting the properties on the Row is vital.

[Sunchaser]

Hello,

I know that It could sound very strange, but I can't get anything good from this object (Crosstab Space).
My purpose /intention is not to say that your solution is not good or something like that (hope you will understand this), and I really appreciate the help you are trying to give.

I won't try some voodoo technique on my computer because I'm too fearful for that  ;D , but sometimes I think It could help...