No data after editing MDX query

[chris777]

I edited the MDX query on a second page of an analysis app to select a three month period. This works fine in the app designer.

The main page has a transition button to change to page 2 and all dimensions are set to "Use Target Members". When I run it in ePortfolio or the CMC page 2 comes up with no data, regardless of whether I use ePortfolio or CMC.

Any idea where to start? Chris
DFW Crystal User Group

http://www.dfwcug.com

 

[OliverS]

So if I understand you correctly you are trying to use a transition to move to a page defined by an MDX query? But not changing the query.

Further to that you are can see it in the designer but not in the Web client.

Which version are you using? Also is the designer on the same machine as the WCS? I think this is where the MSOLAP connection is made in the web case and so this is just to rule out some nasty permissions problem.

I've just given this a quick go and it works for me (everything on a single box: CA8.5 and CE8). I'm not really sure what else to suggest at the moment.

Oliver

 

[chris777]

Your understanding is correct. The transition simply loads the second page without altering the view itself.

I am using CE8.0 and CA 8 sr1 (v 8.0.3.196) and the designer is not on the same machine as CE.

The first page of the application works fine. It is simply displaying a worksheet with selected dimensions. Also, I have created other multiple page applications and they work fine as well. It's just this one which has modified MDX.

Very strange... Chris
DFW Crystal User Group

http://www.dfwcug.com

 

[chris777]

I did some more testing. By turning on the Row, Column, and Slice dimension panels, I found that no members were selected. Once I selected the members, it displayed the data and the graphs I had created. It's like modifing the MDX and then publishing results in no members being selected when the app is run. But that makes no sense.?.?

Any Help? Chris
DFW Crystal User Group

http://www.dfwcug.com

 

[OliverS]

I think something else is happening there. When you select the members you are going back to using the Crystal view on the data rather than the MDX as I understand it. This means that you won't really be executing the MDX that you put in but rather CA has extracted the view from the query and is using that. I've seen this change the MDX.

I haven't managed to get a system set up with SR1 so I can't confirm that it is a problem that has been fixed (or seemingly so) in 8.5 but it looks that way to me.

One other thing does occur to me. If you design the app then save it and reload it, does it still work in the designer?

Oliver

 

[chris777]

Yes, selecting dimensions manually does rebuild the MDX. I have confirmed that also.

Saving and Reloading: Yes. For example, in the Set which contains the Time dimension, I am using " YTD([Day].[Year].[2002].[August]) to get all months to display. When I reopen the app it displays the same but has rewritten the query to name each individual month, rather than using YTD. Probably not a big deal, but it's the only thing different I have noticed.

Additionally, I have another app I just loaded which has no custom queries, and two of the three pages start with a blank worksheet.

This is very frustrating, and I can't find anything on Crystal's KB or the Developer Zone. I appreciate your time and any suggestions you can offer are appreciated. Chris
DFW Crystal User Group

http://www.dfwcug.com

 

[OliverS]

It is beginning to sound as if there are two problems.

Firstly that the MDX isn't being persisted properly (which sounds faintly familiar but I don't really use that facility). I'm not sure what can be done about it except to report it as a bug. If you don't mind me asking, why are you writing your own MDX?

Secondly that sometimes you don't get anything at all. Is that easily reproducable with a sample cube? Have you tried talking to Crystal Tech Support about it? Any more details you have would be great as this is more worrying to me.

Also I have found that 8.5 is better, is there any way you could upgrade to that?

Oliver

 

[chris777]

Tech support isn't possible, as we are doing an eval of the product. We are a CE customer but I am pushing the company to use CAP rather than Crystal Reports Olap Grids (yuuuck!)
I have a call into the pre-sales guy but havn't heard back yet.

I am attempting to write my own MDX because I am wanting to build some views which do things like: show measures by last three months or other more dynamic date interaction (similar to using getdate() in a SQL Statement). That may not even be possible in MDX, but that's what I was exploring.

I will continue testing see if I can reproduce with the SQL Analysis or CAP demo data. I will post when I have done some more.

Thanks Chris
DFW Crystal User Group

http://www.dfwcug.com

 

[chris777]

Oliver,

Well, found the answer. When we got CAP v.8 (mr1) we never upgraded the CE Server. I uninstalled the old 8.0 and then installed the new version and all the applications work fine.

Thanks for your help. Chris
DFW Crystal User Group

http://www.dfwcug.com

 

[OliverS]

Hi Chris,

Thanks for letting me know what the problem was. I guess I should have suggested that Oh well at least it was a relatively easy solution.

Good luck with the implementation.

Oliver