Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2012-03-12
11
532 Views
Last Modified: 2012-06-27
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
Comment
Question by:wshcraft70
11 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 37711616
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
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 500 total points
ID: 37711626
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
 
LVL 1

Author Comment

by:wshcraft70
ID: 37711627
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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 100

Expert Comment

by:mlmcc
ID: 37711642
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
 
LVL 1

Author Comment

by:wshcraft70
ID: 37711643
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
 
LVL 1

Author Comment

by:wshcraft70
ID: 37711656
I'm using a table in an SQL database as the datasource.
0
 
LVL 1

Author Comment

by:wshcraft70
ID: 37711676
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
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 37711720
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
 
LVL 14

Expert Comment

by:LinInDenver
ID: 37712427
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
 
LVL 1

Author Comment

by:wshcraft70
ID: 37720189
@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
 
LVL 100

Expert Comment

by:mlmcc
ID: 37722568
I don't believe you can use a parameter in a SQL Expression.

mlmcc
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

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…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

840 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