Code snippets for user interaction during scripts

[bernatagulloesbrina]

Hi,

I have some doubts on how to make the actions of my scripts more customizable by the end users during runtime to avoid setting parameters by hand

I'd like to have a way to:

Yes / No question returning a boolean to a variable
Make the user choose among an arbitrary list I show
Multiple selection for the above, as well as Table and Column Selection
Ways to filter Table and Column selection interfaces before showing them to the user

This would make a great section in Tabular Editor 3 documentation

Regards!

[samaguire]

Some documentation around the SelectTable(), SelectColumn(), SelectMeasure(), Info(), Warning(), Error() functions would be great!

I've managed to discover that you can feed the 'select' functions collections (which can be pre-filtered) which is very helpful. 👍

Also, native functions for 'Input' and 'Question' would round this off nicely - saving us resorting to calling VB or create our own and would keep the look and feel of the selected TE3 skin/theme.

[bernatagulloesbrina]

Awesome! Do you have any example of code with custom collections of tables, columns or measures? I'm not that good in C# send will save me lots of Googling Regards! El jue., 7 oct. 2021 5:50, Stephen Maguire ***@***.***> escribió:

…

Some documentation around the SelectTable(), SelectColumn(), SelectMeasure(), Info(), Warning(), Error() functions would be great! I've managed to discover that you can feed the 'select' functions collections (which can be pre-filtered) which is very helpful. 👍 Also, native functions for 'Input' and 'Question' would round this off nicely - saving us resorting to calling VB or create our own and would keep the look and feel of the selected TE3 skin/theme. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub <#240 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AJUUXHN5VV6BKJNBLPUK2V3UFUKINANCNFSM5FHUM2XA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[samaguire]

Do you have any example of code with custom collections of tables, columns
or measures?

Yes, I do! I'll link when I've finished my script - just debugging ATM.

[samaguire]

@bernatagulloesbrina
As promised. Have a look at this script https://gist.github.com/samaguire/925a261ab5fcb86c944196b4c316e116 (inspired by yourself https://www.linkedin.com/posts/stephenamaguire_c-script-for-use-in-tabular-editor-3213-activity-6851773470938943488--Pnm)

BTW, I'm self-taught C# (and very beginner). So, understand where you're at.

The below snippet uses LINQ lambda expression to filter all model tables to just those that are calculation group tables (specifically the part .Where(x => x.ObjectType == (ObjectType.CalculationGroupTable))), pushing the filtered collection to the inbuilt (thanks @otykier 👍) SelectTable() method.

// Get calculation group table

var ts = Model.Tables.Where(x => x.ObjectType == (ObjectType.CalculationGroupTable));

var t = null as CalculationGroupTable;

if (ts.Any())
{

    t = SelectTable(ts, label:"Select calculation group table:") as CalculationGroupTable;

    if (t == null) { return; } // doesn't work in TE3 as cancel button doesn't return null in TE3

}
else
{

    Error("No calculation group tables in the model.");

}

You can find another example of this in my DAX formatting script (https://gist.github.com/samaguire/e8f60ea57e967070a5325559ae245f9d) where I've filtered all columns to those that are calculated columns by using two lambda expressions: the first to convert the items in the 'AllColumns' collection to the object type 'CalculatedColumn' and the second to remove empty objects in the collection (i.e. those that didn't convert to a CalculatedColumn object type).

foreach (var c in Model.AllColumns.Select(x => x as CalculatedColumn).Where(x => x != null)) { FormatDax(c); }

All immensely powerful stuff once you get the hang of it!

References
https://docs.tabulareditor.com/te2/Advanced-Scripting.html#working-with-the-selected-object
https://docs.microsoft.com/en-us/dotnet/csharp/linq/query-expression-basics
https://www.tutorialspoint.com/linq/linq_lambda_expressions.htm

[bernatagulloesbrina]

Well done! If you find a way to select among an arbitrary list of strings /array, please do let me know Thank you! El jue., 7 oct. 2021 9:02, Stephen Maguire ***@***.***> escribió:

…

@bernatagulloesbrina <https://github.com/bernatagulloesbrina> As promised. Have a look at this script (inspired by yourself https://www.linkedin.com/posts/stephenamaguire_c-script-for-use-in-tabular-editor-3213-activity-6851773470938943488--Pnm) https://gist.github.com/samaguire/925a261ab5fcb86c944196b4c316e116 BTW, I'm self-taught C# (and very beginner). So, understand where you're at. The below snippet uses LINQ lambda expression to filter all model tables to just those that are calculation group tables (specifically the part .Where(x => x.ObjectType == (ObjectType.CalculationGroupTable))), pushing the filtered collection to the inbuilt (thanks @otykier <https://github.com/otykier> 👍) SelectTable() method. // Get calculation group table var ts = Model.Tables.Where(x => x.ObjectType == (ObjectType.CalculationGroupTable)); var t = null as CalculationGroupTable; if (ts.Any()) { t = SelectTable(ts, label:"Select calculation group table:") as CalculationGroupTable; if (t == null) { return; } // doesn't work in TE3 as cancel button doesn't return null in TE3 } else { Error("No calculation group tables in the model."); } You can find another example of this in my DAX formatting script ( https://gist.github.com/samaguire/e8f60ea57e967070a5325559ae245f9d) where I've filtered all columns to those that are calculated columns by using two lambda expressions: the first to convert the items in the 'AllColumns' collection to the object type 'CalculatedColumn' and the second to remove empty objects in the collection (i.e. those that didn't convert to a CalculatedColumn object type). foreach (var c in Model.AllColumns.Select(x => x as CalculatedColumn).Where(x => x != null)) { FormatDax(c); } All immensely powerful stuff once you get the hang of it! References https://docs.tabulareditor.com/te2/Advanced-Scripting.html#working-with-the-selected-object https://docs.microsoft.com/en-us/dotnet/csharp/linq/query-expression-basics https://www.tutorialspoint.com/linq/linq_lambda_expressions.htm — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#240 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AJUUXHIKQVKW3GYIUPOIGLDUFVAZZANCNFSM5FHUM2XA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

[samaguire]

You can do this by manipulating a measure collection before sending it to the SelectMeasure().

Unfortunately, the object constructors seem to be hidden from the running script, so you're forced to add measures to an existing table (not a new table for compatibility with PBI Desktop). Not ideal. I would prefer not to manipulate the model.

var t = new Table(); results in this error:

'TabularEditor.TOMWrapper.Table.Table(Microsoft.AnalysisServices.Tabular.Table, TabularEditor.TOMWrapper.TabularModelHandler)' is inaccessible due to its protection level

@otykier may be able to suggest another method?

List<string> list = new List<string>
{

    "123", "abc", "456", "def", "789", "ghi"

};

if (!Model.Tables.Any())
{

    Error("No tables in the model.");

    return;

}

var t = Model.Tables.First();

foreach (var l in list) { t.AddMeasure(l); }

var r = SelectMeasure(t.Measures.Where(x => list.Any(y => x.Name == y)), label:"Select list item:");

string result = null;

if (r != null ) { result = r.Name; } else { result = null; }

foreach (var l in list) { t.Measures[l].Delete(); }

Output(result);

[otykier]

Until we have a standard way for prompting the user to select a value, you can use the snippet below:

using System.Windows.Forms;

// Code that defines a local function "SelectString", which pops up a listbox allowing the user to select a 
// string from a number of options:
Func<IList<string>, string, string> SelectString = (IList<string> options, string title) =>
{
    var form = new Form();
    form.Text = title;
    var buttonPanel = new Panel();
    buttonPanel.Dock = DockStyle.Bottom;
    buttonPanel.Height = 30;
    var okButton = new Button() { DialogResult = DialogResult.OK, Text = "OK" };
    var cancelButton = new Button() { DialogResult = DialogResult.Cancel, Text = "Cancel", Left = 80 };
    var listbox = new ListBox();
    listbox.Dock = DockStyle.Fill;
    listbox.Items.AddRange(options.ToArray());
    listbox.SelectedItem = options[0];

    form.Controls.Add(listbox);
    form.Controls.Add(buttonPanel);
    buttonPanel.Controls.Add(okButton);
    buttonPanel.Controls.Add(cancelButton);

    var result = form.ShowDialog();
    if(result == DialogResult.Cancel) return null;
    return listbox.SelectedItem.ToString();
};

// To use, simply call SelectString as shown below:
var select = SelectString(new[]{"hello", "abc", "123"}, "Choose a value");
if(select == null)
    Info("You cancelled!");
else
    Info("You selected: " + select);

[bernatagulloesbrina]

Awesome, thanks! El vie, 8 oct 2021 a las 2:14, Daniel Otykier ***@***.***>) escribió:

…

Until we have a standard way for prompting the user to select a value, you can use the snippet below: using System.Windows.Forms; // Code that defines a local function "SelectString", which pops up a listbox allowing the user to select a // string from a number of options:Func<IList<string>, string, string> SelectString = (IList<string> options, string title) => { var form = new Form(); form.Text = title; var buttonPanel = new Panel(); buttonPanel.Dock = DockStyle.Bottom; buttonPanel.Height = 30; var okButton = new Button() { DialogResult = DialogResult.OK, Text = "OK" }; var cancelButton = new Button() { DialogResult = DialogResult.Cancel, Text = "Cancel", Left = 80 }; var listbox = new ListBox(); listbox.Dock = DockStyle.Fill; listbox.Items.AddRange(options.ToArray()); listbox.SelectedItem = options[0]; form.Controls.Add(listbox); form.Controls.Add(buttonPanel); buttonPanel.Controls.Add(okButton); buttonPanel.Controls.Add(cancelButton); var result = form.ShowDialog(); if(result == DialogResult.Cancel) return null; return listbox.SelectedItem.ToString(); }; // To use, simply call SelectString as shown below:var select = SelectString(new[]{"hello", "abc", "123"}, "Choose a value");if(select == null) Info("You cancelled!");else Info("You selected: " + select); — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#240 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AJUUXHMFK54WOTKVZJKIKVDUFYZX3ANCNFSM5FHUM2XA> . Triage notifications on the go with GitHub Mobile for iOS <https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675> or Android <https://play.google.com/store/apps/details?id=com.github.android&referrer=utm_campaign%3Dnotification-email%26utm_medium%3Demail%26utm_source%3Dgithub>.

-- Bernat Agulló Partner & Data Enthusiast ***@***.*** +34 637 93 42 76 Balmes, 177 08006 Barcelona

[samaguire]

@otykier Do we use the new SelectObject (v3.1.6) like we do with SelectString above?

SelectString(new[]{"hello", "abc", "123"}, "Choose a value")

[otykier]

@samaguire here are the signatures of all currently available methods:

// Generic methods:
T SelectObject<T>(this IEnumerable<T> columns, T preselect = null, string label = "Select object:")
    where T : TabularNamedObject;
IList<T> SelectObjects<T>(this IEnumerable<T> objects, IEnumerable<T> preselect = null, string label = "Select object(s):")
    where T : TabularNamedObject;

// Specific methods:
Table SelectTable(Table preselect = null, string label = "Select table:");
Table SelectTable(this IEnumerable<Table> tables, Table preselect = null, string label = "Select table:");
Column SelectColumn(this Table table, Column preselect = null, string label = "Select column:");
Column SelectColumn(this IEnumerable<Column> columns, Column preselect = null, string label = "Select column:");
Measure SelectMeasure(Measure preselect = null, string label = "Select measure:");
Measure SelectMeasure(this Table table, Measure preselect = null, string label = "Select measure:");
Measure SelectMeasure(this IEnumerable<Measure> measures, Measure preselect = null, string label = "Select measure:");

The difference between SelectObject and SelectObjects is that the latter allow the user to select multiple (or zero) objects in the list. The method returns null if the user cancelled out of the dialog.