Solved

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

Posted on 2012-03-12
11
534 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 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
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.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

733 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