Programatically determine if a table field has been used in a report 2

[flozee]

Hi,

First time get on this forum. Hope to get an answer with a CR .net SDK question.

I am trying to find a way to programatically determine if a table field has been used in a report rpt file. I was trying to use a method called useCount() inherited from the fieldDefinition class. But the useCount of any given field always shows 1 no matter if it really used in the report or not. Somewhere on the BO site tells me this useCount property has been deprecated. I have looked all over the SDK for a replacement method to do the same thing. No luck so far! Is there anyone know how else would I accomplish this without using the useCount property?

thanks in advance

 

[Madawc]

If you're new to Crystal, you need to know the basics about totalling. Remember that Crystal is a reporting tool, not a full programming language, and you have to go along with the standard methods.

There are several ways to find totals: running totals, summary totals and variables. Right-click on a field and choose Insert to get a choice of Running Total or Summary. Or else use the Field Explorer, the icon that is a grid-like box, to add running totals.

Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say. They default to 'Grand Total', but also can be for a group.

Variables are user-defined fields. One useful variant are shared variables to pass data from a subreport back to the main report. You can also use variables to show page totals. For normal counting I find running totals or summary totals much easier.

Directly Calculated Totals within a Formula Field can be coded directly, with commands like Sum ({ADV01.Advance}, {ADV01.AccType}). The same result can be achieved by picking up an existing Variable, and will keep the code even if the Variable itself is later deleted. Formula fields can also include Running Totals and other Formula Fields, with some limits depending on when the values are calculated.

It is also possible to get totals using a Formula Field, which can contain a Variable or a Directly Calculated Total.

To get yourself familiar with the idea, try doing a test report with a summary total and a running total for the same field, placed on the detail line. You'll find that the running total increases as each line is printed, whereas the summary total has the final value all along.

That's for Crystal 10 - it helps to give your version, and also database connectivity when that's relevant.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[flozee]

I'd apprecitate it if you read my questions more carefully. Maybe you meant to answer some other people's questions. For sure, you are not answering my question.

thanks anyway

 

[Turkbear]

Hi,
Do any of the samples in this file:

http://support.businessobjects.com/communityCS/FilesAndUpdates/bexi_net_samples.zip.asp

Help to determine that?





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

 

[synapsevampire]

I guess determining what you mean by used is key here as well.

A field might be used in a relationship in a join, or just in a where clause, etc, and if that is the case, then the SQL in the report (for SQL based reports) should determine that.

Since the Crystal GUI uses a check mark next to fields in use in the report, one would think that there's a means to do so, but perhaps your requirements are different from what they are using to determine use anyway, as referenced above. I think they only check for use in formulas and the report canvas only.

So my bottom line answer is I don't know, other than determining what's in the select statement.

-k

 

[flozee]

What I meant to find out was if a table field is being placed on the report canvas or if it is used in a formula. The check mark by the database field in the field explorer made me think that there must be a way to find out the usage of a table field. In fact, the useCount property was used to indicate the number of times a table field is used in a report at the least in the previous version of the Crystal .net SDK.

In the rpt inspector software(if you have ever used the software), it is also has a property called isusedInReport when you view a specific table field object. This is also another indication that I should be able to determine if a field is used in a report.

I wasn't trying to determin if a field is used in a table joint at all.

Thanks for the example provided by Turkbear. Those example are for the B.O enterprise not for the report unfortunatelly.

anymore help is appreciated. I hope I have clarified myself enough.

thanks,

 

[Becks25Not]

Hello All,

Did you (or anypone) ever figure this out?

Thanks!!

 

[synapsevampire]

The standard export to report definition file does this, but I'm not familiar with the API well enough to tell you how to extract this information.

-k

 

[Becks25Not]

Thanks!! It's a start!

 

[cjlarue]

hi
MSDN also show usecount to be obsolete.

http://msdn2.microsoft.com/en-us/library/ms225824.aspx

How do you determine which field you are looking for? could you loop thru the fields collection looking for the FieldObject.Name Property ? or are you trying to list fields that are not used? Could you compare the collection of DatabaseFieldDefinition.name with FieldObject.name ?

 

[flozee]

Thanks for the continuing interests on my questions. NO, I don't have the answer to the question. Everyone in the subsequent postings had provide some valuable input. My take on this is we should not need to go through so many hoops to figure out if a database field has been used in a report.

I still searching for an answer. It will come to me someday somehow.

 

[Becks25Not]

I found that if you use the Crystal Decision dll's version 9.1.5000.0 (the ones w/ VS 2003), the UseCount still works.

While the SQL statement is not extact, it can sort of be built:

Start:\

 rptdoc = New ReportDocument
        rptdoc.Load(ReportFileAndPath)

    _Database = rptdoc.Database
        _Tables = _Database.Tables

Joins:

  For intL As Int16 = 0 To _Database.Links.Count - 1
            If strWhere <> "" Then strWhere &= " AND "
            strWhere &= " (" & _Database.Links(intL).DestinationTable.Location & "." & _Database.Links(intL).DestinationTable.Name
            Select Case _Database.Links(intL).JoinType
                Case LinkJoinType.Equal : strWhere &= " = "
                Case LinkJoinType.GreaterOrEqual : strWhere &= " >= "
                Case LinkJoinType.GreaterThan : strWhere &= " > "
                Case LinkJoinType.LeftOuter : strWhere &= " LEFT OUTER JOIN "
                Case LinkJoinType.LessOrEqual : strWhere &= " <= "
                Case LinkJoinType.LessThan : strWhere &= " < "
                Case LinkJoinType.NotEqual : strWhere &= " <> "
                Case LinkJoinType.RightOuter : strWhere &= " RIGHT OUTER JOIN "
            End Select
            strWhere &= _Database.Links(intL).SourceTable.Location & "." & _Database.Links(intL).SourceTable.Name
            strWhere &= " ON "
            For intS As Int16 = 0 To _Database.Links(intL).SourceFields.Count - 1
                strWhere &= _Database.Links(intL).DestinationTable.Name & "." & _
                _Database.Links(intL).DestinationFields(intS).Name & " = " & _
                 _Database.Links(intL).SourceTable.Location & "." & _
                _Database.Links(intL).SourceTable.Name & "." & _
                _Database.Links(intL).SourceFields(intS).Name
                If _Database.Links(intL).SourceFields.Count > 1 AndAlso intS <> _Database.Links(intL).SourceFields.Count - 1 Then
                    strWhere &= " AND "
                End If
            Next
            strWhere &= ") "
        Next

Where:

   strWhere &= rptdoc.DataDefinition.RecordSelectionFormula

Select:

  For intF As Int16 = 0 To _Table.Fields.Count - 1
                If _Table.Fields(intF).UseCount > 0 Then
                    If strSelect <> "" Then strSelect &= ", "
                    strSelect &= _Table.Name & "." & _Table.Fields(intF).Name
                End If
            Next