SP datasource not showing new field

[cisscott]

Hi all,

(CR 9.0 , MS SQL Server 2k)

I have a stored procedure that is the datasource of a subreport. THis datasource used to sum amounts for cash, checks, Mastercard and Visa. I have to change it to lump all the credit cards in as a single sum. I changed the SP to do this, but the field I assigned to this funtion in the SP will not show up as a database field in my report explorer. Here is that part of the original SP:

select distinct ep1.eventid as 'eventid', e.event,
  (select sum(ep.amount) from live.dbo.eventpaid ep where ep.paymethod = 'A' and ep.eventid = ep1.eventid ) as 'cash',
  (select sum(ep.amount) from live.dbo.eventpaid ep where ep.paymethod = 'B' and ep.eventid = ep1.eventid ) as 'check',
  (select sum(ep.amount) from live.dbo.eventpaid ep where ep.paymethod = 'C' and ep.eventid = ep1.eventid ) as 'mc',
  (select sum(ep.amount) from live.dbo.eventpaid ep where ep.paymethod = 'D' and ep.eventid = ep1.eventid ) as 'visa'
from live.dbo.eventpaid ep1, live.dbo.event2 e

..........................

I edited the SP to the following:

select distinct ep1.eventid as 'eventid', e.event,
  (select sum(ep.amount) from live.dbo.eventpaid ep where ep.paymethod = 'A' and ep.eventid = ep1.eventid ) as 'cash',
  (select sum(ep.amount) from live.dbo.eventpaid ep where ep.paymethod = 'B' and ep.eventid = ep1.eventid ) as 'check',
  (select sum(ep.amount) from live.dbo.eventpaid ep where ep.paymethod = 'R' and ep.eventid = ep1.eventid ) as 'creditcard'

I should be seeing the field "creditcard" in the database section of my report explorer, but I cannot. Is there something I have forgotten to do? Thanks !

 

[rosemaryl]

Did you try doing a Database/Verify Database?

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[cisscott]

I should have said what I have done to try and resolve the problem. Sorry. Yes, I did that, and I also tried to get it to let me remap the fields, but I'm not having much luck with that, as the option to remap isn't coming up. I am about to change the datasource entirely, and then change it back, so that I can get the remap fields dialog box back.

 

[cisscott]

Remapping the fields worked. I had to change the datasource to something else, save it, and change it back in order to get it to allow me to remap the fields. Isn't there an easier way to do that? The help files talk about remapping but don't actually tell you how to remap without changing data sources. Either that or they do, and i missed it... surely not

 

[rosemaryl]

Dear Ciscott,

No, that's how I have done it ... when the database/verify doesn't work. Usually, I create my sp with an 1 on the end. Then change the report to it. Then run sp again without 1 in name and point report back. Agreed, it is not user friendly.

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.