asked on Business Objects Get Count of Records by Month
Hello. Using Business Objects. I don't have authority to edit the SQL, so I'm limited to using the built-in query builder tools.
I'm running a query that returns all transactions within a date range. One of the fields in the table is a date field. By creating a variable using the function editor, I can get a total record count by doing the following:
=Count([DateField])
Is there any way I can modify it or drag it into my report to give me a monthly record count? For example, let's say the date range I choose is 1/1/2010 through 3/31/2010. Let's say during January, there were 100 transactions, February there were 50, and March there were 200.
Right now my report is displaying all of the detail records (using my example above, that would be 350 detail records). I'm trying to be able to achieve something like this:
January 100
February 50
March 300
instead of or in addition to displaying the detail records. Like I said, I can get a TOTAL record count using the function editor but I don't know to modify it (if possible) to give me a count by month. Again, I CANNOT edit the SQL directly, so I can't type in SELECT statements or anything like that.
I'm running a query that returns all transactions within a date range. One of the fields in the table is a date field. By creating a variable using the function editor, I can get a total record count by doing the following:
=Count([DateField])
Is there any way I can modify it or drag it into my report to give me a monthly record count? For example, let's say the date range I choose is 1/1/2010 through 3/31/2010. Let's say during January, there were 100 transactions, February there were 50, and March there were 200.
Right now my report is displaying all of the detail records (using my example above, that would be 350 detail records). I'm trying to be able to achieve something like this:
January 100
February 50
March 300
instead of or in addition to displaying the detail records. Like I said, I can get a TOTAL record count using the function editor but I don't know to modify it (if possible) to give me a count by month. Again, I CANNOT edit the SQL directly, so I can't type in SELECT statements or anything like that.
Crystal ReportsOracle DatabaseDatabases
Last Comment
James0628
ASKER
I think it's version 6.5. I create a query by dragging fields from a selected universe into the query builder/report builder windows or whatever they're called. When I run the query, it produces a report. Once the report is produced, I'm going into the formula editor or function editor or whatever it's called and doing the =Count([DateField]) thing and saving that as a variable. When I drag that variable into the report window and drop it above my report, it is giving me a total record count. What I'm trying to do is produce a count like that but by month (because my date range spans more than 1 month). Like I said, I cannot edit the SQL directly. If I could, I'd be able to accomplish what I need easily. Since I can't edit the code, I'm wondering if it can be accomplished using the existing tools.
BO produces several reporting tools. Is it Webi, Crystal,...?
mlmcc
mlmcc
Assuming Crystal, just group on the date and set group to change on every month, then put a Count summary in the group footer.
ASKER
it's Webi.
ASKER
By the way, just as an example of the type of functionality that's available to me:
I ran a query with a date range of 1/1/2010 through 3/31/2010. I included 2 fields in the report results:
DateField and InvoiceNumber. So, for that date range there are many records per day. What I'm trying to do is get a total record count for each month within the date range. So, I need a count for Jan of 2010, another count for Feb of 2010 and another count for Mar of 2010.
I created a variable (which is not the correct formula to achieve what I need to achieve but I'm including it here to try to show what's available to me since I can't edit the SQL directly)
This variable is giving me a count of the months in my report, so it's displaying the number 3:
=Count(Month([DateField]))
I'm hoping to be able to modify or create something like it that will give me a record count by month.
I ran a query with a date range of 1/1/2010 through 3/31/2010. I included 2 fields in the report results:
DateField and InvoiceNumber. So, for that date range there are many records per day. What I'm trying to do is get a total record count for each month within the date range. So, I need a count for Jan of 2010, another count for Feb of 2010 and another count for Mar of 2010.
I created a variable (which is not the correct formula to achieve what I need to achieve but I'm including it here to try to show what's available to me since I can't edit the SQL directly)
This variable is giving me a count of the months in my report, so it's displaying the number 3:
=Count(Month([DateField]))
I'm hoping to be able to modify or create something like it that will give me a record count by month.
Generally to get counts based on month you have to create groups. I don't use Webi so I don't know if you have groups or how to create them.
mlmcc
mlmcc
ditto
not in a long while anyway. About to start using it again in the next few weeks, but way rusty right now.
ASKER
yeah that's the thing. I don't see any group by options. That's why I said, if I could modify the sql directly, then it's an easy query to accomplish. but having nothing available to me except a list of certain functions, I can't seem to find the tools/commands to be able to get the results I need. I mean, one thing I could do is run multiple queries with a month date range. Then if I use the basic count function, I can get the record count for that query. but I need a monthly count for like 3 years. So that means I'd have to run 36 separate queries.
I know there is a way to do this in Webi, I just can't remember what. It has something to do with the slice and dice capabilities, or the crosstab capabilities.
ASKER
yeah...before you posted that, I came across the crosstab query thing under templates. however, i haven't figured out yet how to get it to do what I need it to do. So if you wind up remembering, please let me know!
ASKER
Actually turns out that it looks like the slice and dice capabilites are not even available to me. I'm using a really stripped down or highly restricted version.
Perhaps you should talk to your admin about your user rights.
ASKER
ha ha. That has happened numerous times and no go. but thanks for the suggestion anyway.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
mlmcc