• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 672
  • Last Modified:

Crystal Reports: Duplicate Members in Hierarchy Rolling Up Incorrectly

I have a hierarchial report that is incorrectly displaying records.  In my database, there is the real possibility that pairs of parent and dependent members can be duplicated within the hierarchy.  For example, here are the results I'm getting:

[1] AllOrgs
     [2] CONSOPS
          [3] CORP
               [4] KOREA
               [4] POLAND
                    [5] POLAND_BU
                    [5] POLAND_BU_ELIM
                    [5] POLAND_BU_TOT
                    [5] POLAND_BU
                    [5] POLAND_BU_ELIM
                    [5] POLAND_BU_TOT
                    ...
As you can see, there are multiple pairs of dependents that are rolling up under 'POLAND'.  In the database (which I have verified is correct), these multiple child records (and their corresponding parents) roll up under a different "previous" parent entirely.  Again for example, the entire 'POLAND' and its dependents also exist under a different third-level parent (other than 'CORP').  Crystal is grouping all of these duplicate members under the first occurrance of the parent member, rather than displaying all of the records in the order that they appear within the dataset.

In my Crystal report, I am grouping on the child field - in orginal order.  I need to preserve the original database order in this report.

I would think that this incorrect rollup could be controlled within the report itself, but nothing I've tried so far is handling this.

Any and all help would be greatly appreciated!  Please let me know if there is a need for further clarification.

Operation System: Win XP Pro SP2
Crystal version: Crystal Reports XI
Database: Microsoft Jet 4.0
Programming Language: VB .NET
0
Donovan Moore
Asked:
Donovan Moore
  • 7
  • 7
2 Solutions
 
mlmccCommented:
Does Poland show up under both parents?

mlmcc
0
 
Donovan MooreAuthor Commented:
Yes and no (!).

What makes this difficult (in my mind) is that these 'POLAND' dependents could be ANYWHERE within the hierarchy.  They could be under 'POLAND', or under a completely different parent altogether.

Basically, this example is a combination of multiple hierarchies.

Does that make any sense?
0
 
mlmccCommented:
What are the fields in the database?

mlmcc
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
Donovan MooreAuthor Commented:
PK - HierarchyID (AutoNumber)
OrganizationLabel (Text 50)
CategoryLabel (Text 50)
Period (Integer)
PreviousParentEntityLabel (Text 50)
ParentEntityLabel (Text 50)
DependentEntityLabel (Text 100)
ApplicationName (Text 50)

I created the 'PreviousParentEntityLabel' field in order to potentially track three different levels - the Child, its Parent, and its Parents' Parent, but that still hasn't worked.  In addition, a query runs against this table:

"SELECT msysHierarchy.HierarchyID, msysHierarchy.OrganizationLabel, msysHierarchy.CategoryLabel, tblCategory.CategoryDescription, msysHierarchy.Period, [msysHierarchy]![PreviousParentEntityLabel] & [msysHierarchy]![ParentEntityLabel] AS GroupKey, msysHierarchy.ParentEntityLabel, tblEntity_1.EntityDescription AS ParentEntityDescription, [msysHierarchy]![ParentEntityLabel] & [msysHierarchy]![DependentEntityLabel] AS DependentEntityLabel, tblEntity.EntityDescription AS DependentEntityDescription, IIf(IsNull([tblEntity]![EliminationEntityFlag]),"N",([tblEntity]![EliminationEntityFlag])) AS EliminationEntityFlag, msysHierarchy.ApplicationName, [msysHierarchy]![ParentEntityLabel] & ";" & [tblEntity_1]![EntityDescription] & ";" & [msysHierarchy]![DependentEntityLabel] & ";" & [tblEntity]![EntityDescription] AS ParentChildHierarchy
FROM ((msysHierarchy LEFT JOIN tblCategory ON (msysHierarchy.CategoryLabel = tblCategory.CategoryLabel) AND (msysHierarchy.ApplicationName = tblCategory.ApplicationName)) LEFT JOIN tblEntity ON (msysHierarchy.ApplicationName = tblEntity.ApplicationName) AND (msysHierarchy.DependentEntityLabel = tblEntity.EntityLabel)) LEFT JOIN tblEntity AS tblEntity_1 ON (msysHierarchy.ParentEntityLabel = tblEntity_1.EntityLabel) AND (msysHierarchy.ApplicationName = tblEntity_1.ApplicationName);"

You can see that a number of other database elements are being pulled in for the report.

Note: the 'GroupKey' field is a concatenation of the 'PreviousParentEntityLabel' and the 'ParentEntityLabel' fields.

Let me know what else I can provide.

Thanks!
0
 
Donovan MooreAuthor Commented:
Just checking in...

Did my explanation make sense?  Would anyone like me to provide additional information?

Thanks again...
0
 
Donovan MooreAuthor Commented:
Bumping this one back up...

Does anyone know if there is a character limit on a field used for grouping purposes?

I'm toying with the idea of tracking the entire string of parents for each dependent to group on...create a unique ID of sorts.
0
 
mlmccCommented:
Are you using the Crystal hierarhical report or are you trying to do the hierarchy yourself?

mlmcc
0
 
Donovan MooreAuthor Commented:
I am using the Hierarchial Grouping Options for this report - grouping on the dependent member.

however, we are also using VB .NET code to build the back-end dataset.  We need to use recursive logic to allow the user to select a parent member (either the top parent of the hierarchy, or any valid parent anywhere in the hierarchy) and find just the resulting portion of the tree.
0
 
mlmccCommented:
I am not sure Crystal can do what you want.  You may be able to get it to work in VB where you can build the appropriate dataset.

mlmcc
0
 
Donovan MooreAuthor Commented:
Update: I have modified the dataset to hold the entire hierarchial "path" for each parent.  For example, the new parent member would look like - with a "!" as a delimiter:

Parent:
!AllOrgs!CONSOPS!CORP!POLAND
Child:
!AllOrgs!CONSOPS!CORP!POLAND!POLAND_BU

The I'm using the Split() function to just display the last member of the array for the report.

The issue here is with the potential length of the string.  It very well could be over 255 characters.
0
 
mlmccCommented:
Crystal XI can handle much longer strings.  String length is only an issue before CR9

mlmcc
0
 
Donovan MooreAuthor Commented:
Can it handle Memo fields?  Unfortunately, my backend database is Jet.  I'm planning a re-write in SQL Server, but it'll be a while.
0
 
mlmccCommented:
Sure.  Crystal can use memo fields.  I think there is an upper limit but it is like 65k or maybe even bigger.

mlmcc
0
 
mlmccCommented:
Rather than using SPLIT, try using
a formula like

local numbervar stringlength := Length({YourField});
local numbervar lastonestarts := InStrRev({YourField},'!');
Right({YourField},stringlength - lastonestarts )

mlmcc
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 7
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now