Available fields

Question

Post reply

Available fields

QPR

SSC Eights!

Points: 814
More actions
December 13, 2005 at 8:14 pm

#111027

(SQL Server 2000)
I set the datasource and set the query (an SP that accepts 2 input parameter) and click next.
The available fields screen has only one field returned when the actual SP returns at least 6. I want to group on a given field but it is not in the list!
Any ideas? The SP uses a temp table and internal vars. Is reporting services/VS.NET 2003 not able to determine these at the time or report creation?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ofir shoval Ten Centuries Points: 1278 More actions · Answer 1

Well,U need to run the query via the VS then after u c the fields as dataset do a refresh,then the field will apear...

QPR SSC Eights! Points: 814 More actions · Answer 2

the report is "fed" by a stored procedure. Therefore the "query" is exec mySPname param1, param2. A colleague has found a work-a-round and that is to hack the XML (.rdl) file and manually adding the fields.

ofir shoval Ten Centuries Points: 1278 More actions · Answer 3

ofir shoval

Ten Centuries

Points: 1278

December 15, 2005 at 2:15 am

#609653

Did u tryed my solution???

Will SQL 4 Food SSCrazy Points: 2253 More actions · Answer 4

I've had some difficulty with this myself, but it's not too hard to accomplish. My situation involves something similar - I have one stored proc which returns different shaped record sets depending on a type parameter. For example:

usp_report @type = 'TY_LY_YTD' will return entity, product, valty, vally, and valytd fields

usp_report @type= '13MONTH_TREND' will return entity, product, val1, val2, ... val13 fields

One solution is to call your sproc with valid parameters for the report type you want, as mentioned by ofir shoval above and click the refresh fields icon.

You can also right click your fields pane and select 'add.' In the window which pops up, give your field the name you'd like to use in Reporting Services, then the name as passed back in your query, sproc, table, etc. or choose calculated field and define it there.

Of course, working with the RDL directly is an option if you have a lot of fields to add or need find/replace capability when altering an existing report to fit a new mold...

Hope this helps,

Carter

But boss, why must the urgent always take precedence over the important?

QPR SSC Eights! Points: 814 More actions · Answer 5

I'm not I understood what you meant.

Run the query in VS?

QPR SSC Eights! Points: 814 More actions · Answer 6

Thanks, I have added valid params to the exec sp line.

They are date fields so I add '2005-1-1','2005-12-1'

The following screen (which enables you to select which fields to include, grouping and sorting) contains only 1 field. Therefore I can't select a field to group by.