Help with Crystal Reports

Hi, ,am fairly new to crystal reports......pretty much new...

I have a simple report that is to be done......

I need to generate a report....with columns...emp_num,name,ssn

I pull up these columns thru sql server 2000 from Employee table...

The tricky part is here....

The report has to be in such a way that...

1.)First it prompts asking if u have ssn number with u ,if yes enter the ssn no., display the name and emp_num corresponding to that,

2.)IF NO...prompt the user asking for emp_num, if yes ,enter the emp_num ,display the name and ssn corresponding to that...

I've written a stored procedure for this, but this is taking only one value..while connecting to the crystal....

Plz help me on this.......I dont think you can prompt user this way
Are you calling the report from any front end?

Help with complex summary of data please

Hi, I'm fairly new to RS and am looking for pointers to help me with
the following situation.
I have a system that tracks the activity of our warehouse and drivers.
Basically, it logs when an order is entered and when it leaves the
warehouse. There are various types of orders and seven warehouses. I
have a view which gives me all the detail I need including the
TimeToSend of each order (the difference between order time and send
time) all in one record per order. I have to create a report that
summarises all of this data on a daily basis by providing the
following:
At the entire company level and then broken down by each warehouse for
each day:
- #orders of TypeA where TimeToSend < X minutes with a % of Total
orders
- #orders of TypeA where TimeToSend > X and < Y minutes with a % of
Total orders
- #orders of TypeA where TimeToSend >Y minutes with a % of Total
orders
- AVG TimeToSend for the day for TypeA orders
- #orders of TypeB where TimeToSend < X minutes with a % of Total
orders
- #orders of TypeB where TimeToSend > X and < Y minutes with a % of
Total orders
- #orders of TypeB where TimeToSend >Y minutes with a % of Total
orders
- AVG TimeToSend for the day for TypeB orders
I have achieved the above using two datasets and two tables in my
report aligned next to each other and sorted so the warehouses appear
next to each other.
I have been asked to add additional summary data for each warehouse.
Such as:
- Count of orders of TypeC
- Best AVG TimeToSend of all days (i.e. best day ever)
Now, I can get these new data items from more datasets and add more
tables to the report but am I going about this the right way. Ideally,
having more than one dataset per table would be great. Or if I could
link the tables somehow.
Should I look at trying to return all the data in one dataset? Is that
even possible? My sql is fairly good but I don't know how to combine
the disparate data together.
Any suggestions would be appreciated.Hi,
I am not sure whether I have understood you fully, however, I think you may
integrate the data in another new Table first or a temp table?
If you want to use multiple datasets, it is not a easy job as Data Regions
(Table, Matrix, Chart, and List) are bound to a single data set. You can
refer to other data sets but the reference must be wrapped in an aggregate
such as First(Fields!FieldName.Value, "DataSetX").
Your options are to write a single query, using SQL Server's OpenRowset
functionality, that joins the multiple data sources into a single data set.
The other choice is to use multiple side by side tables though this might
introduce formatting issues.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Help with Case Statement

Since I'm fairly new to SQL - I need help making the following into a case
statement:
-- Set Reporting date if the policy is a changed policy
UPDATE STAGE_PHX_FACT_POLICY
SET REPORTING_DATE = CHG_EFF_DATE
FROM STAGE_PHX_FACT_POLICY
WHERE CHG_EFF_DATE IS NOT NULL
-- Set Reporting date if the policy is a termination
UPDATE STAGE_PHX_FACT_POLICY
SET REPORTING_DATE = POL_TERM_DATE
FROM STAGE_PHX_FACT_POLICY
WHERE POL_TERM_DATE IS NOT NULL
-- Set Reporting date if the policy is not changed and not terminated
UPDATE STAGE_PHX_FACT_POLICY
SET REPORTING_DATE = POL_EFF_DATE
FROM STAGE_PHX_FACT_POLICY
WHERE CHG_EFF_DATE IS NULL
AND POL_TERM_DATE IS NULL
-- Set Reporting date if Policy date and time is greater than all of the
above
UPDATE STAGE_PHX_FACT_POLICY
SET REPORTING_DATE = convert(varchar(50),policy_date_time,101
)
FROM STAGE_PHX_FACT_POLICY
WHERE
EXISTS (SELECT NULL FROM STAGE_PHX_FACT_POLICY b
WHERE b.POLICY_NUMBER = STAGE_PHX_FACT_POLICY.POLICY_NUMBER
GROUP BY b.POLICY_NUMBER
HAVING STAGE_PHX_FACT_POLICY.POLICY_DATE_TIME >
STAGE_PHX_FACT_POLICY.REPORTING_DATE)
Thanks in advance for any help!Without table structures & sample data, the following expression is
untested:
CASE WHEN chg_eff_date IS NOT NULL
THEN chg_eff_date
WHEN pol_term_date IS NOT NULL
THEN pol_term_date
WHEN chg_eff_date IS NULL
AND pol_term_date IS NULL
THEN pol_eff_date
WHEN policy_date_time > reporting_date
THEN CONVERT( VARCHAR(50), policy_date_time, 101 )
END
I would also suggest you spend some time on the topic CASE in SQL Server
Books Online and go through the examples.
Anith|||On Fri, 28 Oct 2005 08:35:03 -0700, Patrice wrote:

>Since I'm fairly new to SQL - I need help making the following into a case
>statement:
(snip)
Hi Patrice,
Most of it can be caught in a COALESCE. Only the last requirement calls
for a CASE.
UPDATE Stage_PHX_Fact_Policy
SET Reporting_Date =
CASE
WHEN COALESCE (Chg_Eff_Date, Pol_Term_Date, Pol_Eff_Date)
< Policy_Date_Time
THEN CONVERT(char(8), Policiy_Date_Time, 112)
ELSE COALESCE (Chg_Eff_Date, Pol_Term_Date, Pol_Eff_Date)
The above is untested, and assumes that Policy_Number is the primary key
of your table. If it isn't, or if the query doesn't do what you want,
then see www.aspfaq.com/5006 for the recommended way to explain your
problem here.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help with a very odd query

I am trying to get the distance between every airport in the united states. This is fairly simple to say, but it is a very tough query for me.

I have a table with all airports in the united states (about 20,000 of them) and I have a custom built function that finds the distance between 2 lat / long points. The function works great and is being used on a live site.

I have the following information:
table: dbo.apt
==========================
aptcode: The unique id of every airport
lat: the latitude of every airport
long: the longitude of every airport

the distance is found by doing: dbo.FindDist(lat1, long1, lat2, long2)

This information is relatively easy to get. The probem comes when I try to filter out the duplicate information. This is what I am talking about:

FLL to FXE is 5mi
FXE to FLL is 5mi

I only need one of these rows of information because they are the same.

Another large problem I am facing is that hard drive space is limited on this specific server, so I dont have much room for temporary tables.

PLEASE HELP!!!

Thank you in advance.

PS: I am running SQL Server 2000

Hello,

you should be able to add a where clause

select a.aptcode,b.aptcode, distcalc... FROM apt a

CROSS JOIN apt b

WHERE a.aptcode> b.aptcode

/P

|||Can you please show your sample query? If you are generating the data then you can perform the duplicates elimination.|||This is what I have for the query:

Sample Query

INSERT INTO distance (aptcode1, aptcode2, distance)
SELECT
tab1.aptcode AS aptcode1,
tab2.aptcode AS aptcode2,
dbo.FindDist(tab1.reflat_s,tab1.reflong_s,tab2.reflat_s,tab2.reflong_s) AS distance
FROM
(
(
SELECT
aptcode,
reflat_s,
reflong_s
FROM apt
) tab1
FULL JOIN
(
SELECT
aptcode,
reflat_s,
reflong_s
FROM apt
) tab2
ON 1 = 1
)

|||

Code Snippet

create table #apt (aptcode varchar(5), lat numeric(18,9), long numeric(18,9))

insert into #apt

select 'FLL', 0, 0

union all select 'FXE', 0, 0

union all select 'RDU', 0, 0

union all select 'LAS', 0, 0

select a1.aptcode as orig, a2.aptcode as dest, a1.cksum + a2.cksum as hash, identity(int, 1,1) as row

into #list

from

(select aptcode, checksum(aptcode) as cksum from #apt ) a1

full join

(select aptcode, checksum(aptcode) as cksum from #apt ) a2

on a1.aptcode <> a2.aptcode

select orig, dest

from #list l

inner join

(

select hash, min(row) as row

from #list

group by hash

) h

on l.hash = h.hash

and l.row = h.row

order by orig

|||I have seen people use checksum before, but what exactly is it used for?
|||

It computes a hash value for a row or a given set of columns/fields

|||

Try the following:

SELECT
dbo.apt.AptCode,
Destinations.AptCode,
dbo.FindDist(dbo.apt.lat, dbo.apt.long, Destinations.lat, Destinations.long)
FROM
dbo.apt,
dbo.apt Destinations
WHERE
dbo.apt.AptCode > Destinations.AptCode

Help with "flattening" a table

Hi All,

I have a database that stores some data from two questionnaires. Table One has been created in a fairly straightforward manner. It has the following fields:

ID, variable1, variable2, variable3, ..., variablen

Table Two is a bit more complicated. I'm guessing whoever created it wanted to do things a bit more "efficiently" and it has the following fields.

ID, variable, response.

If I wanted to, say, extract variables 2, 3 and 5 from Table One I could write:

SELECT ID, variable2, variable3, variable 5 FROM TableOne

I don't know how to do a similar thing with Table 2 using only one statement. ie, I can only think of doing:

SELECT ID, Response FROM Table2 WHERE variable = 2

SELECT ID, Response FROM Table2 WHERE variable = 3

SELECT ID, Response FROM Table2 WHERE variable = 5

This isn't much good to me as I need the data from Table Two in a single table. Once I've done that, I need to join it to Table One. Ordinarily I'd know how to do that, but I don't know how to do given the format of Table Two.

So... I was wondering if anyone could offer advice on how to "flatten" Table Two?

I would like to be able to do something like:

SELECT t1.ID, t1.variable1, t1.variable2, t2.variable6, t2.variable7

FROM TableOne t1 JOIN TableTwo t2 ON t1.ID = t2.ID

Any help would be much appreciated!

To tell the truth I dont understand the question.

What is the meaning of your tables? What is variable? What is ID? is that a unique key for one of tables? Maybe for both?
As I understood from your question in second table if, say, variable = 3, then the response field womehow relates to the value of variable3 in Table1? Can't you write
select ID, Response from TABLE2 where variable in (2, 3, 5) instead of

"SELECT ID, Response FROM Table2 WHERE variable = 2

SELECT ID, Response FROM Table2 WHERE variable = 3

SELECT ID, Response FROM Table2 WHERE variable =5"?

And, are you considering any table schema reorganization? Because if the way I understood your table schemas is right, then you should consider some other way to store data-storing variable number in one table as a column name and as a value in the other seems rather illogical to me.