Topic
  • 4 replies
  • Latest Post - ‏2013-05-01T17:49:35Z by DanielWagemann
bb_22
bb_22
4 Posts

Pinned topic Delay query execution until selection is made from value prompt

‏2013-04-30T20:51:06Z |

Is there a way to delay the execution of a query until the user has made a selection from a value prompt?  I have a report that has 2 queries.  The "main" query is executed when the report is run.   The report has a second query that uses a value prompt as a parameter.  I'd like the 2nd query to only be executed if/when the user selects something from the value prompt.  The 2nd query has a filter that uses the parameter like such

        [measure] = ?ValuePromptParam?

 

When the report is run it always requires a value for the ValuePromptParam before the report is run (even if the prompt is marked optional).  I'd like the report to run and execute the main query, and hold off executing the secondary query until the user picks something from the value prompt.

 

thanks,

Brett

  • OpherB
    OpherB
    679 Posts

    Re: Delay query execution until selection is made from value prompt

    ‏2013-05-01T00:27:55Z  

    I don't have time to test this, but you may be able to use a conditional block around the outpuf of the second query and set a rendering variable to "FALSE" at the start of the report. Then if/when the user selects a value from the prompt you can change the rendering variable to "TRUE" and the query would run.

    Not sure if that would work, but that's the approach I would try first.

    Good luck,
    Opher

     

  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: Delay query execution until selection is made from value prompt

    ‏2013-05-01T14:10:49Z  

    Being prompted even though the prompt itself is set to optional could mean one of three things:

    First the prompt object is not marked as optional...(Not the case as you stated.)

    The filter that uses the optional prompt is not set to optional...(Probably the cause of your prompting)

    The prompt is projected within a data container such as a list/crosstab/chart and is causing a hard prompt fault on the spec validation.

     

    The above being said, I would actually make the prompt object and filter required and assign a default value that would result in the data container not returning any data.  I would then apply the no data provider to hide the object.  

    What this means is that if no value is selected for your prompt the default value is used, the default value returns no data so the data container is hidden by the no data provider.

     

  • bb_22
    bb_22
    4 Posts

    Re: Delay query execution until selection is made from value prompt

    ‏2013-05-01T15:25:33Z  

    Being prompted even though the prompt itself is set to optional could mean one of three things:

    First the prompt object is not marked as optional...(Not the case as you stated.)

    The filter that uses the optional prompt is not set to optional...(Probably the cause of your prompting)

    The prompt is projected within a data container such as a list/crosstab/chart and is causing a hard prompt fault on the spec validation.

     

    The above being said, I would actually make the prompt object and filter required and assign a default value that would result in the data container not returning any data.  I would then apply the no data provider to hide the object.  

    What this means is that if no value is selected for your prompt the default value is used, the default value returns no data so the data container is hidden by the no data provider.

     

    Thanks for the suggestion.  I did make the filter optional and it alleviated the forced prompting.  Though I was hoping to not have to execute the query at all until after the report has run and the the user has provided input via the prompt.

  • DanielWagemann
    DanielWagemann
    850 Posts

    Re: Delay query execution until selection is made from value prompt

    ‏2013-05-01T17:49:35Z  
    • bb_22
    • ‏2013-05-01T15:25:33Z

    Thanks for the suggestion.  I did make the filter optional and it alleviated the forced prompting.  Though I was hoping to not have to execute the query at all until after the report has run and the the user has provided input via the prompt.

    Sure!  Then it would be Opher's approach of render variables, but sometimes the logic can become a bit hairy.  You will need to decide which approach to take and the impact of each ... a query that is run returning nothing the first time or the additional logic required to check to see if a prompt value has been set or is the default value.

     

    Both approaches would work.