Using a Sum in Cross tabs? 1

[MFritts253]

I am using Crystal 8, and my data looks like so before i create my cross tab.

Week Contracts %Open
1 1500 100
2 1000 66
3 800 53
4 400 26
5 200 13

Now my Week 1 # of contracts is my total contracts
the formula is 1000/1500 = 66. I can easly create a formula to take the 1500 as @TotalContracts. But when i do that then try to make a cross tab report crystal won't let me use %Open Because @TotalContracts is by Group and the 1st largest of the group. So i'm not sure how to trick crystal to use my %Open in my cross tab?

Sincerely,
Michael

 

[lbass]

If you are able to use SQL expressions, then create the following {%grp1tot}:

(select sum(A.`Contracts`) from Table A where
A.`WeekNo` = 1)

I'm assuming that WeekNo is a database field of numeric type. Replact "Contracts" and "WeekNo" with your exact field names and "Table" with your table name. Leave "A" as is.

Then create a formula:

{table.contracts} % {%grp1tot}

Add this to your crosstab and choose sum as the summary. In preview mode, select this summary and click on the percent icon in the toolbar.

-LB

 

[MFritts253]

lbass,
I'm not sure if i can do that to get what i want in the total column in my Stored Proc. this Is my proc Below that returns my result set for my crystal report.

Select
COUNT(*) as STILLOPEN,
Keep_DR_ID,WeekNumber,
COUNT(
CASE
when (KWID.WeeksOpen = WeekNumber) then 1
end
) AS PayoutCount,

Case @Group1

when 'SVP' then KWID.SVP
when 'DVP' then KWID.DVP
when 'REGION' then KWID.region
when 'TYPE' then KWID.type
when 'ITEM' then KWID.item
when 'STORE' then KWID.store
else ''
end Group1,

Case @Group2

when 'DVP' then KWID.DVP
when 'REGION' then KWID.region
when 'TYPE' then KWID.type
when 'ITEM' then KWID.item
when 'STORE' then KWID.store
else ''
end Group2,
KWID.StartDate,KWID.EndDate

FROM
KWID
INNER JOIN W
ON KWID.Number = W.Keep_DR_ID
WHERE
KWID.WeeksOpen >= W.WeekNumber

When i put the statement in you gave me i'm not quite sure how to work it around the grouping?

 

[lbass]

I have never developed a stored procedure and can't really help you with that, but when you set this up, you want to do the equivalent of narrowing the results at the detail level to the records containing the group 1 field, i.e., the equivalent of writing a formula like:

if {table.groupfield} = 1 then {table.amt}

which would allow you to summarize across groups. You do not want to build grouping into the expression itself because then it will not be available in all crosstab rows. Try to use syntax parallel to what I used in the SQL expression. Not sure this helps...

-LB

 

[MFritts253]

lbass,
thanks for all your help. i'll give it a try and let you know how things work out.

cheers,
Michael