Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Cognos prompt report

Posted on 2008-06-26
13
Medium Priority
?
1,738 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

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 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
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…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

885 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