This topic contains 3 replies, has 0 voices, and was last updated by JCirocco 7 years, 3 months ago.
-
AuthorPosts
-
August 16, 2017 at 12:44 pm #22783
bradsimpsonDoes anybody fancy themselves a SQL guru that would like to give me some pointers on cleaning up this mess of a CASE WHEN statement? We have a list of product lines (item classes) that either roll up as Product or Services that our company offers. My management wants to see Gross Profit and Revenue numbers broken out as well as overall and the SQL statement I've come up with below is the best way I have figured out to make that happen. Below is an example of how I calculate the Services Gross Profit numbers from a search with Main Line set to False in the criteria, so it is an item level search.
Field: Formula (Currency)
Summary Type: Sum
Formula: CASE WHEN {item.class} LIKE 'Implementation Services' THEN {altsalesamount} WHEN {item.class} LIKE 'Consulting Services' THEN {altsalesamount} WHEN {item.class} LIKE 'Managed Services' THEN {altsalesamount} WHEN {item.class} LIKE 'Third Party Implementation' THEN {altsalesamount} WHEN {item.class} LIKE 'Third Party Consulting' THEN {altsalesamount} WHEN {item.class} LIKE 'Third Party Managed Services' THEN {altsalesamount} WHEN {item.class} LIKE 'Third Party Services' THEN {altsalesamount} WHEN {item.class} LIKE 'Training' THEN {altsalesamount} WHEN {item.class} LIKE 'Professional Services' THEN {altsalesamount} WHEN {item.class} LIKE 'Residency' THEN {altsalesamount} WHEN {item.class} LIKE 'Service Desk' THEN {altsalesamount} END
This is a cached copy. Click here to see the original post. -
August 16, 2017 at 1:07 pm #22784
JCiroccoThis should work…
CASE WHEN {item.class} IN ('Implementation Services', 'Consulting Services',, 'Managed Services', 'Third Party Implementation', 'Third Party Consulting', 'Third Party Managed Services', 'Third Party Services', 'Training', 'Professional Services', 'Residency', 'Service Desk' THEN {altsalesamount} END
Me personally I would add a "other custom field" on the Class record that you could use as you "report grouping"… Just a thought.
-
August 16, 2017 at 1:23 pm #22785
bradsimpsonYou hit the nail on the head there JCirocco I had no idea I could create a custom field on Class. That would have saved me so much time writing these CASE WHEN statements. Thank you!
-
August 16, 2017 at 1:24 pm #22786
JCiroccoOriginally posted by bradsimpson
View Post
You hit the nail on the head there JCirocco I had no idea I could create a custom field on Class. That would have saved me so much time writing these CASE WHEN statements. Thank you!
You are very welcome. That is why this group is so helpful!
-
AuthorPosts
You must be logged in to reply to this topic.