| Author |
Message |
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
   Posted: 12 Oct 2010 at 7:22am |
replace the red mostvisited?field with each of the five fields
replace the blue tablename with your actual table ane
select mostvisited1field as NAME, 5 as AMOUNT
from tablename
UNION
select mostvisited2field as NAME, 4 as AMOUNT
from tablename
UNION
select mostvisited3field as NAME, 3 as AMOUNT
from tablename
UNION
select mostvisited4field as NAME, 2 as AMOUNT
from tablename
UNION
select mostvisited5field as NAME, 1 as AMOUNT
from tablename
|
 IP Logged |
|
neha_patel
Groupie
Joined: 06 Oct 2010
Location: United States
Online Status: Offline
Posts: 44
|
Posted: 12 Oct 2010 at 7:51am |
|
i got this but where i have to write this???
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 Oct 2010 at 8:14am |
in the dtabse, database expert
connect to your db (probably an ODBC connection
just under the naem of your connection and aboce the DB name is an 'Add Command' option.
Double click on it and enter your SQL there.
|
|
IP Logged |
|
neha_patel
Groupie
Joined: 06 Oct 2010
Location: United States
Online Status: Offline
Posts: 44
|
Posted: 12 Oct 2010 at 8:44am |
hey thanks i did created that.... can you please tell me how to use that for my problem now???
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 Oct 2010 at 9:24am |
group on NAME
insert a summary as a SUM of AMOUNT at the Name Group LEvel
SUM({AMOUNT},{NAME})
Use Group Sort Expert for TOp N sorting
Sort Type is TOP N
where N is wahtever you want
based on SUM of {AMOUNT}
|
|
IP Logged |
|
neha_patel
Groupie
Joined: 06 Oct 2010
Location: United States
Online Status: Offline
Posts: 44
|
Posted: 12 Oct 2010 at 9:46am |
|
but problem is i am using from date, to date and Site_id as a parameter so is thr any way where i can use this parameter in this Crystal Report Command???
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 Oct 2010 at 10:53am |
add the date field from the to your command and run the paras against it
select mostvisited1field as NAME, 5 as AMOUNT, datefield as DATE
from tablename
UNION
select mostvisited2field as NAME, 4 as AMOUNT, datefield as DATE
from tablename
UNION
...
|
|
IP Logged |
|
neha_patel
Groupie
Joined: 06 Oct 2010
Location: United States
Online Status: Offline
Posts: 44
|
Posted: 13 Oct 2010 at 2:05am |
|
but my site name is from diffrent table.......so i have to pass inner join for that as site id???? siteid is a primary key in siteInfo table and siteid is a foreign key in visitorInfo table...... so now how to pass sitename???
|
|
IP Logged |
|
neha_patel
Groupie
Joined: 06 Oct 2010
Location: United States
Online Status: Offline
Posts: 44
|
Posted: 13 Oct 2010 at 3:29am |
i did this whole thing..... but my problem is i have to display this all thing in one group only......i mean..... in this same report i did groupping on siteName as given date.... and down thr in one box i have to show this site info and right side of this box i have to show other fields total and down of this portion i have to show other information...... i created one more group as you told me and in that this problem is solved but how can i do this all in same group.....???? please help me
|
|
IP Logged |
|
neha_patel
Groupie
Joined: 06 Oct 2010
Location: United States
Online Status: Offline
Posts: 44
|
Posted: 13 Oct 2010 at 4:46am |
|
is thr any way where i can store this value in variables and display that in my second group???
|
|
IP Logged |
|
|
|
