August 7, 2013 at 3:48 am
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 7, 2013 at 4:44 am
Still same.
August 7, 2013 at 4:48 am
I see three iif functions in your expression, but there should only be two. Remove the last one.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 7, 2013 at 8:44 am
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.
August 13, 2013 at 2:54 am
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
August 13, 2013 at 3:00 am
What is the result you expect?
--> Format(Fields!incident_ref.Value,"00000")
Are you sure the format function is OK? Is "00000" the formatting you need?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 13, 2013 at 3:12 am
What I want. In the picture you see the 2 column 1 with a letter and other with number. I want to combine those to so if it is i then i + the number like 64149 what you see in pictures..
So the result will be like;
i64149
And so on.
August 13, 2013 at 3:25 am
karim.boulahfa (8/13/2013)
What I want. In the picture you see the 2 column 1 with a letter and other with number. I want to combine those to so if it is i then i + the number like 64149 what you see in pictures..So the result will be like;
i64149
And so on.
In the original question you didn't say i + <number>, you just said <number>.
You just need to do a very simple modification in your expression.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 14, 2013 at 1:26 am
The qeustion was how can i convert the code in SQL (Expression). When you read the expression its says when event_type is i then + number zo i + 00000..
Im still searching 🙂
August 14, 2013 at 1:47 am
For the formatting you can try this,
Use the case to get the correct character in front (or an empty string), and then add the incident_ref in 5 digits. (include leading zero's if needed)
CASE Event_Type
WHEN 'c' THEN 'R'
WHEN 'i' THEN''
ELSE 'P'
END + RIGHT('00000' + CAST(incident_ref As nvarchar(5) ), 5)
Louis
August 14, 2013 at 2:06 am
Louis Hillebrand (8/14/2013)
For the formatting you can try this,Use the case to get the correct character in front (or an empty string), and then add the incident_ref in 5 digits. (include leading zero's if needed)
CASE Event_Type
WHEN 'c' THEN 'R'
WHEN 'i' THEN''
ELSE 'P'
END + RIGHT('00000' + CAST(incident_ref As nvarchar(5) ), 5)
Louis
I have tweak the qeury and it comes to this;
select event_type,
'Event_Type' = CASE
WHEN 'c' THEN 'R'
WHEN 'i' THEN ''
ELSE 'P'
END + RIGHT('00000' + CAST(incident_ref As nvarchar(5) ), 5)
from incident, inc_data
but get the following error;
Msg 4145, Level 15, State 1, Line 2
An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.
August 14, 2013 at 2:44 am
karim.boulahfa (8/14/2013)
Louis Hillebrand (8/14/2013)
For the formatting you can try this,Use the case to get the correct character in front (or an empty string), and then add the incident_ref in 5 digits. (include leading zero's if needed)
CASE Event_Type
WHEN 'c' THEN 'R'
WHEN 'i' THEN''
ELSE 'P'
END + RIGHT('00000' + CAST(incident_ref As nvarchar(5) ), 5)
Louis
I have tweak the qeury and it comes to this;
select event_type,
'Event_Type' = CASE
WHEN 'c' THEN 'R'
WHEN 'i' THEN ''
ELSE 'P'
END + RIGHT('00000' + CAST(incident_ref As nvarchar(5) ), 5)
from incident, inc_data
but get the following error;
Msg 4145, Level 15, State 1, Line 2
An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.
You need an input_expression for the CASE expression, see BOL.
Simple CASE expression:
CASE input_expression
WHEN when_expression THEN result_expression [ ...n ]
[ ELSE else_result_expression ]
END
So, for your code it will look like this:
select event_type,
'Event_Type' = CASE event_type
WHEN 'c' THEN 'R'
WHEN 'i' THEN ''
ELSE 'P'
END + RIGHT('00000' + CAST(incident_ref As nvarchar(5) ), 5)
from incident, inc_data
August 22, 2013 at 10:57 am
Formulas like this can be fully automatically converted.
Take a look at Crystal Migration Services.
August 22, 2013 at 2:23 pm
rpt2ssrs (8/22/2013)
Take a look at Crystal Migration Services.
http://www.crystalmigration.com[/quote%5D
Why must I pay for a service if I can do it on my own and the whole world can learn it and sharing!!! That is the best solution!!!
August 23, 2013 at 6:51 am
karim.boulahfa (8/22/2013)
rpt2ssrs (8/22/2013)
Formulas like this can be fully automatically converted.Take a look at Crystal Migration Services.
http://www.crystalmigration.com[/quote%5D
Why must I pay for a service if I can do it on my own and the whole world can learn it and sharing!!! That is the best solution!!!
YOU don't have to pay for anything, but it is an option for others who may want to go that route.
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply