Concatenation of two rows

[Former Member]

Hi

i have an object level1 in that have data is

software

subscription

not assigned

i want to apply the prompt funtion in universe level for this object

my data is will look like

software

subscription

software & subscription

is it possible ,if it possible can you please tell me the septs

Advanced thanks

[Former Member]

Thanks every one it's resolved ....

[Former Member]

Hi,

Do you to create an additional row that is the "aggregation" of software & subscription?

If this is the case, you cannot do it at the query level that way.

You have to do it at report level. I don't think there is an easy to do it at universe level.

What is the final goal in your document for doing this?

Didier

[Former Member]

Hi Dider

Thank you for reply

I was tired report level also if i use if statement in report leve it only shows

software

subscription

Basically my final destination is i want to apply the prompt filter on Level object

in run time my user want to select the the levels

some times he want to select the

software

suscripition

software &subscription

so report level not at all work .

Can you please tell me any another way

Thanks in advanced

[Former Member]

if i use that object and apply the prompt in report level i want to show the list of values for all there
like
soft ware
subcription
software and subcription

Create an additional object in the universe. Go to properties -> View SQL -> Edit SQL -> Add a union statement in the query

panel, I am giving the syntax in oracle you can convert it in reqd syntax:

default will be SELECT <Object name> FROM <Table Name>

Addn. reqd: UNION

SELECT "software & subscription" FROM dual

OR you can mention all the three values as: SELECT "sotware", "subscription" , "software & subscription" FROM dual

Now while creating prompt you need to add an OR codition which will fetch the data for the third value:

@Select(table\Object) In @Prompt('Text','A','Class\Lov Object',mono,constrained)

OR

"software & subscription" In @Prompt('Text','A','Class\Lov Object',mono,constrained)

I hope this will help you.

Regards,

Rohit

[Former Member]

Hi Rohit

Thanks for reply

Syntax is not working

I am using same syntax like what u did .. universe level is no errors

if iam using that filter in report level an i run the report i am getting this error

Data base erroro occured :Data base erroro text is [IBM][CLI Driver][DB2NT64] like this error i am getting

Can you please suggest me

Thanks in Advanced

[Former Member]

Actually you have to use the following query for the LoV object:

SELECT <Object name> FROM <Table> (Default query geneartion of the object)

UNION

SELECT "software & subscritption"

Try running the above query directly on the database to get the desired result and if it works you can directly paste it in

the object prop. and save the sql.

Rest code is fine and don't need any change. If you still find the error can you please give me the code you are using.

Regards,

Rohit

[Former Member]

Hi,

Which data source are you using?

The answer is dependent of the data source.

Didier

[Former Member]

Hi

Thanks for Quick Reply

I am using Sql Server 2008

and one more thing

"software & subscription" is not substitute for "not assigned"

its a combination of both "software" and "subscription"

if i use that object and apply the prompt in report level i want to show the list of values for all there

like

soft ware

subcription

software and subcription

Give me the septs in detail

Thanks in Advanced

[Former Member]

Hi,

Here is the syntax you have to wirite.

Replace the placeholder fields by the right values.

CASE
WHEN @Prompt('Select product','A','class\object',mono,constrained) = 'not assigned' then "sofwtare and subscription'
ELSE @Prompt('Select product','A','class\object',mono,constrained)
END

Regards

Didier

[Former Member]

Hi Didier

Thanks for Quick Reply

My requirement is "software & subscription" is not substitute for "not assigned"

and also if i apply the prompt for this object i want to see all the like

Software

Subscription

Software & Subscription

Notassign

How it implement Can you please tell/stepts

Thanks in Advanced