Solved

CF Grid Data

Posted on 2011-09-27
20
522 Views
Last Modified: 2012-05-12
I have a query returning some columns as:

colum1,colum2,column3,column4,column5 - 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,column4,column5, 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
0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 4
  • +1
20 Comments
 
LVL 13

Expert Comment

by:ansudhindra
ID: 36708568
Easily you can do it from SQL itself...

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...
0
 
LVL 4

Expert Comment

by:mustang83
ID: 36708578
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.

0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 36708655
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
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 4

Expert Comment

by:mustang83
ID: 36708872
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
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 36708912
nopes. i rad it before, its not working as such
0
 
LVL 13

Expert Comment

by:ansudhindra
ID: 36708989
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....
0
 
LVL 4

Expert Comment

by:mustang83
ID: 36709007
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.

0
 
LVL 13

Expert Comment

by:ansudhindra
ID: 36709028
are you using cf9 cfspreadsheet or any other third party excel generators?..
one best you can use is jexcel library  http://jexcelapi.sourceforge.net/
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 36709344
no i am using my own Custom tag
no CF9 Spreadsheet
0
 
LVL 13

Expert Comment

by:ansudhindra
ID: 36709405
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
0
 
LVL 3

Expert Comment

by:devilJinKazama
ID: 36713860
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,column4,column5
</cfquery>

<cfset myNewQry = queryNew("colum1,colum2,column3,column4,column5,column6,column7,column8")>

<cfloop query="orgQry">
    <cfset queryAddRow(myNewQry)>
    <cfset querySetCell(myNewQry,"column1",column1)>
    <cfset querySetCell(myNewQry,"column2",column2)>
    <cfset querySetCell(myNewQry,"column3",column3)>
    <cfset querySetCell(myNewQry,"column4",column4)>
    <cfset querySetCell(myNewQry,"column5",column5)>
    <cfset querySetCell(myNewQry,"column6",column1-column2)>
    <cfset querySetCell(myNewQry,"column7",column3-column4)>
    <cfset querySetCell(myNewQry,"column8",column1-column5)>
</cfloop>

//pass back ur new query now to your grid

<cfreturn QueryConvertForGrid(myNewQry)>

if your page times out try increasing the timeout for the page at the top like so,

<cfsetting requesttimeout="500">
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 36714875
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
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 36714882
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
0
 
LVL 3

Expert Comment

by:devilJinKazama
ID: 36720688

sure mate

last column

  <cfset querySetCell(myNewQry,"column8",numberFormat((column1/iif(column5 EQ 0,1,column5))*100,"___.__") & '%')>

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
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 36813213
so the above reads as if Division by zero, Display 0% else display 22.33% right
0
 
LVL 3

Accepted Solution

by:
devilJinKazama earned 500 total points
ID: 36819020

this should work for u, we can do it in a simpler way

<cfif listFind("-1,0",sgn(column5)) NEQ 0>
 <cfset querySetCell(myNewQry,"column8",numberFormat(0,"___.__") & '%')>
<cfelse>
 <cfset querySetCell(myNewQry,"column8",numberFormat((column1/column5)*100,"___.__") & '%')>
</cfif>

that just sets column 8 as 0.00% if column5 is 0 or a negative number, else calculate the percentage

so final version :

<cfquery name="orgQry">
SELECT colum1,colum2,column3,column4,column5
</cfquery>

<cfset myNewQry = queryNew("colum1,colum2,column3,column4,column5,column6,column7,column8")>

<cfloop query="orgQry">
    <cfset queryAddRow(myNewQry)>
    <cfset querySetCell(myNewQry,"column1",column1)>
    <cfset querySetCell(myNewQry,"column2",column2)>
    <cfset querySetCell(myNewQry,"column3",column3)>
    <cfset querySetCell(myNewQry,"column4",column4)>
    <cfset querySetCell(myNewQry,"column5",column5)>
    <cfset querySetCell(myNewQry,"column6",column1-column2)>
    <cfset querySetCell(myNewQry,"column7",column3-column4)>
    <cfif listFind("-1,0",sgn(column5)) NEQ 0>
        <cfset querySetCell(myNewQry,"column8",numberFormat(0,"___.__") & '%')>
    <cfelse>
        <cfset querySetCell(myNewQry,"column8",numberFormat((column1/column5)*100,"___.__") & '%')>
    </cfif>
</cfloop>

//pass back ur new query now to your grid

<cfreturn QueryConvertForGrid(myNewQry)>
0
 
LVL 16

Author Closing Comment

by:Gurpreet Singh Randhawa
ID: 36890106
Thanks Pal, Are you Aussie
0
 
LVL 3

Expert Comment

by:devilJinKazama
ID: 36900659
yep , indian australian
0
 
LVL 16

Author Comment

by:Gurpreet Singh Randhawa
ID: 36902616
see figured out when you said "Mate"

Cheers
0
 
LVL 3

Expert Comment

by:devilJinKazama
ID: 36906530
haha, nicely played
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Cold Fusion Migration 4.5 --> 10 !! 4 54
Using a variable with dateAdd 18 78
Database set up 5 84
Dynamic Table mySQL stored procedure 5 31
This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question