So I have this situation that I have to exclude certain columns from certain users. So if the report has 17 columns, certain users should see only 10. To make it simple, the users who can see all the columns have been put into one single group (let's say 'PowerGroup')
So I created those columns with Query calculations using this expression:
If ( #sq(CSVIdentityNameList())# contains 'PowerGroup') then ([Measure Name]) else (0)
In the column properties, I set the ' 0 Value ' to be blanks by putting a space in there.
So far so good. Anybody belonging to any other group than 'PowerGroup' sees blanks in those columns.
Now take a step further and try to hide those columns - No success so far.
Create a Conditional Variable with the same type of expression-
If ( #sq(CSVIdentityNameList())# contains 'PowerGroup') then ('Show') else ('Hide')
Does not like it at all so I created a data item and used that expression, then in the Conditional Explorer, I created the variable with (If ([Data Item] = 'Show') then ('Show') else ('Hide').
Does not give any error but does not work, does not Hide the columns at all, behaves the same way for all groups of users (Maybe a feature of 10.1 ?) ..and by the way I am posting this in Cognos 8 forum as the traffic is more here than 10.1 forum so likely to get a reply here ;)
So I tried Conditional Blocks and it tells me to add that Data Item to the property sheet. Now I can't, for the life of me, figure out how to solve this because I added it to the Property sheet of the Crosstab and still it says the same thing ! Can't add it to the property sheet of the Page cause it says 'No Query found...'.
>:(
QuoteNow take a step further and try to hide those columns - No success so far
I could able to hide the column by following the same steps provided by you,
QuoteCreate a Conditional Variable with the same type of expression-
If ( #sq(CSVIdentityNameList())# contains 'PowerGroup') then ('Show') else ('Hide')
However, I have a question i.e. even if you hide the Column from the report layout but still the report query would be considering the column and would include the same in the 'Group by' clause.
Correct me if i am wrong?
Thanks
Prit
Forgot to add...
QuoteHowever, I have a question i.e. even if you hide the Column from the report layout but still the report query would be considering the column and would include the same in the 'Group by' clause.
Instead have two layouts, one with 17 columns & the other with 10 columns.
Use a conditional block with the below expression and associate the reports:
If ( #sq(CSVIdentityNameList())# contains 'PowerGroup')
then ('17 columns')
else ('10 columns')
Does it make sense?
Thanks
Prit
Well, that is the whole problem. Logically, it makes sense, does not give any error but it either hides or does not hide no matter who the user is. So the security logic does not work for Conditional Blocks.
Did you try it?
Anyway, for now, I am going the route of showing blanks in the columns instead of hiding it.
Quotedoes not give any error but it either hides or does not hide no matter who the user is. So the security logic does not work for Conditional Blocks.
Did you try it?
Well, correctly said. It doesn't work efficiently with Conditional blocks though, but we can do it with Page rendering option. Means, we can have 2 pages with '10 Columns' & '17 Columns' and render it based on the user login.
However, I have attached the XML which worked fine for me.
Hope will do?
Thanks
Prit
Oh ! Thanks a lot ! Will try it.
Doesn't work in 10.1 but works in 8.4 !
By the way, to answer your earlier question, yes, even if it would have hidden the column, it would still be part of the query and in the Group By clause .
QuoteDoesn't work in 10.1 but works in 8.4 !
Oops! Not yet moved to 10.1 though...... Is it worth creating a ticket against IBM Cognos Support and see what's going wrong?
Thanks
Prit
Yeah, that's what I would do.
By the way, found some other issues in 10.1 too, some features don't work after upgrading the report to 10.1. I had to change bsome settings to make the same feature work in 10.1 !!
It's a bad idea to upgrade when the version is released, should wait for the patch to come up. This has always been a problem with almost every version ;)
QuoteIt's a bad idea to upgrade when the version is released, should wait for the patch to come up. This has always been a problem with almost every version
Made my day:D Good that I haven't upgraded to 10v yet in-spite of chasing my client to get the license from last 2-3 months and no success yet.. ;D
Sometimes the wait pays off? :)
In addition to the page rendering option, you could also try setting the column(s) to render on your csvidentify variable.
If you go to the list column level (highlight the column and select List Column from ancestor), the properties pane will show you a Render option - use your csvidentify variable to set this on the columns you wish to hide.
Note: You could also change the variable from a boolean to a string (if you know the exact name of the group) and say something like #sq(CSVIdentityNameList())# in the expression and then have the values as Value1, Value 2 and render the columns only for Value1 (which would be your powergroup)
Sorry, that shouldn't read "in addition to page render"..it should be an alternative to the page render solution :-\
Is it confirmed that this behavior is a bug? And if so, was it ever patched? I seem to be experiencing the same issue in Cognos 10.1.1, returning an error that my expression was invalid.