Hello, 

Has anyone attempted to transpose the results of #sq(CSVIdentityNameList ())#

Example the macro results will be something like  'Admin Administrator', 'Admin Project Member', 'Admin Shared Files Access', 'Admin Shared Files Developer', 'All Authenticated Users', 'Analytics Users' Can that be transposed as

Admin Administrator
Admin Project Member
Admin Shared Files Access
Admin Shared Files Developer
All Authenticated Users
Analytics Users

Thank you,
Udaya.

Hello Udaya,
You can use a CognosSQL statement that uses a row value constructor.  E.g.

select IdName
from
values (
#CSVIdentityNameList ('),(')#
 ) T(IdName)

The macro will return something like     'a'),('b'),('c'
This will then be surrounded by ( ) tokens, so you end up with 
           select IdName from values ('a'),('b'),('c') T(IdName)

Cognos SQL needs a database connection, you can use any of the ones that the Data Module or FM Package is pointing to.

Hello, 

Has anyone attempted to transpose the results of #sq(CSVIdentityNameList ())#

Example the macro results will be something like  'Admin Administrator', 'Admin Project Member', 'Admin Shared Files Access', 'Admin Shared Files Developer', 'All Authenticated Users', 'Analytics Users' Can that be transposed as

Admin Administrator
Admin Project Member
Admin Shared Files Access
Admin Shared Files Developer
All Authenticated Users
Analytics Users

Thank you,
Udaya.

Hello Udaya,
You can use a CognosSQL statement that uses a row value constructor.  E.g.

select IdName
from
values (
#CSVIdentityNameList ('),(')#
 ) T(IdName)

The macro will return something like     'a'),('b'),('c'
This will then be surrounded by ( ) tokens, so you end up with 
           select IdName from values ('a'),('b'),('c') T(IdName)

Cognos SQL needs a database connection, you can use any of the ones that the Data Module or FM Package is pointing to.

Hello, 

Has anyone attempted to transpose the results of #sq(CSVIdentityNameList ())#

Example the macro results will be something like  'Admin Administrator', 'Admin Project Member', 'Admin Shared Files Access', 'Admin Shared Files Developer', 'All Authenticated Users', 'Analytics Users' Can that be transposed as

Admin Administrator
Admin Project Member
Admin Shared Files Access
Admin Shared Files Developer
All Authenticated Users
Analytics Users

Thank you,
Udaya.

Hi Henk, 

Sorry, this may be a noob question but I'm looking for this exact solution and am unable to make it work.  When I try this:

select IdName
from
values (
#sq(CSVIdentityNameList ('),('))#
 ) T(IdName)

I get this error:

XQE-V5-0017 V5 syntax error found for data item 'Testing1' of query 'Query1', invalid token "IdName" found after "select ".

What is "IdName" supposed to reference?

Hello Udaya,
You can use a CognosSQL statement that uses a row value constructor.  E.g.

select IdName
from
values (
#CSVIdentityNameList ('),(')#
 ) T(IdName)

The macro will return something like     'a'),('b'),('c'
This will then be surrounded by ( ) tokens, so you end up with 
           select IdName from values ('a'),('b'),('c') T(IdName)

Cognos SQL needs a database connection, you can use any of the ones that the Data Module or FM Package is pointing to.

Hello, 

Has anyone attempted to transpose the results of #sq(CSVIdentityNameList ())#

Example the macro results will be something like  'Admin Administrator', 'Admin Project Member', 'Admin Shared Files Access', 'Admin Shared Files Developer', 'All Authenticated Users', 'Analytics Users' Can that be transposed as

Admin Administrator
Admin Project Member
Admin Shared Files Access
Admin Shared Files Developer
All Authenticated Users
Analytics Users

Thank you,
Udaya.

Hi Ryan,

It seems that you added an sq(  ) call in the macro expression. When you remove that call, does it then work properly?

------------------------------
Kind regards,
Henk Cazemier

Original Message:
Sent: Thu December 19, 2024 02:46 PM
From: Ryan Enns
Subject: Transpose #sq(CSVIdentityNameList ())#

Hi Henk, 

Sorry, this may be a noob question but I'm looking for this exact solution and am unable to make it work.  When I try this:

select IdName
from
values (
#sq(CSVIdentityNameList ('),('))#
 ) T(IdName)

I get this error:

XQE-V5-0017 V5 syntax error found for data item 'Testing1' of query 'Query1', invalid token "IdName" found after "select ".

What is "IdName" supposed to reference?

------------------------------
Ryan Enns

Original Message:
Sent: Mon January 08, 2024 12:06 PM
From: HENK CAZEMIER
Subject: Transpose #sq(CSVIdentityNameList ())#

Hello Udaya,
You can use a CognosSQL statement that uses a row value constructor.  E.g.

select IdName
from
values (
#CSVIdentityNameList ('),(')#
 ) T(IdName)

The macro will return something like     'a'),('b'),('c'
This will then be surrounded by ( ) tokens, so you end up with 
           select IdName from values ('a'),('b'),('c') T(IdName)

Cognos SQL needs a database connection, you can use any of the ones that the Data Module or FM Package is pointing to.

------------------------------
Kind regards,
Henk Cazemier

Original Message:
Sent: Fri January 05, 2024 10:16 AM
From: Udaya Rani Palli
Subject: Transpose #sq(CSVIdentityNameList ())#

Hello, 

Has anyone attempted to transpose the results of #sq(CSVIdentityNameList ())#

Example the macro results will be something like  'Admin Administrator', 'Admin Project Member', 'Admin Shared Files Access', 'Admin Shared Files Developer', 'All Authenticated Users', 'Analytics Users' Can that be transposed as

Admin Administrator
Admin Project Member
Admin Shared Files Access
Admin Shared Files Developer
All Authenticated Users
Analytics Users

Thank you,
Udaya.

------------------------------
Udaya Rani Palli
------------------------------

No, that still produces the same error.

If I just do this, I still get the same error:

#CSVIdentityNameList ()#

But if I include the sq() call, it works (non-transposed, of course):

#sq(CSVIdentityNameList ())#

------------------------------
Ryan Enns

Original Message:
Sent: Mon January 06, 2025 03:57 PM
From: HENK CAZEMIER
Subject: Transpose #sq(CSVIdentityNameList ())#

Hi Ryan,

It seems that you added an sq(  ) call in the macro expression. When you remove that call, does it then work properly?

------------------------------
Kind regards,
Henk Cazemier

Original Message:
Sent: Thu December 19, 2024 02:46 PM
From: Ryan Enns
Subject: Transpose #sq(CSVIdentityNameList ())#

Hi Henk, 

Sorry, this may be a noob question but I'm looking for this exact solution and am unable to make it work.  When I try this:

select IdName
from
values (
#sq(CSVIdentityNameList ('),('))#
 ) T(IdName)

I get this error:

XQE-V5-0017 V5 syntax error found for data item 'Testing1' of query 'Query1', invalid token "IdName" found after "select ".

What is "IdName" supposed to reference?

------------------------------
Ryan Enns

Original Message:
Sent: Mon January 08, 2024 12:06 PM
From: HENK CAZEMIER
Subject: Transpose #sq(CSVIdentityNameList ())#

Hello Udaya,
You can use a CognosSQL statement that uses a row value constructor.  E.g.

select IdName
from
values (
#CSVIdentityNameList ('),(')#
 ) T(IdName)

The macro will return something like     'a'),('b'),('c'
This will then be surrounded by ( ) tokens, so you end up with 
           select IdName from values ('a'),('b'),('c') T(IdName)

Cognos SQL needs a database connection, you can use any of the ones that the Data Module or FM Package is pointing to.

------------------------------
Kind regards,
Henk Cazemier

Original Message:
Sent: Fri January 05, 2024 10:16 AM
From: Udaya Rani Palli
Subject: Transpose #sq(CSVIdentityNameList ())#

Hello, 

Has anyone attempted to transpose the results of #sq(CSVIdentityNameList ())#

Example the macro results will be something like  'Admin Administrator', 'Admin Project Member', 'Admin Shared Files Access', 'Admin Shared Files Developer', 'All Authenticated Users', 'Analytics Users' Can that be transposed as

Admin Administrator
Admin Project Member
Admin Shared Files Access
Admin Shared Files Developer
All Authenticated Users
Analytics Users

Thank you,
Udaya.

------------------------------
Udaya Rani Palli
------------------------------

Hi Ryan,

Using #CSVIdentityNameList ()# will not work
but what about when it is
#CSVIdentityNameList ('),(')#
If that produces an error, what is the message that you see?
If it doesn't please let me know and we can jump on a call and see if we can figure out how to make it work with your scenario.

No, that still produces the same error.

If I just do this, I still get the same error:

#CSVIdentityNameList ()#

But if I include the sq() call, it works (non-transposed, of course):

#sq(CSVIdentityNameList ())#

------------------------------
Ryan Enns

Original Message:
Sent: Mon January 06, 2025 03:57 PM
From: HENK CAZEMIER
Subject: Transpose #sq(CSVIdentityNameList ())#

Hi Ryan,

It seems that you added an sq(  ) call in the macro expression. When you remove that call, does it then work properly?

------------------------------
Kind regards,
Henk Cazemier

Original Message:
Sent: Thu December 19, 2024 02:46 PM
From: Ryan Enns
Subject: Transpose #sq(CSVIdentityNameList ())#

Hi Henk, 

Sorry, this may be a noob question but I'm looking for this exact solution and am unable to make it work.  When I try this:

select IdName
from
values (
#sq(CSVIdentityNameList ('),('))#
 ) T(IdName)

I get this error:

XQE-V5-0017 V5 syntax error found for data item 'Testing1' of query 'Query1', invalid token "IdName" found after "select ".

What is "IdName" supposed to reference?

------------------------------
Ryan Enns

Original Message:
Sent: Mon January 08, 2024 12:06 PM
From: HENK CAZEMIER
Subject: Transpose #sq(CSVIdentityNameList ())#

Hello Udaya,
You can use a CognosSQL statement that uses a row value constructor.  E.g.

select IdName
from
values (
#CSVIdentityNameList ('),(')#
 ) T(IdName)

The macro will return something like     'a'),('b'),('c'
This will then be surrounded by ( ) tokens, so you end up with 
           select IdName from values ('a'),('b'),('c') T(IdName)

Cognos SQL needs a database connection, you can use any of the ones that the Data Module or FM Package is pointing to.

------------------------------
Kind regards,
Henk Cazemier

Original Message:
Sent: Fri January 05, 2024 10:16 AM
From: Udaya Rani Palli
Subject: Transpose #sq(CSVIdentityNameList ())#

Hello, 

Has anyone attempted to transpose the results of #sq(CSVIdentityNameList ())#

Example the macro results will be something like  'Admin Administrator', 'Admin Project Member', 'Admin Shared Files Access', 'Admin Shared Files Developer', 'All Authenticated Users', 'Analytics Users' Can that be transposed as

Admin Administrator
Admin Project Member
Admin Shared Files Access
Admin Shared Files Developer
All Authenticated Users
Analytics Users

Thank you,
Udaya.

------------------------------
Udaya Rani Palli
------------------------------

Ah sorry, you're right.  Ok, when I use this:

#CSVIdentityNameList ('),(')#

I get this message:

XQE-V5-0017 V5 syntax error found for data item 'Cognos User Roles' of query 'Query1', invalid token ")" found after "'All Authenticated Users'".

Where 'All Authenticated Users' is the first result in the CSVIdentityNameList.  It follows with 'AllAuthenticatedUsers'),('All_Cognos_Users'),('AnalysisUsers'), etc.

Hi Ryan,

Using #CSVIdentityNameList ()# will not work
but what about when it is
#CSVIdentityNameList ('),(')#
If that produces an error, what is the message that you see?
If it doesn't please let me know and we can jump on a call and see if we can figure out how to make it work with your scenario.

No, that still produces the same error.

If I just do this, I still get the same error:

#CSVIdentityNameList ()#

But if I include the sq() call, it works (non-transposed, of course):

#sq(CSVIdentityNameList ())#

------------------------------
Ryan Enns

Original Message:
Sent: Mon January 06, 2025 03:57 PM
From: HENK CAZEMIER
Subject: Transpose #sq(CSVIdentityNameList ())#

Hi Ryan,

It seems that you added an sq(  ) call in the macro expression. When you remove that call, does it then work properly?

------------------------------
Kind regards,
Henk Cazemier

Original Message:
Sent: Thu December 19, 2024 02:46 PM
From: Ryan Enns
Subject: Transpose #sq(CSVIdentityNameList ())#

Hi Henk, 

Sorry, this may be a noob question but I'm looking for this exact solution and am unable to make it work.  When I try this:

select IdName
from
values (
#sq(CSVIdentityNameList ('),('))#
 ) T(IdName)

I get this error:

XQE-V5-0017 V5 syntax error found for data item 'Testing1' of query 'Query1', invalid token "IdName" found after "select ".

What is "IdName" supposed to reference?

------------------------------
Ryan Enns

Original Message:
Sent: Mon January 08, 2024 12:06 PM
From: HENK CAZEMIER
Subject: Transpose #sq(CSVIdentityNameList ())#

Hello Udaya,
You can use a CognosSQL statement that uses a row value constructor.  E.g.

select IdName
from
values (
#CSVIdentityNameList ('),(')#
 ) T(IdName)

The macro will return something like     'a'),('b'),('c'
This will then be surrounded by ( ) tokens, so you end up with 
           select IdName from values ('a'),('b'),('c') T(IdName)

Cognos SQL needs a database connection, you can use any of the ones that the Data Module or FM Package is pointing to.

------------------------------
Kind regards,
Henk Cazemier

Original Message:
Sent: Fri January 05, 2024 10:16 AM
From: Udaya Rani Palli
Subject: Transpose #sq(CSVIdentityNameList ())#

Hello, 

Has anyone attempted to transpose the results of #sq(CSVIdentityNameList ())#

Example the macro results will be something like  'Admin Administrator', 'Admin Project Member', 'Admin Shared Files Access', 'Admin Shared Files Developer', 'All Authenticated Users', 'Analytics Users' Can that be transposed as

Admin Administrator
Admin Project Member
Admin Shared Files Access
Admin Shared Files Developer
All Authenticated Users
Analytics Users

Thank you,
Udaya.

------------------------------
Udaya Rani Palli
------------------------------

Ah sorry, you're right.  Ok, when I use this:

#CSVIdentityNameList ('),(')#

I get this message:

XQE-V5-0017 V5 syntax error found for data item 'Cognos User Roles' of query 'Query1', invalid token ")" found after "'All Authenticated Users'".

Where 'All Authenticated Users' is the first result in the CSVIdentityNameList.  It follows with 'AllAuthenticatedUsers'),('All_Cognos_Users'),('AnalysisUsers'), etc.

Hi Ryan,

Using #CSVIdentityNameList ()# will not work
but what about when it is
#CSVIdentityNameList ('),(')#
If that produces an error, what is the message that you see?
If it doesn't please let me know and we can jump on a call and see if we can figure out how to make it work with your scenario.

No, that still produces the same error.

If I just do this, I still get the same error:

#CSVIdentityNameList ()#

But if I include the sq() call, it works (non-transposed, of course):

#sq(CSVIdentityNameList ())#

------------------------------
Ryan Enns

Original Message:
Sent: Mon January 06, 2025 03:57 PM
From: HENK CAZEMIER
Subject: Transpose #sq(CSVIdentityNameList ())#

Hi Ryan,

It seems that you added an sq(  ) call in the macro expression. When you remove that call, does it then work properly?

------------------------------
Kind regards,
Henk Cazemier

Original Message:
Sent: Thu December 19, 2024 02:46 PM
From: Ryan Enns
Subject: Transpose #sq(CSVIdentityNameList ())#

Hi Henk, 

Sorry, this may be a noob question but I'm looking for this exact solution and am unable to make it work.  When I try this:

select IdName
from
values (
#sq(CSVIdentityNameList ('),('))#
 ) T(IdName)

I get this error:

XQE-V5-0017 V5 syntax error found for data item 'Testing1' of query 'Query1', invalid token "IdName" found after "select ".

What is "IdName" supposed to reference?

------------------------------
Ryan Enns

Original Message:
Sent: Mon January 08, 2024 12:06 PM
From: HENK CAZEMIER
Subject: Transpose #sq(CSVIdentityNameList ())#

Hello Udaya,
You can use a CognosSQL statement that uses a row value constructor.  E.g.

select IdName
from
values (
#CSVIdentityNameList ('),(')#
 ) T(IdName)

The macro will return something like     'a'),('b'),('c'
This will then be surrounded by ( ) tokens, so you end up with 
           select IdName from values ('a'),('b'),('c') T(IdName)

Cognos SQL needs a database connection, you can use any of the ones that the Data Module or FM Package is pointing to.

------------------------------
Kind regards,
Henk Cazemier

Original Message:
Sent: Fri January 05, 2024 10:16 AM
From: Udaya Rani Palli
Subject: Transpose #sq(CSVIdentityNameList ())#

Hello, 

Has anyone attempted to transpose the results of #sq(CSVIdentityNameList ())#

Example the macro results will be something like  'Admin Administrator', 'Admin Project Member', 'Admin Shared Files Access', 'Admin Shared Files Developer', 'All Authenticated Users', 'Analytics Users' Can that be transposed as

Admin Administrator
Admin Project Member
Admin Shared Files Access
Admin Shared Files Developer
All Authenticated Users
Analytics Users

Thank you,
Udaya.

------------------------------
Udaya Rani Palli
------------------------------

Hello Ryan, 

Sorry to jump in late in this discussion. I just copy pasted the sql and it works. Please see the screenshot how I implemented it in a report. 

Ah sorry, you're right.  Ok, when I use this:

#CSVIdentityNameList ('),(')#

I get this message:

XQE-V5-0017 V5 syntax error found for data item 'Cognos User Roles' of query 'Query1', invalid token ")" found after "'All Authenticated Users'".

Where 'All Authenticated Users' is the first result in the CSVIdentityNameList.  It follows with 'AllAuthenticatedUsers'),('All_Cognos_Users'),('AnalysisUsers'), etc.

Hi Ryan,

Using #CSVIdentityNameList ()# will not work
but what about when it is
#CSVIdentityNameList ('),(')#
If that produces an error, what is the message that you see?
If it doesn't please let me know and we can jump on a call and see if we can figure out how to make it work with your scenario.

No, that still produces the same error.

If I just do this, I still get the same error:

#CSVIdentityNameList ()#

But if I include the sq() call, it works (non-transposed, of course):

#sq(CSVIdentityNameList ())#

------------------------------
Ryan Enns

Original Message:
Sent: Mon January 06, 2025 03:57 PM
From: HENK CAZEMIER
Subject: Transpose #sq(CSVIdentityNameList ())#

Hi Ryan,

It seems that you added an sq(  ) call in the macro expression. When you remove that call, does it then work properly?

------------------------------
Kind regards,
Henk Cazemier

Original Message:
Sent: Thu December 19, 2024 02:46 PM
From: Ryan Enns
Subject: Transpose #sq(CSVIdentityNameList ())#

Hi Henk, 

Sorry, this may be a noob question but I'm looking for this exact solution and am unable to make it work.  When I try this:

select IdName
from
values (
#sq(CSVIdentityNameList ('),('))#
 ) T(IdName)

I get this error:

XQE-V5-0017 V5 syntax error found for data item 'Testing1' of query 'Query1', invalid token "IdName" found after "select ".

What is "IdName" supposed to reference?

------------------------------
Ryan Enns

Original Message:
Sent: Mon January 08, 2024 12:06 PM
From: HENK CAZEMIER
Subject: Transpose #sq(CSVIdentityNameList ())#

Hello Udaya,
You can use a CognosSQL statement that uses a row value constructor.  E.g.

select IdName
from
values (
#CSVIdentityNameList ('),(')#
 ) T(IdName)

The macro will return something like     'a'),('b'),('c'
This will then be surrounded by ( ) tokens, so you end up with 
           select IdName from values ('a'),('b'),('c') T(IdName)

Cognos SQL needs a database connection, you can use any of the ones that the Data Module or FM Package is pointing to.

------------------------------
Kind regards,
Henk Cazemier

Original Message:
Sent: Fri January 05, 2024 10:16 AM
From: Udaya Rani Palli
Subject: Transpose #sq(CSVIdentityNameList ())#

Hello, 

Has anyone attempted to transpose the results of #sq(CSVIdentityNameList ())#

Example the macro results will be something like  'Admin Administrator', 'Admin Project Member', 'Admin Shared Files Access', 'Admin Shared Files Developer', 'All Authenticated Users', 'Analytics Users' Can that be transposed as

Admin Administrator
Admin Project Member
Admin Shared Files Access
Admin Shared Files Developer
All Authenticated Users
Analytics Users

Thank you,
Udaya.

------------------------------
Udaya Rani Palli
------------------------------