Skip navigation
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
 
Replies
  • Currently Being Moderated
    May 19, 2007 6:53 PM   in reply to entyne1
    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:
  • Currently Being Moderated
    May 27, 2007 3:11 AM   in reply to entyne1
    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:
  • Currently Being Moderated
    May 29, 2007 6:42 AM   in reply to entyne1
    Hi,

    May I know which database and version you are using there?...
     
    |
    Mark as:
  • Currently Being Moderated
    May 29, 2007 6:54 AM   in reply to entyne1
    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:
  • Currently Being Moderated
    May 29, 2007 7:07 AM   in reply to entyne1
    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:
  • Currently Being Moderated
    May 29, 2007 7:26 AM   in reply to entyne1
    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:
  • Currently Being Moderated
    May 31, 2007 6:44 AM   in reply to entyne1
    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:
  • Currently Being Moderated
    Jun 1, 2007 6:37 AM   in reply to entyne1
    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:
  • Currently Being Moderated
    Jun 1, 2007 6:39 AM   in reply to entyne1
    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:
  • Currently Being Moderated
    Sep 24, 2009 11:37 AM   in reply to entyne1

    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:
  • Currently Being Moderated
    Feb 1, 2010 5:16 PM   in reply to SmilingRoses

    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:
  • Currently Being Moderated
    Feb 4, 2010 9:09 AM   in reply to Scott_thornton

    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:
  • Currently Being Moderated
    Feb 4, 2010 1:42 PM   in reply to SmilingRoses

    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:
  • Currently Being Moderated
    Feb 4, 2010 3:28 PM   in reply to Scott_thornton

    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:
  • Currently Being Moderated
    Feb 8, 2010 11:27 PM   in reply to entyne1

    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:

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