Re: How to extract the underlying MDX

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

1) The report parameter is coming from AS indeed, and Reporting
Services does auto-generate a MDX that retrieves a list of the valid
values as you described. All is well, except that the consumer
web-application can invoke a report with a URL access without first
validating if that's an valid Dimension member.

Yes, you are absolutely right, the error is from Reporting Services,
because the parameter value received is not in the available value
list!
Now, if I can have another parameter that is accepting the value and is
not bound to the available value list, and when processing a call with
an invalid value, the error displayed is now
"Query (1, 598) The restrictions imposed by the CONSTRAINED flag in the
STRTOSET function were violated. "

Now, that's an AS error, because [Product].[Category].&[5] (a
non-existing Dimension Member) has been used to make such a query:

WITH MEMBER [Measures].[Profit] AS
'[Measures].[Sales Amount]-[Measures].[Standard Product Cost]'
SELECT NON EMPTY { [Measures].[Internet Sales Amount],
[Measures].[Internet Total Product Cost],
[Measures].[Internet Order Quantity] } ON COLUMNS,
NON EMPTY { ([Sales Reason].[Sales Reason].[Sales Reason].ALLMEMBERS *
[Sales Territory].[Sales Territory Group].[Sales Territory
Group].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME,
[Sales Reason].[Sales Reason].[Sales Reason].KEY,
[Sales Territory].[Sales Territory Group].[Sales Territory Group].KEY
ON ROWS
FROM ( SELECT ( STRTOSET('[Product].[Category].&[5]', CONSTRAINED) ) ON
COLUMNS FROM [Adventure Works])
WHERE ( IIF( STRTOSET('[Product].[Category].&[5]', CONSTRAINED).Count =
1,
STRTOSET('[Product].[Category].&[5]', CONSTRAINED),
[Product].[Category].currentmember ) )


In your posting, 'IIF(VBA!ISERROR(STRTOVALUE("MEASURES.[THIS WONT
WORK]")), 1,0)' is able to catch an invalid MEASURE. I suspect maybe
the following can help me to detect invalid dimension members?
WITH MEMBER MEASURES.TEST AS
'IIF(VBA!ISERROR(STRTOSET("[Product].[Category].&[1]",
CONSTRAINED).COUNT),1,0)'
MEMBER MEASURES.TEST2 AS
'IIF(VBA!ISERROR(STRTOSET("[Product].[Category].&[5]",
CONSTRAINED).COUNT),1,0)'
SELECT {MEASURES.TEST, MEASURES.TEST2} ON 0 FROM [Adventure Works]

Then, if I have prepared a 'DEFAULT' Dimension Member
([Product].[Category].&[-999] in the example below) to handle all the
invalid calls, then I may be able to tweak the MDX to the following?

WITH MEMBER [Measures].[Profit] AS '[Measures].[Sales
Amount]-[Measures].[Standard Product Cost]'
SELECT NON EMPTY { [Measures].[Internet Sales Amount],
[Measures].[Internet Total Product Cost], [Measures].[Internet Order
Quantity] } ON COLUMNS,
NON EMPTY { ([Sales Reason].[Sales Reason].[Sales Reason].ALLMEMBERS )
} DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME,
[Sales Reason].[Sales Reason].[Sales Reason].KEY, [Sales
Territory].[Sales Territory Group].[Sales Territory Group].KEY ON ROWS
FROM ( SELECT ( IIF( STRTOSET(@ProductCategory).Count = 1,
STRTOSET(@ProductCategory, CONSTRAINED), [Product].[Category].&[-999] )
) ON COLUMNS
FROM [Adventure Works]) WHERE ( IIF( STRTOSET(@ProductCategory).Count =
1, STRTOSET(@ProductCategory, CONSTRAINED),
[Product].[Category].currentmember ) )



2. Chris, what's the function of the CONSTRAINED flag?
I found that if I include this CONSTRAINED flag into IIF(
STRTOSET(@ProductCategory, CONSTRAINED).Count = 1, the query above
stops working.

.

Relevant Pages

  • RE: Rowsource of unbound object
    ... email address is invalid ... I have a form which has a Gantt chart. ... What I want to do is to change the report rowsource when the report opens to ...
    (microsoft.public.access.modulesdaovba)
  • Re: My Python annoyances
    ... A few weeks later, noticing that you had not challenged his explanation, I closed after changing the Resolution box to Invalid. ... Real bug reports are quite welcome, as any honest person could determine by looking thru the tracker. ... I understand and agree that the number was the same bit pattern. ... All I saw was a comment on what might cause my problem, and then I saw that the problem report was closed. ...
    (comp.lang.python)
  • Invalid user problem
    ... I have a dozen of so users on my freebsd system, but I added a user a month or so ago, and am unable to get the system to recognize them. ... I reset the password a dozen times, even using a simple password like test, and it would still always report that the password was wrong. ... No problem, I added the user to that entry, and it stopped reporting that they were not in the file, but still reports that the user is invalid. ... I deleted the user, and recreated the user, but it still acts like the user does not exist for retrieving emails, sshd and ftp. ...
    (comp.unix.bsd.freebsd.misc)
  • Google report on click fraud
    ... The Lane's Gifts v. Google Report ... I have been asked to evaluate Google's invalid click detection efforts ... examined various internal documents, interviewed several Google's ... employees, have seen different demos of their invalid click inspection ...
    (comp.dcom.telecom)