We help IT Professionals succeed at work.

Business Objects Get Count of Records by Month

6,343 Views
Last Modified: 2012-05-11
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.  
Comment
Watch Question

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Which BO tool are you using?

mlmcc

Author

Commented:
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.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
BO produces several reporting tools.  Is it Webi, Crystal,...?

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.

Author

Commented:
it's Webi.

Author

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

   
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
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
ditto
not in a long while anyway. About to start using it again in the next few weeks, but way rusty right now.

Author

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

Author

Commented:
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!

Author

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

Author

Commented:
ha ha. That has happened numerous times and no go. but thanks for the suggestion anyway.
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.