Solved

Can I Create a Crystal Report (8.0) based on a SQL function?

Posted on 2004-10-07
12
216 Views
Last Modified: 2012-06-27
Hi expert

Can I Create a Crystal Report (8.0) based on a SQL function? If  yes please tell me How.

Thanks
0
Comment
Question by:amedexitt
  • 4
  • 3
  • 2
12 Comments
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 12250175
You can't create a report based on a User-defined Function.  A function is really designed to return a single, distinct value.  Instead, you could create your report based on a Table, View, or Stored Procedure.  The View or Stored Procedure may reference the function.  Additionally, you could create a SQL Expression in Crystal, which would be analagous to using a function or subquery in the SELECT clause.

~Kurt
0
 
LVL 13

Expert Comment

by:vidru
ID: 12250221
Off of a Table function?  Nope, it won't show as a Table in the Data Explorer.  You'd have to create a stored procedure that uses the table function, then use the stored procedure as the report's data source.

If you're asking if you can use a User Defined Function in Crystal, then yes you can, using a SQL Expression field, like this:
dbo.FN_YourFunction("Table.Field")

-dave
0
 
LVL 13

Expert Comment

by:vidru
ID: 12250229
whoops... sorry Kurt!

-dave
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 12250290
lol, np - dave:-)  You added an important coment, referencing the function from within the SQL Expression Vs. writing a SQL Expression to replace a function.

~Kurt
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:amedexitt
ID: 12250426
My issue is the following. I need to create a report that shows me the entire Agent Hierarchy that could be like this:
ID1
-----ID2
-----------ID3
----------------ID4
----------------------ID5
-----------------------------ID6

This means that ID1 is the main Agent and ID2 is under ID1 but ID3 is under ID2 and at the same time under ID1 and so on. ID1 have different ramification. I have to Table AGT and AGTSubAGT linke by agtID.
Table AGT has field AgetID (Amount Other)
Table AGTSubAGT has field AgetID, Agetof (Amount Other)

Thanks.
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 12250544
It sounds like you could benefit from Hierarchical Grouping within Crystal Reports, but this is dependent on your version.

If you're using CR 8 +, the I'd recommend you open your help file and search for 'grouping data hierarchically'.  This will tell you everything you need to know.

Additionally, check out the following links:

http://support.businessobjects.com/library/kbase/articles/c2010719.asp
http://support.businessobjects.com/communityCS/FilesAndUpdates/hierarchical_grouping.zip.asp

~Kurt
0
 

Author Comment

by:amedexitt
ID: 12252695
Hi Kurt You has made my day today. Thank you VERY MUCH

The report is working perfect but there is just one more thing that maybe can be done.
The thing is that the report is showing me the whole Hierarchy but when I sort by specific agent it is giving me just one level under I mean

ParentId
------------ID
Putting this : {Agents.AgtId} = 778 or
{AgentSubagents.SubagentOf} = 778  in the Select Criteria allowed me to get the production of the ParentId and the production of the ID but I still have some agents under ID and they are no coming out when I sort by specific agent. If you have some thing to fix this please let me know
0
 
LVL 26

Accepted Solution

by:
Kurt Reinhardt earned 500 total points
ID: 12260978
Since you're using two different tables, Hiearchical Grouping may not be the best way to go after all.  Its really designed to let you group recursively against the same table.  In your case, you could do the following:

1)  Join SubAgents (primary table) to Agents (secondary table) on SubAgentOf to AgtId

2)  Group the report by {Agents.AgtId}, place Agent fields in the Group Header

3)  Place SubAgent fields in the detail section.  Your report will look like this:

GH - 778     Smith, Bob
DT -                             887     Jones, John
DT -                             878     Marples, Mary
DT -                             788     Cane, Candy

Your record selection statement should work then.

~Kurt

0
 
LVL 13

Expert Comment

by:vidru
ID: 12454811
All points to rhinok.

-dave
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 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

13 Experts available now in Live!

Get 1:1 Help Now