Dynamic Security in SSAS 2005

I am trying to create dynamic security and was hoping to use a named query to link the fact table to a dimension that contains Active Directory users and companies - a user can have access to multiple companies. The problem is that I cannot find out how to get the username of the current user.

Anyone have any ideas?

thanks in advance.

Does the standard MDX keyword: UserName return what you want, like:

>>

With member [Measures].[CurrentUser] as UserName

select {[Measures].[CurrentUser]} on 0

from [Adventure Works]

>>

|||

thanks for the feedback - however, I am relatively new to Analysis Services (and 2005) - and cannot see where this would be created. Are you saying that this is a calculated Dimension (or measure?)

Many thanks

|||

The MDX keyword: UserName will return the name of the current user in an MDX expression, which could be used in many different contexts:

http://msdn2.microsoft.com/en-us/library/ms144884.aspx#expressions_statements_scripts

>>

SQL Server 2005 Books Online

Key Concepts in MDX (MDX)

In MDX, an expression is a combination of identifiers, values, functions, and operators that Analysis Services can evaluate to retrieve an object, such as a set or a member, or a scalar value like a string or a number.

>>

http://msdn2.microsoft.com/en-us/library/ms146016.aspx

>>

SQL Server 2005 Books Online

UserName (MDX)

Returns the domain name and user name of the current connection.

>>

|||Thanks for the feedback.

dynamic query "expression expected" error

I'm using the following SQL and receiving: "The expression for the
query 'NOL' contains an error: [BC30201] Expression expected."
="SELECT C.CardholderLastName, C.CardholderFirstName,
C.CardNumberLast4Digits, C.CUDiscoveryDate,
C.EstimatedLossAmount_Quantity LossAmount, C.CUSTOMERREFERENCE,
(SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
X.REPORTINGTYPE = 'FraudTypeGrouping1' AND X.NSID = Y.LONSID AND X.ID = Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE = CHAR(C.FRAUDTYPE)) as FRAUDTYPE,
(SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
X.REPORTINGTYPE = 'TransactionType' AND X.NSID = Y.LONSID AND X.ID = Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE = CHAR(C.TRANSACTIONTYPE)) as TRANSACTIONTYPE,
(SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
X.REPORTINGTYPE = 'BankCardProgram' AND X.NSID = Y.LONSID AND X.ID = Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE = CHAR(C.CARDPROGRAM)) as CARDPROGRAM, 'ALL' as ALL_TEST
FROM APX.ACUWEBDATAENTRYPLASTICCARD AS C, APX.AICPERLEGALSTRUCTURE AS D
WHERE C.CUSTOMERREFERENCE = D.CUSTOMERREFERENCE " &
IIF(Parameters!CONTRACTNUMBER.Value = 'ALL', "", "AND
C.CUSTOMERREFERENCE = '" & Parameters!CONTRACTNUMBER.Value & "'") & "
ORDER BY C.CUSTOMERREFERENCE"
I've looked over all the message related to Dynamic Queries and from
what I can tell I'm doing this correctly, but for some reason it's not
working. I can remove the IIF and after and it works fine, I'm sure
its a ) or ' that I'm missing but not sure where. CONTRACTNUMBER is a
string value.
ThanksDoes this work?
SELECT C.CARDHOLDERLASTNAME,
C.CARDHOLDERFIRSTNAME,
C.CARDNUMBERLAST4DIGITS,
C.CUDISCOVERYDATE,
C.ESTIMATEDLOSSAMOUNT_QUANTITY LOSSAMOUNT,
C.CUSTOMERREFERENCE,
(SELECT Z.MYLONGLABEL
FROM APX.AENUMTYPETABLE X,
APX.AENUMTYPETABLE_MYENTRIES Y,
APX.AENUMTYPETABLEENTRY Z
WHERE X.REPORTINGTYPE = 'FraudTypeGrouping1'
AND X.NSID = Y.LONSID
AND X.ID = Y.LOID
AND Y.NSID = Z.NSID
AND Y.ID = Z.ID
AND Z.MYCODE = CHAR(C.FRAUDTYPE)) AS FRAUDTYPE,
(SELECT Z.MYLONGLABEL
FROM APX.AENUMTYPETABLE X,
APX.AENUMTYPETABLE_MYENTRIES Y,
APX.AENUMTYPETABLEENTRY Z
WHERE X.REPORTINGTYPE = 'TransactionType'
AND X.NSID = Y.LONSID
AND X.ID = Y.LOID
AND Y.NSID = Z.NSID
AND Y.ID = Z.ID
AND Z.MYCODE = CHAR(C.TRANSACTIONTYPE)) AS TRANSACTIONTYPE,
(SELECT Z.MYLONGLABEL
FROM APX.AENUMTYPETABLE X,
APX.AENUMTYPETABLE_MYENTRIES Y,
APX.AENUMTYPETABLEENTRY Z
WHERE X.REPORTINGTYPE = 'BankCardProgram'
AND X.NSID = Y.LONSID
AND X.ID = Y.LOID
AND Y.NSID = Z.NSID
AND Y.ID = Z.ID
AND Z.MYCODE = CHAR(C.CARDPROGRAM)) AS CARDPROGRAM,
'ALL' AS ALL_TEST
FROM APX.ACUWEBDATAENTRYPLASTICCARD AS C,
APX.AICPERLEGALSTRUCTURE AS D
WHERE C.CUSTOMERREFERENCE = D.CUSTOMERREFERENCE
" &
IIF(Parameters!CONTRACTNUMBER.Value = 'ALL', ", "AND
C.CUSTOMERREFERENCE = '" & Parameters!CONTRACTNUMBER.Value & "'") & "
ORDER BY C.CUSTOMERREFERENCE
formatting thanks to
http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl
I don't think it will, I think the comma in the IIF before the AND is the
problem.
Steve MunLeeuw
<kelkoenig@.gmail.com> wrote in message
news:1139434326.076692.110810@.g43g2000cwa.googlegroups.com...
> I'm using the following SQL and receiving: "The expression for the
> query 'NOL' contains an error: [BC30201] Expression expected."
> ="SELECT C.CardholderLastName, C.CardholderFirstName,
> C.CardNumberLast4Digits, C.CUDiscoveryDate,
> C.EstimatedLossAmount_Quantity LossAmount, C.CUSTOMERREFERENCE,
> (SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
> APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
> X.REPORTINGTYPE = 'FraudTypeGrouping1' AND X.NSID = Y.LONSID AND X.ID => Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE => CHAR(C.FRAUDTYPE)) as FRAUDTYPE,
> (SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
> APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
> X.REPORTINGTYPE = 'TransactionType' AND X.NSID = Y.LONSID AND X.ID => Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE => CHAR(C.TRANSACTIONTYPE)) as TRANSACTIONTYPE,
> (SELECT Z.MYLONGLABEL FROM APX.aENUMTYPETABLE X,
> APX.aENUMTYPETABLE_MYENTRIES Y, APX.aENUMTYPETABLEENTRY Z WHERE
> X.REPORTINGTYPE = 'BankCardProgram' AND X.NSID = Y.LONSID AND X.ID => Y.LOID AND Y.NSID = Z.NSID AND Y.ID = Z.ID AND Z.MYCODE => CHAR(C.CARDPROGRAM)) as CARDPROGRAM, 'ALL' as ALL_TEST
> FROM APX.ACUWEBDATAENTRYPLASTICCARD AS C, APX.AICPERLEGALSTRUCTURE AS D
> WHERE C.CUSTOMERREFERENCE = D.CUSTOMERREFERENCE " &
> IIF(Parameters!CONTRACTNUMBER.Value = 'ALL', "", "AND
> C.CUSTOMERREFERENCE = '" & Parameters!CONTRACTNUMBER.Value & "'") & "
> ORDER BY C.CUSTOMERREFERENCE"
> I've looked over all the message related to Dynamic Queries and from
> what I can tell I'm doing this correctly, but for some reason it's not
> working. I can remove the IIF and after and it works fine, I'm sure
> its a ) or ' that I'm missing but not sure where. CONTRACTNUMBER is a
> string value.
> Thanks
>|||Nope, no luck, still same error w/in Reporting Services. I had to add
the = and "s at the beginning and end of the statement, unless you left
those out intentionally?
THanks|||Try enclosing the word ALL in double quotes instead of single in your
original. SRS doesn't like single quotes at all!
kelkoenig@.gmail.com wrote:
> Nope, no luck, still same error w/in Reporting Services. I had to add
> the = and "s at the beginning and end of the statement, unless you left
> those out intentionally?
> THanks|||Thanks a ton Toolman, that worked like a charm.
kelsql

Dynamic layout & whitespace

Hi,

I have a report that, among other things, contains 3 tables laid out side-by-side horizontally. The first table is conditionally visible and when it is hidden the report contains whitespace instead of the table. The problem is that this causes an unappealing visual look because the other 2 tables look shifted too much to the right.

What is the recommended way for dealing with situations like this? There doesn't seem to be support for specifying the location (i.e. top, left) of elements based on expressions. Am I "stuck" with having to create multiple flavors of the report?

Thanks,

Vitaly

Any help is appreciated! :)

Thanks,

Vitaly

|||

Can someone from the SSRS team please shed some light on this?

Thanks

Dynamic Items position ?

Hi,
I've Got two rectangles which contains report data. (Second one just under
the first one). Visibility of each of them is checked with dataset values.
The point is that I want the second rectangle to move at the top when the
first one's visibility is set to false.
Whatever I try, when the first one is hidden, the second one do not take its
place and I'm facing a huge blank section before it.
Is it possible to change rectangle position dynamically ? Is there a
solution for this problem ?
Thanks for you help.Found the trick.
Just have to insert each of my rectangle in a Table for which I set
visibility expression on row.
Franck wrote:
> Hi,
> I've Got two rectangles which contains report data. (Second one just under
> the first one). Visibility of each of them is checked with dataset values.
> The point is that I want the second rectangle to move at the top when the
> first one's visibility is set to false.
> Whatever I try, when the first one is hidden, the second one do not take its
> place and I'm facing a huge blank section before it.
> Is it possible to change rectangle position dynamically ? Is there a
> solution for this problem ?
> Thanks for you help.

Dynamic Filtering Expression

I have a report which contains a parameter called SuppressZero which depending on its value I want to filter out certain data. This parameter can have 3 different values and for each value I need to have a different filter expression. What I would like to do is implement the following:

If SuppressZero = 1
Filter where Quantity <> 0

If SuppressZero = 2
No Filter

If SuppressZero = 3
Filter where Quantity <> 0 Or InStockFamily = "Y"

How can I do this in my report?

In general its better to filter the data in the query than the report. The report filters are per report object not the entire report so in the case of mutiple objects you may need to set multiple filters. Assuming you have a single table report you can set a filter on the that table filter property. The filter porperty has three fields; expression, operator, value. One solution is

Expression: iif((Paramters!SupprssZero=1 and Fields!Quantity=0) or Parameters!SuppressZero=2 or (Parameters!SuppressZero=3 and Fields!Quantity=0 and InstockFamily="N"),1,0)

Operator: =

Value = 1

|||assuming you pass the SuppressZero param to the stored procedure, you need to add a WHERE clause something like

WHERE
(@.suppressZero = 1 and quantity <> 0)
OR
(@.supressZero = 2)
OR
(@.supressZero = 3 and (quantity <> 0 OR instockfamily <> 'Y')