Vertical Tables

[RobertTimm]

Hello Community -

I'm trying to recreate a table with the field headers on the side like the example shown.  I would only be displaying one row in the table, too.

 

If not, are there any other solutions to display data in a form format (fields on top of each other) rather than a horizontal table?  It doesn't appear I can use text boxes to display fields/dimensions, other than measures.  

Thanks for the help!

- Rob -

[GaryGiles · Accepted solution]

You try using the ValueList(), Pick() and Match() fuctions.

In a table chart, add a Dimension with the following defintion.

ValueList('Bus. Rep.','Claims Reps.','Status','Signing Local','Work Date')

(You will need to add the rest of the rows that you want to show.

Then, add a measure with something like the following:

Pick(Match(ValueList('Bus. Rep.','Claims Reps.','Status','Signing Local','Work Date')),

'Bus. Rep.','Claims Reps.','Status','Signing Local','Work Date',

Only({$<[Claim ID]={'$(vClaimId)'}>} [Bus Rep]),
Only({$<[Claim ID]={'$(vClaimId)'}>} [Claims Rep]),
Expression of Status,
Expression for Signing Local,
Expression for Work Date
)

The Pick and Match functions allow you to define an an aggregate measure to return for each corresponding row of the ValueList.  (Hint, you can create a variable with the ValueList members to make it easier.)

The  trick is that the expressions have to be lined up in the same order as the ValueList and the expression must return a single value.  I put an example in of how the [Bus Rep] field might be rendered using a variable in set analysis.  If a '-' value shows up, it usually means that your expression returned multiple values.  You can use Concat if you want multiple string values to come back as one string.

To top it off, you can add the following to the background color expression of the dimension and measure to give a striping effect:

=if(odd(rowno()),'#e9e7e6')

Hope that helps.

[RobertTimm]

Thanks Gary!  I can definitely work with this solution.  Appreciate it.

Here's my working QS example now: 

[uashbjo]

Hi, I tried to use the solution but I get "Error in expression: Match takes at least 2 parameters".  @GaryGiles to you have an example of the code you used to make it work?

Thank you!

[RobertTimm]

@uashbjo Here is one example:

Dimension:

=ValueList('Performers','Active Members','Members in Good Standing')

Measure:

=Pick(Match(ValueList('Performers','Active Members','Members in Good Standing')
,'Performers','Active Members','Members in Good Standing')
,count ([IDN])
,count({< [Class Desc] = {"Member"}>} [IDN])
,count({<[Good Standing]={"Good Standing"}>}[IDN])
)

[neerajthakur]

Remember it will work only if there is one record for each of the ID, if not it will just show top 1 record.

[uashbjo]

Thank you for the reply; very appreciated.