multiple value in prompt page while using Sp

[pooja]

how can we use multiple value in report studio prompt while using stored procedure.

thank you in advance

[pooja]

 ???

[pooja]

actually we tried #promptmany# in FM, but came to know that does not work. now we need to display multiple value in prompt page from SP.but are able to select only single value.

if anybody knows how to solved this issues......kindly appreciated.......

we have like #prompt('user_id','char')#

thank you again..kindly

[pooja]

 ???

[pooja]

any help kindly appreciated...

[MFGF]

Hi,

Does this question relate back to your other post where you were asking about getting a macro prompt built into a Stored Procedure in FM to display multiple values?

If so - the answer is the same.  You will need to explicitly code a value prompt in your Report Studio report, with the prompt values based on a query that either uses a normal SQL query subject to retrieve the entire list of items, or uses a different Stored Procedure query subject which retrieves all the items.

Regards,

MF.

[pooja]

@ MFGF

question is--

we have sucessefully test SP in FM and get the results set. now while creating report
we need to select multiple value in report prompt page, whuich we we not able.we can able to
generate report, but just doin manually using text prompt.
we are are new on using  SP

sorry for asking same question

[MFGF]

Pooja wrote:

MGMF,

sorry to bother you again by sending pm.

i have been asking same Qs no of times and still not able to get clear, hence sending you a PM.

actually we are totaly newbe reagrding SP. so pleaese help me by giving some ideas--

-we created QS in FM using SP
-gave the argument name---parameter name --and insert macro
ex- #  prompt('USER_ID','char')#
    #  prompt('REPORT_FROM_DT','char')#
    #  prompt('REPORT_TO_DT','char')#
    #  prompt('DEPARTMENT','char')#--- tested and workis fine

---create sinple list report using same SP QS.
--prompt populated autimatically
---NOW Qs is..?
when we give a single value then report works fine....
BUT  we have NO idea how to do for MULTI-SELECT...

i kindly thank you in advacem, please...........

Hi Pooja,

From the looks of things, your Stored Procedure requires four parameters to be passed to it in order to return a result set - USER_ID, REPORT_FROM_DT, REPORT_TO_DT, DEPARTMENT.  Does the SP allow multiple values to be passed in for each parameter?

You are currently using Prompt() macro functions to generate single-value prompts for each parameter.  Prompt() will not allow multiple values to be selected and passed in - this would require using the Promptmany() macro function (and also would require your SP to be written in such a way that multiple values per parameter are allowed).

MF.

[pooja]

yes, our SP gives the result set while giving value on those four-parameter.we tried #promptmany#..but is gave error...and came to know it  does not allow #promptmany#
...

[MFGF]

But you haven't answered the key question - is the stored procedure written in such a way that it will allow multiple values to be passed in for each parameter?

MF.

[pooja]

YEs it is........

We tried with '~' it gave multiple values.
now, how we going to create in prompt page?, please

[MFGF]

Hi Pooja,

No - sorry - I mean passing multiple values in for a single parameter, not receiving multiple rows back in the result set.  '~' is a single value being passed in, not multiple values.  How does the SP behave when you pass multiple values in via a single parameter?  Is it coded to accept these?

MF.

[pooja]

this is what happen in FM while testing SP parameter

when we give single value  = got results
when multiple value = just a field name with NO DATA,

[MFGF]

Sounds like your SP has not been written to accept multiple values for each parameter.  This will explain why your single-select prompts work, but not multi-select.  If that is the case it is nothing to do with Cognos 8 - rather a limitation of the Stored Procedure you are using.

Regards,

MF.

[dlcognos]

The first step required for implementing reports with multiple sp's would be having a stored procedure which is ddesigned to accept multiple values of prompts.

For eg: if i am to display report for product type and i am selecting Product TypeID's in prompt selection, the sp should contain a string paramter say ProductTypeID which will accept my selected multiple values separated by some special charcter.
Say: 1:2

If we selected 1,2 productTypeId's, the input to SP should be like 1:2. The SP should decode this 1:2, to process for 1 and 2 multiple selected value