At my current client we have a new data item based on a stored procedure (SQL Server 2012). This stored procedure accepts a single value at a time. We now want to use this stored procedure table for a burst report. So far I have made a query that gets me a list of values to give to the stored procedure. This has a parameter as the value. For a single value this works well. When I try the query with the list of values and connect this to the parameter in the other query based on the stored procedure, then I get no results.
It seems to me that the master detail relation gives all the values of the master query to the detail query at once. How do I set up my report so that the detail query gets one value at a time to process? This is needed to work with the stored procedure.
I'm a little confused on the flow of the process here.
If I understand you have a burst table which generates multiple reports. Each page in the report is supposed to feed a value from the burst to the stored proc.
This should be working fine. The sproc is defined with prompt macros in the fm for the input parameters, right? And you're connecting the burst table fields to the parameters? Try pulling the burst fields as text items into the page of the report. If nothing is directly referencing those items from the burst, the query may not be including them in the SQL when generating the query.
Hello CognosPaul, if I understand you right, the issue may be that the SSCC from the master query is missing in the detail query or page and so missing from the actual query? How should I do that exactly? I have made a master-detail between the list on the page and a page set (that is the method to get 1 PDF per value?). Should I make a singleton on the detail page with also a master-detail relation to the main query? Or is it enough to have the master query related to the page and take the query item from this query in the text item?
You probably don't need a pageset if it's a single page burst. The bursting should take care of that for you.
Singletons don't actually provide master/detail relationships for some reason (and I have an RFE about that here: https://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=62883 (https://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=62883)). Try using a repeater.
But yeah, drag the items from your stored procedure into your repeater or list or whatever data container you're using. When you define the master/detail on that you should see the parameters that are defined in the framework model. Just attach the burst to those parameters.
Somehow I have another issue. When I get my query that produces the list to my page and I put a master-detail to my repeater table, I get 1 page as a result, even though the list contains many values. It takes only the first value from the result list. I can't take the item from the master query in the repeater table. I've tried putting the parameter in the repeater table, but that still gets me only a one page result.
My final report will use 5 different stored procedures in 5 page elements. I already notice that I have to make a connection with each stored procedure. I've now tested with only one of them.
So, my setup now:
1) query with values connected to my page
2) query with stored procedure and parameter connected to repeater table
3) master-detail between the page query values and repeater query parameter
what am I missing / failing?
I'm having a lot of trouble visualizing this. Can you attach the report xml?
Paul, I want to PM you but I'm blocked to send you messages. I prefer to get you the XML outside of the thread. How can we arrange this?
I find that the issue is in my testing. I test the report by running it from Report Studio and then expect multiple page results. That doesn't happen. When I set up the burst and test with bursting, it works as expected.
Paul thanks for mailing me to look at the issue.