Coast Line
asked on
CF Grid Data
I have a query returning some columns as:
colum1,colum2,column3,colu mn4,column 5 - These columns are coming from DB.
Now i want to o some extra processing and display their results in the cfgrid as:
colum1,colum2,column3,colu mn4,column 5, column6, column7, column8
column6, column7, column8 - should be calculated as
column1 - column2 as column 6
column3 - column4 as column 7
column1 - column5 as column 8
ZCurrently i am oing in cfquery, but i want to get the data which comes from tables as and do calculation on coldfusion side and return the data using
QueryConvertForGrid(), because my query will return data using this tag in cfgrid
colum1,colum2,column3,colu
Now i want to o some extra processing and display their results in the cfgrid as:
colum1,colum2,column3,colu
column6, column7, column8 - should be calculated as
column1 - column2 as column 6
column3 - column4 as column 7
column1 - column5 as column 8
ZCurrently i am oing in cfquery, but i want to get the data which comes from tables as and do calculation on coldfusion side and return the data using
QueryConvertForGrid(), because my query will return data using this tag in cfgrid
just do it in sql its quicker and easier.
select
colum1,
colum2,
column3,
column4,
column5,
(colum1 - colum2) as colum 6,
(colum3 - colum4) as colum 7,
(colum1 - colum5) as colum 8
where etc etc
This will display the extra collums that you need and get your database to do the calculations.
select
colum1,
colum2,
column3,
column4,
column5,
(colum1 - colum2) as colum 6,
(colum3 - colum4) as colum 7,
(colum1 - colum5) as colum 8
where etc etc
This will display the extra collums that you need and get your database to do the calculations.
ASKER
right i know, i am currently doing in sql. But the issue lies in performance and when i export all the data to excel it throws me java heap error
so bring data which sql gives, and store using a loop in variables and store the data which is converted also in variables and pass in cfgrid. that is my question!
aslo i calculate the colum 7 as percentage sobring from sql as %tage will be cumbersome and suppose if thereis a diviosn by zero. That will be an issue
so bring data which sql gives, and store using a loop in variables and store the data which is converted also in variables and pass in cfgrid. that is my question!
aslo i calculate the colum 7 as percentage sobring from sql as %tage will be cumbersome and suppose if thereis a diviosn by zero. That will be an issue
If you dont want to do it using sql. you will have to use a cfc and ajax to get the data into the grid.
see tutorial
http://coldfusionhell.blogspot.com/2008/08/cfgrid-and-cfc-binding-how-to.html
see tutorial
http://coldfusionhell.blogspot.com/2008/08/cfgrid-and-cfc-binding-how-to.html
ASKER
nopes. i rad it before, its not working as such
you get java heap error while retrieving the data from database or while generating the excel?
If it is while generating excel, then try increasing JVM memory....
If it is while generating excel, then try increasing JVM memory....
thats only a basic example.
It is the only way you can mess around with your query before you submit into the grid.
I spent days with grids and queries. I ended up not using them and outputting into standard html tables and setting up pagination because i want to play with the data when i was outputting it line by line.
It is the only way you can mess around with your query before you submit into the grid.
I spent days with grids and queries. I ended up not using them and outputting into standard html tables and setting up pagination because i want to play with the data when i was outputting it line by line.
are you using cf9 cfspreadsheet or any other third party excel generators?..
one best you can use is jexcel library http://jexcelapi.sourceforge.net/
one best you can use is jexcel library http://jexcelapi.sourceforge.net/
ASKER
no i am using my own Custom tag
no CF9 Spreadsheet
no CF9 Spreadsheet
In one of my previous project I was facing same problem, then we used this jexcel library..
pass the record set from coldfusion as a hashmap to the java function that creates the excel. that solved my issue...
You can make templates and access that templates to create the download.
BR-Sudhindra-www.clicksperday.com
pass the record set from coldfusion as a hashmap to the java function that creates the excel. that solved my issue...
You can make templates and access that templates to create the download.
BR-Sudhindra-www.clicksperday.com
you will have to create a new query and then pass that back to your grid. but if u are getting performance issues in SQL i doubt CF will do a much better job. i'm guessing ur data size is massive. i'll take u through the process anywaz
<cfquery name="orgQry">
SELECT colum1,colum2,column3,colu mn4,column 5
</cfquery>
<cfset myNewQry = queryNew("colum1,colum2,co lumn3,colu mn4,column 5,column6, column7,co lumn8")>
<cfloop query="orgQry">
<cfset queryAddRow(myNewQry)>
<cfset querySetCell(myNewQry,"col umn1",colu mn1)>
<cfset querySetCell(myNewQry,"col umn2",colu mn2)>
<cfset querySetCell(myNewQry,"col umn3",colu mn3)>
<cfset querySetCell(myNewQry,"col umn4",colu mn4)>
<cfset querySetCell(myNewQry,"col umn5",colu mn5)>
<cfset querySetCell(myNewQry,"col umn6",colu mn1-column 2)>
<cfset querySetCell(myNewQry,"col umn7",colu mn3-column 4)>
<cfset querySetCell(myNewQry,"col umn8",colu mn1-column 5)>
</cfloop>
//pass back ur new query now to your grid
<cfreturn QueryConvertForGrid(myNewQ ry)>
if your page times out try increasing the timeout for the page at the top like so,
<cfsetting requesttimeout="500">
<cfquery name="orgQry">
SELECT colum1,colum2,column3,colu
</cfquery>
<cfset myNewQry = queryNew("colum1,colum2,co
<cfloop query="orgQry">
<cfset queryAddRow(myNewQry)>
<cfset querySetCell(myNewQry,"col
<cfset querySetCell(myNewQry,"col
<cfset querySetCell(myNewQry,"col
<cfset querySetCell(myNewQry,"col
<cfset querySetCell(myNewQry,"col
<cfset querySetCell(myNewQry,"col
<cfset querySetCell(myNewQry,"col
<cfset querySetCell(myNewQry,"col
</cfloop>
//pass back ur new query now to your grid
<cfreturn QueryConvertForGrid(myNewQ
if your page times out try increasing the timeout for the page at the top like so,
<cfsetting requesttimeout="500">
ASKER
That's Great..
Also i had to perform a calculation on my Last Column as a-b/*100 - Gives result in Percentage..
Can you Show me how to do that
Also i had to perform a calculation on my Last Column as a-b/*100 - Gives result in Percentage..
Can you Show me how to do that
ASKER
in the above if there is a division by zero or negative number, it should not through an error and if value comes as decimal, it should show upto 2 decimal points
sure mate
last column
<cfset querySetCell(myNewQry,"col
adding the '%' symbol at the end will make it look good but it will convert it to a string value , so u may not be able to sort real time properly in the grid, leave it out and it'll work
ASKER
so the above reads as if Division by zero, Display 0% else display 22.33% right
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Pal, Are you Aussie
yep , indian australian
ASKER
see figured out when you said "Mate"
Cheers
Cheers
haha, nicely played
select c1, c2, c3, c4, c5, c1-c2 as c6, c3-c4 as c7, c1-c5 as c8
from......
and pass this query results to grid...