Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

convert crystal to SQL Expand / Collapse
Author
Message
Posted Tuesday, August 6, 2013 2:32 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:13 AM
Points: 81, Visits: 174
Hello SQLservercentral members,

How do I translate this crystal report sql in Microsoft sql. The following is the syntax:

if {inc_data.event_type}="c" then "R"+CStr({incident.incident_ref},"####0")
else if {inc_data.event_type}="i" then CStr({incident.incident_ref},"####0")
else "P"+CStr({incident.incident_ref},"####0")
Post #1481201
Posted Tuesday, August 6, 2013 2:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 13,295, Visits: 11,086
This is not SQL, it is some sort of Visual Basic.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1481203
Posted Tuesday, August 6, 2013 2:43 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:13 AM
Points: 81, Visits: 174
This is Crystalreport. In crystal report you can use Formula Fields. know i wanne convert it.

When you read the code you can see that the difference is little. I hope you can help me forwards.

(Koen ben je Nederlands?)
Post #1481204
Posted Tuesday, August 6, 2013 2:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 13,295, Visits: 11,086
So you want to use this formula as a SSRS formula?
In that case, it would be something like this:

iif(Fields!event_type == "c","R" + Format(Fields!event_type,"####0"),iif(Fields!event_type == "i",Format(Fields!event_type,"####0"),"P" + Format(Fields!event_type,"####0"))

Not 100% sure about the format function though, so you better check if it works.

ps: ik ben van België, maar ik spreek Nederlands




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1481206
Posted Tuesday, August 6, 2013 5:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:13 AM
Points: 81, Visits: 174
Koen Verbeeck (8/6/2013)
So you want to use this formula as a SSRS formula?
In that case, it would be something like this:

iif(Fields!event_type == "c","R" + Format(Fields!event_type,"####0"),iif(Fields!event_type == "i",Format(Fields!event_type,"####0"),"P" + Format(Fields!event_type,"####0"))

Not 100% sure about the format function though, so you better check if it works.

ps: ik ben van België, maar ik spreek Nederlands


Ik voegde het in mijn expression bij waar de gegevens aangepast moeten worden alleen het werkt niet... Doe ik iets verkeerd.

look pictures


  Post Attachments 
Capture.JPG (8 views, 11.26 KB)
Capture1.JPG (7 views, 21.22 KB)
Post #1481257
Posted Tuesday, August 6, 2013 6:25 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 13,295, Visits: 11,086
I'll continue in English for people who accidentilly stumble upon this thread.

You entered the expression in the text field itself, so it is just text, not an expression.
Right click on the text box and select expression from the context menu. Enter your expression there.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1481280
Posted Tuesday, August 6, 2013 7:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:13 AM
Points: 81, Visits: 174
It is in the expression box.

Look picture


  Post Attachments 
Capture23.JPG (6 views, 32.56 KB)
Post #1481308
Posted Tuesday, August 6, 2013 7:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:42 AM
Points: 13,295, Visits: 11,086
Any errors?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1481312
Posted Tuesday, August 6, 2013 7:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:13 AM
Points: 81, Visits: 174
In the example you see that i must use the = :) now im use it but.. I get a syntax error..

See picture.


  Post Attachments 
Capture4.JPG (59 views, 47.13 KB)
Capture5.JPG (5 views, 16.02 KB)
Post #1481333
Posted Tuesday, August 6, 2013 8:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:13 AM
Points: 1,708, Visits: 4,854
I'm not certain what formatting "####0" represents, it could be padding with spaces or zeros, but basically it would be a CASE expression like so:

case inc_data.event_type
when 'c' then 'R'+left('00000'+cast(incident.incident_ref as varchar),5)
when 'i' then left('00000'+cast(incident.incident_ref as varchar),5)
else 'P'+left('00000'+cast(incident.incident_ref as varchar),5)
end

Post #1481367
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse