Reputation: 5867
I have a stored procedure that returns three columns worth of data to our SSRS report. The stored procedure does not alias two of those columns, so they are returned unnamed. Here is an example of what the return dataset might look like:
[FundName] [blank] [blank]
Abc col2val1 col3val1
Def col2val2 col3val2
Ghi col2val3 col3val3
I'd like to be able to use an expression in SSRS to retrieve the values from column 2 and 3. Here's an example of what retrieving data from FundName would look like:
=Fields!FundName.Value
Is there any way to replace the column name (in this example, FundName) with say, the index or position of the column, like so:
=Fields![0].Value //returns FundName values
=Fields![1].Value //returns column 2 values
=Fields![2].Value //returns column 3 values
Thank you in advance.
Upvotes: 0
Views: 3426
Reputation: 5867
Fortunately, a way to accomplish this has been found. Since our stored procedure could not be changed and it did not return enough information for SSRS to generate a report (missing column names in the resulting DataSet), we changed the way our DataSet gets populated.
In the DataSet query builder, we created a temporary table and had the stored procedure insert into that temporary table. Once inserted, we selected all the values in our temporary table which populated the DataSet. Now the DataSet has 3 columns with 3 column names to be used by our report.
CREATE TABLE #tempTable (
FundName varchar(50),
col2 dec(15,4),
col3 char(8)
)
insert into #tempTable
exec storedProcedureName
select * from #tempTable
drop table #tempTable
Then you can access those column values in an expression just like before:
=Fields!FundName.Value //returns FundName values
=Fields!col2.Value //returns column 2 values
=Fields!col3.Value //returns column 3 values
I hope this helps anyone else with this particular issue.
Upvotes: 1
Reputation: 12291
If your stored procedure is not returning the columns names then you can't create a dataset in SSRS as it will throw an error
An item with the same key is already been added
and there is now way you can reference the column name using index in SSRS
Upvotes: 1