Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2012-03-12
11
Medium Priority
?
540 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
[X]
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
11 Comments
 
LVL 41

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 101

Assisted Solution

by:mlmcc
mlmcc earned 2000 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 101

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 101

Accepted Solution

by:
mlmcc earned 2000 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 101

Expert Comment

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

mlmcc
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

618 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