Why does my Dataset display no fields in ReportServer, and can I get it to do so?

Question

I opened a report I started in BIDS in MS SQL Server Report Builder 3.0, as I read an answer here on SO that said that was the easiest way to create a table containing all the values in a Dataset.

So I opened my .rdl file there, selected the Insert tab, then Table > Table Wizard, and the dataset from the "Choose an existing dataset in this report or a shared dataset" list.

When I select the "Next" button of the wizard, though, all lists are empty (Available fields, Column groups, Row groups, Values).

If I select "Next" again, I get, "The values field list must contain at least one field."

Those are auto-populated, though, and, as written above, are as empty as a politican's brain.

Is it because my dataset is a StoredProc, and returns data from a temp table? If so, is there a workaround?

Note: I also tried the Matrix > Matrix Wizard, with the same results.

UPDATE

Also and doubtless relatedly (no pun intended), when I try to run the report from within ReportBuilder, I see:

What a revoltin' development!

UPDATE 2

And when I return to BIDS to work on the project and try to add an Expression in a Matrix, in the Edit Expression dialog, on selecting the Dataset of interest, I get, " dataset has no fields."

Ay, caramba!

UPDATE 3

In response to lrb's answer: I don't know if my SP is really unparseable or not; it does return values from a temp table - Here is the end of it:

SELECT PLATYPUSDESCRIPTION, WEEK1USAGE, WEEK2USAGE, USAGEVARIANCE,
WEEK1PRICE, WEEK2PRICE, PRICEVARIANCE, PRICEVARIANCEPERCENTAGE
FROM #TEMPCOMBINED 
ORDER BY PLATYPUSDESCRIPTION;

Could that (using a temp table) be the problem?

UPDATE 4

When adding an Expression to a textbox like so:

=Fields!PLATYPUSDESCRIPTION.Value

...I get the following fingerwag on the Preview tab:

The definition of the report 'bla' is invalid. The Value expression for the textbox 'textbox4' refers to the field 'PLATYPUSDESCRIPTION'. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.

Surely there's a way to use results from temp tables in an SSRS report, no es cierto?

Answer 1

This will happen when the query or stored procedure can not be parsed with certainty. For example, if your data set is a store procedure that returns something like the following:

IF(@SomVariable=1)
    SELECT 1,2,3,4
ELSE
   SELECT 'A','B','C'

The above logic in a SP would be horrible, however, the field name and datatypes can not be determined. The same holds true in other edge case scenarios.

What you can do for a work around is to trick the parser by modifying your sp and offering up a clean return statement, then changing the sp back to its original form. Since the metadata is persistent until the next refresh, your values will hold. NOTE : If the problem occurs when returning temporary tables in your dataset see #4 below.

1. Modify your existing stored procedure

ALTER PROCEDURE MyProcedureThatDoesNotParse()
AS
BEGIN
    /*COMMENT OUT CURRENT SP LOGIC
    ...
   */
    SELECT 
      MyField1=1,
      MyField2='String',
      MyField3=0.01
END

2. IN SSRS Refresh the fields for your dataset. NOTE : You will see MyField1,MyField2 and MyField3 in the fields list.

3. Revert the changes to your stored procedure.

4. For queries or SP's that return a local #temporary table, global ##temporary table or a table valued @variable, it seems that aliasing the temp structure works. I.E

SELECT * FROM #TABLE --Does not always parse in SSRS
SELECT * FROM #TABLE T --Seems to be able to be parsed by SSRS