its urgent
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=11359
Printed Date: 28 Apr 2024 at 2:03pm
Topic: its urgent
Posted By: neha_patel
Subject: its urgent
Date Posted: 07 Oct 2010 at 7:12am
|
hey can please some one help me........
my problem is i have one field in my sql db that is visitor... and i have date field also thr..... i have to take two date ranges and have to group by data with site name..... and according to date have to count how many visitors was thr on tht site during that dates.....
i am doing that with formula....
if({Visitor_info.Date}in({?CFromDate}to{?CTodate})) then
Sum ({Visitor_info.DailyUnique}, {Site_Info.Site_Name}) for one date and same foe second only parameter is diffrent..... but its giving me total number.....
please help me some expert i have to finish this today evening.......
|
Replies:
Posted By: DBlank
Date Posted: 07 Oct 2010 at 8:05am
|
you cannot do condtional sums
use a Running total.
field to sumamrize={Visitor_info.DailyUnique}
type=SUM
evaluate=use a formula
{Visitor_info.Date}in({?CFromDate}to{?CTodate}
reset=on a group ({Site_Info.Site_Name})
place in {Site_Info.Site_Name} footer
|
Posted By: neha_patel
Date Posted: 07 Oct 2010 at 8:32am
|
thanks for your reply..... i did try this but in running total its adding that value to next record.... see my problem is....
i have couple of site name and site id in my one table and have visitor info in second tabel.....
i have to create report and give two date ranges
for both range i have to fetch data from table and have to do group by site name and for that site i have to display data in two diffrent columns and at last have to display total of this two field.... and in other fields i have to do comparission between this two fields..... so may be this running total will not work..... please help me....... i really appriciate it.........
|
Posted By: neha_patel
Date Posted: 07 Oct 2010 at 8:50am
|
i did this with formula as i told before
if({Visitor_info.Date}in({?CFromDate}to{?CTodate})) then
Sum ({Visitor_info.DailyUnique}, {Site_Info.Site_Name}) its giving me result but the thing is when date is in range its not checking all dates for this site.... once date is match its calculating all visitor for that site.......
i want total withing given range only......
please help me some one time is running.....
|
Posted By: DBlank
Date Posted: 07 Oct 2010 at 9:02am
|
i do not understand your design.
as i said before you cannot do condtional sums.
Likely multiple RTS will work.
Originally posted by neha_patel i have to do group by site name and for that site i have to display data in two diffrent columns create a group on the sitename
create 2 running totals to display each summation based on your 2 sets of params
you can also create a formula field to compare these summaries at the group level
Originally posted by neha_patel and at last have to display total of this two field....
create 2 more RTS that are the exact same as your others (group totals)but they are not set to reset on the group (same thing as adding all the group level rts togther). |
Posted By: neha_patel
Date Posted: 07 Oct 2010 at 9:14am
|
what i am telling you is running total is giving me like this
if i have 4 site macy's,khols,kmart,target and if i am displaying total visitor's for each site within date thn.....
macy's 100 this is correct
khols 400+macy's100 means 500 which is wrong
kmart 300+500 from above.... which is wrong.....
target 100+800 from above
its giving me like this.....
|
Posted By: DBlank
Date Posted: 07 Oct 2010 at 9:23am
|
Did you group on the store name?
Did you set the Running total Reset value to = On cange opf Group (Store name)?
|
Posted By: neha_patel
Date Posted: 07 Oct 2010 at 9:24am
| I dnt knw how to do that can you please tell me??? |
Posted By: DBlank
Date Posted: 07 Oct 2010 at 9:25am
| which part? |
Posted By: neha_patel
Date Posted: 07 Oct 2010 at 9:26am
| Did you set the Running total Reset value to = On cange opf Group (Store name)? |
Posted By: neha_patel
Date Posted: 07 Oct 2010 at 9:29am
| hey i got it...... thanks a lot.... i relly appriciate it.... |
Posted By: neha_patel
Date Posted: 07 Oct 2010 at 9:30am
| hey can you help me with one more problem if you can..... that report i have to submit by tomorrow evening..... |
Posted By: DBlank
Date Posted: 07 Oct 2010 at 9:30am
|
Are you really using a Crystal Running Total (RT) or are you using a variable formula? I was suggesting to use a RT.
Right Click on RT and select NEW
Enter a Name
field to summarize={Visitor_info.DailyUnique}
type of summary=SUM
evaluate=use a formula
{Visitor_info.Date}in({?CFromDate}to{?CTodate}
reset=on a group pick the group name from the pick list that is = to{Site_Info.Site_Name} |
Posted By: neha_patel
Date Posted: 07 Oct 2010 at 9:31am
Thank you thank you thank you........ you knw i was working on this from last day
|
Posted By: DBlank
Date Posted: 07 Oct 2010 at 9:31am
| what is the other issue? |
Posted By: neha_patel
Date Posted: 07 Oct 2010 at 9:36am
|
oh god that issu is very big for me......... my report is almost over but bcoz of this one field i m stuck......
i have 5 fields in my table.....for each site......
that field is for..... it store's 5 most visited site... for each site... every day.....
in my second report i m passing date range and site id..... according to that i have to display all data...... which i already did.... but i have to show most visited 5 pages from that date range......
|
Posted By: DBlank
Date Posted: 07 Oct 2010 at 9:41am
| please explain 'most visited 5 pages from that date range' |
Posted By: neha_patel
Date Posted: 07 Oct 2010 at 9:46am
|
i am passing parameter as date
user will give date like 01/01/2010 to 09/09/2010
so for this time i have to display data for user given site id and
i have to find 5 most visited pages from db's 5 fields.....
|
Posted By: DBlank
Date Posted: 07 Oct 2010 at 9:53am
|
i don't know your data...
what is a 'visited page' and what is 'db's 5 fields'?
how are you counting it or summing it?
is this the RT result?
is it in the main report?
|
Posted By: neha_patel
Date Posted: 07 Oct 2010 at 11:32am
|
see i have data base(report_info) in that i have 3 table one for which type of site is it. 2nd is site info. and 3rd is main table visitor information and in this table i have all information about visitor's with site id....as foreign key.... in this table i have lots of fields but 5 of them are most visited1,most visited 2, most visited 3,most visited 4, most visited 5 in this field site names are thr..... like which 5 site's are most visited today.... like that for every day......
in my report i have to pass site id....and date range as parameter...... and with this site id i have to do group by data...... but i have to find 5 most visited site from table visitor information table's most visited1,2,3,4,5 field......
hope i cleared my self....
|
Posted By: DBlank
Date Posted: 07 Oct 2010 at 11:42am
| you won't be able to do a top n or grouping in the main report from a sub report... |
Posted By: neha_patel
Date Posted: 07 Oct 2010 at 11:51am
|
its an independent report..... all other field i did..... but for this i have to use some logic that i knw..... like most visited 1 ==>5 point, most visited 2==>4 point, most visited 3==>3,most visited 4==>2, most visited 5==>1 thr are lots of site name..... every 5 site name will be thr some of them are common and some of them are diffrent. so when ever any site repeate thn have to add that much points to already exist point...... and at the end have to find 5 most high pointed site as most visited 5 sites and have to display..... but i am new to crystal report..... so dnt knw how to do that???
please help me if you can.....
thanks
|
Posted By: DBlank
Date Posted: 07 Oct 2010 at 12:07pm
|
I think I see Each column stores a name and you are assigning a value of 5 to 1.
You need to evaluate all of the sites that fall into each of the 5 columns and give the top 5 based on the sum of your assigned 5 to 1 value.
That is a nightmare in crystal.
how many data rows are you talking about?
If you cannot scrub your data outside of crystal (a view or stored procedure) you could do a crystal command object and uniono the table onto itself 5 times like so:
grab column 1 and call it 'name' make another column called 'score' as a value of 5
union it to
grab column 2 and call it 'name' make another column called 'score' as a value of 4
union it to
grab column 3 and call it 'name' make another column called 'score' as a value of 3
etc.
then you can easily do a grouping on the name and summing on the score and get your top 5 from that.
|
Posted By: neha_patel
Date Posted: 08 Oct 2010 at 2:25am
|
no no.... i m telling you..... i have 5 field in my table name
"Most visited 1"
"Most visited 2"
"Most visited 3"
"Most visited 4"
"Most visited 5"
and this field stores.... most visited 5 sites where "Most visited 1" is highest... and so on......
when i will group by site name...... thn i am doing all other fields total.... but for this 5 field i have to find...... most visited 5 site....... and have to display that in reports..........
and i am telling you what i am thinking to do for this......
i am thinking if thr is any way to do all this field combine and rank them 1 to 5 for all that 5 field.... like
"Most visited 1" 5 points..
"Most visited 2" 4 points and so on...... and i will find that same site again thn i will add that points to my current point total..... and if tht site name is not in list thn i will add tht field with its point in list..... so i can check for that site name next time......
this is only what i think.... i dnt knw this i can do in crystal report or not..... and i dnt knw what is other way to do this thing in crystal report..... please help me........ |
Posted By: DBlank
Date Posted: 11 Oct 2010 at 4:49am
|
Originally posted by neha_patel
"Most visited 1"
"Most visited 2"
"Most visited 3"
"Most visited 4"
"Most visited 5"
These are all on one data row correct?
This makes getting the top # a nightmare at best.
I am suggesting you alter it so that each of these are on one row.
Then you can group on that row and get your sums easily. |
Posted By: neha_patel
Date Posted: 11 Oct 2010 at 4:55am
| but i can not alter that bcoz my comp. provides me this database and they are using this as main data base.... |
Posted By: neha_patel
Date Posted: 11 Oct 2010 at 4:57am
| hey that day you gave me that running total formula's answer..... i created that running total.... and i am using that running total in my formula.... and i genrated one field..... now i want to do total of that formula field.... at the end of each group...... can you tell me how to do that..... thanks |
Posted By: DBlank
Date Posted: 11 Oct 2010 at 5:01am
|
Sicne you cannot alter the DB use a crystal COMMAND to do the UNION approach as i suggested earlier.
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 |
Posted By: neha_patel
Date Posted: 12 Oct 2010 at 6:43am
| thanks for your help i will try this...... |
Posted By: neha_patel
Date Posted: 12 Oct 2010 at 7:08am
|
hey i have to write this in Sql Command..... sorry for this type of questions but i am new to crystal report...... please explain me in brief.....how to work with my this problem.....
thank you....
|
Posted By: DBlank
Date 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
|
Posted By: neha_patel
Date Posted: 12 Oct 2010 at 7:51am
| i got this but where i have to write this??? |
Posted By: DBlank
Date 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.
|
Posted By: neha_patel
Date 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??? |
Posted By: DBlank
Date 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}
|
Posted By: neha_patel
Date 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??? |
Posted By: DBlank
Date 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
... |
Posted By: neha_patel
Date 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??? |
Posted By: neha_patel
Date 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 |
Posted By: neha_patel
Date 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??? |
Posted By: DBlank
Date Posted: 13 Oct 2010 at 4:48am
| i don't understand your question /design |
Posted By: neha_patel
Date Posted: 13 Oct 2010 at 4:55am
|
see i have already one group..... where i have lots of other fields(Total of other fields and all that)....... and you told me to create one more group and use group sort expert...... i did that and that works good,,,,,, but this group's value i have to show in my already exists group.... bcoz i already have reports design on papper and thr this 5 mostvisited site name is on below my already exist fields.....(left side on right side i have to add some more fields and down of this two fields thr is some more fields and running total .......
so now my question is can i use this group(Which you told me and i created) value in my already exist group????
can i store this value in any variable and display that in already exists group???
please please help me......
with ur help i almost solved this problem..... please make it 100%.....
|
Posted By: neha_patel
Date Posted: 13 Oct 2010 at 5:07am
| hey please help me............. |
Posted By: DBlank
Date Posted: 13 Oct 2010 at 5:33am
| i thought you were using this in a subreport? I really cannot visualize what you are trying to do here... |
Posted By: neha_patel
Date Posted: 13 Oct 2010 at 5:43am
|
see my report design is this
----------------------------------------------------------------------------------------------
visitorInfo DailyVisitor Totalvisit Pages PgsperVisit
And thn Running Total (SiteName Group by) AND DARE PARAMETER RANGE
----------------------------------------------------------------------------------------------
here
Most visited1 Total Email Contact
mostvisited2 running total
mostvisited3
mostvisited4 Total email Contact
mostvisited5 Running Total
Mobile Visit
Runnning Total
----------------------------------------------------------------------------------------------
Date RAnge Total visitor Demand Conversion cart
----------------------------------------------------------------------------------------------
and Running total For this all Field as Above(SiteName Group by and Date range Parameter....
|
Posted By: neha_patel
Date Posted: 13 Oct 2010 at 5:44am
| so i want that most visited 5 site in my inbuilt group....... so how can i do that??? |