QlikView App - Show and Hide Sheets based on User or Menu

[stevedark]

As QlikView applications grow the number of tabs that information is spread across can grow rapidly as well.   The new Ajax view makes it easier to navigate when there are many tabs (with the drop down) but if users are using the IE Plugin or an older version of QlikView a lot of the screen can be taken up with tabs.

One way of solving this is to group the tabs into functional areas and place a menu on the welcome tab that allows the user to select which functional area they want to look at.

Furthermore, if some tabs are simply not relevant to some users then it is possible to hide those tabs (and the menu options to show them) from those users.

This document gives an example of a menu that switches tabs on and off and implements hiding of tabs from users based on their OSUser name - loaded from an Inline table in the load script.

I hope you find this document useful, you can find links to other documents I have uploaded here: http://www.quickintelligence.co.uk/qlikview-examples/

Steve

http://www.quickintelligence.co.uk/qlikview-blog/

[stevedark]

Hi Omosalewa,

Many thanks for your comments.  You can permanently hide sheets by just setting the show condition to =0, or better still to a variable with a value of zero (so you can toggle it if you need).

To show all hidden objects (including sheets) simply press Ctrl+Shift+S.  Press the same combination to hide again.  Make sure you hide things again before you save, as that setting will be remembered.

You may find that the expression you have for showing and hiding tabs eats a fair chunk of CPU.  It's generally recommended not to use now() in variables.

If you are using this in access point you could use an iFrame and URL parameters (as mentioned in akl 's blog AJAX and URL parameters) to pick each sheet.

A better way of doing a rotating display may be to write HTML, as in this example http://www.quickintelligence.co.uk/write-csv-qlikview-store/ , or use the excellent QlikView NPrinting (which has recently been added to the Qlik suite of tools).

Hope that helps,

Steve

[#3]

stevedark

Thanks Steve. It was very very helpful.

[#4]

stevedark‌

Please, what I'm I doing wrong here?

I wanted this...

and the only time I was able to achieve is when I copy the object from your design to mine but doesn't work when I try to work on a new chart from scratch.

Thanks!

[stevedark]

You need to expand your pivot chart by clicking on the plus symbols.  This should then show you the correct mix of Yes and No values, but in the wrong location.

You then need to drag the Sheet Name dimension up to the top of the pivot.  This can be more fiddly than you would expect!  Left click and hold on the Blanked POs and then drag it to the top of the chart.  When you have the correct position you will see a horizontal blue arrow.  At this point you can release the mouse.

Moving pivot dimensions is something that gets easier with practice...

Hope that helps

Steve

[#6]

Thanks a lot stevedark‌

After so much scuffling and not getting the horizontal blue arrow line, I had to consult the QlikView Reference Manual: Chapter 13 Pivot Table. Realize I had unchecked the Allow Pivoting on the Presentation page in the Properties dialog of my pivot table. Got the checkbox checked and voilà!...I was able to enable "the pivoting of a pivot table" and got what was expecting.

Thanks again!

[stevedark]

Glad you got it working.  I hadn't twigged that allow pivoting may have been off.  The default is for it to be on.

[#8]

Steve - I'm able to follow this easily and implement sheet level access. But there is a security concern with this approach. I think it easy to break when you have permission to add input box with variable of vOSuser., so my recommendation is to have hard-code user-id in the expression instead of using variable 'vOSUser'.

[stevedark]

Hi Laks,

That is a good point.  It's no so much a worry if it is accessed via AccessPoint and editing is locked down.  If a user is in QlikView Desktop then there are many ways they could circumvent the showing and hiding of tabs.  Ctrl+Shift+S, for one.

What I would say is that show/hide conditions should never be used for security. This should only be used to keep a less cluttered environment where things a user doesn't need to see are hidden.  If there are things that they are not allowed to see, then this should be enforced via Section Access - the use of the OMIT column header allowing certain information to be inaccessible to specified users.

Thanks for your comments.

Steve

[NickBentley82]

Thanks for your insights Steve, your blog is of great help. 

Are you able to explain the expression used in the conditional show for the sheets in the example? 

Thanks in advance!

[stevedark]

Hi @NickBentley82 

Thank you for your kind words.

The expression on the sheet show/hide is:

=sum({<SheetName={'Sheet 1'},OSUser={'$(vOSUser)'}>}if(index(vSheetGroup,'G') > 0,SheetAllowed, 0)) > 0

Basically, this will return a 1 or zero, based on whether the sheet group is toggled on or off, or (if security is turned on) whether the user has access.

The reason for the security switch is because in the app I uploaded I couldn't put in usernames of the people downloading, so there needed to be an override.

The main expression works with set analysis, looking for a row in the security table with the users name against it.

The vSheetGroup variable has the initial letter of all sheets currently turned on, so G if just Green is switched on, or RGBY if all sheets are visible. If the letter being checked on the sheet is not in the string the result will be zero, regardless of the security permissions.

The security override switch is powered by the variables vOSUser and vSheetSecurity. The sheet security variable contains either zero or one (on or off), toggled by the button. This is used in the OS User variable, here:

=if(vSheetSecurity = 1, OSUser(), '*')

If security is turned off a star is returned, and this will match in the set analysis with all possible values.

Hope that makes sense?

What is really great news is that show/hide sheets is going to be available in Sense in the next release (hopefully), so this same technique will work there. I may have to do another app to demonstrate it there.

Cheers,
Steve