Cognos prompt report

Hi

I have a cognos report that passes the prompts to a stored procedure that i have 'imported' into relasional model. The problem is one of the prompts using a data item from the Dimentional model and the stored procedure does not seem to recognise it as it returns no data??

The other uses the Relational prompt and is fine

Any ideas?

Adam
ac_davis2002Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
RWrigleyConnect With a Mentor Commented:
No, becuase it isn't assigned at the FM level.  The MUN is created dymanically in a DMR to represent a specific member of a dimension.  As such, its a byproduct of the dimensional modeling.  You normally wouldn't be all that aware of it, except that you're trying to prompt off of a dimension and pass that value to a stored procedure (and the fact that you're doing that is questionable in and of itself.  If you're running a stored procedure, you probably aren't doing an analysis, and if you aren't doing an analysis, why are you using OLAP techniques?)
0
 
RWrigleyCommented:
What is the member caption defininition for the data item that this prompt is coming from?
0
 
ac_davis2002Author Commented:
can you tell me how to check that please, I'm not sure what that means?
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
ac_davis2002Author Commented:
it comes from the dimension view
the prompt is as follows

[Dimension View].[Industry].[Industry].[Industry]=?Industry?

does that answer your question at all?
0
 
ac_davis2002Author Commented:
HI RWrigley

Not progressing at all with this, i have put a trace on the database when I run the report and this is the execution string the report is sending to the stored proceduer empindcount

exec ICAS.dbo.EmpIndCount 'Dec  1 2007 12:00:00:000AM', 'Dec 31 2008 12:00:00:000AM', '[Dimension View].[Industry].[Industry].[Industry]->[all].[12].[43]'

But still no data



Any pointer would be greatly apprecited

Adam
0
 
RWrigleyCommented:
It's passing a 'MUN'; try putting a caption around the expression:

caption([Dimension View].[Industry].[Industry].[Industry]=?Industry?)

That'll convert the MUN to its "display" format.
0
 
ac_davis2002Author Commented:
Can I just confirm that is within the filter that creates the prompt?

Sorry ver new at this!
0
 
ac_davis2002Author Commented:
I tried putting it around the expression in the filter but it failed

Can you confirm where I need to wrap the expression in the caption

caption([Dimension View].[Industry].[Industry].[Industry]=?Industry?)
0
 
ac_davis2002Author Commented:
it was a typo I have found that caption([Dimension View].[Industry].[Industry].[Industry])=?Industry? does not raise any errors but if I use it on the filter It changes data else where on the report. Is there anything else you suggest?
0
 
RWrigleyCommented:
It sounds like you are trying to use one prompt in two different ways.  I'm guessing that the "use" value for the prompt is a dimensional member, so its making the parameter (Industry) a MUN.  YOu want to pass the caption of this to the stored procedure, so I'd suggest you modify the prompt (and prompt query) to have a use value that is a caption.  So assuming that the use value data item expression is:
[Dimension View].[Industry].[Industry].[Industry]
you want to change it to
caption([Dimension View].[Industry].[Industry].[Industry])

Then, the filter will be:
caption([Dimension View].[Industry].[Industry].[Industry])=?Industry?

Note that this will have a performance impact on the report, because cognos can't send that kind of a filter to the cube directly, so it'll probably retrieve all the industries from the datasource, and filter it locally.
0
 
ac_davis2002Author Commented:
ok I think I understand.....

yes it is a dimensional member, should i change the use value data item expression at framework manager level then?
0
 
ac_davis2002Author Commented:
Fair point RWrigley, hay thanks for your help with all this its starting to make sence! although i expect to be back with a least a thousand more questions!!!

enjoy your weekend

Adam
0
 
ac_davis2002Author Commented:
Thanks again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.