Format all columns in a table based on field name instead of field value in Dashboard Studio

[Ariane]

I have a table in Dashboard Studio that shows percentage values, where the rows are a client code, and the columns are the values of the the field "username". How do I set the column format based on the field name ("username") instead of the field value ("Jenny Staffmember")? 

I want to add units, as well as colour formatting of the text, and by default it's done by column label, which is the value of the field it's split by, instead of the name of the field, which results in a lot of repeated config which needs to be updated every time a new user turns up. 

[Ariane]

Here is a second attempt at providing more information. I've reduced some of the duplication by using a single ColumnFormatEditorConfig, but I can't work out how to identify multiple columns in one columnFormat definition.

Following is the code as it stands. 

"viz_cost_detail": {

"type": "splunk.table",

"dataSources": {

"primary": "ds_cost_detail"

},

"title": "Cost detail by Client for $client_filter$",

"options": {

"headerVisibility": "fixed",

"columnFormat": {

"Task": {

"width": 428

},

"Person1": {

"data": "> table | seriesByName(\"Person1\") | formatByType(Team_MemberColumnFormatEditorConfig)"

},

"Person2": {

"data": "> table | seriesByName(\"Person2\") | formatByType(Team_MemberColumnFormatEditorConfig)"

}

},

"count": 120

},

"context": {

"Team_MemberColumnFormatEditorConfig": {

"number": {

"thousandSeparated": false,

"unitPosition": "after",

"precision": 1,

"unit": "hrs"

}

}

},

"description": "Uses time range and Client inputs"

}

},

"ds_cost_detail": {

"type": "ds.search",

"options": {

"queryParameters": {

"earliest": "$time_filter.earliest$",

"latest": "$time_filter.latest$"

},

"query": "| datamodel Data_Model wtf search \n| search wtf.client_code=\"$client_filter$\"\n| chart sum(wtf.time_worked) as hours by wtf.task_name wtf.username \n| rename wtf.task_name as Task \n| addtotals row=true col=true\n\n"

},

"name": "client_cost_detail"

}

},

Essentially I'm looking for the equivalent of a default entry for the column format section, so that I don't have to manually repeat this entry: 

"Person2": {

"data": "> table | seriesByName(\"Person2\") | formatByType(Team_MemberColumnFormatEditorConfig)"

}

for every single staff member, and every time a new staff member comes on board, which is unworkable. Any insight is greatly appreciated, thanks. 

[Ariane]

This is another use case for the same issue, with a simpler search

[ITWhisperer]

I am not sure there is a way to do this in Dashboard Studio (yet). Try using SimpleXML / Classic dashboards

(I don't use Studio for anything serious as it is not good enough for my needs.)

[Ariane]

OK, thanks. For my purposes, the things DS does better are also important (among other things, I need to be able to format the UI so that it's useable by non-technical, non-Splunk people, which means explanatory text, carefully managed layout etc). I knew DS was still kinda in the dev stage, but confess I thought it had got a little further along than this. I'm genuinely not sure what course I should take, but that's not a question for a community forum. Thanks for your response!

[ITWhisperer]

There is quite a lot you can do with Classic by using CSS and HTML panels, so I guess it depends on how urgent your need is. 😎

[Ariane]

This is the search:

index="myindex" sourcetype="timesheets_json" OR sourcetype="tasks_json" work_order="$task_selector$"
| stats values(username) as username values(time_spent) as time_spent values(time_estimate) as estimate values(work_order) as work_order values(list_name) as list_name values(task_name) values(status) as status by task_id
| where ( status="complete" AND estimate>0)
| eval time_perc_est=round((time_spent/estimate)*100), code=substr(list_name,1,9)
| chart avg(time_perc_est) as "Actual vs Estimated Time (%)" by code username useother=f

The data comes from 2 JSON indexes, one looks a bit like this: 
"timestamp": 1689067863, "id": "3578049312326190828", "task_id": "860r9v3p6", "username": "User1", "billable": false, "end": 1689067863, "start": 1689064497, "at": 1689067863, "space_id": "7032145", "list_name": "ABCDEF000 Company Name Pty Ltd", "task_name": "Clear Email", "due_date": 1689012000, "start_date": 1689012000, "date_created": 1688976115, "client_type": "Priority", "pt_support": "Someone", "pt": "Someone Else", "work_order": "Client Comms"

And the other:
"due_date": 1688666400, "start_date": 1688666400, "date_created": 1688545863, "date_done": 1688717285, "time_estimate": 300, "status": "complete", "time_spent": 458, "client_type": "Blah Blah", "pt_lead": "Someone", "pt": "Someone Else", "work_order": "Client Comms", "task_id": "860r9v3p6"

I want to end up with something like this:

Client	User1	User2	User3	…
ABCDEF000	25%	150%
ABCDEF001		75%	300%
…

(Somewhat random colour coding there, but you get the idea). The number of columns, and names of the columns will change over time, obviously (and with different searches). The table is being generated, but I can't work out how to format the columns using the field name ("username"), instead of each column individually ("User1", "User2 etc) which requires a lot of copy/pasta, and ongoing manual maintenance every time a new person is onboarded. 

[Ariane]

Oh, this morning this reply is here! It wasn't yesterday afternoon! 😂

[ITWhisperer]

Please share the search for your table, some anonymised sample data and a visualisation / mock-up of your expected results.

[Ariane]

ARRRGGG!!! 

I wrote a very long reply to this, and posted, and it reported it was posted successfully, but it seems not to have actually done so. 

I'm not ignoring your question, I just don't have the spare time right now to completely reproduce all of that, which was not speedy to produce in the first place. I will come back and try again when I have the time, and am less frustrated.