MDX Ideas needed

Question

Post reply

MDX Ideas needed

Viewing 12 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

gsc_dba SSCertifiable Points: 5407 More actions · Answer 1

Have you tried replacing the .Children with .Members (or .AllMembers) via your OLE DB data connection?

You may have to specify an additional scope level like this:

[Questionnaire].[Questionnaire Question Order].[Questionnaire Question Order].MEMBERS

gsc_dba

danielagger98 Mr or Mrs. 500 Points: 502 More actions · Answer 2

It wont make a difference. It will still club all the fields on columns together. The only change is it will give an extra row for the ALL field.

gsc_dba SSCertifiable Points: 5407 More actions · Answer 3

Seems you have done all the things I would have done already... 😉

I would probably next try to create the resultset in SSMS (Analysis Services) by browsing the cube

Or

Use the query builder (Report Wizard) in SSRS limiting to a single questionnaire first.

Good luck in your endeavours!

gsc_dba

danielagger98 Mr or Mrs. 500 Points: 502 More actions · Answer 4

danielagger98

Mr or Mrs. 500

Points: 502

June 15, 2010 at 9:16 am

#1180507

Aight! Thanks for your time.

MWise SSCarpal Tunnel Points: 4074 More actions · Answer 5

Have you tried moving any of those Customer attributes to calculated members instead of having them in the cross joins?

danielagger98 Mr or Mrs. 500 Points: 502 More actions · Answer 6

WITH MEMBER Test AS

([Questionnaire].[Question Code]*

[Questionnaire].[Question Code Suffix])

SELECT {Test,[Measures].[Survey Count]} ON 0,

([Customer].[First Name].[First Name],

[Customer].[Last Name].[Last Name]) ON 1

FROM [Pet Co]

I tried doing this, if this is what you have in mind. But here, it gives me just one column with calculated member name(Test). Where as I want all the fields to show up in the report.

danielagger98 Mr or Mrs. 500 Points: 502 More actions · Answer 7

I even tried this way. To make the measure as a calculated member. So that I can trick the query designer to take it. Because the query designer will take only the measure dimension. Damn!

WITH MEMBER Test AS

[Measures].[Survey Count]

SELECT {([Questionnaire].[Question Code].CHILDREN

,Test)} ON 0,

NON EMPTY ([Customer].[First Name].[First Name],

[Customer].[Last Name].[Last Name]) ON 1

FROM [Pet Co]

MWise SSCarpal Tunnel Points: 4074 More actions · Answer 8

Does this work?

WITH MEMBER [Measures].[Question Code] AS '[Questionnaire].Properties("Question Code")'

MEMBER [Measures].[Question Code Suffix] AS [Questionnaire].Properties("Question Code Suffix")'

SELECT {[Measures].[Survey Count],[Measures].[Question Code], [Measures].[Question Code Suffix]} ON 0,

([Customer].[First Name].[First Name],

[Customer].[Last Name].[Last Name]) ON 1

FROM [Pet Co]

I have a Question hierarchy in my cube with other fields that describe the Question level set in an attribute relationship so then I can use the MDX above to bring in each the fields without having to cross join them. Using the calculated measure method was a work around to SSRS 2005 query designer bombing out when you bring in a lot of cross joined attributes.

danielagger98 Mr or Mrs. 500 Points: 502 More actions · Answer 9

The Query Builder does take this.

But the issue is, it wont show me the Questions codes and Question Code Suffix on Columns.

Instead it treats them as measures and gives me null calculated field for the calculated members.

I need to bring in Question code, Question Code Suffix and all Customer columns as separate fields in the SSRS data set. So that I can use each filed in the report designer.

MWise SSCarpal Tunnel Points: 4074 More actions · Answer 10

You saying that you are getting all null values for the Question Code and suffix when trying to use them as calculated measures? If so, that usually means that the query isn't right or your dimension isn't set up to use those attributes as properties. You might want to look into purchasing Intelligencia Query to help with difficult MDX in SSRS. I had to look into it for some of our complex reports and I believe it can put measures on columns in SSRS. You can get an evaluation copy for a certain amount of days to see if it would work for you. I'd also recommend moving this question to the BI or SSRS forums instead - there are lot more MDX experts available there.

danielagger98 Mr or Mrs. 500 Points: 502 More actions · Answer 11

I dont think my company would agree with that. Anywho, How do i move this topic to the analysis service forum?

danielagger98 Mr or Mrs. 500 Points: 502 More actions · Answer 12

Hey I got this issue fixed. Nothing worng with the code. unfortunately I was using the server which had less memory and the SSRS crashed to get this report.

I setup reprot server on my lcoal machine and I was able to render the report. Thanks for those who helped. SSC rocks.