SQL Server and VB Crystal Report Help Needed Please

[anarchy-rabbit]

Hi

I'm busy with my final year project and now I'm having trouble generating a crystal report, I have the following "Client" table:

CREATE TABLE Client
(
Client_ID int PRIMARY KEY Identity(1000,1) NOT NULL,
Client_Name varchar(20),
Client_Surname varchar (30),
Client_Tel_h varchar(10),
Client_Tel_c varchar (10),
Client_Pass varchar(5),
Client_Gender varchar(1),
Client_IDNo varchar(13),
Client_Blacklisted bit,
AccOpen_Date datetime,
Client_StreetName varchar (40), 
Client_AreaCode varchar(5),
Client_RemainingBalance float(10),
Title_ID int references Title(Title_ID),
Suburb_ID int references Suburb(Suburb_ID),
City_ID int references City(City_ID),
)
GO

Now I have the following Views and Stored Procedure:

create view Gender_M
as
select Count(Client_Gender) as Males
from client
where Client_Gender = 'M'
go

Create View Gender_F
as
select count(Client_gender) as Females
from client
where client_gender = 'F'
go

---------------------------------------------------------
Create Procedure SP_DemographicReportGender_Sel
(
@StartDate as datetime,
@EndDate as datetime
)
as
select B.Females, A.Males
from Gender_M as A, Gender_F as B, Client as C
where c.AccOpen_Date between @StartDate and @EndDate
GO

So what I'm trying is creating 2 views, one for the males count and one for the females count, then I'm using a stored procedure that retrieves the count from the views in 2 seperate columns.

However, when I try to create a crystal report, with a pie chart, it only shows either the males or only the females in the pie chart.

Does anyone perhaps have any ideas o fixing this?

Thanks

 

[Osiris85]

Hi

I'm busy with my final year project and now I'm having trouble generating a crystal report, I have the following "Client" table:

CREATE TABLE Client
(
Client_ID int PRIMARY KEY Identity(1000,1) NOT NULL,
Client_Name varchar(20),
Client_Surname varchar (30),
Client_Tel_h varchar(10),
Client_Tel_c varchar (10),
Client_Pass varchar(5),
Client_Gender varchar(1),
Client_IDNo varchar(13),
Client_Blacklisted bit,
AccOpen_Date datetime,
Client_StreetName varchar (40), 
Client_AreaCode varchar(5),
Client_RemainingBalance float(10),
Title_ID int references Title(Title_ID),
Suburb_ID int references Suburb(Suburb_ID),
City_ID int references City(City_ID),
)
GO

Now I have the following Views and Stored Procedure:

create view Gender_M
as
select Count(Client_Gender) as Males
from client
where Client_Gender = 'M'
go

Create View Gender_F
as
select count(Client_gender) as Females
from client
where client_gender = 'F'
go

---------------------------------------------------------
Create Procedure SP_DemographicReportGender_Sel
(
@StartDate as datetime,
@EndDate as datetime
)
as
select B.Females, A.Males
from Gender_M as A, Gender_F as B, Client as C
where c.AccOpen_Date between @StartDate and @EndDate
GO

So what I'm trying is creating 2 views, one for the males count and one for the females count, then I'm using a stored procedure that retrieves the count from the views in 2 seperate columns.

However, when I try to create a crystal report, with a pie chart, it only shows either the males or only the females in the pie chart.

Does anyone perhaps have any ideas o fixing this?

Thanks

Why not try doing an INNER JOIN at the SELECT statement in the SP? See what happens.

I see in the SP code that you are referencing the Client column with C ( on its own ) yet the client column is in both the male and female views? Me thinks SQL gets confused when you run the SELECT in the SP so it tries to determine which Client data you want.

Give this a try:

select B.Females, A.Males
from Gender_M as A, Gender_F as B
INNER JOIN A.Client = B.Client
where A.AccOpen_Date between @StartDate and @EndDate

 

[anarchy-rabbit]

The inner join seems to give an error. Shouldn't the join have a common field for A.Client and B.Client?

 

[Osiris85]

The inner join seems to give an error. Shouldn't the join have a common field for A.Client and B.Client?

ah right, I didn't see the CREATE TABLE at the top

What if instead of using a SELECT statement which selects from 3 different sources, try to isolate the data you want from a single source? Like a temp table or another view with the data you want in separate columns?

 

[anarchy-rabbit]

ah right, I didn't see the CREATE TABLE at the top

What if instead of using a SELECT statement which selects from 3 different sources, try to isolate the data you want from a single source? Like a temp table or another view with the data you want in separate columns?

No worries

I'm not quite sure what you mean? I think this is what I want to do if I'm understanding you correctley.

Is there maybe a way to get the count of the males and the females in the same column of the view? Cuz I think I might be able to create the report from that.

Otherwise maybe there is something I'm missing in the crystal report wizard which may make this this earier...

 

[Osiris85]

No worries

I'm not quite sure what you mean? I think this is what I want to do if I'm understanding you correctley.

Is there maybe a way to get the count of the males and the females in the same column of the view? Cuz I think I might be able to create the report from that.

Otherwise maybe there is something I'm missing in the crystal report wizard which may make this this earier...

That SELECT statement within the SP, does it return the correct results as you want it from Management Studio?

 

[anarchy-rabbit]

It's ok, I got it working. I just added another table which has GenderName and GenderCount.

Then I count them in VB and add the counts to the table. Probably not the correct way to do it but functionality is what counts for our marks

Thanks for the help

 

[Osiris85]

It's ok, I got it working. I just added another table which has GenderName and GenderCount.

Then I count them in VB and add the counts to the table. Probably not the correct way to do it but functionality is what counts for our marks

Thanks for the help

Thats great! lol that is what I meant by instead of selecting from 3 different sources, make it 1 source

My philosophy on SQL is: It it works, then don't f**k with it