Best Of

Hi Stefan Batalia,

Forgive me if I've misunderstood but I am making the following assumptions based on what you've written:

1. You have a data category [entity], currently with four members [-,0,8,9].
2. You want to load and display ALL records, regardless of category, at dataload.
3. You want to exclude from view all records which are categorised [8 - Closed by finance] for specific users/profiles.
4. You want to be able to see all records for specific users/profile holders.

With the above assumptions, you can achieve all that you need by creating a parent group to include everything but the excluded member/members as required. 

I do not know your level of BOARD understanding.  Also, I like to provide more complete answers which may help future community members.  for these reasons, I will illustrate the steps required to achieve what I think you're asking.  I do appreciate that you will probably know a number of these steps so please don't be offended 

Using a database security filter and applying a parent entity - One of many methods.

Overview and principle solution

In principle, to apply a security filter on a dataset that does not have the required parent category, we must modify the existing hierarchy.  This is also what Alexander Kappes has outlined in his response; In fact, my response illustrates the steps required to achieve what Alexander summerised - I clearly had way more time to play with, on a Sunday!...I really, really need a life.  Anyway...

Image 1 illustrates a hierarchy where [Record] is the primary dimension and [Record Status] is the categorisation you currently have.  [Record Status Group] is the parent entity and used to hide/show the relevant entities in database security.

Image 1 - Hierarchy to group shown and hidden members:

Application

STEP 1 - Firstly, create the [Record Status] entity - you will have already done this but I've included the step for continuity.  Image 2 shows the 3 principle entities and the members of the [Record Status] entity.  Note, by hiding the [Record Status Group], users would never see this 'helper' entity.

Image 2 - Entities used within Hierarchy shown in Image 1 plus an extended number of members in the [Record Status] Entity to represent changes over successive dataloads.

STEP 2 - Create a parent entity [Record Status Group] - Image 3 shows the members SHOW and HIDE

Image 3 - Record Status Group parent entity members

STEP 3 - Create the Hierarchy as seen in Image 1 using the 'relationship editor'.

STEP 4 - Amend dataload, adjusted to upload the [Record Status Group].  The process could be applying a CASE statement in your SQL routine, using BOARD ETL processes or a data table as seen in Image 4.

Image 4 - Data table including the parent entity members mapped to easch record and record status.

STEP 5a - Application of [Record Status Group] for filtering out hidden members.

Select a User or profile to apply the filtered selection to - Image 5 shows the steps to apply the selection to a profile.

Image 5 - Applying the filtered selection in database security agailst a profile.  Note the filter symbol next to the Profile name.  This is the only indicator a developer has that a filter is being used.  The developer must investigate to establish what the entity and settings are.  There is an alternative approach outlined in STEP 5b.

STEP 6 - Construct a screen to use the filter. 

Image 6 shows the same screen but for two different users; LiteP and Developer.  Note that for the litep user, the parent entity and the main category entity only show what is permitted - entity member 8 is missing (filtered) but no filtering is applied to the Developer's view.

Image 6 - The same screen but for two users; LiteP (filtered) and Developer (Not filtered).

STEP 5b - Custom Selection Script: Aternative Application of [Record Status Group] for filtering out hidden members.

Step 5a is effective in applying a security filter but is hard to see what has been filtered without first opening the filter selection and scanning down it.  An alternative approach is to type in the security filter in the custom selection script area.  The syntax is: [INSTRUCTION] [ENTITY] [OPERATOR] [VALUES] or [Select] [Record Status Group] [=] [SHOW].  Image 7 shows where this script is entered and here is the BOARD manual entry relating to the 'Database security profile concepts'.

Image 7 - Custom Selection Script

Stefan Batalia, I hope that this was what you were looking to achieve?  If it was not, please feel free to contact me and we can discuss the matter further.

Regards,

Paul Wyatt

Hi Annika Fava

You may like to look at this post How to create view with Top X values + 'Others' grouped in one value by Robert Brooke last year.

By analysing Robert's solution, you may find that you can devise a number of different ways of achieving what you want and applying them in more versatile ways.  And, as Ray Martens demonsterates very well, it opens the 'crossview' feature to those not aware or sure of it.

Hope this helps.

Regards,

Paul

Hi C Tnu,

Sorry for providing a BOARD 10 example.  Have you selected 'Single Formula'? This is an extra step required for BOARD 11 that was a default setting on BOARD 10.

AVERAGEIF does work in BOARD 11 in the exact same way as I've shown; I don't think the NEXEL engine has changed, just the BOARD interface to access it.  

To avoid any confusion, here are the steps to enter the formula you require in BOARD 11.x:

1.   Select the dataview to activate it.

-----------------------------------------------------

2.   Select FX to access Nexel

-----------------------------------------------------

3.   Select [+BLOCK] to add a new block

-----------------------------------------------------

4.   Enter a Block Heading and select 'Single formula' 

------------------------------------------------------

5.   Double click in first cell of new block (column) to enable the formula entry area: 

------------------------------------------------------

6.   To enter the formula for an 'averageif', type as follows:

      a.   AVERAGEIF(

      b.   Select first column of range: [@b;*;*]

      c.   Enter colon symbol - ':'

      d.  Select last column of range: [@d;*;*]

      e.   Enter a single comma ','

      f.   Enter condition in double quotes: "<>0"

      g.   Close bracket: ')'

      h.   Select the green TICK mark to commit formulation.

If you were looking for an average of three row values but only where a value exists, your formula should now look like the line shown below, in the FX area

Let me know how you get on.

Regards,

Paul

Hi, I know I'm quite late to your query so first, can I ask, is this still something you are looking at doing?  I have created a process to carry out this task as we have a user who is required to create property 'catalogues' where a list of property references (entity members between 5 to 400) is provided, in a specific order.  This list must be pasted from excel and into a dataview which, with a procedure, is then used as a valid selection to create a pdf 'catalogue' of properties (one each to a page and including photo, financial breakdown and lease details) in the given order.  I am presently considering a suite of tutorial videos to capture all of what I've learnt on BOARD over the last year and sharing them on this forum and I think this one could be a priority.

Let me know what you think. 

Thanks Björn.  What a simply BRILLIANT answer - I would never have considered simply using a block reference on its own.  Your answer also emphasised the need to fully understand what an MXC cube does with the figures (context) over a simple COUNT cube.  This is why I raised the question, to identify the varying properties of each method and so understand their strengths and weaknesses.

Can I expand on my question and ask you if you know a method to TOTAL the sub-totals as can be achieved with the algorithm (rt(a) in a COUNT Cube dataview (DV)?

In the image below, the DV using the COUNT cube can also be summarised so that the totals can be seen as the first column.  This is achieved with the algorithm rt(b).  This cannot be done with the MXC Cube DV and so the only way to see the grand totals is to use the native total column to the end of the DV - which I've greyed out.  The reason for greying the totals column is that I expressly need the totals column as the first one and I do not want to use 2 separate DVs which must then be carefully aligned to 'appear' as one DV.

.

I see another dev practice change with the imminent release of 10.5.0 which is the loss of multiple sessions with the same login credentials when using desktop. I will really miss this feature as to be able to log into several models and make comparisons simultaneously is a fantastic feature. To be able to immediately stop what one is developing and log into another model to assist a power user is so useful as I’m less likely to forget where I had gotten to before needing to assist a user. Also, being able to log into the same server after a model crash to check task manager has helped greatly. With 10.5.0, one will have to either save their progress to open the relevant model, use support software like gotoassist, or use another licence for accessing the same server; who’s company will give a developer 2 if those rather expensive DEV licenses for each server?. I am quite upset at this as I see it to be a backward step in versatility and a little erosion to BOARDs USP.