how to format this layout?

hi,
I have to incorporate a subreport into my main report. if my main report shows state, county, city and my subreport shows county and school name. how can I show just the schools that are in that county.
my question is how can I apply a condition to show schools in the subreport where county name (subreport ) = county name (main report) ?

attached is an image of the layout.

I know it's probably easy but I'm new to Crystal.

thank youYou can link the subreport with the main report. Right-click on sub-report and select 'Change Sub-report links' option. In the Link window,select the 'County Name' field from the Main report. Select the checkbox 'Select Data in Subreport based on field'.
Hope it helps !!!

Rashmi|||yup that was it .thank you very much

how to fixed the size of the report and landscape view state?

Good day.

How to make or fixed the size of the templates? and how to make the report in landscape mode? i have make a layout in the landscape mode but the priblem in print preview it is not in the landscape mode.

thanks your help is greatly appreciated

Check the width and height property of the "Report" and "Page". Both should be proper, for your report to print (preview) in landscape.|||

thanks rajeeb

but there's a way to make it fixed landscape view. Coz when previewing in the Web form its ok coz the layout of the report is in the landscape but when you export it example in the PDF file it will not set in landscape mode.

thanks

|||

If your widht and height property have proper values and you can view the report in landscape mode, then the export to pdf will also not be a problem.

I have used it, no issues, though I export to PDF using Render method of the ReportViewer control, which is same as what you are trying to do.

How to find 1st Maximum & 2nd maximum No. in a table?

Hi

I have table like this

State Version

CA 19

CA 8

CA 15

CA 6

MA 12

MA 7

MA 10

MA 5

Like this I have 50 states with different Version No.s (Version No.s are unique)

I want to display output like this(in SQL Server 2000)

State 1^st Maximum 2^nd Maximum

CA 19 15

MA 12 10

Please advice

Thanks

Here you go..

Code Snippet

Create Table #data (

[State] varchar(3) ,

[Version] int

);

Insert Into #data Values('CA','19');

Insert Into #data Values('CA','8');

Insert Into #data Values('CA','15');

Insert Into #data Values('CA','6');

Insert Into #data Values('MA','12');

Insert Into #data Values('MA','7');

Insert Into #data Values('MA','10');

Insert Into #data Values('MA','5');

Code Snippet

--Only on SQL Server 2005

;With CTE

as

(

Select *, Row_number() Over(Partition By [State] Order By [Version] Desc) RowId From #data

)

Select

[State]

,Max(Case When RowId=1 Then [Version] End) [1st Max]

,Max(Case When RowId=2 Then [Version] End) [2nd Max]

From

CTE

Group By

[State]

Code Snippet

--or

--Only on SQL Server 2005

;With CTE

as

(

Select *, Row_number() Over(Partition By [State] Order By [Version] Desc) RowId From #data

)

Select

[State]

,[1] as [1st Max]

,[2] as [2nd Max]

From

CTE

Pivot

(

Max([Version])

For RowId in ([1],[2])

) as PVT

Code Snippet

--Any SQL Server version

Select

[State]

,Max(Case When RowId=1 Then [Version] End) [1st Max]

,Max(Case When RowId=2 Then [Version] End) [2nd Max]

From

(

Select

[State]

,[Version]

,(Select Count(*) From #data Sub Where Sub.[Version] >= Main.[Version]

And Sub.[State] = Main.[State]) RowId

From

#data Main

) as data

Group By

[State]

|||

wow!!!!!!!

Thanks a lot, Sekar

I got my answer from your nice queries.

Once again Thanks

With Regards

VJ

|||

Try:

Code Snippet

createtable dbo.t1 (

[State] char(2)notnull,

Version intnotnull

)

go

insertinto dbo.t1 values('CA', 19)

insertinto dbo.t1 values('CA', 8)

insertinto dbo.t1 values('CA', 15)

insertinto dbo.t1 values('CA', 6)

insertinto dbo.t1 values('MA', 12)

insertinto dbo.t1 values('MA', 7)

insertinto dbo.t1 values('MA', 10)

insertinto dbo.t1 values('MA', 5)

go

select

[State],

max(version)as [1st Maximum],

min(version)as [2nd Maximum]

from

(

select

[state], version

from

dbo.t1 as a

where

version in(

selecttop 2 b.version

from dbo.t1 as b

where b.[state] = a.[state]

orderby b.version desc

)

)as t

groupby

[state]

orderby

[state]

go

-- 2005 - 1

;with cte

as

(

select

[state], version,

row_number()over(partitionby [state] orderby version desc)as rn

from

dbo.t1

)

select

[state],

max(version)as [1st Maximum],

min(version)as [2nd Maximum]

from

cte

where

rn <= 2

groupby

[state]

orderby

[state]

go

-- 2005 - 2

select

a.[state],

max(c.version)as [1st Maximum],

min(c.version)as [2nd Maximum]

from

(

selectdistinct

[state]

from

dbo.t1

)as a

crossapply

(

selecttop 2

b.version

from

dbo.t1 as b

where

b.[state] = a.[state]

orderby

b.version desc

)as c

groupby

a.[state]

orderby

a.[state]

go

droptable dbo.t1

go

AMB