31 Replies Latest reply: Feb 8, 2010 11:27 PM by eliza_mfs RSS

    Report Builder Issue

    entyne1 Community Member
      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
        • 1. Re: Report Builder Issue
          tmessier Community Member
          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.
          • 2. Re: Report Builder Issue
            entyne1 Community Member
            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
            • 3. Re: Report Builder Issue
              entyne1 Community Member
              I can also provide the .cfr file if necessary.

              I still cant figure it out!!

              • 4. Re: Report Builder Issue
                Matrix16
                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.
                • 5. Re: Report Builder Issue
                  entyne1 Community Member
                  Thanks for your responses so far... Anyone care to show me how to do this? It would be appreciated.

                  • 6. Re: Report Builder Issue
                    Daverms Community Member
                    Hi,

                    May I know which database and version you are using there?...
                    • 7. Re: Report Builder Issue
                      entyne1 Community Member
                      Yes, of course.

                      I am usually mysql server 5.1.

                      • 8. Re: Report Builder Issue
                        Daverms Community Member
                        What if you can write a group query to count the total number of records looping through the months using the 'Select MONTHNAME()' function?...
                        • 9. Re: Report Builder Issue
                          entyne1 Community Member
                          Sounds like a plan. How would I write this?
                          • 10. Re: Report Builder Issue
                            entyne1 Community Member
                            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.

                            • 11. Re: Report Builder Issue
                              Daverms Community Member
                              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)
                              • 12. Re: Report Builder Issue
                                entyne1 Community Member
                                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 ##


                                • 13. Re: Report Builder Issue
                                  Daverms Community Member
                                  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....
                                  • 14. Re: Report Builder Issue
                                    entyne1 Community Member
                                    Hi,

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

                                    • 15. Re: Report Builder Issue
                                      entyne1 Community Member
                                      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.

                                      • 16. Re: Report Builder Issue
                                        Daverms Community Member
                                        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 :-)
                                        • 17. Re: Report Builder Issue
                                          entyne1 Community Member
                                          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.
                                          • 18. Re: Report Builder Issue
                                            entyne1 Community Member
                                            Oh.. And yes.. Its the total counts per month. Thank you Daverms...
                                            • 19. Re: Report Builder Issue
                                              entyne1 Community Member
                                              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.
                                              • 20. Re: Report Builder Issue
                                                entyne1 Community Member
                                                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
                                                • 21. Re: Report Builder Issue
                                                  entyne1 Community Member
                                                  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.
                                                  • 22. Re: Report Builder Issue
                                                    Daverms Community Member
                                                    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
                                                    • 23. Re: Report Builder Issue
                                                      Daverms Community Member
                                                      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...
                                                      • 24. Re: Report Builder Issue
                                                        entyne1 Community Member
                                                        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.
                                                        • 25. Re: Report Builder Issue
                                                          entyne1 Community Member
                                                          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.
                                                          • 26. Re: Report Builder Issue
                                                            SmilingRoses Community Member

                                                            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.

                                                            • 27. Re: Report Builder Issue
                                                              Scott_thornton Community Member

                                                              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

                                                              • 28. Re: Report Builder Issue
                                                                SmilingRoses Community Member

                                                                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

                                                                • 29. Re: Report Builder Issue
                                                                  Scott_thornton Community Member

                                                                  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.

                                                                  • 30. Re: Report Builder Issue
                                                                    SmilingRoses Community Member

                                                                    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

                                                                    • 31. Re: Report Builder Issue
                                                                      eliza_mfs Community Member

                                                                      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