Solved

Cognos prompt report

Posted on 2008-06-26
13
1,726 Views
Last Modified: 2010-05-18
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
0
Comment
Question by:ac_davis2002
  • 9
  • 4
13 Comments
 
LVL 12

Expert Comment

by:RWrigley
ID: 21878134
What is the member caption defininition for the data item that this prompt is coming from?
0
 

Author Comment

by:ac_davis2002
ID: 21878760
can you tell me how to check that please, I'm not sure what that means?
0
 

Author Comment

by:ac_davis2002
ID: 21878818
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

 

Author Comment

by:ac_davis2002
ID: 21882255
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
 
LVL 12

Expert Comment

by:RWrigley
ID: 21883048
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
 

Author Comment

by:ac_davis2002
ID: 21883174
Can I just confirm that is within the filter that creates the prompt?

Sorry ver new at this!
0
 

Author Comment

by:ac_davis2002
ID: 21883262
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
 

Author Comment

by:ac_davis2002
ID: 21884218
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
 
LVL 12

Expert Comment

by:RWrigley
ID: 21884831
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
 

Author Comment

by:ac_davis2002
ID: 21885712
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
 
LVL 12

Accepted Solution

by:
RWrigley earned 500 total points
ID: 21886254
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
 

Author Comment

by:ac_davis2002
ID: 21889618
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
 

Author Closing Comment

by:ac_davis2002
ID: 31471121
Thanks again
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question