how to take data from different column in excel
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=7830
Printed Date: 29 Apr 2024 at 10:56pm
Topic: how to take data from different column in excel
Posted By: i-dot
Subject: how to take data from different column in excel
Date 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.
|
Replies:
Posted By: Jyothi Yepuri
Date Posted: 30 Sep 2009 at 5:30pm
|
Is every item has only one price value among all the month columns?
Jyothi |
Posted By: i-dot
Date 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.
|
Posted By: Jyothi Yepuri
Date 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 |
Posted By: i-dot
Date Posted: 30 Sep 2009 at 7:19pm
|
The records is tabled in the excel and the data is inserted manually. |
Posted By: Jyothi Yepuri
Date 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 |
Posted By: i-dot
Date 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?
|
Posted By: Jyothi Yepuri
Date 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 |
Posted By: i-dot
Date Posted: 01 Oct 2009 at 2:43am
|
tq.
your information is very helpful.
 |