views:

1149

answers:

4

I am trying to remove all references to a table from a Crystal XI report. Crystal is telling me that a column from that table is currently being used, because there is a little green check mark over the field in the field viewer. Also, if I try to remove the entire table, I get a warning. The warning is almost useless though because it doesn't tell me where the field is used. Now, back when programmers were real programmers, and mice were things cats chased, I could just grep a directory or file and find all references to a variable I was interested in. But how do I do this in Crystal? I have already tried exporting the report to a Report Definition, which helped find some instances of the troublesome field. Unfortunately, that format does not include all formulas, just some. Please tell me I don't have to buy a third party app (or write my own COM thingy) just to do this seemingly simple thing.

EDIT to add details about tangential point: In case anyone is wondering, I am not crazy - I have duplicated the issue where a formula's definition does not show up in the exported Report Definition. I created a new blank report, created one formula named stealth that returns 1234. I then used that formula in the Section Expert for the details section, in the "suppress" formula, setting it to {@stealth} == 0. the use of the formula shows up, but not the definition. So when my unwanted column was used in the formula, I was not be able to find it! Here's what the rpt def looks like (after deleting some blank lines):

     Crystal Report Professional v11.0 (32-bit) - Report Definition
    1.0 File Information
     Report File: 
     Version: 11.0
    2.0 Record Sort Fields
    3.0 Group Sort Fields
    4.0 Formulas
    4.1 Record Selection Formula

    4.2 Group Selection Formula

    4.3 Other Formulas
    5.0 Sectional Information
    5.1 Page Header Section
     Visible, Keep Together
    5.2 Page Footer Section
     Visible, New Page After, Keep Together, Print At Bottom of Page
    5.3 Report Header Section
     Visible, New Page Before
    5.4 Report Footer Section
     Visible, New Page After
    5.5 Details Section
     Visible
     Subsection.1 
      Visible, Keep Together
      Format Formulas
      Visible: {@stealth}= 0

+2  A: 

If you right click on the field in Field Explorer and select Find in Formulas, it should bring up a dialog listing all of the places it is being used in formulas. On the left hand side of the dialog is a tree of all the possible places it could be, including oddball places like record selector and page formatting functions. Unfortunately, it does not seem to list running total fields.

EDIT: Oops, all the places it exists is listed at the bottom of the dialog; the tree view is the entire "DOM" of the report.

pjabbott
Heh. If it were as simple as right clicking I would have been done with this chore hours ago! Now that I knew what to look for, it was easy to learn that the feature I want, and that you describe, was only introduced in XI Release 2 (http://help.sap.com/businessobject/product_guides/boexir2/en/xir2_cr_whatsnew_en.pdf) I will attempt to upgrade my workstation tomorrow, assuming the vendor we got crystal from can supply me with the newer version. Thank You! (upvote for now, and a best answer tomorrow if no one has a way to do it in the old version)
Peter Recore
Heh, I didn't even realize that was an R2 only feature. BusinessObjects said that R2 was just made for .NET 2.0 compatibility and nothing new was added...XIR2 is available as a download...you just need your license number (which you should be able to get from Help->About.http://resources.businessobjects.com/support/additional_downloads/service_packs/crystal_reports_en.asp#CRXIR2 (service pack 2 full build).
pjabbott
If you have XIr1, I think R2 was a free upgrade. Of course, since SAP is even worse than Business Objects was, finding that download might prove to be difficult, but it shouldn't be impossible, and it should be legal. This might be helpful: http://resources.businessobjects.com/support/additional_downloads/service_packs/crystal_reports_en.asp#CRXIR2
Adrien
A: 

If all else fails ...

File -> Export -> Export Report, then choose the Report Definition (TXT) option.

That will give you a plain-text representation of every element of the report. You can grep or CTRL-F or (insert search tool of your choice) through that. "Find in Formulas" usually works, but I've had to go the export route a couple of times, for no apparent reason.

Edit: Of course, if I'd bothered to completely read your post, I'd see that you've already done this.

Very curious.

Adrien
As I mentioned in my question, I already tried to export to Report Definition, but that format did not include a listing for all the formulas in my report. Maybe it only shows formulas that are placed into the report to be printed, as opposed to formulas that are used by other formulas?
Peter Recore
You're right, you did mention that. And I read your post three times before posting my answer ... (Head, meet Desk). Unfortunately, I actively use 9, 10, and XI daily, so sometimes I confuse myself. At any rate, truthfully, this is the first time I've heard of Report Definition not showing everything. Before "Find in Formulas", Report Def was the official way. Of course, I've had an "official answer" from Crystal Decisions that was "we can't help, try re-generating the report from scratch." :(
Adrien
I'm sure you know this, but remember that, although "Section 4.0" of the Report Definition calls itself the "Formulas" section, there can be *lots* of other formulas (formulae?) scattered throughout the document. Conditional formatting, suppression, etc, formulas will be farther down, with the actual object that they are attached to, and *not* up in the Formulas section. I'm going to have nightmares about this one.
Adrien
I have duplicated the stealth formula problem with an extremely simple report. see edited post above for details. @Adrien, time to start having nightmares :)
Peter Recore
Good `$diety` I hate Crystal. Now I hate it even more; I got precisely the same behavior you described. How awesome would it be to have a reporting component that was designed to be used by programmers rather than PHB's? Honestly. Crystal was clearly written with the non-technical-(l)user in mind, yet I've never seen it in production with that level of user. And for us ... It's just a kludgey nightmare. That thumping sound you hear is my head meeting my desk some more.
Adrien
well, I was lucky. my first reporting suite was Actuate, years ago. It was actually more programmer friendly, at least in theory. But having to save your file every 15 minutes to guard against the daily experience of it being corrupted was programmer unfriendly in practice. The funniest annoyance (in hindsight) was when the scroll bars would disappear, stranding important dialog boxes offscreen, forever. I have to thank Actuate for being so bad that crystal always feels great to me, even at times like this :)
Peter Recore
I was spoiled by the system that we rolled in-house two or three gigs ago. Unfortunately, that company is gone and the product is no longer available; a copy of the source was *not* included in my silver handshake. (At least I got the silver handshake, unlike some).
Adrien
+1  A: 

This was tested on XIr2...

You change the tables datasource through the "set datasource location" dialog. Now, when it goes into the column mapping mode, uncheck match-type and pick a new column that would cause an error in a formula. (i.e if the column you're looking for is a string replace it with a datetime column). Go to the preview and you should get an error box like "A string is required here.", close that error and up pops the offending formula!

dotjoe
+1 that's both sneaky and clever . will see if it works in R1 tomorrow morning, (it seems like it would have to work.) this is now just an intellectual exercise, as i have found the actual offending formula! Thanks :)
Peter Recore
it will probably crash r1. :)
dotjoe
+1  A: 

I know this is an old post, but...

Not knocking the Find in Formulas, it's been saving me today, but i was having trouble finding the last instance of the field. Even after all of the formulas and the droppings on the report were taken care of, I still had one lone use hiding somewhere.

I found it hiding as a Subreport Link. Right click on the Subreport -> "Change Subreport Links..." and there was the culprit. Dropping in this post because I figured someone else might have this problem too.