SSRS Stored Procedure CASE statement not being returned to dataset

Question

I have a report that is using a Stored Procedure as the dataset. I have a simple case statement in my stored procedure that manipulates a set of captions based on the results in the record.

CASE 
WHEN CWLI.CAPTION='Hypo Tax – Medical'
THEN 'Medical Tax' 
WHEN CWLI.CAPTION='Hypo Tax Social' 
THEN 'Social Tax'
ELSE CWLI.CAPTION
END AS CAPTION,

CASE 
WHEN CWD.CAPTION='Hypo Tax – Medical'
THEN 'Medical Tax' 
WHEN CWD.CAPTION='Hypo Tax Social' 
THEN 'Social Tax'
ELSE CWD.CAPTION
END AS CWDCAPTION,

I see the expected results when I execute the stored procedure in SSMS, as below:

However, in my report I still see "Hypo Tax Social"

I have closed BIDS. I deleted the dataset and added it as a new data set. I have also changed my config file RSReportDesigner.config and changed CacheDataForPreview to false and deleted my .data file.

What could be going wrong?

I searched for rdl.data files and did not return anything. Though I changed my RSReportDesigner.config file Add Key="CacheDataForPreview" Value="false" , I would still expect to find residual .data files. Nevertheless, I rolled back this change to Add Key="CacheDataForPreview" Value="true"

I deleted my data source, my dataset and recreated from within VSS. I then created an external tool to delete these files for me. I get this message when I use my tool.

Could Not Find C:\Users\Pennie\Documents\Visual Studio 2008\Projects\Client*.rdl.data

I still see the correct results (the new CASE captions) in SSMS and the old captions in my report. I really want to handle this in the Stored Procedure, but I have a deliverable. Can I manipulate the captions in the expression of my tablix? Currently the expression is looking for the value from two fields. One field is at a company level of my application (these could be blank). Fields!CAPTION.Value, the other is at the system level (these will never be blank) Fields!CWDCAPTION.Value

Here is my current expression w/o the additional case.

=Iif(Fields!CAPTION.Value="",Fields!CWDCAPTION.Value,Fields!CAPTION.Value)

When I try to add an additional Iif in this expression. I just return True or False.

Thank again. Pennie

Well this lesson has taught me quite a bit about cache and .data... but the issue was that the idiot developer (me) was calling my test SP in my report.. My test SP did not contain the case statement. Wish I had more exciting news but the truth is easy to prove ;-)

I'm all set. Pennie

Answer 1

Sounds like you have added the CASE statement after you initially created the dataset. If that is the case you need to re-create the dataset again. Just to be clear not only delete the dataset from the report but also the dataset itself.