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.
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
INNER JOIN tblEducationalType ON tblEducPrograms.Focus = tblEducationalType.EducNname
ORDER By HALL, from_date
I can also provide the .cfr file if necessary.
I still cant figure it out!!
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.
Thanks for your responses so far... Anyone care to show me how to do this? It would be appreciated.
May I know which database and version you are using there?...
Yes, of course.
I am usually mysql server 5.1.
What if you can write a group query to count the total number of records looping through the months using the 'Select MONTHNAME()' function?...
Sounds like a plan. How would I write this?
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.
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)
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 ##
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....
Thank you for your help so far. If I do this using the calculated fields, how exactly do I perform this function?
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.
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 :-)
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
Oh.. And yes.. Its the total counts per month. Thank you Daverms...
Daverms. I tried pretty much everything on the Calculated Field area with no luck. Any light you can shed would be VERY appreciated.
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.
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
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...
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.
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.
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.
Here is some SQL ( MS SQL ) that will return the count of invoices by month
datename( m, sb_invoice_date),
sb_invoice_date > getdate() - 365
datename( m, sb_invoice_date)
change according to your needs\database environment
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.
305 964 0611 H
305 772 3687 C
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.
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.
305 964 0611 H
305 772 3687 C
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 ROLLUPOutput:
CONTACT ID TOTAL 117 5 118 0 119 10 GRAND TOTAL 15You can't use the ORDER BY clause with the WITH ROLLUP. They are mutually exclusive.Thanks