Skip navigation
entyne1
entyne1
Currently Being Moderated

Report Builder Issue

May 17, 2007 6:49 AM

Hello Community,

Okay, here is what I am trying to do. I am currently using the Coldfusion Report Builder in creating all of our reports. I would like to use the report builder to display any data "By Month". So in summary, I would like to add some fields like so..

January
February
March , etc and have the total # of records in a table displayed by month... So the final product will look like this.

January "25" (25= the total number of records for January) etc, etc.

How do I do this in the report builder? Thx for your patience. This is my first year using report builder.

Nelson
6899 Views   31 Replies   Latest reply: eliza_mfs, Feb 8, 2010 11:27 PM
 
Replies
  • tmessier
    Currently Being Moderated
    1. tmessier,
    May 19, 2007 6:53 PM   in reply to entyne1
    Report
    Do you have any example of a query you would want to do that for? It would help in giving an answer. You could probably use a group by clause and then calculated fields combined with the Evaluation Time property to pull it off.
     
    |
    Mark as:
  • entyne1
    Currently Being Moderated
    2. entyne1,
    May 23, 2007 11:06 AM   in reply to tmessier
    Report
    My query for this report is a little messy, but here it is... HOw to do currently write an expression or a function that will display in the header information like so:

    August "Total number of Records for August"
    September "Total number of Records for September"
    October "Total number of Records for October"
    November "Total number of Records for November"
    December "Total number of Records for December" Etc etc etc.

    I am VERY new to the report builder and reporting in Coldfusion. I would like to know the total number of records by month..

    Thx a butch to anyone that can land a hand.



    SELECT tblEducPrograms.Hall, tblEducPrograms.hallID, tblEducationalType.Contactgroup, tblEducPrograms.Floor, tblEducPrograms.from_date, Month(tblEducPrograms.from_date) Monumber, Monthname(tblEducPrograms.from_date) Moname, tblEducPrograms.Title, tblEducPrograms.Presenter, tblEducPrograms.Presenter_Dept, tblEducPrograms.Total, tblEducPrograms.Male, tblEducPrograms.Female, tblEducPrograms.Transgender, tblEducPrograms.None_Attended, tblEducPrograms.Event_Out_of_Hall, tblEducPrograms.Weekly, tblEducPrograms.Focus, tblEducPrograms.Format, tblEducPrograms.Organized_By
    FROM tblEducPrograms
    INNER JOIN tblEducationalType ON tblEducPrograms.Focus = tblEducationalType.EducNname
    ORDER By HALL, from_date
     
    |
    Mark as:
  • entyne1
    Currently Being Moderated
    3. entyne1,
    May 24, 2007 10:23 AM   in reply to entyne1
    Report
    I can also provide the .cfr file if necessary.

    I still cant figure it out!!

     
    |
    Mark as:
  • Matrix16
    Currently Being Moderated
    4. Matrix16,
    May 27, 2007 3:11 AM   in reply to entyne1
    Report
    My initial thoughts on this would be to add a count clause to your sql statement and then group by month in an ascending date order.
     
    |
    Mark as:
  • entyne1
    Currently Being Moderated
    5. entyne1,
    May 29, 2007 6:19 AM   in reply to Matrix16
    Report
    Thanks for your responses so far... Anyone care to show me how to do this? It would be appreciated.

     
    |
    Mark as:
  • Daverms
    Currently Being Moderated
    6. Daverms,
    May 29, 2007 6:42 AM   in reply to entyne1
    Report
    Hi,

    May I know which database and version you are using there?...
     
    |
    Mark as:
  • entyne1
    Currently Being Moderated
    7. entyne1,
    May 29, 2007 6:48 AM   in reply to Daverms
    Report
    Yes, of course.

    I am usually mysql server 5.1.

     
    |
    Mark as:
  • Daverms
    Currently Being Moderated
    8. Daverms,
    May 29, 2007 6:54 AM   in reply to entyne1
    Report
    What if you can write a group query to count the total number of records looping through the months using the 'Select MONTHNAME()' function?...
     
    |
    Mark as:
  • entyne1
    Currently Being Moderated
    9. entyne1,
    May 29, 2007 6:58 AM   in reply to entyne1
    Report
    Sounds like a plan. How would I write this?
     
    |
    Mark as:
  • entyne1
    Currently Being Moderated
    10. entyne1,
    May 29, 2007 7:04 AM   in reply to entyne1
    Report
    Daverms,

    Please know that I am using the Coldfusion Report Builder for this. Do I do this doing the calculated field report function? I know that you can write an expression and it has some data and time functions we can choose. I have been unsuccessful in doing this so far.

    A detailed explanation would be great. I have found the report builder easy to use, but complicated in situations such as this one. Thx in advance.

     
    |
    Mark as:
  • Daverms
    Currently Being Moderated
    11. Daverms,
    May 29, 2007 7:07 AM   in reply to entyne1
    Report
    Hi,

    As I am using MSSql I would write a query like this, (Just to give you an outline)

    select count(*) from patientCaseNote group by month(dateOfCaseNote)
     
    |
    Mark as:
  • entyne1
    Currently Being Moderated
    12. entyne1,
    May 29, 2007 7:25 AM   in reply to entyne1
    Report
    In that case I will have to do a sub-report and assign it its own count query. I need to figure out how I am going to make display like this..

    January ## July ##
    February ## August ##
    March ## September ##
    April ## October ##
    May ## November ##
    June ## December ##


     
    |
    Mark as:
  • Daverms
    Currently Being Moderated
    13. Daverms,
    May 29, 2007 7:26 AM   in reply to entyne1
    Report
    Hi Nelson,

    if you are going to put individual report pages for each month then you may go for the 'Calculated Fields' if your need is to put one consolidated page featuing all months figures you can put up a simple query field and that will do....
     
    |
    Mark as:
  • entyne1
    Currently Being Moderated
    14. entyne1,
    May 29, 2007 8:14 AM   in reply to Daverms
    Report
    Hi,

    Thank you for your help so far. If I do this using the calculated fields, how exactly do I perform this function?

     
    |
    Mark as:
  • entyne1
    Currently Being Moderated
    15. entyne1,
    May 31, 2007 6:39 AM   in reply to entyne1
    Report
    This is so frustrating. All I would like to do is display a total number of records in a database table by month. This report builder does not have crosstab and the calculated field function is not doing it. Daverms, your replies have been helpful, maybe im just not getting it.

    Not sure what to do. If anyone can walk me through it would be highly appreciated.

     
    |
    Mark as:
  • Daverms
    Currently Being Moderated
    16. Daverms,
    May 31, 2007 6:44 AM   in reply to entyne1
    Report
    Hi Nelson,

    Just to confirm with you again... You want to display the counts per month (or) you want to consolidate them all in one page?...

    Don't get frustrate buddy :-)
     
    |
    Mark as:
  • entyne1
    Currently Being Moderated
    17. entyne1,
    May 31, 2007 6:49 AM   in reply to Daverms
    Report
    Daverms,

    Thx for your help and your patience. I would like to display the total number either in a sub-report, or on the same .cfr as the rest of the data. So in essence it should look like this one the report.

    January: # Count

    February: Count#

    March: Count#


    Etc, etc.
     
    |
    Mark as:
  • entyne1
    Currently Being Moderated
    18. entyne1,
    May 31, 2007 6:57 AM   in reply to entyne1
    Report
    Oh.. And yes.. Its the total counts per month. Thank you Daverms...
     
    |
    Mark as:
  • entyne1
    Currently Being Moderated
    19. entyne1,
    May 31, 2007 9:58 AM   in reply to entyne1
    Report
    Daverms. I tried pretty much everything on the Calculated Field area with no luck. Any light you can shed would be VERY appreciated.

    Thx again.
     
    |
    Mark as:
  • entyne1
    Currently Being Moderated
    20. entyne1,
    May 31, 2007 10:33 AM   in reply to Daverms
    Report
    Here is a link to a sample PDF report. The top area with the counts is what I need.

    Thank you much.


    PDF Report Sample
     
    |
    Mark as:
  • entyne1
    Currently Being Moderated
    21. entyne1,
    Jun 1, 2007 6:24 AM   in reply to entyne1
    Report
    Daverms or anyone,

    I have had yet another unsuccessful day in trying to make this work. I know this may be so simple to so many people, but I find it frustration. lol. I have been on this for weeks?

    How do you guys do it? How do you get counts and displays them in your reports? How do you format it in a way where its by Month? I have searched online everywhere. Maybe Coldfusion Report Builder doesnt count records and displays them by month?

    So many questions, I know... Please help.
     
    |
    Mark as:
  • Daverms
    Currently Being Moderated
    22. Daverms,
    Jun 1, 2007 6:37 AM   in reply to entyne1
    Report
    Hi Nelson,

    MyQuery is like this,

    select distinct(datename(month,dateOfCaseNote)) as Month_Name,count(*) as Patients from patientCaseNote group by dateOfCaseNote

    and I am able to create the report just like you need...

    Btw I am using MS SQL
     
    |
    Mark as:
  • Daverms
    Currently Being Moderated
    23. Daverms,
    Jun 1, 2007 6:39 AM   in reply to entyne1
    Report
    Also,

    Just try altering this query to your DB and Tables and then try creating a report by the report wizard first and see if you can able to get a report you need...
     
    |
    Mark as:
  • entyne1
    Currently Being Moderated
    24. entyne1,
    Jun 1, 2007 6:42 AM   in reply to Daverms
    Report
    Daverms,

    Thx again for this. Is there a possible way you can show me or provide me a copy of your Coldfusion Report .cfr file? Maybe I can duplicate what you did.

    Thx again buddy.
     
    |
    Mark as:
  • entyne1
    Currently Being Moderated
    25. entyne1,
    Jun 4, 2007 7:17 AM   in reply to entyne1
    Report
    I am SOOO done with the Coldfusion report builder. My Issue is not the query.... the issue is how to get the query to work with the coldfusion report builder and how to get it to display in a PDF report from the template.

    I would have easily just created a .cfm page and did the report manually. I wanted to use the CRB to have more structure to my report.

    When I did my query as you said.. it worked fine. I got my counts by month etc.. Now trying to make the work within Coldfusion Report Builder is a different story.

    IM done.. and tired of trying. Screw report builder.
     
    |
    Mark as:
  • SmilingRoses
    Currently Being Moderated
    26. SmilingRoses,
    Sep 24, 2009 11:37 AM   in reply to entyne1
    Report

    I feel your pain. I have been going through the same thing. Grouping by month in an Access Report is very easy, and I have tried to do the same thing with Report Builder and have had no luck. It seems like it should be such an easy thing to do and that there should be some experienced Report Builder users out there who can do this.

     

    I'm not ready to give up yet, so if you or anyone else out there has found the answer I would be happy to hear about it.

     
    |
    Mark as:
  • Scott_thornton
    Currently Being Moderated
    27. Scott_thornton,
    Feb 1, 2010 5:16 PM   in reply to SmilingRoses
    Report

    Here is some SQL ( MS SQL ) that will return the count of invoices by month

     

    select
    datename( m, sb_invoice_date),
    count(sb_invoice_date)
    from
    sb_invoice
    where
    sb_invoice_date > getdate() - 365
    group by
    year( sb_invoice_date),
    month( sb_invoice_date),
    datename( m, sb_invoice_date)

    order by
    year( sb_invoice_date),
    month( sb_invoice_date)

     

    change according to your needs\database environment

     

    eg:

     

    February 23942
    March 28009
    April 26122
    May 27207
    June 31282
    July 28805
    August 27697
    September 28117
    October 25483
    November 28136
    December 24410
    January 21013
    February 1984

     
    |
    Mark as:
  • SmilingRoses
    Currently Being Moderated
    28. SmilingRoses,
    Feb 4, 2010 9:09 AM   in reply to Scott_thornton
    Report

    Thank you, thank you, THANK YOU, so much! So simple, but I couldn't figure

    it out. I can't tell you how much it means to me that I finally have a

    solution to this problem.

     

    May all the blessings of the world shower down upon you.

     

    Malinda

     

    www.Malinda-Bruce.com

    305 964 0611 H

    305 772 3687 C

    MalindaBruce.Photoshop.com

     
    |
    Mark as:
  • Scott_thornton
    Currently Being Moderated
    29. Scott_thornton,
    Feb 4, 2010 1:42 PM   in reply to SmilingRoses
    Report

    wow.. you really didn't give up.. your post was from september last year.

     

    people try to do too much in CF and in reporting, when sql is usually the answer.

     
    |
    Mark as:
  • SmilingRoses
    Currently Being Moderated
    30. SmilingRoses,
    Feb 4, 2010 3:28 PM   in reply to Scott_thornton
    Report

    I think it is a matter of what software you are accustomed to using. I do

    use sql server express 2008 for database and I also use mysql.  I bought

    ColdFusion and it came with ColdFusion Report builder, so that is what I am

    using to create reports. I use ColdFusion to communicate with the databases

    with Flex 3 as my front end.

     

    Thank you again for your help.

     

    Malinda

     

    www.Malinda-Bruce.com

    305 964 0611 H

    305 772 3687 C

    MalindaBruce.Photoshop.com

     
    |
    Mark as:
  • eliza_mfs
    Currently Being Moderated
    31. eliza_mfs,
    Feb 8, 2010 11:27 PM   in reply to entyne1
    Report

    Hi.Hope this would solve your problem.I am explaining with the help of an example.Suppose we have a Sales table having SalesId, ContactId and SalesTotal. If we need a summary report, which will display total SalesTotal group by its ContactId.
    This can be done using a simple query:

     

    SELECT IFNULL(`ContactId` , "GRAND TOTAL") AS 'CONTACT ID', SUM(`SalesTotal`) AS `TOTAL`  FROM  Sales  GROUP BY  `ContactId`  WITH ROLLUP
    Output:

    CONTACT ID TOTAL
    1175
    1180
    11910
    GRAND TOTAL15

    You can't use the ORDER BY clause with the WITH ROLLUP. They are mutually exclusive.
    Thanks

     
    |
    Mark as:
Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points