Crystal Reports 8.5 - Running Totals

Hi all,

Fairly new to Crystal, I am trying to build a report to show averages.

Have found this formulae in another report.

whileprintingrecords;

numbervar GrpTsec;
local numbervar AverageFix:=GrpTsec/DistinctCount ({SW_CASE.swCaseId}, {SW_CASE.dsInboxAssignedTo});
numbervar LenOfDay;

Local numberVar days := truncate(AverageFix / LenOfDay);
Local numberVar hr := truncate((AverageFix-(days*lenOfDay))/3600);
Local numberVar min := truncate((AverageFix-((days*LenOfDay)+(hr*3600)))/60);
Local numberVar sec := (AverageFix-((days*LenOfDay)+(hr*3600)+(min*60)));


Totext(days,"###") + " Day(s) " + totext(hr,"##") + " hours " + totext(min,"##") + " minutes " + totext(sec,"##") + " seconds";

averagefix/60

But on running the report it is returning an error message about Running Totals and Summary

Any ideas ?Public variables GrpTsec and LenOfDay are probably initialized in another formula. If this formula is not present, you will get "Divide by Zero" error when calculating value of "days".

Anyway, you should provide more details about the error you get.|||Hi Wrapper

The Error message that I am getting "The Summary / Running total field could not be created"

Many Thanks|||This error is common when trying to use invalid grouping option in functions like Sum(), Count() etc.

DistinctCount ({SW_CASE.swCaseId}, {SW_CASE.dsInboxAssignedTo});

Sure you have a group by {SW_CASE.dsInboxAssignedTo} in your report?|||Hi Wrapper,

Thanks, runs now !!!

Crystal Header Totals

I am a .Net developer and I am trying to print the totals from the detail section of the report into the Header. Is this possible. I am currently trying to Count the detail record that need to be counted and displaying them in the Header. ALL I get in the header is the first record in the Detail. Any help would be great.

ThanksWhy do you want to do this in Page Header? You can do this in Page Footer|||CR won't allow insertion of summary objects into the Page Header. You can put them in the Report Header though.|||CR won't allow insertion of summary objects into the Page Header. You can put them in the Report Header though.

It will show the summary at the end of report not everypage
You can do this pagewise by using formulae

Crosstab. 2 totals ?

Hi,

Am using robvolks crosstab-procedure to generate a crosstab query.

I get this result:
Total A B C
juli 455 1 107 347
okt 83 1 9 73
aug 612 1 113 498
juni 451 1 108 342

So I get a total for each month. But I would also like a total of each
letter
Total A B C
juli 455 1 107 347
okt 83 1 9 73
aug 612 1 113 498
juni 451 1 108 342
Total 1601 4 337 1260

Is that possible?

/jim
--call to procedure
execute crosstab 'select DATENAME(month,(theDate)) as '' '', count(*) as
'MonthsTotal'' from tblData group by
DATENAME(month,(theDate))','count(letter)','letter ','tblData'

----Robvolks procedure--
CREATE PROCEDURE crosstab
@.select varchar(8000),
@.sumfunc varchar(100),
@.pivot varchar(100),
@.table varchar(100),
@.where varchar(1000)='1=1'

AS

DECLARE @.sql varchar(8000), @.delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
SET LANGUAGE Danish

EXEC ('SELECT ' + @.pivot + ' AS pivot INTO ##pivot FROM ' + @.table + ' WHERE
1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @.pivot + ' FROM ' + @.table +
' WHERE '
+ @.where + ' AND ' + @.pivot + ' Is Not Null')

SELECT @.sql='', @.sumfunc=stuff(@.sumfunc, len(@.sumfunc), 1, ' END)' )

SELECT @.delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date',
data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @.sql=@.sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@.sumfunc,charindex( '(', @.sumfunc )+1, 0, ' CASE ' + @.pivot + ' WHEN '
+ @.delim + convert(varchar(100), pivot) + @.delim + ' THEN ' ) + ', ' FROM
##pivot

DROP TABLE ##pivot

SELECT @.sql=left(@.sql, len(@.sql)-1)
SELECT @.select=stuff(@.select, charindex(' FROM ', @.select)+1, 0, ', ' + @.sql
+ ' ')

EXEC (@.select)
SET ANSI_WARNINGS ON
GOJim Andersen (jba020@.politiSLET.dk.invalid) writes:

Quote:

Originally Posted by

Am using robvolks crosstab-procedure to generate a crosstab query.
>
I get this result:
Total A B C
juli 455 1 107 347
okt 83 1 9 73
aug 612 1 113 498
juni 451 1 108 342
>
So I get a total for each month. But I would also like a total of each
letter
Total A B C
juli 455 1 107 347
okt 83 1 9 73
aug 612 1 113 498
juni 451 1 108 342
Total 1601 4 337 1260
>
Is that possible?

You would use INSERT EXEC to capture the result from the crosstab
procedure into a temp table, and then compute a total row from the
data in it.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||SELECT month_name,
COUNT(*) AS tot,
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar
GROUP BY month_name
UNION ALL
SELECT 'All months',
COUNT(*) AS tot
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar;

But why not use a report writer in the front end, like you are supposed
to?|||--CELKO-- wrote:

Quote:

Originally Posted by

SELECT month_name,
COUNT(*) AS tot,
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar
GROUP BY month_name
UNION ALL
SELECT 'All months',
COUNT(*) AS tot
SUM(CASE WHEN foo = 'A' THEN 1 ELSE 0 END) AS tot_a,
SUM(CASE WHEN foo = 'B' THEN 1 ELSE 0 END) AS tot_b,
SUM(CASE WHEN foo = 'C THEN 1 ELSE 0 END) AS tot_c
FROM Foobar;
>
But why not use a report writer in the front end, like you are
supposed to?

So I don't have to hardcode my A, B and C's ?
Because I am using Visual Studio .NET and that leaves me with Crystal
Reports (yuckk, hark, spit) as a reporting tool.

I think I will try Erlands suggestion.

/jim

Crosstab Report

I am creating report with total costs by job, it repeats "column totals"
two times, and at the end column "grand total". "grand total" column is okay.
Is there any way I can drop/disable repeating "column totals". See example below, help me to drop repeating column.

Thanks

Job 1 Job 2 GrandTotal
Total Total

Cost 1 177,855 177,855 162,755 162,755 340,610
Cost 2 169,847 169,847 161,063 161,063 330,910
Cost 3 37,404 37,404 102,416 102,416 139,820In CR,right click on the cross tabl. Choose format Cross tabl. U ve a tab like "Customize Style". Choose each and every row in Rows List and below that u ve Grouping Option. Suppres that grouping option. I think this may be a soln. Try it out.