Help with 2 levels of aggregation

[PruSQLer]

I did a Search but didn't find a topic specific to what I'm encountering so here goes:

I'm using BO V 6.1

I have a report with 2 Measures that need to be grouped by the same 2 parameters (Business and Company). The first measure is Total Sales - no problem. The 2nd measure is Total Sales GT 500,000 which must be summed by salesman (who will have multiple records) before being grouped into business and company.

I can do this quite easily with SQL but I don't know how I would do this (having sum(sales) > 500000) in a universe and get it grouped within the 2 parameters.

Any ideas?

 

[skrandel]

You need to do this within the query panel. It cannot be done at the universe level. It simply isn't possible.

In 6.5, you could do it with a derived table that does the SQL for you. It would act like a view.

Steve Krandel
VERITAS Software

 

[PruSQLer]

Thanks Steve,

No 6.5 for us yet; maybe by 4th Qtr of this year.

Doing this via the Query Panel would be fine. Unfortunately, I have no idea how to do that so any hints would be greatly appreciated.

 

[skrandel]

If your measure for Total Sales is defined correctly in the universe as sum(totalsales), then when you use it as a condition in the query panel BO will automatically create the having clause.

You're going to need 2 data providers or a union to retrieve both the total and the total GT 500,000.

Steve Krandel
VERITAS Software

 

[PruSQLer]

But won't that sum the sales within Business and Company? I need it to be summed by salesman, then have those that are GT 500,000 included within the Business and Company dimensions.

 

[sh4kil]

Run 2 Queries as:

Query 1

Business, Company, Salesperson, Total Sales GT

and second query with

Busines, Company, Total Sales


Synchronise data using the common dimensions and drop Total Sales against Total Sales GT. You will see the Total Sales value repeating itself within a Business and Company as we are different granularity

 

[PruSQLer]

Thanks for the feedback. It works fine. I didn't realize it would synchronize like that.

My solution was to run a subquery from the query panel to get agents with sales GE 500,000 and sum those within Biz and Co. That worked as well. I think I might go with this approach though. No need to run a subquery if it's not required.