Crystal report problem

Hi ..I have a bit of a problem.
I want my crystal report to display the information of a specific person (e.g. student, like when you print a student's academic record). I want it to display the information of a specific person instead of taking all the records that are in the database.
What I do is that, I search for the person in my windows application (using their surname) and I want to transfer the information to the crystal report viewer...

Like this:

Student No Surname Initials Class
2365565 Moloi J Grade 1 A

Please help

By da way I'm using Visual studio 2003use record selection formula
or
pass parameter to report to use in report query

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
>
>

Cross Tab Missing Column

Hi,

Have a bit of a strange problem here.

Am working with a colleague on a system which generates "simple" tables and cross tables, both using the same SQL views.

The "simple" tables show all the data correctly... When we come to the cross tabs however, something very weird happens. The x axis is for the section in the factory - for 14 out of the 15 sections the data appears perfectly, but for one column we get nothing, no data, no column heading, nothing. Looking at the info pulled back from the SQL views all the info is there as well.

If we change the data to point to a different section code, it quite happily appears on the report...

Anyone had anything similar, or any ideas as to what could be happening.

I have googled this and come up with nothing, have asked on another forum with no response - am rapidly pulling out what little hair I have left!Problem Solved...

Spelling error in the Select string....

Sorry !!!