Hello,
I am playing a bit around creating a prompt in FM 10.2.1. To test, I did set up a model with just one simple Query Subject. It is referencing the GOSALESDW.gosalesdw.SLS_PRODUCT_BRAND_LOOKUP table on a SQL Server 2012 database. There is a data source 'great_outdoors_warehouse' going against that db/schema.
I initially set the SQL to:
Select * from [great_outdoors_warehouse].SLS_PRODUCT_BRAND_LOOKUP WHERE PRODUCT_BRAND_EN = 'Star'
This way it works fine. However, I am trying to build in a prompt by changing it to:
Select * from [great_outdoors_warehouse].SLS_PRODUCT_BRAND_LOOKUP WHERE PRODUCT_BRAND_EN = #PROMPT('P_Input')#
When I select the Test tab now and click 'Test Sample', it indeed give me a 'Prompt Value' screen where I can enter a value for the P_Input prompt. I enter 'Star' (without quotes) as the value. But when I click ok now, the 'Prompt Value' window stays open and it gives me the following error message at the bottom:
RQP-DEF-0354 The query contains one or more unresolved prompts.
QE-DEF-0144 Prompt 'P_Input' has no substitution values.
Pretty sure I am missing something very basic here, but did not manage to figure it out yet. Any ideas? Thanks in advance!
Try explicitly setting the datatype of the prompt: #PROMPT('P_Input','string')#
Thanks for your quick reply. Tried that as well before, but same error result. :(
(Normally it should not make a difference since leaving 'string' out implicitly defaults it to 'string')
Pretty sure I am overlooking something very basic. Also because I can't really find anything out there in any post on the internet.... (But not basic enough for me to trace it down >:()
As you said, it should default to being a string. The default parameter for prompts (when none is selected) is something like "abc123". I was thinking that maybe if it's not explicitly set as a string, it may be returning the "abc123" as a fragment (without quotes).
It may not be able to get the meta data from the table without a fully constructed SQL. Try giving it a default value or moving the filter to the filters tab.
No luck.
I created a separate filter instead and added 'string' to the prompt definition. So the SQL now looks like:
Select * from [great_outdoors_warehouse].SLS_PRODUCT_BRAND_LOOKUP
And the definition of the filter is:
[great_outdoors_warehouse].[SLS_PRODUCT_BRAND_LOOKUP].[PRODUCT_BRAND_EN] = #PROMPT('P_Input','string')#
But I still get the same error when trying to test it and get a Test Sample.
I suppose you could always make it an optional prompt by either giving it a default or making it default to 1=1
#PROMPT('P_Input','string','1=1','[great_outdoors_warehouse].[SLS_PRODUCT_BRAND_LOOKUP].[PRODUCT_BRAND_EN] = ')#
or
[great_outdoors_warehouse].[SLS_PRODUCT_BRAND_LOOKUP].[PRODUCT_BRAND_EN] = #PROMPT('P_Input','string',sq('Default Value'))#
But that wouldn't fix the problem if you need a required prompt.
I'm going to be installing 10.2.1 on my laptop in a few days, I'll see if I can reproduce the problem then.
Thanks again. I tried removing the Query Subject Filter again and changed the SQL to:
Select *
from [great_outdoors_warehouse].SLS_PRODUCT_BRAND_LOOKUP
WHERE [great_outdoors_warehouse].[SLS_PRODUCT_BRAND_LOOKUP].[PRODUCT_BRAND_EN] = #PROMPT('P_Input','string',sq('Default Value'))#
But even then I still keep getting the messages when I fill out the prompt value in the 'Prompt Values' screen. Thanks for looking into this and let me know if you happen to find something!
We too are experiencing the same behavior in 10.2.1 Framework Manager.
We have problems when we try to validate query subjects with #prompt()# macros in them in FM. The prompt screen dialog box comes up as normal, but when you enter a value, it says there are unresolved prompts and keeps endlessly popping back up asking for the same prompt value over and over.
But....if we just publish that same "invalid" query subject in a package to our 10.2.1 server, the query subject works as expected, with the prompt macro filtering results.
Seems to be a bug in FM. Anyone found a workaround?
Hello,
I have the same issue in 10.2.1 Framework Manager . Does anyone know how it can be resolved?
Thanks!
Have you tried:
Select *
from [great_outdoors_warehouse].SLS_PRODUCT_BRAND_LOOKUP
WHERE [great_outdoors_warehouse].[SLS_PRODUCT_BRAND_LOOKUP].[PRODUCT_BRAND_EN] = #sq(prompt('P_Input','string','Default Value'))#
See position of SQ(... )
It's worth you installing the new Fix Pack 1 for Cognos 10.2.1 and trying this again.
MF.
Unfortunately FixPack1 did not help. The error still appear and I cannot add any stored procedure in Framework Manager. Any other ideas?
Did you install FP1 to both the BI server and the FM install?
Hi the FP1 for BI server and FM is different or same.
In single server installation should I have to install FP1 only once?
Is there any fP 1 for framework manager?
What abt 32 bit and 64 bit Fix pack?
Thanks
Thank you guys! After the 32 bit installation into FM folder this problem was resolved.
See answers in-line...
Hi the FP1 for BI server and FM is different or same.
A: Same
In single server installation should I have to install FP1 only once?
A: Yes
Is there any fP 1 for framework manager?
A: No, you would use the same FP1 install but the 32bit version as FM is only 32 bit.
What abt 32 bit and 64 bit Fix pack?
A: The 64bit FP goes on 64Bit server installs. The 32Bit goes on 32bit installs. Check your cmplst.txt to see if you have 32 or 64bit installs.
Thanks for this post. I resolved the recursive prompt when having stored procedure query subject with parameters after installing the 32 bit fp 2 "fix pack: 10.2.1-BA-CBI-Win32-FP002" in the FM directory.
Please let me know which FP did you applied for Framework manager? I applied the BI server FP2 in cognos install folder but was unable to install the same fix pack in Framework manager install folder.
Quote from: nehaleo03 on 07 May 2014 08:55:52 AM
Please let me know which FP did you applied for Framework manager? I applied the BI server FP2 in cognos install folder but was unable to install the same fix pack in Framework manager install folder.
You need to install the 32-bit version of the fix pack into your FM install.
MF.
Hi Thanks..We have the same issue ..it's not even show me columns under query subject to publish and test my package..
I went through IBM site rgd FM installation ..this is what I see ..
*****Framework Manager is available as a 32-bit installation only. It must be installed on a 32-bit computer with a Microsoft Windows operating system.****
Can you please give me more steps on how you are able to resolve this issue..
Thanks
I am having the same problem today. I installed FP3 to 10.2.1 2 months ago but since FM is still 32 bit so I have to install 32 bit pack? How do I get it? The IBM site has either 32 bit or 64 bit FP3 for BI Server. My BI server is 64 bit but FM is 32 bit and FP3 has been
applied to 64 bit BI server. So do I have to apply 32-bit FP3 to FM install? If so, where do I get it? I couldn't find it on IBM site.
Does the 64 bit FP3 made for BI Server apply the fixes to 32 bit FM too?
Quote from: cognostechie on 30 Oct 2014 06:58:28 PM
I am having the same problem today. I installed FP3 to 10.2.1 2 months ago but since FM is still 32 bit so I have to install 32 bit pack? How do I get it? The IBM site has either 32 bit or 64 bit FP3 for BI Server. My BI server is 64 bit but FM is 32 bit and FP3 has been
applied to 64 bit BI server. So do I have to apply 32-bit FP3 to FM install? If so, where do I get it? I couldn't find it on IBM site.
Does the 64 bit FP3 made for BI Server apply the fixes to 32 bit FM too?
On the fixcentral site, there are two downloads for FP3 - a 64-bit FP3 and a 32-bit FP3. The 32-bit FP3 download is the one to use - it is for *all* FM installs plus it is for 32-bit server installs (if anyone has a 32-bit server install these days). If you have a 64-bit server install, you need to download both FP3 installs, and apply the 64-bit to your server, and the 32-bit to wherever you have FM installed (which may be in multiple places, including the server).
Cheers!
MF.
As always, muppet comes to help ! Thanks so much MFGF, that worked perfectly.