Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Sharing a Formula 2

Status
Not open for further replies.

jcoleman

MIS
Dec 24, 2002
87
CA
I always seem to be asking simple questions, I hope that this is another easy one for the brains here.

I have a formula that converts location numbers to their respective addresses. I use this formula repeatedly in many of the reports that I produce. As the company that I work for expands more locations are added. Therefore I have to go into each report and update the formula with the new address information.

Is there a way (there must be) of setting up one formula and having the reports link to that formula to get the correct information? That way I would only have to change the information in one place instead of every single report.

Thanks,
 
What version of Crystal are you using? The functionality you need was introduced in version 9 with the Repository. You can turn formulas into custom functions and store them in this repository. You can share these functions with other reports. Through some configuration and database changes, you can also share the repository with other machines.

~Brian
 
Thanks, Brian

I'm using 8 1/2 so I don't have the repository.

But it's a pretty good reason to upgrade isn't it.

In the mean time does any one have a suggestion for 8.5?
 
In CR8.5 (and any version since CR5) you can use a User Function Library to compile your calculation into a DLL.

If you can write a Visual Basic function to do your conversion, wrap it inside a Class, inside a DLL project, compile it, place the DLL in Windows\Crystal, register it and it is available to all your reports. I've a powerpoint presentation on my site explaining how to get the names right.

Use the UFL function in all your formulas. Then when your locations expand, just replace the DLL on the machines.

Editor and Publisher of Crystal Clear
 
bdreed35,
I was not aware you could store your formulas as functions, then store them in the repository. How did you do this??? That is a wonderfull tip!
 
Mack2:

Here are the basic steps.

Open the existing formula that you have in the Formula Editor.
Click the Down arrow next to the New button.
Choose Custom Function from the drop down list.
Give it a name. This is the name you will see in the repository later on.
Click Use Extractor.
In the dialog box, highlight the formula you wish to turn into a custom function.
You can add more information in the summary section.
Any fields you used in your formula will now be arguements to pass to the function. I suggest renaming them to make it obvious as to what the function is looking for.
Click OK.
The new function will now be available under Report Custom Functions.
If you want to add it to the repository, right click the name in the tree, and choose Add to Repository. It will be available to other reports or possibly other users if you share a repository.

To use the functions, just select the function from the custom function list when you create formulas.

Also, the only place the functions will be visible in the repository is from the Formula Workshop, not from the Repository explorer.


~Brian
 
Once the function is created, how do you get it on your report? Just dragging and dropping doesn't work.
 
A bit more on the repository.....

It uses an ODBC connection to a local database (MSAccess) to store the repository objects (bitmaps, text objects, functions). The DSN is called 'Crystal Repository', and is created when you install Crystal Reports. Although the database is created locally, the real power of the repository comes in the ability to centrally store commonly used images, text, functions etc.

By moving the MSAccess database to a central server, you can change the ODBC connection on each workstation to 'point' to the centralized database - then, when you add an image etc. to the repository, everyone in your organization has access to it. It's worth noting too that the repository database does not have to be MSAccess. The ODBC connection can be setup to point to a table with the same layout in ANY ODBC compliant datasource e.g. Oracle, MS/SQL Server etc.

One last thing - don't invest too much into setting up and using the repository as this feature was REMOVED in Crystal Reports v10 - it was moved to Crystal Enterprise instead.

Peter Shirley
 
Peter,
Can you tell me how to get the function (formula) over to the actual report once created in the repository?
Thanks
 
Open the formula editor.
Docked on the right side will be the Formula Workshop tree.
Expand the Repository Custom Functions node and naviagte until you see the function you want to add to the report.
Right click the function name, and choose Add to Report from the pop up menu.
Now scroll down in the workshop tree and create a new formula.
In the middle window above the editor window will be the functions list.
Expand the function node to expose the different folders.
The last folder will be Custom Functions.
Drill down through the folders until you get to the actual function (there may be multiple levels)
Double click the function to add it to the formula just as you would with any other function.
Add any arguements as necessary.

~Brian
 
To get back to jcoleman's original question. For Crystal 8.5, there is a dodge for a mass of very similar formulae. Open a dummy report. Use cut-and-paste to copy several copies from main to dummy. Change the names and copy them back.

Madawc Williams (East Anglia)
 
Thanks Brian, but I'm confused on the last step ... "add any arguments as necessary". What goes in as arguments? For example, say the new formula is named "testformula". As the 2nd to the last step indicates, once I double click on the custom function, the following appears: Testformula (, )

What arguments is it looking for, since the function itself actually contains the formula?

Thanks again
 
When you extracted the formula into the function, it replaced any database fields with function arguements. This way you could pass values to the function so that it could perform some sort of calculation on them.

~Brian
 
Hi,

I am working with CE10 and CR10. To be able to save the custom function to the repository, do you need some kind of rights set up in CMC?

I tried to create and save a test function to the repository by following the above instruction (which is very helpful, by the way). When I saved it with an ID that only had 'View on Demand', I was not able to see the function when creating a new report. But I can see it if I saved it with an ID that has 'Full Control'. I wonder if I wasn't doing it right or maybe the rights do have something to do with it.

Thanks.
 
Hi,
I think that to save anything to the repository you need to be connected as an administrator - Not sure about that because:Editorial Note Follows:

The Repository and its usage may be the least documented major feature I have ever seen in a software system!!
[curse]






[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top