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

Hi expert

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

Thanks
amedexittAsked:
Who is Participating?
 
Kurt ReinhardtConnect With a Mentor Sr. Business Intelligence Consultant/ArchitectCommented:
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
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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
 
vidruCommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
vidruCommented:
whoops... sorry Kurt!

-dave
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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
 
amedexittAuthor Commented:
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
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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
 
amedexittAuthor Commented:
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
 
vidruCommented:
All points to rhinok.

-dave
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.