Crystal reports: passing DATA to main report

hey everyone:

I have a report with a number of subreports which basically contain ranking data with ranks stores in certain catagories. What I would like to be able to do is at the end of the report, take all of the store_ids that show up on the subreports, and total up their rankings for one aggregate score.

This involves sending data back from the subreports to the main reports, and I would need to pass both the store_id and ranking number.

Is there a way to accomplish this with the current report setup? The one idea I had is to use a shared array for each subreport, and at the end of the report I can handle each array, but that would involve looking for matching store id's, and since i am NOT a programmer, that would take me a long time to accomplish.

so if anyone has any simpler solutions, i would really appreciate it.

edit: Crystal reports 8.5, fwiwWhat is the difficulty you are facing with comparison?
Give more details

cross tab query

Hi - I have what I think is a "simple problem".

We currently have a database table that stores ItemProperties by
ItemID, PropertyID and Value. (The PropertyID references another table
for property names and types.)

This ItemProperties table is indexed and provides a flexible way of
storing our item metadata. However, I would now like to return
recordsets to the application layer that list these properties in
column fashion, grouped by ItemID

I have seen that most cross-tab queries examples assume numerical data
and are based around using SUM and the GROUP BY phrase. But our data
is a mixture of string and numbers (of various formats) and so GROUP
BY is not an obvious solution. I have tried using CASE in the select
list but this returns one row for each property with one column having
the correct value, and all the other colums are NULL. I cannot think
of how to combine these into one full record!!

I could achieve the desired resultset by using a SELECT sub-statement
for every column, but I was hoping there was a more efficient method.

Can anyone offer advice on this? It would be most appreciated.

Best,

Bill> I have seen that most cross-tab queries examples assume numerical data
> and are based around using SUM and the GROUP BY phrase. But our data
> is a mixture of string and numbers (of various formats) and so GROUP
> BY is not an obvious solution. I have tried using CASE in the select
> list but this returns one row for each property with one column having
> the correct value, and all the other colums are NULL. I cannot think
> of how to combine these into one full record!!

Hi Bill,

Put your table into a hashish table, implement the IUnpronounceable
interface to convert it to XML, overload it with a semi-private method
name foo, call it from another method with a meaningless name that is
also a swear word, drag and drop into the trash can (sorry I meant
recycle bin), and do start->run->format c:. Seriously, I think this
might give you an idea.

SELECT
itemID,
name=MAX(case when propertyID='name' then value else null end),
color=MAX(case when propertyID='color' then value else null end),
size=MAX(case when propertyID='size' then value else null end)
FROM Property
GROUP BY itemID

Cross Referencing The Same Table

Hi all,
I have a PERSON table that stores personal info, and a RELATIONSHIP table
which just has a list of relationships.
Now I want to be able to define a family by means of a crossref table
between PERSON and PERSON using the RELATIONSHIP.
PERSON
PersonID Name Sex Birthdate
1 FRED M 1956/6/29
2 WILMA F 1959/4/18
3 PEBBLES F 1985/9/14
RELATIONSHIP
RelationshipID RelationshipText
1 SPOUSE
2 CHILD
3 PARENT
First off, is it best to create a record for every PERSON and different
RELATIONSHIP? That would mean the following is correct:
PERSON_PERSON
PersonID PersonID RelationshipID
1 2 1
2 1 1
1 3 2
2 3 2
3 1 3
3 2 3
This creates quite a few records... And if I add a 4th type:
4 SIBLING
And add a 4th child, "SANDY"
Then we get:
PERSON_PERSON
PersonID PersonID RelationshipID
1 2 1
2 1 1
1 3 2
1 4 2
2 3 2
2 4 2
3 1 3
3 2 3
3 4 4
3 1 3
3 2 3
3 3 4
Any thoughts on this? Should I just add a ReverseRelationshipID that
identifies what relationship is the inverse of another?Andrew
It seems that the relationship between two tabls is many-to-many , thus you
attempt to create a "junction table" , am I right?
Take a look at NorthWind database shipped by MS and relation between
Orders --Products and a junction table [Order Details
]
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OQwi81IgGHA.3996@.TK2MSFTNGP03.phx.gbl...
> Hi all,
> I have a PERSON table that stores personal info, and a RELATIONSHIP table
> which just has a list of relationships.
> Now I want to be able to define a family by means of a crossref table
> between PERSON and PERSON using the RELATIONSHIP.
> PERSON
> PersonID Name Sex Birthdate
> 1 FRED M 1956/6/29
> 2 WILMA F 1959/4/18
> 3 PEBBLES F 1985/9/14
> RELATIONSHIP
> RelationshipID RelationshipText
> 1 SPOUSE
> 2 CHILD
> 3 PARENT
> First off, is it best to create a record for every PERSON and different
> RELATIONSHIP? That would mean the following is correct:
> PERSON_PERSON
> PersonID PersonID RelationshipID
> 1 2 1
> 2 1 1
> 1 3 2
> 2 3 2
> 3 1 3
> 3 2 3
> This creates quite a few records... And if I add a 4th type:
> 4 SIBLING
> And add a 4th child, "SANDY"
> Then we get:
> PERSON_PERSON
> PersonID PersonID RelationshipID
> 1 2 1
> 2 1 1
> 1 3 2
> 1 4 2
> 2 3 2
> 2 4 2
> 3 1 3
> 3 2 3
> 3 4 4
> 3 1 3
> 3 2 3
> 3 3 4
> Any thoughts on this? Should I just add a ReverseRelationshipID that
> identifies what relationship is the inverse of another?
>|||I know the relationship is many-to-many, and don't have a visualisation
problem when it's between 2 different tables, such as orders/products, but
in this case both sides of the cross-reference are the same table.
For example, the Northwind Order Details table has OrderID and ProductID...
But if I want to be consistant then my Family x-ref table would be PersonID
and PersonID. Can't do that.
So what I have done for the time being is create a Family table with a PK of
FamilyID and a FK of PersonID (which is where the family members name, sex
and birthdate would come from), and have a new x-ref table Person_Family
linking Person and Family, and including the RelationshipID.
This adds an extra table into the mix, so joins get a tad more complicated,
but it normalises OK.
In most cases I'm only interested in family of a certain person (namely an
employee).
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e7nZTQJgGHA.4004@.TK2MSFTNGP04.phx.gbl...
> Andrew
> It seems that the relationship between two tabls is many-to-many , thus
> you attempt to create a "junction table" , am I right?
> Take a look at NorthWind database shipped by MS and relation between
> Orders --Products and a junction table [Order Details
>
> ]
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:OQwi81IgGHA.3996@.TK2MSFTNGP03.phx.gbl...
>|||Andrew
Well , have you conisdered creating a "tree" implemenation table
I took this example for Employees , however if it suites you can modified
it. Actually , I tried to give you an idea that may help you ,since i do
not know your business requirements
CREATE TABLE Employees
(
empid int NOT NULL, ( a person (personid))
mgrid int NULL, --a manager ( could be parent)
empname varchar(25) NOT NULL,
salary money NOT NULL,
CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
CONSTRAINT FK_Employees_mgrid_empid
FOREIGN KEY(mgrid)
REFERENCES Employees(empid)
)
CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
GO
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:OrYMkWKgGHA.4304@.TK2MSFTNGP05.phx.gbl...
>I know the relationship is many-to-many, and don't have a visualisation
>problem when it's between 2 different tables, such as orders/products, but
>in this case both sides of the cross-reference are the same table.
> For example, the Northwind Order Details table has OrderID and
> ProductID... But if I want to be consistant then my Family x-ref table
> would be PersonID and PersonID. Can't do that.
> So what I have done for the time being is create a Family table with a PK
> of FamilyID and a FK of PersonID (which is where the family members name,
> sex and birthdate would come from), and have a new x-ref table
> Person_Family linking Person and Family, and including the RelationshipID.
> This adds an extra table into the mix, so joins get a tad more
> complicated, but it normalises OK.
> In most cases I'm only interested in family of a certain person (namely an
> employee).
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:e7nZTQJgGHA.4004@.TK2MSFTNGP04.phx.gbl...
>|||I use something similiar for handling positions in the company, but for
family I have to include not only parents, but spouse, kids, grandkids and
anyone else that could be considered a dependent of the employee, as that
has an affect on their tax.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23pquoeKgGHA.2040@.TK2MSFTNGP03.phx.gbl...
> Andrew
> Well , have you conisdered creating a "tree" implemenation table
> I took this example for Employees , however if it suites you can modified
> it. Actually , I tried to give you an idea that may help you ,since i do
> not know your business requirements
> CREATE TABLE Employees
> (
> empid int NOT NULL, ( a person (personid))
> mgrid int NULL, --a manager ( could be parent)
> empname varchar(25) NOT NULL,
> salary money NOT NULL,
> CONSTRAINT PK_Employees_empid PRIMARY KEY(empid),
> CONSTRAINT FK_Employees_mgrid_empid
> FOREIGN KEY(mgrid)
> REFERENCES Employees(empid)
> )
> CREATE INDEX idx_nci_mgrid ON Employees(mgrid)
> INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00)
> INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00)
> INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00)
> INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00)
> INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00)
> INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00)
> INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00)
> INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00)
> INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00)
> INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00)
> INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00)
> INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00)
> INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00)
> INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
> GO
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:OrYMkWKgGHA.4304@.TK2MSFTNGP05.phx.gbl...
>|||I would use an Employees table and a Dependents table.
While it is true that all of these folks are people, I would consider them
different entities. Dependents and emplyees, should not, in my opinion, be
in the same table. An employee that happens to be another employee's
dependent would exist as two seperate entities, and employee and a
dependent, and the two would not be associated.
So I would ditch the people table entirely, and create a dependents table
with (employeeID, dependentID) as the primary key. Store all your dependent
information there.
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:uNl%23$ALgGHA.4464@.TK2MSFTNGP04.phx.gbl...
> I use something similiar for handling positions in the company, but for
> family I have to include not only parents, but spouse, kids, grandkids and
> anyone else that could be considered a dependent of the employee, as that
> has an affect on their tax.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23pquoeKgGHA.2040@.TK2MSFTNGP03.phx.gbl...
modified
do
but
PK
name,
thus
message
different
>|||That's the way I had it before when it was just employees, but I'm going for
full normalisation now as I also have people that are customer/vendor
contacts, people that are candidates, etc.
Which is why I'm going for a person table, rather than 6 or more tables with
firstname, lastname, and fullname.
Maybe, down the road, after the design is done and we are doing testing, we
might denormalise portions of the database to improve performance, but I'd
rather not take the easy route at the beginning and find I lose flexibility
and functionality later on.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:e7kJKYNgGHA.3588@.TK2MSFTNGP04.phx.gbl...
>I would use an Employees table and a Dependents table.
> While it is true that all of these folks are people, I would consider them
> different entities. Dependents and emplyees, should not, in my opinion,
> be
> in the same table. An employee that happens to be another employee's
> dependent would exist as two seperate entities, and employee and a
> dependent, and the two would not be associated.
> So I would ditch the people table entirely, and create a dependents table
> with (employeeID, dependentID) as the primary key. Store all your
> dependent
> information there.
>
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:uNl%23$ALgGHA.4464@.TK2MSFTNGP04.phx.gbl...
> modified
> do
> but
> PK
> name,
> thus
> message
> different
>|||Actually, I think you are losing flexibility, functionality, and
maintainability now. Just because several entities share certain attributes
does not mean you should put them in the same table. I think you are
confusing good object oriented design with database normalization. The two
are quite different. You are going to have different rules surrounding
customers, contacts, dependents, and employees, and you will be able to
enforce these much simpler and more dependably if you treat them as separate
entities to begin with.
Your database will be properly normalized by having 6 different tables for
the different entities rather than trying to squeeze them all together into
a single table. Customers, employees, and dependents really have nothing in
common by a business standpoint.
I know I have seen articles discussing this approach, unfortunately I can
not seem to track any of them down at the moment.
"Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
news:e3aM7drgGHA.4404@.TK2MSFTNGP05.phx.gbl...
> That's the way I had it before when it was just employees, but I'm going
for
> full normalisation now as I also have people that are customer/vendor
> contacts, people that are candidates, etc.
> Which is why I'm going for a person table, rather than 6 or more tables
with
> firstname, lastname, and fullname.
> Maybe, down the road, after the design is done and we are doing testing,
we
> might denormalise portions of the database to improve performance, but I'd
> rather not take the easy route at the beginning and find I lose
flexibility
> and functionality later on.
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:e7kJKYNgGHA.3588@.TK2MSFTNGP04.phx.gbl...
them
table
that
i
message
visualisation
orders/products,
table
a
between
RELATIONSHIP
that
>|||I ran across some articles, and a couple of old SQL Server books, and yes,
I'm confusing OOD with database design. While at first glance it seems to
make sense, further research shows that, in this instance, it is better to
use seperate entities as you described.
Heck. I do it that way normally, but I was blinded by the idea of a person
as the primary entity, since all our products center around people
management.
I had thought it was going to create more work for me linking the various
people enties to the addresses, but a closer look shows that only employees
*need* to have more than one address.
Still, this is a huge undertaking, but at least now I can look at it
sensibly.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:u0dF31%23gGHA.2208@.TK2MSFTNGP05.phx.gbl...
> Actually, I think you are losing flexibility, functionality, and
> maintainability now. Just because several entities share certain
> attributes
> does not mean you should put them in the same table. I think you are
> confusing good object oriented design with database normalization. The
> two
> are quite different. You are going to have different rules surrounding
> customers, contacts, dependents, and employees, and you will be able to
> enforce these much simpler and more dependably if you treat them as
> separate
> entities to begin with.
> Your database will be properly normalized by having 6 different tables for
> the different entities rather than trying to squeeze them all together
> into
> a single table. Customers, employees, and dependents really have nothing
> in
> common by a business standpoint.
> I know I have seen articles discussing this approach, unfortunately I can
> not seem to track any of them down at the moment.
> "Andrew Hayes" <AndrewHayes@.discussions.microsoft.com> wrote in message
> news:e3aM7drgGHA.4404@.TK2MSFTNGP05.phx.gbl...
> for
> with
> we
> flexibility
> them
> table
> that
> i
> message
> visualisation
> orders/products,
> table
> a
> between
> RELATIONSHIP
> that
>|||Hmm. What about a Person supertype?
I would still have seperate enties for Employee, Candidate, Customer
Contact, Vendor Contact, User and Dependent but the data they share
(firstname, lastname, fullname, sex, address, email, phone) would only be in
Person.