TOTAL #numberformat(totalloads)# #numberformat(totalclicks)# #ReportMonth# #getdata.name# #numberformat(getdata.load)# #numberformat(getdata.click)# #getdata.timestamp# [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => JethroTull [ip] => webforumsuser@m [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 8 [islastshown] => 1 [isfirstshown] => [attachments] => [allattachments] => ) --> Report - Query & Display help - How to get totals? - Macromedia ColdFusion

Report - Query & Display help - How to get totals? - Macromedia ColdFusion

Hope everyone had a great weekend. I needed help with this report. I have a banner Load and click tool. following is the DB structure: Table: Banners ------- BannerID BannerName Table: BannerTrack ------------ BannerID Load Click TimeStamp I using this query, which gets the load click information from both the tables SELECT BannerTrack.BannerID, Banners.Name, BannerTrack.Load, BannerTrack.Click, BannerTrack.TimeStamp FROM BannerTrack INNER JOIN Banners ON BannerTrack.BannerID = Banners.ID WHERE (((DatePart('m',[BannerTrack.TimeStamp]))= 5) AND ((DatePart('yyyy',[BannerTrack.TimeStamp]))= 2005)) ORDER BY Banners.Name, BannerTrack.TimeStamp I want to display the report like this: Banner Load Click TimeStamp XX 3 2 5/2/2005 XX 4 1 5/4/2005 TOTAL: 7 3 YY ...

  1. #1

    Default Report - Query & Display help - How to get totals?

    Hope everyone had a great weekend.

    I needed help with this report.

    I have a banner Load and click tool. following is the DB structure:

    Table: Banners
    -------
    BannerID
    BannerName

    Table: BannerTrack
    ------------
    BannerID
    Load
    Click
    TimeStamp

    I using this query, which gets the load click information from both the tables

    SELECT BannerTrack.BannerID, Banners.Name, BannerTrack.Load,
    BannerTrack.Click, BannerTrack.TimeStamp
    FROM BannerTrack INNER JOIN Banners ON BannerTrack.BannerID = Banners.ID
    WHERE (((DatePart('m',[BannerTrack.TimeStamp]))= 5) AND
    ((DatePart('yyyy',[BannerTrack.TimeStamp]))= 2005))
    ORDER BY Banners.Name, BannerTrack.TimeStamp

    I want to display the report like this:

    Banner Load Click TimeStamp

    XX 3 2 5/2/2005
    XX 4 1 5/4/2005
    TOTAL: 7 3

    YY 4 3 5/2/2005
    YY 2 1 5/4/2005
    TOTAL: 6 4

    Question: I wanted help with the totals - How do i get the total to be
    displayed?

    JethroTull Guest

  2. #2

    Default Re: Report - Query & Display help - How to get totals?

    can someone help me with this please

    thank you
    JethroTull Guest

  3. #3

    Default Re: Report - Query & Display help - How to get totals?

    Set the total to zero outside the cfoutput:-

    <cfset Totals = StructNew()>
    <cfset Totals.Load = 0>
    <cfset Totals.Clicks = 0>

    Then within the cfoutput loop add the latest amount to the total:-

    <cfset Totals.Load = Totals.Load + Load>
    <cfset Totals.Clicks = Totals.Clicks + Clicks>

    Then output it when you need it????

    Make Sense?

    Stressed_Simon Guest

  4. #4

    Default Re: Report - Query & Display help - How to get totals?

    Hello Simon,

    Thanks for the reply. That is exactly where i need the help. I am looping
    though the query results. <cfloop query="getData">...I need help in coding
    where to put the total - I need to add a blank row to display the TOTAL: after
    records for XX banner and before the YY banner starts... How do I do this?

    Thank you in advance

    JethroTull Guest

  5. #5

    Default Re: Report - Query & Display help - How to get totals?

    OK you cannot do this with a cfloop you'll have to use the grouping function in cfoutput.

    Why do you have to tables? What is there separation?
    Stressed_Simon Guest

  6. #6

    Default Re: Report - Query & Display help - How to get totals?

    We are using the goupBy in the query... Dont know how else we can do it... Here
    are some thoughts.. we can loop though the query -- set a variable
    CurrentBanner --- display it... set another variable LastDisplayed... then
    check it.. I tried doing this but dont know how to code it.. what do you feel?
    is this the right way?

    JethroTull Guest

  7. #7

    Default Re: Report - Query & Display help - How to get totals?

    can you help please ... i am having brain freeze with this..
    JethroTull Guest

  8. #8

    Default Re: Report - Query & Display help - How to get totals?

    I finally managed to code this: this may not be the smartest solution, but
    please take a look and would be great to get your comments on it... thanks


    <CFPARAM DEFAULT=0 NAME="showTot" type="boolean">
    <CFPARAM DEFAULT="" NAME="LastDisplayed">
    <CFPARAM DEFAULT=0 NAME="totalLoads">
    <CFPARAM DEFAULT=0 NAME="totalclicks">

    <cfoutput>
    <table>
    <cfloop query="getData">
    <!--- We would be setting variable 'LastDisplayed' after its displayed --->
    <!--- then we will check if the LastDisplayed and the current name is
    differt --->
    <!--- If its different, then it means the Row for TOTAL is to be displayed
    --->
    <!--- AND - if the lastDisplayed is empty then it is the first record --->
    <!--- if you dont include len(LastDisplayed) then it will show the row for
    Total at the begining --->
    <cfif (LastDisplayed NEQ getdata.name) and len(LastDisplayed)>
    <cfset showTot = 1>
    <cfelse>
    <cfset showTot = 0>
    </cfif>

    <cfif showTot><!--- If the current row is a New Banner - then display the
    total --->
    <tr>
    <td align="right" height="35"><b>TOTAL</b></td>
    <td align="center"><b>#numberformat(totalloads)#</b></td>
    <td align="center"><b>#numberformat(totalclicks)#</b></td>
    <td align="center"><b>#ReportMonth#</b></td>
    </tr>
    <cfset totalLoads = 0><!--- the total is displayed: now reset for the next
    record --->
    <cfset totalclicks = 0><!--- the total is displayed: now reset for the next
    record --->
    </cfif>
    <tr><td><font color="800000"><strong>#getdata.name#</strong></font></td>
    <td bgcolor="008080" align="center">#numberformat(getdata.load)#</td>
    <td align="center">#numberformat(getdata.click)#</td>
    <td>#getdata.timestamp#</td></tr>
    <cfset LastDisplayed = getdata.name><!--- set this variable to compare with
    the current row --->
    <cfset totalLoads = totalLoads + getdata.load><!--- set the total loads --->
    <cfset totalClicks = TotalClicks + getdata.click><!--- set the total clicks
    --->
    </cfloop>
    </cfoutput>
    </table>
    <cfoutput>

    JethroTull Guest

Similar Threads

  1. How to display Crystal Report
    By Kamal Ahmed in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: July 5th, 02:05 PM
  2. Generating a Report - Help with the Query
    By JethroTull in forum Macromedia ColdFusion
    Replies: 10
    Last Post: May 5th, 08:29 PM
  3. SQL Query in Report Builder
    By SarojSrini in forum Macromedia ColdFusion
    Replies: 0
    Last Post: March 21st, 05:54 PM
  4. Query a date field in report builder
    By Boise-J-Man in forum Coldfusion Database Access
    Replies: 1
    Last Post: March 4th, 04:12 AM
  5. ASP totals and sub-totals from an Access or SQL DB
    By Laphan in forum ASP Database
    Replies: 3
    Last Post: April 2nd, 08:15 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •