?
Solved

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

Posted on 2012-03-12
11
Medium Priority
?
537 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 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 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

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 this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

764 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