tfertil
Posts: 43
|
| Posted: 06/08/2011, 4:04 AM |
|
Hello:
I'm trying to use a Stored Procedure as datasource for a report (I'm using PHP and SQL-Server 2008).
When using the Report Builder, I select the procedure as datasource, and get the correct list of input parameters plus the RETURN VALUE.
But then CCS shows me the "Available Fields" list to select the report colums, but no fields are show but the parameters list. That is: I don't see the columns from my procedure, but @Param1, @Param2, etc...
The last sentence in the procedure is a SELECT, I already set the NOCOUNT ON in my procedure, end it with a " RETURN 0 " statement and, of course, tested it in SQL-Server Management Studio.
Any ideas? Thanks.
|
 |
 |
tfertil
Posts: 43
|
| Posted: 06/08/2011, 6:23 AM |
|
ADDITIONAL INFO:
I design the report using the parameters as "fields" as the designer shows me. Then I selected some columns and replace the Control source from @Param1 to "Code' (Where "code" is one of the colums for the data set returned by the stored procedure), publish it and guess what? It works!
Apparently, the problem is in the designer.
Setting a specific set of parameters, the procedure returns 14 rows in SQL Server, and also shows 14 rows in the report. But only the columns I manually changed to a correct field name are showing data.
So this is a partial solution.
But : how can I make the designer works (so it shows me the columns from the result dataset instead of the parameters)?
Thanks.
|
|
|
|
datadoit
|
| Posted: 06/08/2011, 6:46 AM |
|
Send this to support. Working with SP's in the designer is still in
it's infancy and this could help the product grow as kinks are worked out.
|
|
|
|
tfertil
Posts: 43
|
| Posted: 06/24/2011, 10:21 AM |
|
WORKAROUND:
I created a temporary table in my database with the same columns returned by the stored procedure. I designed my report around this table, and at the end, I replaced the datasource with the stored procedure.
If I want to filter, however, I can't specify a WHERE condition (which seems pretty logical because I'm using a stored procedure as datasource).
So I modified my Stored Procedure to receive some parameters and filter the data explicity inside my procedure. Then, back in CodeCharge, I assigned the parameters to be the URL values from the search record.
|
|
|
|
|
