Conditional Formatting within SQL Query (changing cell color on the basis of condition)

Transact-SQL
1

Dear Experts,

In need your help with respect to following query.

I have of BOM (Bill of Material Table) in which Selling Price is calculated by the system for almost all the items except few. For those items whose S.P. is not calculated by the system I have used the formula (Pur. Price * Margin Factor) to calculate the Selling Price.

Total BOM Selling Price is calculated by totaling the Selling Price at the bottom of the column. I have already designed the query for subtotaling of the selling price column at the bottom of it. The only problem is the cell in which selling price is null I need to take from Calculated Selling Price column and I need to highlight the cell color in yellow so that I should know by looking a the cell that it is taken from calculated Selling Price

CREATE TABLE BOMPrice (
Parent_Item VARCHAR(4) NOT NULL PRIMARY KEY
,Child_Item VARCHAR(2) NOT NULL
,Selling_Price_Rs INT
,Pur_Price_Euro INT NOT NULL
,Margin_Factor INT NOT NULL
,Calc_Selling_Price_Rs INT NOT NULL
);

INSERT INTO BOMPrice (
Parent_Item
,Child_Item
,Selling_Price_Rs
,Pur_Price_Euro
,Margin_Factor
,Calc_Selling_Price_Rs
)
VALUES (
'ABCD'
,'A'
,5
,2
,3
,6
);

INSERT INTO BOMPrice (
Parent_Item
,Child_Item
,Selling_Price_Rs
,Pur_Price_Euro
,Margin_Factor
,Calc_Selling_Price_Rs
)
VALUES (
'ABCD'
,'B'
,2
,1
,3
,3
);

INSERT INTO BOMPrice (
Parent_Item
,Child_Item
,Selling_Price_Rs
,Pur_Price_Euro
,Margin_Factor
,Calc_Selling_Price_Rs
)
VALUES (
'ABCD'
,'C'
,NULL
,3
,3
,9
);

INSERT INTO BOMPrice (
Parent_Item
,Child_Item
,Selling_Price_Rs
,Pur_Price_Euro
,Margin_Factor
,Calc_Selling_Price_Rs
)
VALUES (
'ABCD'
,'D'
,13
,5
,3
,15
);

When the Selling Price (Rs.) = Null then Selling Price (Rs.) = Pur. Price (Euro)*Margin Factor (with cell background color to be yellow) else Selling Price (Rs.)

Desired Output

image

2

are you asking about conditional coloring in SSRS?

3

i am not generating report rather it is the output of sql query which i need. I am using this query in Microsoft Excel to get the output. I am getting the required results but the only thing is i want to color the cell on the basis of condition specified.

Thanks for the interest shown in helping me.

4

To run a SP from MS Excel, go to the tab "Data".
Click on "New Query" "From Database" "From SQL Server Database"
Type in your Server, Database and then use the following to execute your Stored Procedure:
EXEC usp_Your_Store_Procedure
That will bring your data into a sheet.

Once your data is in the sheet, you can use the following:

OR

You can just select the column of your choice.
Then go to the tab "Home" and select "Conditional Formatting" "Highlight Cells Rules" "Equal To"

5

Thanks. As mentioned in my query i am able to run this query successfully and getting the results in excel. But In excel how will i know that value 9 is calculated using formula in sql?? i need this formatting to be integrated in sql query itself.

6

Use a calculated value in your query to create another column to show if it is calculated

select *,
	Case when Selling_Price_Rs is null then	
		Pur_price_Euro * Margin_Factor
	else
		Selling_Price_Rs 
	end as CalculatedSelling_Price_Rs,
	Case when Selling_Price_Rs is null then	
		1 
	else
		0
	end as Calculated
 from BOMPrice
7

That's a nice idea but is there no way to integrate the required feature in the query itself?

8

what @mike01 has for you is what you need

select *,
	Case when Selling_Price_Rs is null then	
		Pur_price_Euro * Margin_Factor
	else
		Selling_Price_Rs 
	end as CalculatedSelling_Price_Rs,
	Case when Selling_Price_Rs is null then	
		'CALCULATED'   --Mike had this for you as boolean/bit 1 - True
	else
		'NOT_CALCULATED' --Mike had this for you as boolean/bit 0 - False
	end as Calculated
 from BOMPrice