Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello everyone,
I have a question and I think it should be very simple.
I am developing a dashboard based on a Navision database.
Some settings for the dashboard I place in an Excel file which I then load up in the script.
In the Excel file I have 3 columns ID, Name and Value.
The ID = 4, Name = InternalTTCodes, Values = TT115, TT330, TT365, TT400 etc.
In the script I created a variable with a Peek function;
LET vInternalTT = Peek('ExcelSetupValue', 4, 'ExcelSetup');
When I created a textbox with the variable like this = vInternalTT I retrieve the values.
Ony When I created a Set Analysis like this;
=Sum({<ReasonCode = {'$(vInternalTT)'}>} VESalesAmountActual)
I retrieve the value 0.
Can some help me with this issue.
Thank you in advance.
I would use bookmarks where at all possible. These are very easy for users to work with. When enabled on the server it's also possible to let users create and share bookmarks. Using bookmarks may not always be possible, like where you need set expressions to specifically exclude/include values independent of selections. Using an external file to create variables will work. It's up to you to decide if it's worth the effort or if manually adding the variables and modify the expressions is less work.
With regards your load script. You could use an if statement to leave out the InternalReasonCodes table:
Setup:
LOAD
ID,
Setup,
if(ID=4,Chr(39) & Replace(Replace(Value,' ',''),',',Chr(39) &','&Chr(39)) & Chr(39),Value) as Value
FROM
[Control File .xlsx]
(ooxml, embedded labels, table is Setup);
LET vInternalTT = Peek('Value', 4, 'Setup');
Does the ReasonCode field also contain comma separated values? Are you expecting ReasonCode = {'TT115, TT330, TT365, TT400'} or ReasonCode ={'TT115', 'TT330', 'TT365', 'TT400'}? The first is what you're getting. If you need the second you could split the Values field with the subfield function so you get a record for every value in the string and rename it to ReasonCode so it's associated the the other ReasonCode field. You can then use ID and Name in your set analysis expression instead to select the correct ReasonCodes.
Gysbert,
In the Navision database the values are seperated per row.
The table in NAV looks like this;
Code
TT115
TT330
TT456
Etc.
I created a seperated Excel sheet where the customer can add specific settings. One of the settings are to indicated what kind off Reason Codes are Internal codes.
The Excel file looks like
ID Name Value
4 InternalTTCodes TT115, TT330, TT456, Etc.
In the load script I use the Peek function to fill the the variable like this
LET vInternalTT = Peek('ExcelSetupValue', 4, 'ExcelSetup');
In a chart expression I want to use the Value in a SET ANALYSIS like this.
=Sum({<ReasonCode = {'$(vInternalTT)'}>} VESalesAmountActual)
How can I ensure that I can use the external excel setup file where I can fill in the reason codes and retrieve them through a variable.
Thx for your help
Jeroen,
I think I understand your setup now. You need the codes in the Value field in the excel sheet enclosed in single quotes. One way is to make the users put in the single quotes. Another way is to put in the quotes in the script
chr(39) & replace(replace(Value,' ',''),',', chr(39)&','&chr(39)) & chr(39) as Value. This is a more user friendly option.
You also need to remove the quotes in the set expression:
=Sum({<ReasonCode = {$(vInternalTT)}>} VESalesAmountActual)
Gysbert,
Thx for your help.
I add a sample Excel and QVW file where I used your suggestion.
Can you check if there is a better way to do like I did?
Thx again for the help
I would use bookmarks where at all possible. These are very easy for users to work with. When enabled on the server it's also possible to let users create and share bookmarks. Using bookmarks may not always be possible, like where you need set expressions to specifically exclude/include values independent of selections. Using an external file to create variables will work. It's up to you to decide if it's worth the effort or if manually adding the variables and modify the expressions is less work.
With regards your load script. You could use an if statement to leave out the InternalReasonCodes table:
Setup:
LOAD
ID,
Setup,
if(ID=4,Chr(39) & Replace(Replace(Value,' ',''),',',Chr(39) &','&Chr(39)) & Chr(39),Value) as Value
FROM
[Control File .xlsx]
(ooxml, embedded labels, table is Setup);
LET vInternalTT = Peek('Value', 4, 'Setup');
Gysbert,
Thanks again for your tremendous help!!