BMC - How do I get calculated fields output in a single row

Bharath Kumar shared this question 4 weeks ago
Awaiting Reply

Hi,

I am creating 2 calculated fields to display the roles as below:

CASE WHEN `TS:H2:PersonRoleSystem`.`Role` = 'Business Solution Owner' THEN `TS:H2:PersonRoleSystem`.`Full Name` END

CASE WHEN `TS:H2:PersonRoleSystem`.`Role` = 'IT Solution Owner' THEN `TS:H2:PersonRoleSystem`.`Full Name` END

When I add this 2 calculations in the columns, the result set is divided into 2 different rows as per the attachment. How do I make this to display the result set in a single column. I have event tried with Sub-queries, but the result was same.

Let me know if it possible to achieve this?

-Bharath

Comments (6)

photo
1

Hi Bharath,

I'm assuming the table data is something like this:

/7A3gVCJLaSCAAAAAElFTkSuQmCC

in which case I would use the Yellowfin feature called Freehand SQL Report with the following code:

SELECT
    SysName, 
    MAX(CASE WHEN Role = 'Business Solution Owner' THEN FullName END) AS BSO,
    MAX(CASE WHEN Role = 'IT Solution Owner' THEN FullName END) AS ISO
FROM
    SystemName
GROUP BY
    SysName
ORDER BY
    SysName ;


which would then yield the following report:


/H98iwpE8OviNgAAAABJRU5ErkJggg==

I hope you like my solution!

regards,

David

photo
1

Hi Dave,

Thanks for trying that, but our free hand sql does not allow select, from clause :(

-Bharath

photo
1

Hi Bharath,

thanks for the screen capture, I can see that you are talking about Freehand SQL Calculated Field, whereas I am talking about Freehand SQL Report:

/HrbO1BcjGv4AAAAASUVORK5CYII=

regards,

David

photo
1

Hi Dave,

Sorry we are 7.3, don't have the free hand sql feature :( .

-Bharath

photo
1

Hi Bharath,

I have checked an instance of a 7.3 BMC build and it definitely has the Freehand SQL Report feature.

I have attached a short video to demonstrate this to you.

regards,

David

photo
1

Hi Bharath,

just wondering how you got along with this matter?

regards,

David