Solved

Cognos prompt report

Posted on 2008-06-26
13
1,722 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

777 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