Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 7, 2015 2:47 AM
Points: 114, Visits: 258
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:09 AM
Points: 15,501, Visits: 13,163
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 SQLKover.

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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 7, 2015 2:47 AM
Points: 114, Visits: 258
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:09 AM
Points: 15,501, Visits: 13,163
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 SQLKover.

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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 7, 2015 2:47 AM
Points: 114, Visits: 258
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:09 AM
Points: 15,501, Visits: 13,163
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 SQLKover.

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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 7, 2015 2:47 AM
Points: 114, Visits: 258
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


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 2:09 AM
Points: 15,501, Visits: 13,163
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 SQLKover.

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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 7, 2015 2:47 AM
Points: 114, Visits: 258
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


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 1:42 PM
Points: 4,107, Visits: 9,198
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




"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Post #1481367
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse