Crystal Reports Create External Custom Functions

[SundancerKid]

Hi All,

Is it possible to create a custom function that queries an Oracle Table and brings back the results??

I have a Table in the Main (Relationship=1-1) and another Table (Relationship=1-Many) which has nothing to do with my report except I need to know if the userid exists against the SurveyID.

The Main Table has SurveyID and the other Table has SurveyID and UserID.

Since I can not pass the UserID easily back to a stored procedure because I am using Business View Manager with a default UserID for all of the reports. (Using one user because they did not want these users to be managed in Oracle they wanted to Manage them in Business Objects).

Well I need something simple and I thought a Custom Function would work.

Is there any examples or documentation on creating "External Custom Functions"??

Please Help...

 

[Turkbear]

Hi,
You can create/compile User-Defined Functions ( UFLs) using VB or C, but it should be easier to create a SQL COMMAND to check the second table for the UserId given the SurveyId..

No mention of how the stored Procedure is involved in your post, so, without seeing exaclty the structure of your report, this is just a guess.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SundancerKid]

Hi Turkbear thanks for your response.

Using
Business Objects XI R2 SP4
Business View Manager XI R2 SP4
Crystal Reports XI R2 SP4
Oracle 10g

Report is built using Business View Manager.

My Problem is I can not pass the UserName to a Stored Procedure because I am using One UserName as my Data Connection in Business View Manager because they don't want all of the users referenced on the Oracle Security Server.

This report was moved from a standalone webbased package to BOE XI R2 and I am trying to integrate it.

Here is the former Stored Procedure Where Clause

[COLOR=blue] WHERE [/color] (upper(IHSV.createdby) = upper(user)
[COLOR=blue] OR [/color]not exists (select null [COLOR=blue] from [/color] IHSampledBy [COLOR=blue] where [/color] upper(monitorby) = upper(user) [COLOR=blue] and [/color] TechnicianConsultant = -1)
[COLOR=blue] OR [/color] exists (select null [COLOR=blue] from [/color] IHSurveySampledBy IHSSB [COLOR=blue] where [/color] IHSV.Survey_ID = IHSSB.SurveyID [COLOR=blue] and [/color] upper(IHSSB.MonitorBy) = upper(user)))

I was able to select everything using Crystal Reports Select Expert thru the first OR operator to:

 ([COLOR=blue]IF [/color] UPPERCASE(Q.createdby) =  UPPERCASE({MV_TECHNICIANS.USER_ID}) [COLOR=blue] OR [/color] 
 {MV_TECHNICIANS.TECHNICIANCONSULTANT} <> -1 [COLOR=blue] THEN [/color]
  [COLOR=red] TRUE [/color]
[COLOR=blue] ELSE [/color]
  [COLOR=red] FALSE[/color])

MV_TECHNICIANS is being filtered by UserID (1-1 Relationship with the main table) in Data Foundation section of Business View Manager and it will have only one user the CurrentCEUserName (This satisfy's the 1st OR operator.

but I am still missing this section

 [COLOR=blue] OR [/color] exists (select null from IHSurveySampledBy IHSSB [COLOR=blue] where [/color] IHSV.Survey_ID = IHSSB.SurveyID [COLOR=blue] and [/color] upper(IHSSB.MonitorBy) = upper(user)))

I have never used UFL's is it difficult?

 

[Turkbear]

Hi,
I find them so..They need to be compiled into a DLL and registered - this site has details:

http://www.slxdeveloper.com/page.aspx?action=viewarticle&articleid=103.

Sorry,I can't help on your existing query since I always avoided using Business Views ( Much better to use Oracle's views and Stored Procedures - In our shop we preferred controlling access and handling data structures in the database not with a propietary product - that way, if we stopped using Crystal, the data and access was still intact)





To Paraphrase:"The Help you get is proportional to the Help you give.."