• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Report Builder Issue

Explorer ,
May 17, 2007 May 17, 2007

Copy link to clipboard

Copied

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
TOPICS
Reporting

Views

10.2K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Participant ,
May 19, 2007 May 19, 2007

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 23, 2007 May 23, 2007

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 24, 2007 May 24, 2007

Copy link to clipboard

Copied

I can also provide the .cfr file if necessary.

I still cant figure it out!!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
May 27, 2007 May 27, 2007

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 29, 2007 May 29, 2007

Copy link to clipboard

Copied

Thanks for your responses so far... Anyone care to show me how to do this? It would be appreciated.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
May 29, 2007 May 29, 2007

Copy link to clipboard

Copied

Hi,

May I know which database and version you are using there?...

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 29, 2007 May 29, 2007

Copy link to clipboard

Copied

Yes, of course.

I am usually mysql server 5.1.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
May 29, 2007 May 29, 2007

Copy link to clipboard

Copied

What if you can write a group query to count the total number of records looping through the months using the 'Select MONTHNAME()' function?...

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 29, 2007 May 29, 2007

Copy link to clipboard

Copied

Sounds like a plan. How would I write this?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 29, 2007 May 29, 2007

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
May 29, 2007 May 29, 2007

Copy link to clipboard

Copied

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)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 29, 2007 May 29, 2007

Copy link to clipboard

Copied

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 ##


Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
May 29, 2007 May 29, 2007

Copy link to clipboard

Copied

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....

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 29, 2007 May 29, 2007

Copy link to clipboard

Copied

Hi,

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 31, 2007 May 31, 2007

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
May 31, 2007 May 31, 2007

Copy link to clipboard

Copied

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 🙂

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 31, 2007 May 31, 2007

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 31, 2007 May 31, 2007

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 31, 2007 May 31, 2007

Copy link to clipboard

Copied

Oh.. And yes.. Its the total counts per month. Thank you Daverms...

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
May 31, 2007 May 31, 2007

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 01, 2007 Jun 01, 2007

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jun 01, 2007 Jun 01, 2007

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jun 01, 2007 Jun 01, 2007

Copy link to clipboard

Copied

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...

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jun 01, 2007 Jun 01, 2007

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation