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 Report Report Footer help

Hello,

I just joined this site and new to the crystal report. I build the attached report with 2 group footer and want to add Grand Total in report footer but its not working. My group footer is calculated field, and when i try to sum it the calculated field doesn't show under the report fields,any help please?You cannot use summary functions on already summarized fields..|||What other way I can get the grand total?|||Repeat in report footer section the same calculations you do at group footer section but with grand total data

CrossTab(?) Query Problem

I've got a fairly tricky report to produce, so I'm trying to build it up bit
by bit, but I've fallen at the first hurdle!
I need to produce a view that lists all PartNo's and for each, what quantity
is in what location, e.g.
PartNo, GoodsIn, PostInspection, Quarantine, Stock, Total
WE12024, 5, null, 3, 10, 18
WE12067, 1, 1, null, 5, 7
etc...
I have the SQL to extract each bit of the data, but the problem is
presenting it in this way.
SQL:
Select PartNo, Count(*) as InGoodsIn
from Filters
Where LocationID = 15 /* for GoodsIn */
The PostInspection, Quarrantine & Stock locations have the following
LocationIDs - 16, 14, 17
How do I combine these result into one structure as indicated? Can somebody
point me in the right direction,please?
[In case it isn't clear from the above, the Filters table has this
structure: SerialNo (primary key), PartNo, LocationID]
Thanks in advance
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:%237q365qlFHA.360@.TK2MSFTNGP09.phx.gbl...
> I've got a fairly tricky report to produce, so I'm trying to build it up
> bit by bit, but I've fallen at the first hurdle!
> I need to produce a view that lists all PartNo's and for each, what
> quantity is in what location, e.g.
> PartNo, GoodsIn, PostInspection, Quarantine, Stock, Total
> WE12024, 5, null, 3, 10, 18
> WE12067, 1, 1, null, 5, 7
> etc...
> I have the SQL to extract each bit of the data, but the problem is
> presenting it in this way.
> SQL:
> Select PartNo, Count(*) as InGoodsIn
> from Filters
> Where LocationID = 15 /* for GoodsIn */
> The PostInspection, Quarrantine & Stock locations have the following
> LocationIDs - 16, 14, 17
> How do I combine these result into one structure as indicated? Can
> somebody point me in the right direction,please?
> [In case it isn't clear from the above, the Filters table has this
> structure: SerialNo (primary key), PartNo, LocationID]
> Thanks in advance
>
Apologies - problem solved.
I was on the right lines but had made a couple of errors. The solution I
have is:
SELECT Partno,
[GoodsIn] = SUM(CASE LocationID WHEN 15 THEN 1 ELSE 0 END),
[PostInspection] = SUM(CASE LocationID WHEN 16 THEN 1 ELSE 0 END),
[Quarantine] = SUM(CASE LocationID WHEN 14 THEN 1 ELSE 0 END),
[Stock] = SUM(CASE LocationID WHEN 17 THEN 1 ELSE 0 END)
FROM Filters
GROUP BY PartNo|||Try,
select
PartNo,
sum(case when location = 15 then 1 else 0 end) as GoodsIn,
sum(case when location = 16 then 1 else 0 end) as PostInspection,
sum(case when location = 14 then 1 else 0 end) as Quarrantine ,
sum(case when location = 17 then 1 else 0 end) as Stock
from
Filters
order by
PartNo
go
HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
AMB
"CJM" wrote:

> I've got a fairly tricky report to produce, so I'm trying to build it up b
it
> by bit, but I've fallen at the first hurdle!
> I need to produce a view that lists all PartNo's and for each, what quanti
ty
> is in what location, e.g.
> PartNo, GoodsIn, PostInspection, Quarantine, Stock, Total
> WE12024, 5, null, 3, 10, 18
> WE12067, 1, 1, null, 5, 7
> etc...
> I have the SQL to extract each bit of the data, but the problem is
> presenting it in this way.
> SQL:
> Select PartNo, Count(*) as InGoodsIn
> from Filters
> Where LocationID = 15 /* for GoodsIn */
> The PostInspection, Quarrantine & Stock locations have the following
> LocationIDs - 16, 14, 17
> How do I combine these result into one structure as indicated? Can somebod
y
> point me in the right direction,please?
> [In case it isn't clear from the above, the Filters table has this
> structure: SerialNo (primary key), PartNo, LocationID]
> Thanks in advance
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>|||"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:191A665E-2D8E-43F0-845D-EDAAAD54C844@.microsoft.com...
> Try,
> select
> PartNo,
> sum(case when location = 15 then 1 else 0 end) as GoodsIn,
> sum(case when location = 16 then 1 else 0 end) as PostInspection,
> sum(case when location = 14 then 1 else 0 end) as Quarrantine ,
> sum(case when location = 17 then 1 else 0 end) as Stock
> from
> Filters
> order by
> PartNo
> go
>
Curious...
Your syntax is different to mine but still works - eg. "Case when LocationID
= 15" rather than "Case LocationID when 15"
Is this difference significant?
Chris|||The result of both expressions are the same. See CASE in BOL to read more
about "simple case" and "search case" formats.
AMB
"CJM" wrote:

> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messag
e
> news:191A665E-2D8E-43F0-845D-EDAAAD54C844@.microsoft.com...
> Curious...
> Your syntax is different to mine but still works - eg. "Case when Location
ID
> = 15" rather than "Case LocationID when 15"
> Is this difference significant?
> Chris
>
>

Crosstab - Supress Columns

I'm using CR8.5 with RDC, VB 6, SQL Server 7, I build the recordset in VB and pass it to CR. The query can't be changed...

I need to build a crosstab...

Example Data:
Bob - Value1
Bob - Value2
Joe - Value1
Joe - Value3
John - Value2
Mary - Value2
Mary - Value3
Mary - Value3
George - Value3
George - Value3
George - Value3

The crosstab would look like this:

Name Value1 Value2 Value3 Total
Bob 1 1 2
Joe 1 1 2
John 1 1
Mary 1 2 3
George 3 3

Problem 1 - I need Value1 and Value2 displayed all the time, regardless if there is any data in the column. I tried to use 'Specified Order' in the Group Options, but it always leaves out a column if there's no data.

Problem 2 - There's a column that always returns data that I don't want displayed (In this example, it would be Value3). I was able to leave this column out by not specifying it in the Specified Order list of columns, but I was looking for an easier way.

Problem 3 - (part of Problem2) - I was able to use a conditional suppress formula that said if the columnheader was Value3 then suppress the column, but then I get a blank column even though the option for suppress empty columns is turned on. I always thought suppress meant to make it invisible in runtime, but it seems that in the crosstab is just makes the data invisible and leaves the column there.Hi
PROBLEM 2:
you can filter the record through Recod Selection Formula which u can write by Selecting Report->Record Selection->Record(CR9) menu, let say if you want to filter records where Second colum contain "Value3" record selction formula will be

left({TableName.ColumName},6)<> 'Value3'

OR

{TableName.ColumName} <> 'Value3'

Where TableName will be the name of Source Table or in your case query and colum name will be the colum on which you apply the filter

PROBLEM1:

you cannot display a value for colum source which is not in the repot data in crystal reports if you are using Crystal Reports Crosstab, however, if the colums of the crosstab reports are static, by static i mean that your are sure(in your example) about that you have to just display "Value1" "Value2","Value3" then you can create a Customise crosstab report, otherwise it is not possible, just have a look at the sample report which I have created for you based on the sample data you provided

please see the attached repot which I have created for you.
Hope this will help you(report is created in crystal reports9)

PROBLEM3:
the answer is in Report file, please view the report

(unfortunately I am not able to attache the report as its extention is .rpt which is not allowed by the forum, send me an email and I will send it to you)
MY EMEIL ADD: jadoger_1@.hotmail.com

Cheers|||jadoger,

I'm not able to send or recieve emails from this computer at this time. Can you put the rpt file into a zip file and attach it that way? If not, don't worry about it. I've already "solved" my problem with a workaround. I created my own crosstab which I have more control over.

If you can send the rpt file though, I would be curious to see what you did and see if that would have solved my problem. I appreciate you putting the time and effort into creating it for me.

CrossDB Model

Hi,

I'm used to AS while designing and using OLAP-Cubes.

Now I have to build and publish several models directly on relational base. So far, no problem with the wizards, every DB got a model with attribs and roles. But the information i wanna analyze, is splitted across multiple databases, where there is no foreign key relationship designed in DB. I have to link the tables of different DBs together by hand and design (or let design :-)) a model across more than one database for analysations.

So how would you build up a single model for several databases?

Hope my problem is understandable.

Thanks in advance.

Thorsten

Hm, maybe with using views which joins the multiple tables as one fact table, and using natural keys where there are no foreign key relationships designed?
|||

Call me noob,

but is there a way to define cross-db-views?

|||noob ( ;-) )

Well, I am kinda a noob too, but can't it be done by something like this?:
SELECT * FROM
database1.dbo.table1 JOIN database2.dbo.table2 ON database1.dbo.table1.id = database2.dbo.table2.id

I don't know it exactly, but it might be worth trying :-)
(In this case, I assume you're using just one database system: SQL Server)
|||

For sure, you can join across DBs the way you wrote. But you can't save these queries as views, or?

Because views are DB related.

|||Just tried it (in a view) and it works just fine :-)

Cross tabs with Drill downs

Hi Guys
I need to generate a report where I need to have drill downs in a matrix(cross tab query) in Business Intelligence.
When I try to build a report using the report wizard, I can either select matrix or Drill downs and subtotals.
Can I have both of these options?

Thanks
Mita

Moving to AS forum.

Cross tab based on DateDIFF

I'm trying trying to build a crosstab query using a Case statement to count
the number of records in one column with the DateDiff >=0 and the other
column to be a count of the Records with the DateDiff <0. I can do this
easlily in MS Access with the IIF Function in a crosstab query but I can't
get the Sql Statement to work in an MS Sql 2000 View
Geoff,
Is this what you are after?
SELECT
SUM (CASE
WHEN Col1 >= 0 THEN 1
ELSE 0
END) AS NonNegative,
SUM (CASE
WHEN Col1 >= 0 THEN 0
ELSE 1
END) AS Negative
FROM YourTable
--RLF
"Geoff" <Geoff@.discussions.microsoft.com> wrote in message
news:253BF539-8139-4E63-B824-73440A6302E9@.microsoft.com...
> I'm trying trying to build a crosstab query using a Case statement to
> count
> the number of records in one column with the DateDiff >=0 and the other
> column to be a count of the Records with the DateDiff <0. I can do this
> easlily in MS Access with the IIF Function in a crosstab query but I can't
> get the Sql Statement to work in an MS Sql 2000 View
|||How do I put in the condition of wether the DateDiff is >= 0. I was trying
someting similiar to this view below but replacing the Count(CASE ACTCAT WHEN
N'X4' Then ACTCAT END with something to make one column count the number of
records where DATEDIFF(DD, dbo.JobLogTbl.JSTime, dbo.ContActivTbl.AddDate)
AS DATEDIFF >=0 and another column show the count if DATEDIFF(DD,
dbo.JobLogTbl.JSTime, dbo.ContActivTbl.AddDate) AS DATEDIFF < 0 in a view
SELECT TOP 100 PERCENT DATENAME(MM, dbo.JobLogTbl.JSTime) AS Month,
MONTH(dbo.JobLogTbl.JSTime) AS MoNum,
COUNT(CASE ACTCAT WHEN N'X4' THEN ACTCAT END) AS X4,
COUNT(CASE ACTCAT WHEN N'AG' THEN ACTCAT END) AS AG
FROM dbo.ContActivTbl INNER JOIN
dbo.JobLogTbl ON dbo.ContActivTbl.JobNo =
dbo.JobLogTbl.JobNo
WHERE (dbo.ContActivTbl.ActCat = 'X4') OR
(dbo.ContActivTbl.ActCat = 'AG') AND
(dbo.JobLogTbl.JSTime >= 01 / 01 / 07)
GROUP BY DATENAME(MM, dbo.JobLogTbl.JSTime), MONTH(dbo.JobLogTbl.JSTime)
ORDER BY MONTH(dbo.JobLogTbl.JSTime)
I can do this in MS Access with
TRANSFORM Count(ContActivTbl.JobNo) AS CountOfJobNo
SELECT Format([JSTime],"mmmm") AS [Month], Count(ContActivTbl.JobNo) AS
[Total Shipments], JobLogTbl.DLocNo
FROM ContActivTbl RIGHT JOIN JobLogTbl ON ContActivTbl.JobNo = JobLogTbl.JobNo
WHERE (((ContActivTbl.ActCat)="X4") AND ((JobLogTbl.JobStart)>#1/1/2007#)
AND ((JobLogTbl.JSTime) Is Not Null) AND ((JobLogTbl.SoType)="1") AND
((JobLogTbl.ShipStatus)<>"Cancelled"))
GROUP BY Month([JSTime]), Format([JSTime],"mmmm"), ContActivTbl.CoNo,
JobLogTbl.DLocNo, JobLogTbl.SoType, JobLogTbl.ShipStatus
ORDER BY Month([JSTime])
PIVOT IIf([JobLogTbl].JSTime>=[ContActivTbl].AddDate,"Before Arrival","After
Arrival");
"Russell Fields" wrote:

> Geoff,
> Is this what you are after?
> SELECT
> SUM (CASE
> WHEN Col1 >= 0 THEN 1
> ELSE 0
> END) AS NonNegative,
> SUM (CASE
> WHEN Col1 >= 0 THEN 0
> ELSE 1
> END) AS Negative
> FROM YourTable
> --RLF
> "Geoff" <Geoff@.discussions.microsoft.com> wrote in message
> news:253BF539-8139-4E63-B824-73440A6302E9@.microsoft.com...
>
>
|||Geoff,
If I am tracking you correctly then you would want something like:
SELECT
DATENAME(MM, dbo.JobLogTbl.JSTime) AS Month,
MONTH(dbo.JobLogTbl.JSTime) AS MoNum,
SUM (CASE
WHEN DATEDIFF(DAY,dbo.JobLogTbl.JSTime, GETDATE())>= 0 THEN 1
ELSE 0
END) AS NonNegative,
SUM (CASE
WHEN DATEDIFF(DAY,dbo.JobLogTbl.JSTime, GETDATE()) >= 0 THEN 0
ELSE 1
END) AS Negative
FROM YourTable
GROUP BY DATENAME(MM, dbo.JobLogTbl.JSTime),
MONTH(dbo.JobLogTbl.JSTime)
ORDER BY MONTH(dbo.JobLogTbl.JSTime)
I have not put all columns in, but I hope that this helps.
RLF
"Geoff" <Geoff@.discussions.microsoft.com> wrote in message
news:DC89754E-0A4C-4D78-B5C2-75F547C62D67@.microsoft.com...[vbcol=seagreen]
> How do I put in the condition of wether the DateDiff is >= 0. I was trying
> someting similiar to this view below but replacing the Count(CASE ACTCAT
> WHEN
> N'X4' Then ACTCAT END with something to make one column count the number
> of
> records where DATEDIFF(DD, dbo.JobLogTbl.JSTime,
> dbo.ContActivTbl.AddDate)
> AS DATEDIFF >=0 and another column show the count if DATEDIFF(DD,
> dbo.JobLogTbl.JSTime, dbo.ContActivTbl.AddDate) AS DATEDIFF < 0 in a view
>
>
> SELECT TOP 100 PERCENT DATENAME(MM, dbo.JobLogTbl.JSTime) AS Month,
> MONTH(dbo.JobLogTbl.JSTime) AS MoNum,
> COUNT(CASE ACTCAT WHEN N'X4' THEN ACTCAT END) AS X4,
> COUNT(CASE ACTCAT WHEN N'AG' THEN ACTCAT END) AS AG
> FROM dbo.ContActivTbl INNER JOIN
> dbo.JobLogTbl ON dbo.ContActivTbl.JobNo =
> dbo.JobLogTbl.JobNo
> WHERE (dbo.ContActivTbl.ActCat = 'X4') OR
> (dbo.ContActivTbl.ActCat = 'AG') AND
> (dbo.JobLogTbl.JSTime >= 01 / 01 / 07)
> GROUP BY DATENAME(MM, dbo.JobLogTbl.JSTime), MONTH(dbo.JobLogTbl.JSTime)
> ORDER BY MONTH(dbo.JobLogTbl.JSTime)
> I can do this in MS Access with
> TRANSFORM Count(ContActivTbl.JobNo) AS CountOfJobNo
> SELECT Format([JSTime],"mmmm") AS [Month], Count(ContActivTbl.JobNo) AS
> [Total Shipments], JobLogTbl.DLocNo
> FROM ContActivTbl RIGHT JOIN JobLogTbl ON ContActivTbl.JobNo =
> JobLogTbl.JobNo
> WHERE (((ContActivTbl.ActCat)="X4") AND ((JobLogTbl.JobStart)>#1/1/2007#)
> AND ((JobLogTbl.JSTime) Is Not Null) AND ((JobLogTbl.SoType)="1") AND
> ((JobLogTbl.ShipStatus)<>"Cancelled"))
> GROUP BY Month([JSTime]), Format([JSTime],"mmmm"), ContActivTbl.CoNo,
> JobLogTbl.DLocNo, JobLogTbl.SoType, JobLogTbl.ShipStatus
> ORDER BY Month([JSTime])
> PIVOT IIf([JobLogTbl].JSTime>=[ContActivTbl].AddDate,"Before
> Arrival","After
> Arrival");
> "Russell Fields" wrote: