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 1:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 13,639, Visits: 10,534
Try with a single =.

Double == is SSIS expression language




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 #1481522
Posted Wednesday, August 7, 2013 2:14 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:26 AM
Points: 81, Visits: 173
Its say now this.

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'case'.


I have change a little bit (select and from statement) And the '0000' (in crystal) that the numbers must begin from 0 1 2 3 and if it line down its 0 #0 if it is the then 10.. Its weird i know.. :)


  Post Attachments 
Capture9.JPG (1 view, 26.26 KB)
Post #1481707
Posted Wednesday, August 7, 2013 2:22 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:26 AM
Points: 81, Visits: 173
Koen Verbeeck (8/6/2013)
Try with a single =.

Double == is SSIS expression language


if i do that i get a syntax error.


  Post Attachments 
Capture89.JPG (3 views, 29.79 KB)
Post #1481710
Posted Wednesday, August 7, 2013 2:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 13,639, Visits: 10,534
karim.boulahfa (8/7/2013)
Koen Verbeeck (8/6/2013)
Try with a single =.

Double == is SSIS expression language


if i do that i get a syntax error.


I think you are missing a bracket ) at the end.




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 #1481727
Posted Wednesday, August 7, 2013 2:56 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:26 AM
Points: 81, Visits: 173
I just trying that but is solve only the 2 and the third telling that the syntax is incorrect.

  Post Attachments 
Capture87.JPG (2 views, 27.05 KB)
Post #1481740
Posted Wednesday, August 7, 2013 3:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 13,639, Visits: 10,534
karim.boulahfa (8/7/2013)
I just trying that but is solve only the 2 and the third telling that the syntax is incorrect.


You forgot to put the "P" before the + in the last expression.




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 #1481763
Posted Wednesday, August 7, 2013 4:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:26 AM
Points: 81, Visits: 173
Still same.

  Post Attachments 
Capture5678.JPG (2 views, 14.86 KB)
Post #1481784
Posted Wednesday, August 7, 2013 4:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:51 PM
Points: 13,639, Visits: 10,534
I see three iif functions in your expression, but there should only be two. Remove the last one.



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 #1481785
Posted Wednesday, August 7, 2013 8:44 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, July 31, 2014 8:13 AM
Points: 287, Visits: 293
Below are instructions for handling your concern in SSRS, the case expression should handle it in SQL.

I set up a simple data set to make this work, containing the following:
select
'c' as event_type,
1 as incident_ref

What I gathered from the original Crystal report is that it checks the value of the "event_type" and if it is "c" then it takes the value in "incident_ref" and applies some formatting to it.

Below is the code to make it do that in SSRS (note the ".value" after both the event_type and incident_ref field references):

=iif(Fields!Event_Type.Value = "c","R" + Format(Fields!incident_ref.Value,"00000"),iif(Fields!Event_Type.value = "i",Format(Fields!incident_ref.Value,"00000"),"P" + Format(Fields!incident_ref.Value,"00000")))

With the sample data the report returns the expected result as you can see in the picture.



  Post Attachments 
SSRS_Report.JPG (4 views, 9.17 KB)
Post #1481914
Posted Tuesday, August 13, 2013 2:54 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 9, 2014 1:26 AM
Points: 81, Visits: 173
pgt892 (8/7/2013)
Below are instructions for handling your concern in SSRS, the case expression should handle it in SQL.

I set up a simple data set to make this work, containing the following:
select
'c' as event_type,
1 as incident_ref

What I gathered from the original Crystal report is that it checks the value of the "event_type" and if it is "c" then it takes the value in "incident_ref" and applies some formatting to it.

Below is the code to make it do that in SSRS (note the ".value" after both the event_type and incident_ref field references):

=iif(Fields!Event_Type.Value = "c","R" + Format(Fields!incident_ref.Value,"00000"),iif(Fields!Event_Type.value = "i",Format(Fields!incident_ref.Value,"00000"),"P" + Format(Fields!incident_ref.Value,"00000")))

With the sample data the report returns the expected result as you can see in the picture.



The Expression works but the value dont change (Look pictures):

This is my sql query:

SELECT inc_data.event_type, incident.incident_ref, bldng_room.bldng_room_sc, incident.usr_ref, inc_cat.inc_cat_sc, product.product_sc, usr.usr_n, incident.inc_status, incident.date_logged, incident.inc_resolve_act, incident.inc_close_date, incident.inc_resolve_sla, sectn_dept.sectn_dept_sc, inc_prior.inc_prior_sc, incident.callback_rmk, inc_data.total_service_time, sectn_dept.dept_sc, sla.sla_sc, incident.inc_resolve_due, inc_data.u_date1, bldng.address2, sectn_dept.sectn_sc, item.item_sc, item.item_keya
FROM Assyst.dbo.bldng bldng
INNER JOIN ((((Assyst.dbo.sectn_dept sectn_dept INNER JOIN
(Assyst.dbo.inc_cat inc_cat INNER JOIN
(Assyst.dbo.inc_prior inc_prior INNER JOIN
((Assyst.dbo.product product INNER JOIN
Assyst.dbo.item item ON product.product_id=item.product_id)
INNER JOIN (Assyst.dbo.inc_data inc_data
INNER JOIN Assyst.dbo.incident incident ON inc_data.incident_id=incident.incident_id) ON item.item_id=incident.item_id) ON inc_prior.inc_prior_id=incident.inc_prior_id) ON inc_cat.inc_cat_id=incident.inc_cat_id) ON sectn_dept.sectn_dept_id=incident.sectn_dept_id)
INNER JOIN Assyst.dbo.usr usr ON incident.aff_usr_id=usr.usr_id)
INNER JOIN Assyst.dbo.sla sla ON incident.sla_id=sla.sla_id)
INNER JOIN Assyst.dbo.bldng_room bldng_room ON incident.bldng_room_id=bldng_room.bldng_room_id) ON bldng.bldng_id=bldng_room.bldng_id
WHERE NOT (inc_data.event_type='n' OR inc_data.event_type='p')
AND NOT (inc_cat.inc_cat_sc=N'RFC BACK-OFFICE' OR inc_cat.inc_cat_sc=N'RMA')
AND incident.date_logged<{ts '2013-08-07 00:00:00'} and incident.incident_ref = '64169'

this is a qeury that gets the value in expression1(Picture).jpg



  Post Attachments 
expression1.JPG (4 views, 10.61 KB)
expression2.JPG (5 views, 9.56 KB)
Post #1483621
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse