Sorry but being mostly a backend guy and modeller, I am weak in RS..
I have a Crosstab with one dimension in the rows and 6 measures in the columns.
One user needs to see only 5 of those measures.
So I created a Calculated Measure as:
If (#sq($account.defaultname)# = 'John Doe') then (1) else (0)
It changes the values between and 1 and 0 perfectly depending on who is running the report.
I created a String Variable in Conditional Explorer saying :
If ([Date Item] = 1) then ('Hide') else ('Show')
Now the problem:
The 6th column hides and shows perfectly depending on who ran the report BUT the column header of the 6th column either hides or shows no matter who is logged on.
I specified Style Variable for both column and column header with Show and Hide both selected and Box Type = None for Hide.
So why does the column work but not the header?
I think a render variable is the easiest way to hide/show stuff. I don't know the answer to your specific question, but here's how it can be done with a render variable:
- Create a boolean variable instead of a string variable and use the expression:
#sq($account.defaultname)# = 'John Doe'
- Click on the column to be hidden or shown and use the ancestor button to navigate to List Column
- Put the name of your variable in the render variable property
- No need for the calculated item in the query
Pretty sure that's it...post back if i screwed up :)
It's not a List , it's a Crosstab.
I forgot to add that it works perfectly in a List !
The Boolean variable does not accept the command #sq($account.defaultname)# = 'John Doe'
It accepts a Data Item when you insert that command in a Data Item. Inserting a Data Item in a List works but for a Crosstab, that Data Item containing #sq($account.defaultname)# does not return the name of the user. So I used a Calculated Measure and that returns the name of the User.
Moreover, how do I use a Render Variable only for a Column. That is available only when I select the entire Crosstab.
Which button is the ancestor button?
So I used a Calculated Measure and that returns the name of the User.
I mean I inserted this in a Calculated Measure
If (#sq($account.defaultname)# = 'John Doe') then (1) else (0)
then used this in a String Variable -
If ([Query1].[Data Item] = 1) then ('Hide') else ('Show')
Oh crosstabs are a pain! I will defer to those with greater brains than mine....
Quote from: Lynn on 03 Feb 2011 02:24:52 PM
Oh crosstabs are a pain! I will defer to those with greater brains than mine....
Does such a person even exist?
Nice try MF but it's not gonna work unless you resolve my problem ;)
BTW, the smileys here are horrible >:( sapfans.com has good ones.
The Administrator should put better smileys..
Oh, all right then. I was just running scared from Lynn because I joked about banning her earlier. I was using the old adage "flattery gets you everywhere, especially with the ladies, and especially especially when they are on the warpath!" :D
One quick question - are you using a dimensional or a relational package?
MF.
You are absolutely correct MF ! Flattery does work. I know a girl who once told me that I am not a good guy. When I asked her why she said that, she said that I never praise her !! This is real, no joke ! :)
I am using a dimensional package (Transformer Cube)
Quote from: cognostechie on 03 Feb 2011 04:39:35 PM
You are absolutely correct MF ! Flattery does work. I know a girl who once told me that I am not a good guy. When I asked her why she said that, she said that I never praise her !! This is real, no joke ! :)
I am using a dimensional package (Transformer Cube)
For the record....it wasn't me :)
Hmmm - I was thinking along the lines of an alternate approach whereby your columns were defined as a single query calculation with an expression like
if (#sq($account.defaultName)# = 'John Doe') then (set([Measure 1],[Measure 2])) else(set([Measure 1],[Measure 2],[Measure 3]))
However, it doesn't like my expression. A bit like my daughter when she comes home late! :)
I tried your approach in the shiny new Cognos 10, but I get the same results as you - the column header always appears, but the column body hides and shows as expected.
I also tried
except(set([Measure 1],[Measure 2],[Measure 3]),if (#sq($account.defaultName)# = 'John Doe') then (emptyset([Namespace],[Measures folder])) else (set([Measure 3])))
Sadly this gives me a non-explicit coercion error when referring to the measures folder.
I'm tired and it's late, so I will go to bed now and sleep on it...
MF.
I think it might be a bug that is not fixed yet, like some of my friends..
Sleep tight, appreciate your time and efforts.
I also have to work on something else otherwise I would have tried Conditional Blocks too (to create two crosstabs and hide one of them)..will try tomorrow
Ok, since this is for security, this method works:
Instead of hiding the measure column, zero it out for that user with the same type expression:
If (#sq($account.defaultname)# = 'John Doe') then (0) else ([Fact Table].[Measure])
I think I may know the problem, but haven't had time to test it. Variables on data items only work within the context of the data item.
If the header is out of the context, then the variable won't be able to fire, and it will remain unhidden.
Try doing the following:
Create a new query with that data item. If (#sq($account.defaultname)# = 'John Doe') then (1) else (0)
Drag in a singleton and tie it to that query, and put data item into the properties of the singleton.
Put the crosstab into the singleton and test the report.
Since the crosstab header is now in the context of the data item, it should work.
As a side note, I strongly recommend using zeroing out the measure in any case. If the report is being viewed in HTML, Box Type none will only hide the cell. It will still exist on the page and a nosy user can very easily see it if they use firebug or developer toolbar.