CODEWITHSUNDEEP
stored-procedurescrystal-reportssql-server-2008-r2
aMazing
aMazing

Reputation: 1460

Stored Procedure not showing fields in Crystal reports

Using: CR in VS2010, SQL Server 2008R2

I have a SP that returns results when run in SQL Server, but when I add this to a CR it doesnt show any fields in the Field Explorer. But if I try any other SP, it works fine I can see its fields. Steps I am performing:

  1. Open existing CR
  2. Right CLick Database Fields --> Database Expert --> remove the old SP --> Add the new SP
  3. A window with Enter Values pops-up, I set all to NULL and click OOK
  4. The New SP shows up in the Database expert right side
  5. I can see the new SP in the Database Fields Section in the Field Explorer.
  6. However there is no PLUS + Sign next to it. That means it doesnt show its fields.

Infact the new SP and the old have both have the same number of fields. Just some sql changed.

Pissed of with this one..Please help.

Thanks

Upvotes: 4

Views: 13797

Answers (4)

ali
ali

Reputation: 329

To see the stored procedure fields in the crystal report, follow the below steps:

  1. Right click in the report, and from the vertical tab select (Field Explorer) enter image description here

  2. Right click in the stored procedure that you selected before, and from the vertical tab select (Verify Database)

enter image description here

  1. You will see page that show the default value as null as showing in the below image enter image description here
  2. Uncheck the (Set to Null) checkbox and enter the correct values that once you enter it; It will retrieve data using your stored procedure.
  3. In my case, I should put the data for both parameters as shown in the below image: enter image description here
  4. Then click ok button, you will see dialog message as shown in the below image: enter image description here
  5. Click (Ok) button and another dialog message will be shown to you (The database is now up to date.); Click (Ok) button for this dialog also. enter image description here
  6. Now, you should see the full selected columns as shown in the below image: enter image description here

Upvotes: 0

SHESHAGIRI RAO
SHESHAGIRI RAO

Reputation: 1

I had the same issue, almost spent 1 day looking for different aspects of crystal report, then I found

  1. There was problem in stored procedure which I corrected still field editor was not visible.
  2. Then I found crystall reports installed version was corrupted, hence re-installed it again and it worked.

Upvotes: 0

keltins
keltins

Reputation: 41

HOW TO update the Stored Procedure in Crystal Report after altering the Stored procedure in the database.

This procedure can also be used when you have added a parameter to the Stored Procedure in the database.

  1. Make sure your SP returns a dataset not via another SP inside your SP.

    ex. this will not work: @sql = 'select * from sometable' exec(@sql)

    If this is the case, temporarily add a select statement at the end of your SP

    @sql = 'select column1, column2 from table1' exec(@sql)

    select column1, column2 from table1

  2. In Crystal Report, Fields Explorer, right click your SP and Set DataSource Location. Fill in the connection details to the DB.

    • select your the existing SP and the SP from the new DB connection, then click the Update button.

  3. In CR menu, Database->Verify Database

  4. After Database has been verified, remove the temporary select statement from Step1.

Upvotes: 4

aMazing
aMazing

Reputation: 1460

I got it by not accepting the default selection crystal makes in the subreport links on the left side. I had to change it to my own and no tuse the defaults.

That worked. Thanks

Upvotes: 1

Related Questions