BusinessObjects Board

Group a variable

building reports desktopintelligence
#1

Hi

I am using 6.0 version of BO. I am attaching the report to make it more clear.

How do I ‘Group’ the values - ‘less than 2%’ in ‘try’ column into ‘All Others’ as shown at the bottom. I have got this result manually by going to Data —> Variables—> group.

I would like to have this dynamically done, the criterion being, Group all the leadtypes which are less than 2% into ‘All Others’.

Any help will be appreciated.

Thanks
Neha
New Microsoft Word Document.doc (32.0 KB)

neha008 (BOB member since 2007-07-09)

Derived variable
#2

I dont know what you mean by dynamic here but you can use If then statement instead of doing group

 =if <try>="less than 2%" then "ALL others" else <try>

Add remaining groups to the above expression

.

haider :es: (BOB member since 2005-07-18)

#3

Hi

This is a better way of asking question. Pls consider this report not the previous report.

report is attached for the question to be more clear. Version 6.1 BO.

I want to group all the values in leadtype source (dimension) as “All Others” when the percent (measure) is less than 2, and display this in a Pie chart.

I am able to get this manually by Data—> variables-----> Leadtype Source—> group.
then I created Final variable inside which I made “All others” group which has all the lead types less than 2 which are InList( “Dealer/Distributor Referrals” , “Direct Mail” , “Magazines (Ads & PR)” , “Other MT” , “Other Web” , “PS Sales” , “Technical Support” , “Trade Show”).

How do I make this more generic instead of hardcoding the values??

Any help will be greatly appreciated.

Thanks
New Microsoft Word Document (2).doc (26.0 KB)

neha008 (BOB member since 2007-07-09)

#4

Create a variable using if then statement as mentioned earlier

=if <percent> <2 then "ALL others"

This will not require to hard code the lead types.

.

haider :es: (BOB member since 2005-07-18)

#5

Thanks a lot for the reply.

When I use the If-then statement, the variable BOB turns into ‘Measure’ automatically. So, I have year(dimesion), Percent (measure) and BOB ( measure). Now if I am using these variables in the pie chart, then It is now displayed as I want it to be.

Instead if we can have a table as attached , then the pie chart comes out to be right. The table has Year(dimension), Percent ( measure) and Final ( dimension).

So, the basic key is to convert BOB (measure) into dimension variable. Is this possible?

Help will be really appreciated.

Thanks
New Microsoft Word Document (3).doc (26.0 KB)

neha008 (BOB member since 2007-07-09)

#6 
=if <percent> <2 then "ALL others"

This should be dimension and not a measure.
Give the exact expression you are using to create the variable (BOB)

.

haider :es: (BOB member since 2005-07-18)

#7

Thanks for your reply.

the code for BOB is:

= If < 2 Then “All Others” Else .

Here percent is a measure. As a result of which, I am assuming, BOB is also a measure.

As a piece of info, I just have leadtype count ( measure) from the excel sheet ( data provider). I have then calculated the percent at the report level, which uses this formula.

= (<Lead Type Count(PD 2 in C:\Documents and Settings\gupta-3\My Documents\My Business Objects Documents\userDocs\Leads With Macros - Canada.xls)> / Sum(<Lead Type Count(PD 2 in C:\Documents and Settings\gupta-3\My Documents\My Business Objects Documents\userDocs\Leads With Macros - Canada.xls)>) In Report ) * 100

Any hints will be appreciated.

Thanks
Neha

neha008 (BOB member since 2007-07-09)

#8

Thanks for your reply.

the code for BOB is:

= If < 2 Then “All Others” Else .

Here percent is a measure. As a result of which, I am assuming, BOB is also a measure.

As a piece of info, I just have leadtype count ( measure) from the excel sheet ( data provider). I have then calculated the percent at the report level, which uses this formula.

= (<Lead Type Count(PD 2 in C:\Documents and Settings\gupta-3\My Documents\My Business Objects Documents\userDocs\Leads With Macros - Canada.xls)> / Sum(<Lead Type Count(PD 2 in C:\Documents and Settings\gupta-3\My Documents\My Business Objects Documents\userDocs\Leads With Macros - Canada.xls)>) In Report ) * 100

Any hints will be appreciated.

Thanks
Neha

neha008 (BOB member since 2007-07-09)

#9

Second time was posted by mistake.

Sorry!!!

neha008 (BOB member since 2007-07-09)

#10

You could have deleted the duplicate post

= If <percent> < 2 Then "All Others" Else <Lead Type>. 
Here percent is a measure. As a result of which, I am assuming, BOB is also a measure.

Percent is infact measure which you are only checking in if part, but the value returned in the then part is a string (dimension) of type character

.

haider :es: (BOB member since 2005-07-18)

#11

Thanks for correcting me.

But still I am not aware why the ‘BOB’ variable is becoming a measure. If this measure gets converted in into a ‘dimension’ variable, then the problem will be solved.

Is there a way how this can be done.

Any hints will be greatly appreciated.

Thanks
Neha

neha008 (BOB member since 2007-07-09)

#12

Give the exact syntax you are using for BOB variable.
If you use aggregate functions to create variables then they show as measures
I think there is an aggregate function max() or min() used for creating leadtype object. Just check into that.

.

haider :es: (BOB member since 2005-07-18)

#13

I have a leadtype Count object which is not having any aggregate function. This is directly coming from the excel sheet dataprovider.

Now as I want to calculate the percentages, I am using this formula for percent:
= ( / Sum( ) In Report ) * 100.

Also as asked by you, exact code for BOB is:

= If < 2 Then “All Others” Else .

Now as variable ‘Percent’ is using SUM aggregate function, it is becoming a measure . And as BOB is using ‘Percent’ (indirectly SUM), it is also converted into a Measure. Correct me if I am wrong.

So is there any way to do convert ‘BOB’ into a dimension???

thanks for all your help.

neha008 (BOB member since 2007-07-09)

#14

No. As soon as you use an aggregate function, the variable must become a measure.

Dave Rathbun :us: (BOB member since 2002-06-06)

#15

Hi,

If this is not posscible, then is there any way or work around to :

group all the ‘Leadtype Source’ that are less than 2% of the total into “all others” and displayed in a Pie chart.

Thanks
Neha
melissa.doc (26.0 KB)

neha008 (BOB member since 2007-07-09)

#16

You can create a VBA data provider to process your incoming data and create a new cube, then use that cube to drive your chart.

Dave Rathbun :us: (BOB member since 2002-06-06)

#17

Thanks Dave for all your replies.

Do you mean that I need to calculate Percentages from macro and then group the ‘LeadType Source’??

Pls let me know .

Thanks

neha008 (BOB member since 2007-07-09)

#18

Can you still try mapping them to the pie chart.
Because the object (BOB) is returning character string though its a measure.
What you are expecting in the chart to show and what is displayed

.

haider :es: (BOB member since 2005-07-18)

#19

A VBA data provider is a way to generate a “cube” based on your code. You would parse your input data from the query and generate the rolled-up values required for your chart. You would then be able to create the chart that you want.

There was a tech note on the Business Objects site a long time ago that provided the code to do this, but I have not kept a copy and it has been a long time since I looked. It may or may not be there anymore.

There is a sample showing how to create a VBA data provider in this article from BOB’s Downloads:

Dave Rathbun :us: (BOB member since 2002-06-06)