Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: how to take data from different column in excel Post Reply Post New Topic
Author Message
i-dot
Newbie
Newbie
Avatar

Joined: 17 Sep 2009
Location: Malaysia
Online Status: Offline
Posts: 14
Quote i-dot Replybullet Topic: how to take data from different column in excel
    Posted: 29 Sep 2009 at 1:10am
Hi,
im now creating a report link to Excel as our database. in the report i'd like to show the price of the item. however the prices of each item are put in columns. for example:
 
Col1       Col2          Co3             Col4           Col5             Col6............
Item       Qty           Price Aug      Price Jul      Price Jun      Price Mei
AA           3               $1                 -                 $1.10          -
BB           6                -                    $5              -                 -
CC           9               -                    -                -                  $2
 
Therefore i need to take item with the last price. for example:
 
AA (qty) * $1 = $3
BB (qty) * $5 =$30
CC (qty) * $2  = $18
 
So, how i can do the condidtion so that system able to check every column in excel and take data from the latest price.
hope anyone can help on this.
Tq.
IP IP Logged
Jyothi Yepuri
Senior Member
Senior Member


Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
Quote Jyothi Yepuri Replybullet Posted: 30 Sep 2009 at 5:30pm
Is every item has only one price value among all the month columns?

Jyothi
IP IP Logged
i-dot
Newbie
Newbie
Avatar

Joined: 17 Sep 2009
Location: Malaysia
Online Status: Offline
Posts: 14
Quote i-dot Replybullet Posted: 30 Sep 2009 at 6:35pm

In the database, each item has many prices because the price data is stored since it been purchased. but in report i would like to take the latest data. For example:

Item           Price(May'09)    Price(Jun'09)    Price(Jul'09)      Price(Aug'09)
aa              $1                         $1.02           -                        $1.05
bb              -                            $0.90           $0.95                -
 
So for each item i  need to take the latest price. from table above;
item aa, the latest price is in Aug'09 which is $1.05,
while for item bb, the latest price is in Column July which is $0.95.
IP IP Logged
Jyothi Yepuri
Senior Member
Senior Member


Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
Quote Jyothi Yepuri Replybullet Posted: 30 Sep 2009 at 6:46pm
How are records stored in database table?
are you using SQL query or Database tables in Crystal report?

Jyothi



IP IP Logged
i-dot
Newbie
Newbie
Avatar

Joined: 17 Sep 2009
Location: Malaysia
Online Status: Offline
Posts: 14
Quote i-dot Replybullet Posted: 30 Sep 2009 at 7:19pm

The records is tabled in the excel and the data is inserted manually.
IP IP Logged
Jyothi Yepuri
Senior Member
Senior Member


Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
Quote Jyothi Yepuri Replybullet Posted: 30 Sep 2009 at 7:36pm
Try like this

Numbervar price;
IF NOT ISNULL(Price May) THEN
price:=Price May
else if not isnull(price jun) then
price:=price jun
else if not isnull(price jul) then
price:=price jul
else if not isnull(price Aug)then
price:=price Aug;
price

check the order of the if..else based on Month to get latest price

HTH,
Jyothi
IP IP Logged
i-dot
Newbie
Newbie
Avatar

Joined: 17 Sep 2009
Location: Malaysia
Online Status: Offline
Posts: 14
Quote i-dot Replybullet Posted: 30 Sep 2009 at 10:04pm
the data will update each month..
so if im using that syntax, i need to modified it every month, right?
IP IP Logged
Jyothi Yepuri
Senior Member
Senior Member


Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
Quote Jyothi Yepuri Replybullet Posted: 30 Sep 2009 at 10:19pm
Yes. You need to update the formula.

As per my knowledge, If we are using Excel as Datasource, excel should always retain its layout( Excel File Name,File location,sheet names, Column names, data types)

you have to refresh the data source every time you add columns to the excel.

HTH,
Jyothi




Edited by Jyothi Yepuri - 30 Sep 2009 at 10:20pm
IP IP Logged
i-dot
Newbie
Newbie
Avatar

Joined: 17 Sep 2009
Location: Malaysia
Online Status: Offline
Posts: 14
Quote i-dot Replybullet Posted: 01 Oct 2009 at 2:43am
tq.
your information is very helpful.Smile
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.