"James Woo via SQLMonster.com" <forum@SQLMonster.com> wrote in message
news:5150E35762913@SQLMonster.com...
>I want the data in the pDiv since it will list all the Division from which
>i
> can select one division to query on. I just have a problem with the syntax
> and have tried different things to solve it wih no avail. Still need to
> get
> the parameter passed to the .member in the MDX query. no solution here
> yet.
> HELP :).
>
> mike wrote:
>>first ensure that Parameters!PDiv.Value is actually the data you want, you
>>may find out that Parameters!PDiv.Label is actually what you want: I
>>usually
>>have the ".Value" being the mdx unique member name, and the ".Label" being
>>the user friendly display name.
>>
>>> Hi All,
>>> Have the following query in an RS report that tracks Projects and their
>>[quoted text clipped - 28 lines]
>>>
>>> Thanks
>
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200507/1

"James Woo via SQLMonster.com" <forum@SQLMonster.com> wrote in message
news:5152FC4E590A3@SQLMonster.com...
> 1- thanks for all the Advice.
> 2- the Query in the report worked after changing to Non
> Empty(SET1*SET2*SET3,
> etc).
> 3- AS 2000: Should I use NON EMPTY (SET1*SET2*SET3,etc). instead of NECJ
> (SET1,SET2,SET3,etc) ? What is the difference?
> 4- Switching to SQLServer/AS2005 in three weeks. New set of books, new
> ulcer
> precription, and lots of questions.:). Do you recommend for sql 2000 to
> sql
> 2005 Analysis services migration, books, articles, site. etc.?
>
> Thank you,
> James
>
> Mark Job wrote:
>>If the * syntax worked and Nonemptycrossjoin didn't, was there something
>>data-wise that was a problem with the * version? Why not just use what
>>works? NECJ has been deprecated in AS 2005, while it still functions, its
>>use is discouraged.
>>
>>Also, there is no need to nest four NonEmptyCrossJoins - unlike the
>>CrossJoin which only takes two arguments, you can combine several
>>dimensions
>>in a single function call: nonemptycrossjoin(A, B, C, D, E). This will
>>return a result with dimensionality A*B*C*D*E. If you want to ensure
>>non-emptyness across these five dimensions but have a result with A*B*C
>>dimensionality, use nonemptycrossjoin(A,B,C,D,E,3)
>>
>>If that doesn't work, I can only fall back on the RS 2000 Samples. The
>>parameterized Foodmart sample report actually brings back all of the
>>Product
>>data, and then uses a filter on the BrandSales matrix object to restrict
>>data to the product family selected from the families listed in the
>>parameter query. In your case, you would have a second MDX query that
>>pulls
>>back a list of Divisions, and the main query would pull back all project
>>data, but you could filter it to only the desired Division. Because the
>>parameter is evaluated in the RS context (as opposed to MDX) it works.
>>But
>>this is not a true parameterized query, it is a clever hack.
>>
>>In RS 2005, MDX parameters are more like the @syntax of SQL parameters,
>>and
>>can be evaluated on the query side. The parameter is usually selected
>>through the FROM clause. I doubt if this will work for you in RS 2000,
>>but
>>here is a sample RS 2005 dataset with two parameters:
>>SELECT NON EMPTY { [Measures].[Total Amount] } ON COLUMNS, NON EMPTY {
>>([Time].[MONTH LDESC].[MONTH LDESC].ALLMEMBERS *
>>[Providers].[Category].[Provider].ALLMEMBERS ) } DIMENSION PROPERTIES
>>MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( [Buyers] ) ON
>>COLUMNS FROM ( SELECT ( STRTOSET(@ProvidersCategory, CONSTRAINED) ) ON
>>COLUMNS FROM ( SELECT ( STRTOMEMBER(@TimeMONTHLDESC,
>>CONSTRAINED):STRTOMEMBER(@TimeMONTHLDESC, CONSTRAINED).Lead(11) ) ON
>>COLUMNS
>>FROM [Core Knowledge]))) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,
>>FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
>>
>>Note the * syntyax within NON EMPTY( ... ) instead of NonEmptyCrossJoin.
>>Cheers, Mark
>>
>>>I want the data in the pDiv since it will list all the Division from
>>>which
>>>i
>>[quoted text clipped - 16 lines]
>>>>>
>>>>> Thanks
>
>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200507/1

Mark Job wrote:
>If the * syntax worked and Nonemptycrossjoin didn't, was there something
>data-wise that was a problem with the * version? Why not just use what
>works? NECJ has been deprecated in AS 2005, while it still functions, its
>use is discouraged.
>
>Also, there is no need to nest four NonEmptyCrossJoins - unlike the
>CrossJoin which only takes two arguments, you can combine several dimensions
>in a single function call: nonemptycrossjoin(A, B, C, D, E). This will
>return a result with dimensionality A*B*C*D*E. If you want to ensure
>non-emptyness across these five dimensions but have a result with A*B*C
>dimensionality, use nonemptycrossjoin(A,B,C,D,E,3)
>
>If that doesn't work, I can only fall back on the RS 2000 Samples. The
>parameterized Foodmart sample report actually brings back all of the Product
>data, and then uses a filter on the BrandSales matrix object to restrict
>data to the product family selected from the families listed in the
>parameter query. In your case, you would have a second MDX query that pulls
>back a list of Divisions, and the main query would pull back all project
>data, but you could filter it to only the desired Division. Because the
>parameter is evaluated in the RS context (as opposed to MDX) it works. But
>this is not a true parameterized query, it is a clever hack.
>
>In RS 2005, MDX parameters are more like the @syntax of SQL parameters, and
>can be evaluated on the query side. The parameter is usually selected
>through the FROM clause. I doubt if this will work for you in RS 2000, but
>here is a sample RS 2005 dataset with two parameters:
>SELECT NON EMPTY { [Measures].[Total Amount] } ON COLUMNS, NON EMPTY {
>([Time].[MONTH LDESC].[MONTH LDESC].ALLMEMBERS *
>[Providers].[Category].[Provider].ALLMEMBERS ) } DIMENSION PROPERTIES
>MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( [Buyers] ) ON
>COLUMNS FROM ( SELECT ( STRTOSET(@ProvidersCategory, CONSTRAINED) ) ON
>COLUMNS FROM ( SELECT ( STRTOMEMBER(@TimeMONTHLDESC,
>CONSTRAINED):STRTOMEMBER(@TimeMONTHLDESC, CONSTRAINED).Lead(11) ) ON COLUMNS
>FROM [Core Knowledge]))) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,
>FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
>
>Note the * syntyax within NON EMPTY( ... ) instead of NonEmptyCrossJoin.
>Cheers, Mark
>
>>I want the data in the pDiv since it will list all the Division from which
>>i
>[quoted text clipped - 16 lines]
>>>>
>>>> Thanks

Mark Job wrote:
>Before I answer you questions, a quick one for you. I assumed that your MDX
>was in a dataset, but then I saw the expression (=' <MDX query> ') syntax.
>Did you have that embedded in some other RS object?
>
>Now for your questions. I apologize in advance if I run on...
>
>3) well, one difference is that NON EMPTY (S1 * S2 * S3) works ;-)
>
>the other difference is in the dimensionality of the returned data. It is
>harder to see this in RS, you might want to try the MDX Sample application
>which helps you to visualize the returned data better. RS distorts the true
>query results into a flattened rowset, whereas true MDX can return a
>multi-dimensional result set, or combinations of dimensions on rows and
>columns (and up to 63 total dimensions).
>
> What I mean by "dimensionality" is that a S1 * S2 * S3 will be a result set
>with all three of those dimensions - a set of three element tuples. With
>NECJ you can test for non-null combinations across more attributes, but then
>return a result set with fewer dimensions than what you tested, which is
>sometimes useful. So if you use Nonemptycrossjoin(Set1, Set2, Set3, Set4,
>Set5, 3), that last numeric argument says "I want to check all five of these
>dimensions to make sure the intersection yields a non-null result, but when
>you've done that, return a set of tuples on only the first three dimensions
>Set1, Set2 and Set3."
>
>I read that NECJ has been deprecated because it has problems. If you read
>some of the textbooks and whitepapers out there, there are times when you
>may want to use one or the other for efficiency - there is a good recorded
>TechNet webcast on Common Mistakes in MDX that you can Google for that
>constrasts NECJ and Nonempty. I can dig it up if you hace trouble finding
>it.
>
>4) Not sure I am parsing your question correctly. If you are looking for an
>endorsement of SS / AS 2005, then yes, emphatically. Lots of cool new
>stuff. If you are looking for texts, that is a broad question - SS, RS, AS,
>MDX, etc. As for books, things are still pretty sparse.
>
>I've heard that both Spofford et al (MDX Solutions) and Pasumansky et al
>(Fast Track to MDX) are writing new versions. The former is a great
>reference source and has pretty much everything you'd ever want to know
>about MDX, but can be a difficult slog some times and a great sleeping pill
>at others. But I couldn't do MDX without it. Mosha's Fast Tracks book is
>more accessible, but not as complete as Spofford's. But then Mosha is right
>there on the development team, and is a great authority.
>
>For RS, I am sure that Stacia Misner, Teo Lachev, Peter Blackburn and
>everyone else will update their existing texts sooner or later. IMO RS in
>some ways has fewer changes than most of SS2005, but in others (especially
>the ad hoc Report Builder) there is a lot of new material to write about.
>The appetite for RS books is also greater than all the rest due to the end
>user impact.
>
>As for AS 2005, the changes there are huge. There weren't many good texts
>on AS 2000, but Reed Jacobsen's Analysis Services Step by Step was all you
>really needed. I sincerely hope that he will be updating his book for
>Yukon.
>
>Amd if you're looking for consultants, well, I know a few ;-)
>
>> 1- thanks for all the Advice.
>> 2- the Query in the report worked after changing to Non
>[quoted text clipped - 63 lines]
>>>>>>
>>>>>> Thanks

Mark Job wrote:
>Before I answer you questions, a quick one for you. I assumed that your MDX
>was in a dataset, but then I saw the expression (=' <MDX query> ') syntax.
>Did you have that embedded in some other RS object?
>
>Now for your questions. I apologize in advance if I run on...
>
>3) well, one difference is that NON EMPTY (S1 * S2 * S3) works ;-)
>
>the other difference is in the dimensionality of the returned data. It is
>harder to see this in RS, you might want to try the MDX Sample application
>which helps you to visualize the returned data better. RS distorts the true
>query results into a flattened rowset, whereas true MDX can return a
>multi-dimensional result set, or combinations of dimensions on rows and
>columns (and up to 63 total dimensions).
>
> What I mean by "dimensionality" is that a S1 * S2 * S3 will be a result set
>with all three of those dimensions - a set of three element tuples. With
>NECJ you can test for non-null combinations across more attributes, but then
>return a result set with fewer dimensions than what you tested, which is
>sometimes useful. So if you use Nonemptycrossjoin(Set1, Set2, Set3, Set4,
>Set5, 3), that last numeric argument says "I want to check all five of these
>dimensions to make sure the intersection yields a non-null result, but when
>you've done that, return a set of tuples on only the first three dimensions
>Set1, Set2 and Set3."
>
>I read that NECJ has been deprecated because it has problems. If you read
>some of the textbooks and whitepapers out there, there are times when you
>may want to use one or the other for efficiency - there is a good recorded
>TechNet webcast on Common Mistakes in MDX that you can Google for that
>constrasts NECJ and Nonempty. I can dig it up if you hace trouble finding
>it.
>
>4) Not sure I am parsing your question correctly. If you are looking for an
>endorsement of SS / AS 2005, then yes, emphatically. Lots of cool new
>stuff. If you are looking for texts, that is a broad question - SS, RS, AS,
>MDX, etc. As for books, things are still pretty sparse.
>
>I've heard that both Spofford et al (MDX Solutions) and Pasumansky et al
>(Fast Track to MDX) are writing new versions. The former is a great
>reference source and has pretty much everything you'd ever want to know
>about MDX, but can be a difficult slog some times and a great sleeping pill
>at others. But I couldn't do MDX without it. Mosha's Fast Tracks book is
>more accessible, but not as complete as Spofford's. But then Mosha is right
>there on the development team, and is a great authority.
>
>For RS, I am sure that Stacia Misner, Teo Lachev, Peter Blackburn and
>everyone else will update their existing texts sooner or later. IMO RS in
>some ways has fewer changes than most of SS2005, but in others (especially
>the ad hoc Report Builder) there is a lot of new material to write about.
>The appetite for RS books is also greater than all the rest due to the end
>user impact.
>
>As for AS 2005, the changes there are huge. There weren't many good texts
>on AS 2000, but Reed Jacobsen's Analysis Services Step by Step was all you
>really needed. I sincerely hope that he will be updating his book for
>Yukon.
>
>Amd if you're looking for consultants, well, I know a few ;-)
>
>> 1- thanks for all the Advice.
>> 2- the Query in the report worked after changing to Non
>[quoted text clipped - 63 lines]
>>>>>>
>>>>>> Thanks