Solved

Relate data without using SQL

Posted on 2011-02-10
8
235 Views
Last Modified: 2012-08-13
I'm pulling data from XML/ XSD. Here is my challenge.
I have ID,Full Name,Title fields. I need to display the Title if the name isn't present. How to relate this back using the ID field. I don't see any options for SQL formulas.  Bellow is what I have so far but I need to relate the ID field otherwise it's just blank.

If IsNull({RateExcDet_NoActions.Timekeeper1__SortName}) Then
{RateExcDet_NoActions.Timekeeper1__TkprDate__Title1__Description}
Else
{RateExcDet_NoActions.Timekeeper1__SortName}
0
Comment
Question by:mowit
  • 5
  • 3
8 Comments
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34865615
What do you mean by "relate"? To me, it means you're pulling data from two or more tables and you're trying to link the tables on a related field.  In your case, you seem to be pulling all fields from one data source. Please note, Crystal is designed to pull data from table-like structures and it doesn't like inconsistency.  So, even when you're using XML as a data source the output should be a consistent table format.  In other words, the result of the xml should be the same columns in the same order every time.  If a tag is present when you build the report, but isn't present the next time you run the report, that's a problem.  

So, what's going on with your XML?  Is it a standard table structure?  Are all the fields available every time you use it?  Are all of the fields in the same XML (meaning it's being treated like a table, you're not joining multiple sources)?

~Kurt
0
 

Author Comment

by:mowit
ID: 34865673
I'm using XSD as the data source. The XSD is created by a report object designed to work with Crystal Never mind its difficult to explain. I can resolve this by making changes to my report object, i just wanted to know if there was a way to do it in crystal. I was said "relate" i was referring to one table relating to itself.

0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34865839
OK - so you really don't want to relate anything at all.  You simply want to display one value if another is missing, correct?

If so, your formula should work, but only if the Timekeeper1__SortName is actually NULL. If it's an empty value, the NULL check won't work.  So, you might want to modify it slightly:

If
  (
  IsNull({RateExcDet_NoActions.Timekeeper1__SortName})
  or Trim({RateExcDet_NoActions.Timekeeper1__SortName}) = ''//just in case there are spaces
  )
Then
  {RateExcDet_NoActions.Timekeeper1__TkprDate__Title1__Description}
Else
  {RateExcDet_NoActions.Timekeeper1__SortName}

~Kurt
0
 

Author Comment

by:mowit
ID: 34865875
Kurt,

I did this in my formula above but it's not working, just shows up blank. its like i need to tell it to look at the id field then show me the title for that id.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34865958
You shouldn't have to, since that's not how Crystal works.  It doesn't matter if you're using an actual table or XSD - it reads all of the fields and data into an object.  If you can see it under a single database in your database field explorer, then  you should be able to do it  Sort Name and Description are just fields in a single record.  Can you upload a copy of the report with saved data (assuming it's not confidential)?

~Kurt
0
 

Author Comment

by:mowit
ID: 34866146
I can't upload sorry. I'll just resolve by modifying the report object. Thanks for you help.
0
 

Accepted Solution

by:
mowit earned 0 total points
ID: 34866389
Figured it out i was using the wrong descriptions field.
0
 

Author Closing Comment

by:mowit
ID: 34895069
resolved on my own.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Changing Image in report header based on parameter value 12 49
Mask on Parameter CR2008 28 43
Crystal Formula to "conditionally count/sum" 3 38
Crystal Reports 13 5 43
Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now