Hi,
I'm trying to create a very simple report. This report consists of 4 fields where 3 already are found in 1 table
For example: Table 'Products' consists of:
productId - primary key (not in report)
productname - (printed in report)
createDate - (printed in report)
parentId - foreign key which maps to another product and so creating a hierarchy. Not directly printed
For re-generating the hierarchy i've used a stored procedure which takes 2 parameters: 1 IN parameter (productid) and 1 OUTPUT parameter which should return the whole product string (e.g. product1 > base product > another product > soap). I think this works correctly. In the query analyzer I've tried this and upon execution the next time this parameter is populated correctly.
My questions now:
1) how can I first do the query on the products table and pass the result (parentid) to the stored procedure
2)How can I place the result from the stored procedure in the report
Thx
Stored Procedure & reporting services
Mike Duke
Hi Brad, thanks for the reponse!
That would likely solve my first question, but I'm still searching how I can return the value from the stored procedure.
When I create a new dataset referencing the stored procedure, when I click the exclamation mark (execute query) ,I have to provide parameters (e.g. product id). When executed it looks like nothing happens, but when I try to execute the same stor. proc. the result is in the second parameter, which was returned by me.
Is there another way of returning the string from the stored procedure and thus using it in the dataset in reporting services When I click in the layout view, on a table, right-click it, choose properties, select value it says: 'dataset has no fields'.
THX
JoeSox
Hi Jack,
Take a look at subreports. You should be able to run the first part of your report, get a value, then pass that value to the sub report.
Here is one link to MSDN for subreports.
http://msdn.microsoft.com/library/default.asp url=/library/en-us/RSCREATE/htm/rcr_creating_layout_v1_2584.asp
Your report would have query one that returns the parentid. That parentid would be passed to the subreport where the SP is run, populating the rest of the data.