is there agg functions for string?

building semantic layer
1

i’m facing the following problem,
the table has two columns
emp_id team_name
1 medical
1 tech
the problem is my customer want the team_name displayed in one column, i.e.
1 medical,tech
i think it’s impossible to have this accomplished in SQL ,then i tried defining the team_name as a “measure” object but it seems that projections cannot be applied to strings. what i want is just concatenate the team_names under the same emp_id.
plz help me out!

AlexZ (BOB member since 2004-06-01)

2

This is not possible with BO unless you know how many teams there are each of these.

This has been discussed here before and on the BO tech support site. I believe it’s even a tip on their site.

Steve Krandel :us: (BOB member since 2002-06-25)

3

thanks steve,i even don’t know how to search on this topic,
i’ll advise my customer to change their requirement

AlexZ (BOB member since 2004-06-01)

4

anyway,do u think it’s possible like this?
first create another measure object with the definition:

sum(decode(team,"medical",1,"tech",2,0))

this is the “coding” step,then we can have the numeric result “decoded” into string

 if value=1 then "medical" elseif value=2 then "tech" elseif value=3 then "medical,tech" end

a little bit too complicated, infeasible if too many items to code

AlexZ (BOB member since 2004-06-01)

5

This would work assuming that there are only 2 possible values of team.

Steve Krandel :us: (BOB member since 2002-06-25)

6

This is probably best accomplished via a view on the database side. That will let you have about 256 groups or so through the use of a BITAND type operation as you’ve described it.

It would take 2-3 views to flatten out your data, but it can be done as long as you are not dealing with too many variables. (There really isn’t an upper limit on how much this can handle, but there is on how much code you want to have to maintain).

-RM

digpen :us: (BOB member since 2002-08-15)

7

i created two views

create view emp_coding(emp_id,team_code)
as select emp_id,sum(decode(team,"medical",1,"tech",2,0))
from emp
group by emp_id;
create view emp_decoding(emp_id,teams)
as 
select emp_id,decode(bitand(team_code,2),2,"tech)||","||decode(bitand(team_code,1),1,"medical");
from emp_coding

since there are not too many teams , it’s at least acceptable,
i hope it would turn out to be a maintenance nightmare

AlexZ (BOB member since 2004-06-01)

8

Which version are you on??? Defining it as a Measure and setting the projection to SUM should have worked… Also you need to set the TYPE of the Object to either Character or Long Text… Now when you do this you will not get Comma between various Team Name but should get the Team Name for an Emp ID in a single row… If you want “,” between Team Name you need to change the object definition at the Universe Level to concatenate “,” and then at the report side trim the last “,”.

Also Look Here

Sridharan :india: (BOB member since 2002-11-08)

9

i’m working on bo 5.1.2
the definition is : emp.team and type is char/long text
projection :sum
but it doesn’t work

AlexZ (BOB member since 2004-06-01)

10

Maybe the version but not sure!!! When you do that what is the output you are getting??? Does it show the name in separate rows… If possible send the report to me and will check how it shows it at my side…

Sridharan :india: (BOB member since 2002-11-08)

11

thanks Sridharan,but i’m working on a Chinese version BO,
so it’s difficult to convert all things into English

AlexZ (BOB member since 2004-06-01)