CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-expression
StoriKnow
StoriKnow

Reputation: 5867

SSRS Creating an Expression with unkown column names

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

Answers (2)

StoriKnow
StoriKnow

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

praveen
praveen

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

Related Questions