Building Data Report

[giyer555]

Using Data Report I need to build a report
Where DataEnvironment1.command1 has the following sql statement

select a.br_code, b.br_name, a.status, count(*) Total from emp_mast a, branch b where a.br_code = b.br_code
group by a.status, a.br_code, b.br_name

The entries retrieved will be as follows:
Br_code br_name Status Total
X abc Working 20
X abc On Leave 5
X abc Inactive 2
Y pqr Working 15
Y pqr On Leave 3
Y pqr Inactive 0
Z lmn Working 25
Z lmn On Leave 8
Z lmn Inactive 2

I need to display report which should be as follows:

Br_code br_name Working On Leave Inactive
X abc 20 5 2
Y pqr 15 3 0
Z lmn 25 8 2

Total 60 16 4

The field Status has 3 type of entries – Working, On Leave & Inactive

Kindly help

 

[kmfna]

Hey,

I found some really nifty report builder samples at

http://www.freevbcode.com

You may be able to find what you need there, as far as a full blown project example.

good luck
Kevin

 

[giyer555]

Thanx Kmfna

I will try it out

 

[giyer555]

More replies please

 

[VDavid]

giyer555,

I think you can use the sql statement to do it an then send it to the datareport.

From what I can see in

select a.br_code, b.br_name, a.status, count(*) Total from emp_mast a, branch b where a.br_code = b.br_code
group by a.status, a.br_code, b.br_name

you have all the status for every br_code separate, so you can group them by the br_code. Without the data I'm not sure if it will work but it should be something like:

select a.br_code as code, b.br_name, 
(select count(*) from emp_mast where (br_code=code)and (status='Working')) as Working, 
(select count(*) from emp_mast where (br_code=code)and(status='On Leave')) as On_Leave, 
(select count(*) from emp_mast where (br_code=code)and(status='Inactive')) as Inactive 
from emp_mast as a, branch as b 
where (a.br_code=b.br_code)

Again without the data and the structure of the tables it is a little dificult to do the query just in my head, so maybe it is not very accurate, but with it you should figure out the correct answer.

If any doubts tell me,
David.

 

[giyer555]

Thanx VDavid

I will try it out & let u know

 

[giyer555]

Hi!!! VDavid

I tried out the sql given by u but it gives an error
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'code'.

I think in Sql Server the alias cannot be taken as a column

If u can give me ur email id i can mail u the mdf, ldf file for ur reference.

Thanx

 

[VDavid]

I dont have the sql server installed, I'm moving right now, just post the structure of the tables and some data and I'll work with that.

But i see a small detail,

select a.br_code, b.br_name, 
(select count(*) from emp_mast where (br_code=a.br_code)and (status='Working')) as Working, 
(select count(*) from emp_mast where (br_code=a.br_code)and(status='On Leave')) as On_Leave, 
(select count(*) from emp_mast where (br_code=a.br_code)and(status='Inactive')) as Inactive 
from emp_mast as a, branch as b 
where (a.br_code=b.br_code)

Try it out, anyway post the structure to check it.

David.

 

[giyer555]

Thanx VDavid

The query was the one i was looking for except the grouping a.br_code & b.br_name was missed out.
Anyways it worked out as desired. Thanks once again.

giyer555