What to substitute ‘WHERE’ clause with if SQL Expression Fields is 2

[Katya85S]

Crystal report (8.5) runs out of stored procedure. It has a numeric ‘RecType’ field. The report should count percentage of RecType=0 against total number of records.
I thought I could assign to a variable (let’s say @Type0s) something like
COUNT(*) WHERE RecType=0 in the SQL Expression Fields. And then calculate the percentage of the variable @Type0s out of total number of records (Count(*), etc.)
But since the report uses stored procedure as data source, the SQL Expression Fields is not an available option of the ‘Field Explorer’ window. Any ideas on how to find the percent of records, WHERE RecType=0 in my situation?
Thank you all in advance

 

[Katya85S]

Another thoughts: is it possible to calculate percentage within the stored procedure the report runs from? I’ve tried adding variable (@Persent) to the stored procedure (beside the SELECT statement, which data I need in my report) to calculate that percentage but don’t see how to extract that variable to the Crystal report.
The SP that includes percent-calculation looks something like this:
CREATE PROCEDURE [sp_Name]

@StartDate datetime,
@EndDate datetime
AS
SELECT Field1, Field2, Filed3, RecType
FROM tblTable1
group by Field1, Field2, Filed3, RecType
having count(Field1)>=1 and
opening_date >= @StartDate AND
opening_date < @EndDate

declare @Percent int
declare @CountTotal int
declare @CountType0s int

set @CountTotal = (select count(*) From tblTable1
where
opening_date >= @StartDate AND
opening_date < @EndDate

set @CountType0s = (select count(*) From tblTable1
where
opening_date >= @StartDate AND
opening_date < @EndDate

set @Percent = 100*@CountType0s/@CountTotal

GO
Any help?

 

[lbass]

Why can't you just do the following in CR? Create a formula:

//{@rectype0}:
if {table.rectype} = 0 then 1

Then create a formula {@percent}:

sum({@rectype0}) % count({table.rectype})

For the count, if {table.rectype} can be null, then use some other recurring field. Click on the % icon in the toolbar. This assumes you are evaluating this at the report level. If you are evaluating at a group level, add the group condition into each summary of the percent formula.

-LB

 

[Katya85S]

LB,
thank you very much for the solution and the tricks!
it works just right.
Many thanks!

 

[synapsevampire]

You can probably just get away with creating a Running Total and in the evaluate->use a formula place:

{table.rectype} = 0

Then in the report footer use:

{#MyRunning Total} % count({table.rectype})

If the percent is no a group level, consider doing this is the SP.

-k

 

[Katya85S]

Thank you, K.!
Your advice works perfect too.
You've mentioned a Stored Procedure. Is it possible in the same SP have a select statement and calculate Percentage and then display records and a variable calculating Percentage in the report? As i've mentioned above I've tried in the SP to write code for @Percent below the select statement (I'll copy the percent-calculating part of SP below), but then I didn't know how to extract that @Percent to my Crystal Report.

declare @Percent int
declare @CountTotal int
declare @CountType0s int

set @CountTotal = (select count(*) From tblTable1
where
opening_date >= @StartDate AND
opening_date < @EndDate

set @CountType0s = (select count(*) From tblTable1
where
opening_date >= @StartDate AND
opening_date < @EndDate AND
RecType=0
set @Percent = @CountType0s%@CountTotal

 

[synapsevampire]

The dificulty with doing this is that you need to return a value for every row in the SP, but of course you can do so.

The final select would have:

select field1, field2, @percent etc..

-k

 

[Katya85S]

I see... Then within Crystal computation looks more effecient.
Thank you much K

 

[synapsevampire]

Depends on the volume of the data, an SP should prove much faster than Crystal, but you will be returning more data in the SP.

-k

 

[Katya85S]

This helps
Thank you!