Solved

Cognos prompt report

Posted on 2008-06-26
13
1,713 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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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 recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now