asked on

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?

Kyle Abrahams, PMP

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.

SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wshcraft70

ASKER

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?

Mike McCracken

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

wshcraft70

ASKER

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

wshcraft70

ASKER

I'm using a table in an SQL database as the datasource.

wshcraft70

ASKER

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

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LinInDenver

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.

wshcraft70

ASKER

@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

Mike McCracken

I don't believe you can use a parameter in a SQL Expression.

mlmcc