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

Crystal Report 2011 (Can SQL be used to return a value) see below

SELECT SUM (T2.Duration) as TOTAL FROM dbo.CallDetail AS T1, dbo.CallDetail AS T2
WHERE T1.InternalID = 'VIP' and T1.BaseID = T2.BaseID AND T2.Times > '2012-01-08'
AND T2.InternalNum >= 600 and T2.InternalNum < 700
GROUP BY T2.BaseID
ORDER BY T2.BaseID


This SQL statement above returns a SUM and is EXACTLY what I need to pull into my Crystal Report.  I'm able to write the SQL but I don't know how to add this into Crystal..  Is there a way to add a SQL statement and then dump that into a row to return the sum?
0
wshcraft70
Asked:
wshcraft70
2 Solutions
 
Kyle AbrahamsSenior .Net DeveloperCommented:
yeah, you can either add that sql as a column to your main report.

eg:


set @total =
(SELECT SUM (T2.Duration) as TOTAL FROM dbo.CallDetail AS T1, dbo.CallDetail AS T2
WHERE T1.InternalID = 'VIP' and T1.BaseID = T2.BaseID AND T2.Times > '2012-01-08'
AND T2.InternalNum >= 600 and T2.InternalNum < 700
GROUP BY T2.BaseID
ORDER BY T2.BaseID )

select *, @total from main_table

so you have a reference to it.


The other way is just to grab T2.Duration, and then use a running total to sum it up within crystal.
0
 
mlmccCommented:
WHat database are you using?

The answer is yes and no.

Crystal has a feature called SQL Expressions.  They can be used for exactly what you want to do.  However some databases don't support using report values in the query.

Another way would be to use a subreport with that as the datasource.  You would have to change the SQL to use a parameter

SELECT SUM (T2.Duration) as TOTAL FROM dbo.CallDetail AS T1, dbo.CallDetail AS T2
WHERE T1.InternalID = 'VIP' and T1.BaseID = {?BaseID} AND T2.Times > '2012-01-08'
AND T2.InternalNum >= 600 and T2.InternalNum < 700
GROUP BY T2.BaseID
ORDER BY T2.BaseID

You would link the subreport to the main report on the parameter field

mlmcc
0
 
wshcraft70Author Commented:
So would I just create the formula

set @total =
(SELECT SUM (T2.Duration) as TOTAL FROM dbo.CallDetail AS T1, dbo.CallDetail AS T2
WHERE T1.InternalID = 'VIP' and T1.BaseID = T2.BaseID AND T2.Times > '2012-01-08'
AND T2.InternalNum >= 600 and T2.InternalNum < 700
GROUP BY T2.BaseID
ORDER BY T2.BaseID )

Then drop that formula into the details and hopefully be able to add a group sum?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
mlmccCommented:
No.  That is added to the SQL used for the report.

Are you using tables, views, stored procedures or a command as the report datasource?

mlmcc
0
 
wshcraft70Author Commented:
I tried adding this to the SQL Expressions and it is not compiling and providing me errors.  This would be my first experience adding SQL into a Crystal Report..  So learning as I go here..
0
 
wshcraft70Author Commented:
I'm using a table in an SQL database as the datasource.
0
 
wshcraft70Author Commented:
Headed out until Wednesday where I'll be able to pick back up.. I have not forgotten about you..  Thank you very much in advance..  Looking forward to returning and building the solution..
0
 
mlmccCommented:
WHat did you add?

In some cases you have to use (   ) around the expression as

(
SELECT SUM (T2.Duration) as TOTAL FROM dbo.CallDetail AS T1, dbo.CallDetail AS T2
WHERE T1.InternalID = 'VIP' and T1.BaseID = {?BaseId} AND T2.Times > '2012-01-08'
AND T2.InternalNum >= 600 and T2.InternalNum < 700
GROUP BY T2.BaseID
ORDER BY T2.BaseID
)

mlmcc
0
 
LinInDenverCommented:
I would try it with a Command as your data source instead of a table.

Commands are basically SQL statements stored inside the report -

select field1 from table where field = 'VIP'

You could make that your command, and "field1" would show up in your field browser on the report.
0
 
wshcraft70Author Commented:
@mlmcc

I just copied and pasted the below..  I added a subreport where I'm just pulling the "Duration" and then created a link on the subreport to the SQL {?BaseId} and I'm getting an error telling me that there is a problem with the SQL expression in that it is looking for a value that isn't being returned...  Thoughts?


(
SELECT SUM (T2.Duration) as TOTAL FROM dbo.CallDetail AS T1, dbo.CallDetail AS T2
WHERE T1.InternalID = 'VIP' and T1.BaseID = {?BaseId} AND T2.Times > '2012-01-08'
AND T2.InternalNum >= 600 and T2.InternalNum < 700
GROUP BY T2.BaseID
ORDER BY T2.BaseID
)

mlmcc
0
 
mlmccCommented:
I don't believe you can use a parameter in a SQL Expression.

mlmcc
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.

Join & Write a Comment

Featured Post

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.

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