HI! i am a college student and i need help with my PL/SQL project. we have to create a package with subprograms (1 private and 1 public).
the public function will accept a SSN as a parameter and return a formatted SSN (###-##-####) or an error message that identifies what test failed and why the SSN was not good, no testing will occur in the public function.
the public function will call a private function/procedure in the same package. the private program unit will test a parameter that is passed in by the public function:
--the parameter passed in may not contain any alphas
--may not contain all the same numbers (111-11-1111)
--will accept only the following values as good:
123456789
123-45-6789
123-456789
12345-6789
create a table called Test_SSN with one column (varchar2(11)). create a database trigger that calls your packaged function to test the SSN before inserting it in the table.
If you have ANY suggestions, PLEASE RESPOND!!! My teacher gave me a hint to use counter:=counter + 1. HELP HELP HELP!!!Post what you have managed to do yourself, and people can offer suggestions and corrections. There is no educational value in subcontracting your homework to others!|||Sorry, i wasnt trying to get someone to do it for me, ok, i have made a couple of tests, they are anonymous blocks right now, but they will be part of my functions in my package..
DECLARE
SSN VARCHAR(100) := '123-45-6789';
BEGIN
IF LENGTH(SSN) <9 OR LENGTH(SSN) >11 THEN
RAISE_APPLICATION_ERROR(-20001,'SSN MUST BE BETWEEN 9 AND 11 CHARACTERS');
ELSE
IF LENGTH(SSN) = 11 AND
SUBSTR(SSN, '-') = 4 AND
SUBSTR(SSN, '-',2) =7 THEN
DSMS_OUTPUT.PUT_LINE('SSN FORMAT CORRECT');
ELSE
IF LENGTH (SSN) = 10 AND
SUBSTR(SSN, '-') = 4 OR
SUBSTR(SSN, '-') = 6 THEN
DBMS_OUTPUT.PUT_LINE('SSN FORMAT CORRECT');
ELSE
IF LENGTH = 9 AND
SSN NOT LIKE '%-%' THEN
DBMS_OUTPUT.PUT_LINE('SSN FORMAT CORRECT');
ELSE RAISE_APPLICATION_ERROR(-20001,'HYPHEN ENTERED
INCORRECTLY');
END IF;
END IF;
END IF;
END IF;
END;
/
To test to make sure the SSN does not contain any alphas, must i repeatedly continue on like this : SSN NOT LIKE '%a%' AND SSN NOT LIKE '%b%' ...... and so on? Any help will be Extremely Appreciated!!|||Originally posted by oraculous
To test to make sure the SSN does not contain any alphas, must i repeatedly continue on like this : SSN NOT LIKE '%a%' AND SSN NOT LIKE '%b%' ...... and so on? Any help will be Extremely Appreciated!!
The TRANSLATE function will be helpful here:
TRANSLATE( ssn, 'x0123456789-', 'x' )
This will translate all digits and '-' to NULL, leaving behind any other (invalid) characters. So if the result is NOT NULL that means there were some invalid characters in the string.
The 'x' is just there as a dummy, to prevent the 3rd argument being '', which doesn't work. It gets translated to 'x', so doesn't affect the result.
You could test and display the invalid chars like this:
v_invalid VARCHAR2(11);
...
v_invalid := TRANSLATE( ssn, 'x0123456789-', 'x' );
if v_invalid is not null then
raise_application_error(-20001,'Invalid chars in SSN: '||v_invalid);
end if;|||thank you so much!! i didnt even think about translate...you are a great help...it is appreciated!!|||Does anyone know how i can use a loop if i use something like TEMP_SSN VARCHAR2 := SUBSTR(SSN,1,3)||SUBSTR(SSN,5,2)||SUBSTR(SSN,8,4) and then use a counter:=counter + 1...??
does anyone know how i could use this or if it would work here instead of using all those [SUBSTR(SSN,1) <> '-' AND].....???
CREATE PROCEDURE SSN_PROC (SSN VARCHAR2)
-- SSN VARCHAR(100) := '123-45-6789';
SSN_INVALID VARCHAR2 (11):=TRANSLATE(SSN,'x0123456789-','x');
TEMP_SSN VARCHAR2;
BEGIN
IF LENGTH(SSN) <9 OR LENGTH(SSN) >11 THEN
RAISE_APPLICATION_ERROR(-20001,'SSN Must Be Between 9 And 11 Characters');
ELSE
IF LENGTH(SSN) = 11 AND
INSTR(SSN, '-') = 4 AND
INSTR(SSN, '-',1,2) =7 AND
SUBSTR(SSN,1) <> '-' AND
SUBSTR(SSN,2) <> '-' AND
SUBSTR(SSN,3) <> '-' AND
SUBSTR(SSN,5) <> '-' AND
SUBSTR(SSN,6) <> '-' AND
SUBSTR(SSN,8) <> '-' AND
SUBSTR(SSN,9) <> '-' AND
SUBSTR(SSN,10) <> '-' AND
SUBSTR(SSN,11) <> '-' THEN
DBMS_OUTPUT.PUT_LINE('SSN Entered Correctly');
ELSE
IF LENGTH (SSN) = 10 AND
INSTR(SSN, '-') = 4 OR
INSTR(SSN, '-') = 6 THEN
DBMS_OUTPUT.PUT_LINE('SSN Entered Correctly');
ELSE
IF LENGTH = 9 AND
SSN NOT LIKE '%-%' THEN
DBMS_OUTPUT.PUT_LINE('SSN Entered Correctly');
ELSE RAISE_APPLICATION_ERROR(-20001,'Hyphen Entered Incorrectly');
IF SSN_INVALID IS NOT NULL THEN
RAISE_APPLICATION_ERROR(-20001,'Invalid Characters In SSN:'||SSN_INVALID);
END IF;
END IF;
END IF;
END IF;
END IF;
END;
/|||You can use a FOR loop:
FOR i IN 1..11 LOOP
IF i NOT IN (4,7) AND SUBSTR(SSN,i,1) = '-' THEN
RAISE_APPLICATION_ERROR(-20001,'Hyphen Entered Incorrectly');
END IF;
END LOOP;
Note the 3rd parameter to SUBSTR, i.e. the length of the substring required. You had SUBSTR(SSN,1) which is the substring from 1 to end of SSN, i.e. is equal to SSN.|||oooOOHH thanks man, you are a great help!!sql
Showing posts with label public. Show all posts
Showing posts with label public. Show all posts
Wednesday, March 28, 2012
Wednesday, March 21, 2012
HELP! Need a Light Green color that exports
I have a report that needs to have a green cell (based on a condition of
course - which is decided in a code function) Here is my function:
Public Function SetColor(ByVal N As Double) As String
Select N
Case 0
Return "Salmon"
Case 1
Return "PaleGoldenrod"
Case 2
Return "LightGreen"
Case 3
Return "RoyalBlue"
Case Else
Return "Transparent"
End Select
End Function
All of the colors work *except* LightGreen. I can even *see* Light Green
in the excel color palette (notice the "space" in the excel color name) ... I
dont even *see* Salmon, Palegoldenrod or anything else there ... and yet
*those* show properly. It is only the green color that shows wrong and it
displays as GREY. Not good. I have tried other light green colors but the
only GREEN I can get to work is GREEN and that is too dark. Can someone help
with this? TIA ...Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
#80ff80.
Case 0
Return "#80ff80"
I would have used hex codes for all colours that are not plain red, yellow,
blue and green, just to be sure it was rendered OK.
Kaisa M. Lindahl Lervik
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
>I have a report that needs to have a green cell (based on a condition of
> course - which is decided in a code function) Here is my function:
> Public Function SetColor(ByVal N As Double) As String
> Select N
> Case 0
> Return "Salmon"
> Case 1
> Return "PaleGoldenrod"
> Case 2
> Return "LightGreen"
> Case 3
> Return "RoyalBlue"
> Case Else
> Return "Transparent"
> End Select
> End Function
> All of the colors work *except* LightGreen. I can even *see* Light Green
> in the excel color palette (notice the "space" in the excel color name)
> ... I
> dont even *see* Salmon, Palegoldenrod or anything else there ... and yet
> *those* show properly. It is only the green color that shows wrong and it
> displays as GREY. Not good. I have tried other light green colors but
> the
> only GREEN I can get to work is GREEN and that is too dark. Can someone
> help
> with this? TIA ...
>|||Thanks Kaisa ... I will try that. How can I find the hex codes for the
palette colors in reporting services? For instance what PaleGoldenrod is.
"Kaisa M. Lindahl Lervik" wrote:
> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
> #80ff80.
> Case 0
> Return "#80ff80"
> I would have used hex codes for all colours that are not plain red, yellow,
> blue and green, just to be sure it was rendered OK.
> Kaisa M. Lindahl Lervik
>
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
> >I have a report that needs to have a green cell (based on a condition of
> > course - which is decided in a code function) Here is my function:
> >
> > Public Function SetColor(ByVal N As Double) As String
> >
> > Select N
> > Case 0
> > Return "Salmon"
> > Case 1
> > Return "PaleGoldenrod"
> > Case 2
> > Return "LightGreen"
> > Case 3
> > Return "RoyalBlue"
> > Case Else
> > Return "Transparent"
> >
> > End Select
> >
> > End Function
> >
> > All of the colors work *except* LightGreen. I can even *see* Light Green
> > in the excel color palette (notice the "space" in the excel color name)
> > ... I
> > dont even *see* Salmon, Palegoldenrod or anything else there ... and yet
> > *those* show properly. It is only the green color that shows wrong and it
> > displays as GREY. Not good. I have tried other light green colors but
> > the
> > only GREEN I can get to work is GREEN and that is too dark. Can someone
> > help
> > with this? TIA ...
> >
> >
>
>|||I'm not sure, I thought you could use the .Net object browser, but I didn't
see the hex codes.
System.Drawing.Color has the color enum that you are looking for.
Ah, here's a page:
http://www.opinionatedgeek.com/DotNet/Tools/Colors/default.aspx
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:0260F30C-FB37-4186-8501-E93516FD7C89@.microsoft.com...
> Thanks Kaisa ... I will try that. How can I find the hex codes for the
> palette colors in reporting services? For instance what PaleGoldenrod is.
> "Kaisa M. Lindahl Lervik" wrote:
>> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
>> #80ff80.
>> Case 0
>> Return "#80ff80"
>> I would have used hex codes for all colours that are not plain red,
>> yellow,
>> blue and green, just to be sure it was rendered OK.
>> Kaisa M. Lindahl Lervik
>>
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
>> >I have a report that needs to have a green cell (based on a condition of
>> > course - which is decided in a code function) Here is my function:
>> >
>> > Public Function SetColor(ByVal N As Double) As String
>> >
>> > Select N
>> > Case 0
>> > Return "Salmon"
>> > Case 1
>> > Return "PaleGoldenrod"
>> > Case 2
>> > Return "LightGreen"
>> > Case 3
>> > Return "RoyalBlue"
>> > Case Else
>> > Return "Transparent"
>> >
>> > End Select
>> >
>> > End Function
>> >
>> > All of the colors work *except* LightGreen. I can even *see* Light
>> > Green
>> > in the excel color palette (notice the "space" in the excel color name)
>> > ... I
>> > dont even *see* Salmon, Palegoldenrod or anything else there ... and
>> > yet
>> > *those* show properly. It is only the green color that shows wrong and
>> > it
>> > displays as GREY. Not good. I have tried other light green colors but
>> > the
>> > only GREEN I can get to work is GREEN and that is too dark. Can
>> > someone
>> > help
>> > with this? TIA ...
>> >
>> >
>>|||Thank you Steve ... that is exactly what I needed!!! Between you and Kaisa I
am all set now. Thanks to you both!!
"Steve MunLeeuw" wrote:
> I'm not sure, I thought you could use the .Net object browser, but I didn't
> see the hex codes.
> System.Drawing.Color has the color enum that you are looking for.
> Ah, here's a page:
> http://www.opinionatedgeek.com/DotNet/Tools/Colors/default.aspx
>
>
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:0260F30C-FB37-4186-8501-E93516FD7C89@.microsoft.com...
> > Thanks Kaisa ... I will try that. How can I find the hex codes for the
> > palette colors in reporting services? For instance what PaleGoldenrod is.
> >
> > "Kaisa M. Lindahl Lervik" wrote:
> >
> >> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
> >> #80ff80.
> >>
> >> Case 0
> >> Return "#80ff80"
> >>
> >> I would have used hex codes for all colours that are not plain red,
> >> yellow,
> >> blue and green, just to be sure it was rendered OK.
> >>
> >> Kaisa M. Lindahl Lervik
> >>
> >>
> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> >> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
> >> >I have a report that needs to have a green cell (based on a condition of
> >> > course - which is decided in a code function) Here is my function:
> >> >
> >> > Public Function SetColor(ByVal N As Double) As String
> >> >
> >> > Select N
> >> > Case 0
> >> > Return "Salmon"
> >> > Case 1
> >> > Return "PaleGoldenrod"
> >> > Case 2
> >> > Return "LightGreen"
> >> > Case 3
> >> > Return "RoyalBlue"
> >> > Case Else
> >> > Return "Transparent"
> >> >
> >> > End Select
> >> >
> >> > End Function
> >> >
> >> > All of the colors work *except* LightGreen. I can even *see* Light
> >> > Green
> >> > in the excel color palette (notice the "space" in the excel color name)
> >> > ... I
> >> > dont even *see* Salmon, Palegoldenrod or anything else there ... and
> >> > yet
> >> > *those* show properly. It is only the green color that shows wrong and
> >> > it
> >> > displays as GREY. Not good. I have tried other light green colors but
> >> > the
> >> > only GREEN I can get to work is GREEN and that is too dark. Can
> >> > someone
> >> > help
> >> > with this? TIA ...
> >> >
> >> >
> >>
> >>
> >>
>
>|||I tried using the hex codes and it didnt work. I thought it should too!
What could I be doing wrong? I have a conditional expression in addition to
the function in the cell ... maybe it has something to do with that:
=IIF(IsNothing(Fields!PERCENT.Value), "white", code.SetColor(
Fields!PerformanceValue.Value))
It is not setting all cells to white ... so it isnt the behavior I have seen
written about elsewhere in this newsgroup. I tried "#90EE90" and "#80ff80"
and I still see grey where green should appear. Any other suggestions?
"Steve MunLeeuw" wrote:
> I'm not sure, I thought you could use the .Net object browser, but I didn't
> see the hex codes.
> System.Drawing.Color has the color enum that you are looking for.
> Ah, here's a page:
> http://www.opinionatedgeek.com/DotNet/Tools/Colors/default.aspx
>
>
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:0260F30C-FB37-4186-8501-E93516FD7C89@.microsoft.com...
> > Thanks Kaisa ... I will try that. How can I find the hex codes for the
> > palette colors in reporting services? For instance what PaleGoldenrod is.
> >
> > "Kaisa M. Lindahl Lervik" wrote:
> >
> >> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
> >> #80ff80.
> >>
> >> Case 0
> >> Return "#80ff80"
> >>
> >> I would have used hex codes for all colours that are not plain red,
> >> yellow,
> >> blue and green, just to be sure it was rendered OK.
> >>
> >> Kaisa M. Lindahl Lervik
> >>
> >>
> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> >> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
> >> >I have a report that needs to have a green cell (based on a condition of
> >> > course - which is decided in a code function) Here is my function:
> >> >
> >> > Public Function SetColor(ByVal N As Double) As String
> >> >
> >> > Select N
> >> > Case 0
> >> > Return "Salmon"
> >> > Case 1
> >> > Return "PaleGoldenrod"
> >> > Case 2
> >> > Return "LightGreen"
> >> > Case 3
> >> > Return "RoyalBlue"
> >> > Case Else
> >> > Return "Transparent"
> >> >
> >> > End Select
> >> >
> >> > End Function
> >> >
> >> > All of the colors work *except* LightGreen. I can even *see* Light
> >> > Green
> >> > in the excel color palette (notice the "space" in the excel color name)
> >> > ... I
> >> > dont even *see* Salmon, Palegoldenrod or anything else there ... and
> >> > yet
> >> > *those* show properly. It is only the green color that shows wrong and
> >> > it
> >> > displays as GREY. Not good. I have tried other light green colors but
> >> > the
> >> > only GREEN I can get to work is GREEN and that is too dark. Can
> >> > someone
> >> > help
> >> > with this? TIA ...
> >> >
> >> >
> >>
> >>
> >>
>
>|||I tried it and it actually did not work. I think there is something else I
need to do to fix it in addition. I have a conditional expression deciding
whether to perform the function (because I could have nulls in the cell but I
am replacing them with the words "No Data") So the expression in the data
cell is this (it is a matrix cell)
=IIF(IsNothing(Fields!PERCENT.Value), "No Data",
Sum(Fields!PERCENT.Value)/100 )
and the expression for the background color to be set for this cell is this:
=IIF(IsNothing(Fields!PERCENT.Value), "white", code.SetColor(
Fields!PerformanceValue.Value))
and the Function is this:
Public Function SetColor(ByVal N As Double) As String
Select N
Case 0
Return "Salmon"
Case 1
Return "PaleGoldenrod"
Case 2
Return "LightGreen"
Case 3
Return "RoyalBlue"
Case Else
Return "Transparent"
End Select
End Function
I tried changing the function to return values "#90EE90" etc and I altered
the IIF statement to read "#FFFFFF" instead of "white" ... but that didnt
work - it rendered correctly except didnt export to excel and show green ...
it still showed grey. I did get a warning message "The background color
expression used in textbox â'textbox8â' returned a data type that is not valid"
- textbox 8 is the cell I am using. I know when I have used hex codes for
colors in the past ... I dont believe I enclosed them in quotes in the cell,
but I cant return #90EE90 from my function without putting quotes around it,
can I? What would you do next?
"Kaisa M. Lindahl Lervik" wrote:
> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
> #80ff80.
> Case 0
> Return "#80ff80"
> I would have used hex codes for all colours that are not plain red, yellow,
> blue and green, just to be sure it was rendered OK.
> Kaisa M. Lindahl Lervik
>
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
> >I have a report that needs to have a green cell (based on a condition of
> > course - which is decided in a code function) Here is my function:
> >
> > Public Function SetColor(ByVal N As Double) As String
> >
> > Select N
> > Case 0
> > Return "Salmon"
> > Case 1
> > Return "PaleGoldenrod"
> > Case 2
> > Return "LightGreen"
> > Case 3
> > Return "RoyalBlue"
> > Case Else
> > Return "Transparent"
> >
> > End Select
> >
> > End Function
> >
> > All of the colors work *except* LightGreen. I can even *see* Light Green
> > in the excel color palette (notice the "space" in the excel color name)
> > ... I
> > dont even *see* Salmon, Palegoldenrod or anything else there ... and yet
> > *those* show properly. It is only the green color that shows wrong and it
> > displays as GREY. Not good. I have tried other light green colors but
> > the
> > only GREEN I can get to work is GREEN and that is too dark. Can someone
> > help
> > with this? TIA ...
> >
> >
>
>|||I will report back that I now have it working. I cant tell you what fixed
it ... maybe changing the case of the letters in hex? Thats about all I did.
Here is the expression that works.
=IIF(IsNothing(Fields!PERCENT.Value), "#ffffff", code.SetColor(
Fields!PerformanceValue.Value))
AND the function code:
Public Function SetColor(ByVal N As Double) As String
Select N
Case 0
Return "#fa8072"
Case 1
Return "#eee8aa"
Case 2
Return "#c0ffc0"
Case 3
Return "#4169e1"
Case Else
Return "#ffffff"
End Select
End Function
Thanks for all of your help!! I guess case was important in this CASE ...
pun intended!
"MJT" wrote:
> I tried using the hex codes and it didnt work. I thought it should too!
> What could I be doing wrong? I have a conditional expression in addition to
> the function in the cell ... maybe it has something to do with that:
> =IIF(IsNothing(Fields!PERCENT.Value), "white", code.SetColor(
> Fields!PerformanceValue.Value))
> It is not setting all cells to white ... so it isnt the behavior I have seen
> written about elsewhere in this newsgroup. I tried "#90EE90" and "#80ff80"
> and I still see grey where green should appear. Any other suggestions?
> "Steve MunLeeuw" wrote:
> > I'm not sure, I thought you could use the .Net object browser, but I didn't
> > see the hex codes.
> >
> > System.Drawing.Color has the color enum that you are looking for.
> > Ah, here's a page:
> >
> > http://www.opinionatedgeek.com/DotNet/Tools/Colors/default.aspx
> >
> >
> >
> >
> > "MJT" <MJT@.discussions.microsoft.com> wrote in message
> > news:0260F30C-FB37-4186-8501-E93516FD7C89@.microsoft.com...
> > > Thanks Kaisa ... I will try that. How can I find the hex codes for the
> > > palette colors in reporting services? For instance what PaleGoldenrod is.
> > >
> > > "Kaisa M. Lindahl Lervik" wrote:
> > >
> > >> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
> > >> #80ff80.
> > >>
> > >> Case 0
> > >> Return "#80ff80"
> > >>
> > >> I would have used hex codes for all colours that are not plain red,
> > >> yellow,
> > >> blue and green, just to be sure it was rendered OK.
> > >>
> > >> Kaisa M. Lindahl Lervik
> > >>
> > >>
> > >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> > >> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
> > >> >I have a report that needs to have a green cell (based on a condition of
> > >> > course - which is decided in a code function) Here is my function:
> > >> >
> > >> > Public Function SetColor(ByVal N As Double) As String
> > >> >
> > >> > Select N
> > >> > Case 0
> > >> > Return "Salmon"
> > >> > Case 1
> > >> > Return "PaleGoldenrod"
> > >> > Case 2
> > >> > Return "LightGreen"
> > >> > Case 3
> > >> > Return "RoyalBlue"
> > >> > Case Else
> > >> > Return "Transparent"
> > >> >
> > >> > End Select
> > >> >
> > >> > End Function
> > >> >
> > >> > All of the colors work *except* LightGreen. I can even *see* Light
> > >> > Green
> > >> > in the excel color palette (notice the "space" in the excel color name)
> > >> > ... I
> > >> > dont even *see* Salmon, Palegoldenrod or anything else there ... and
> > >> > yet
> > >> > *those* show properly. It is only the green color that shows wrong and
> > >> > it
> > >> > displays as GREY. Not good. I have tried other light green colors but
> > >> > the
> > >> > only GREEN I can get to work is GREEN and that is too dark. Can
> > >> > someone
> > >> > help
> > >> > with this? TIA ...
> > >> >
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >|||Hmmm, I wonder if it's running into a problem with the IIF(). If you put
the output out as text, rather than trying to set the color, are you getting
the color hex values you expect? If that's working correctly, try not using
the expression and seeing if you can set the background color to the light
green value that isn't working....If that is failing, then you know it's
not the expression and it's a bug or limitation in the excel rendering
engine. I'm using...was using LightGreen ( I swear) and it was rendering
to PDF just fine, not sure about Excel, never tested. I'd try exporting to
PDF and see if that works. If this helps, here's the code I'm using to set
the color of the chart bars.
public static Int32 PickColor(int index)
{
Int32 ReturnVal = Color.Tan.ToArgb();
switch (index)
{
case 0:
ReturnVal = Color.MediumSlateBlue.ToArgb();
break;
case 1:
ReturnVal = Color.MediumVioletRed.ToArgb();
break;
case 2:
ReturnVal = Color.LightYellow.ToArgb();
break;
case 3:
ReturnVal = Color.PaleTurquoise.ToArgb();
break;
case 4:
ReturnVal = Color.Purple.ToArgb();
break;
}
return ReturnVal;
}
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:CE2F86F8-6482-4CB0-A8A7-3D3A8E13FD06@.microsoft.com...
>I tried it and it actually did not work. I think there is something else I
> need to do to fix it in addition. I have a conditional expression
> deciding
> whether to perform the function (because I could have nulls in the cell
> but I
> am replacing them with the words "No Data") So the expression in the data
> cell is this (it is a matrix cell)
> =IIF(IsNothing(Fields!PERCENT.Value), "No Data",
> Sum(Fields!PERCENT.Value)/100 )
> and the expression for the background color to be set for this cell is
> this:
> =IIF(IsNothing(Fields!PERCENT.Value), "white", code.SetColor(
> Fields!PerformanceValue.Value))
> and the Function is this:
> Public Function SetColor(ByVal N As Double) As String
> Select N
> Case 0
> Return "Salmon"
> Case 1
> Return "PaleGoldenrod"
> Case 2
> Return "LightGreen"
> Case 3
> Return "RoyalBlue"
> Case Else
> Return "Transparent"
> End Select
> End Function
> I tried changing the function to return values "#90EE90" etc and I altered
> the IIF statement to read "#FFFFFF" instead of "white" ... but that didnt
> work - it rendered correctly except didnt export to excel and show green
> ...
> it still showed grey. I did get a warning message "The background color
> expression used in textbox 'textbox8' returned a data type that is not
> valid"
> - textbox 8 is the cell I am using. I know when I have used hex codes for
> colors in the past ... I dont believe I enclosed them in quotes in the
> cell,
> but I cant return #90EE90 from my function without putting quotes around
> it,
> can I? What would you do next?
>
> "Kaisa M. Lindahl Lervik" wrote:
>> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
>> #80ff80.
>> Case 0
>> Return "#80ff80"
>> I would have used hex codes for all colours that are not plain red,
>> yellow,
>> blue and green, just to be sure it was rendered OK.
>> Kaisa M. Lindahl Lervik
>>
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
>> >I have a report that needs to have a green cell (based on a condition of
>> > course - which is decided in a code function) Here is my function:
>> >
>> > Public Function SetColor(ByVal N As Double) As String
>> >
>> > Select N
>> > Case 0
>> > Return "Salmon"
>> > Case 1
>> > Return "PaleGoldenrod"
>> > Case 2
>> > Return "LightGreen"
>> > Case 3
>> > Return "RoyalBlue"
>> > Case Else
>> > Return "Transparent"
>> >
>> > End Select
>> >
>> > End Function
>> >
>> > All of the colors work *except* LightGreen. I can even *see* Light
>> > Green
>> > in the excel color palette (notice the "space" in the excel color name)
>> > ... I
>> > dont even *see* Salmon, Palegoldenrod or anything else there ... and
>> > yet
>> > *those* show properly. It is only the green color that shows wrong and
>> > it
>> > displays as GREY. Not good. I have tried other light green colors but
>> > the
>> > only GREEN I can get to work is GREEN and that is too dark. Can
>> > someone
>> > help
>> > with this? TIA ...
>> >
>> >
>>|||Steve,
read my post that is a couple of lines up in the thread (2/24) that tells
you I got it working. The only thing I can figure out is it might be the
case I was using. I tried many things ... I took out the IIF statement to
see what would export correctly in hex and for whatever reason it didnt seem
to like #90EE90 for light green but it accepted #C0FFCO ... and I noticed in
reporting services that when I chose a hex code from their custom colors ( I
started with what they had predefined before going to the "web colors" tab)
it made the hex value lower case #c0ffc0 ... and it exported and showed
correctly. So I changed the case of the hex letters in my function and it
seemed to work. Strange. I am snipping your code though. Must be c# ...
doesnt look like vb .net. I love it when people submit examples of code ...
I am just learning and it is so helpful to collect examples. Thanks for your
help! remember to use lower case hex letters ... lol
"Steve MunLeeuw" wrote:
> Hmmm, I wonder if it's running into a problem with the IIF(). If you put
> the output out as text, rather than trying to set the color, are you getting
> the color hex values you expect? If that's working correctly, try not using
> the expression and seeing if you can set the background color to the light
> green value that isn't working....If that is failing, then you know it's
> not the expression and it's a bug or limitation in the excel rendering
> engine. I'm using...was using LightGreen ( I swear) and it was rendering
> to PDF just fine, not sure about Excel, never tested. I'd try exporting to
> PDF and see if that works. If this helps, here's the code I'm using to set
> the color of the chart bars.
> public static Int32 PickColor(int index)
> {
> Int32 ReturnVal = Color.Tan.ToArgb();
> switch (index)
> {
> case 0:
> ReturnVal = Color.MediumSlateBlue.ToArgb();
> break;
> case 1:
> ReturnVal = Color.MediumVioletRed.ToArgb();
> break;
> case 2:
> ReturnVal = Color.LightYellow.ToArgb();
> break;
> case 3:
> ReturnVal = Color.PaleTurquoise.ToArgb();
> break;
> case 4:
> ReturnVal = Color.Purple.ToArgb();
> break;
> }
> return ReturnVal;
> }
>
>
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:CE2F86F8-6482-4CB0-A8A7-3D3A8E13FD06@.microsoft.com...
> >I tried it and it actually did not work. I think there is something else I
> > need to do to fix it in addition. I have a conditional expression
> > deciding
> > whether to perform the function (because I could have nulls in the cell
> > but I
> > am replacing them with the words "No Data") So the expression in the data
> > cell is this (it is a matrix cell)
> >
> > =IIF(IsNothing(Fields!PERCENT.Value), "No Data",
> > Sum(Fields!PERCENT.Value)/100 )
> >
> > and the expression for the background color to be set for this cell is
> > this:
> >
> > =IIF(IsNothing(Fields!PERCENT.Value), "white", code.SetColor(
> > Fields!PerformanceValue.Value))
> >
> > and the Function is this:
> >
> > Public Function SetColor(ByVal N As Double) As String
> >
> > Select N
> > Case 0
> > Return "Salmon"
> > Case 1
> > Return "PaleGoldenrod"
> > Case 2
> > Return "LightGreen"
> > Case 3
> > Return "RoyalBlue"
> > Case Else
> > Return "Transparent"
> >
> > End Select
> >
> > End Function
> >
> > I tried changing the function to return values "#90EE90" etc and I altered
> > the IIF statement to read "#FFFFFF" instead of "white" ... but that didnt
> > work - it rendered correctly except didnt export to excel and show green
> > ...
> > it still showed grey. I did get a warning message "The background color
> > expression used in textbox 'textbox8' returned a data type that is not
> > valid"
> > - textbox 8 is the cell I am using. I know when I have used hex codes for
> > colors in the past ... I dont believe I enclosed them in quotes in the
> > cell,
> > but I cant return #90EE90 from my function without putting quotes around
> > it,
> > can I? What would you do next?
> >
> >
> > "Kaisa M. Lindahl Lervik" wrote:
> >
> >> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
> >> #80ff80.
> >>
> >> Case 0
> >> Return "#80ff80"
> >>
> >> I would have used hex codes for all colours that are not plain red,
> >> yellow,
> >> blue and green, just to be sure it was rendered OK.
> >>
> >> Kaisa M. Lindahl Lervik
> >>
> >>
> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> >> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
> >> >I have a report that needs to have a green cell (based on a condition of
> >> > course - which is decided in a code function) Here is my function:
> >> >
> >> > Public Function SetColor(ByVal N As Double) As String
> >> >
> >> > Select N
> >> > Case 0
> >> > Return "Salmon"
> >> > Case 1
> >> > Return "PaleGoldenrod"
> >> > Case 2
> >> > Return "LightGreen"
> >> > Case 3
> >> > Return "RoyalBlue"
> >> > Case Else
> >> > Return "Transparent"
> >> >
> >> > End Select
> >> >
> >> > End Function
> >> >
> >> > All of the colors work *except* LightGreen. I can even *see* Light
> >> > Green
> >> > in the excel color palette (notice the "space" in the excel color name)
> >> > ... I
> >> > dont even *see* Salmon, Palegoldenrod or anything else there ... and
> >> > yet
> >> > *those* show properly. It is only the green color that shows wrong and
> >> > it
> >> > displays as GREY. Not good. I have tried other light green colors but
> >> > the
> >> > only GREEN I can get to work is GREEN and that is too dark. Can
> >> > someone
> >> > help
> >> > with this? TIA ...
> >> >
> >> >
> >>
> >>
> >>
>
>
course - which is decided in a code function) Here is my function:
Public Function SetColor(ByVal N As Double) As String
Select N
Case 0
Return "Salmon"
Case 1
Return "PaleGoldenrod"
Case 2
Return "LightGreen"
Case 3
Return "RoyalBlue"
Case Else
Return "Transparent"
End Select
End Function
All of the colors work *except* LightGreen. I can even *see* Light Green
in the excel color palette (notice the "space" in the excel color name) ... I
dont even *see* Salmon, Palegoldenrod or anything else there ... and yet
*those* show properly. It is only the green color that shows wrong and it
displays as GREY. Not good. I have tried other light green colors but the
only GREEN I can get to work is GREEN and that is too dark. Can someone help
with this? TIA ...Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
#80ff80.
Case 0
Return "#80ff80"
I would have used hex codes for all colours that are not plain red, yellow,
blue and green, just to be sure it was rendered OK.
Kaisa M. Lindahl Lervik
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
>I have a report that needs to have a green cell (based on a condition of
> course - which is decided in a code function) Here is my function:
> Public Function SetColor(ByVal N As Double) As String
> Select N
> Case 0
> Return "Salmon"
> Case 1
> Return "PaleGoldenrod"
> Case 2
> Return "LightGreen"
> Case 3
> Return "RoyalBlue"
> Case Else
> Return "Transparent"
> End Select
> End Function
> All of the colors work *except* LightGreen. I can even *see* Light Green
> in the excel color palette (notice the "space" in the excel color name)
> ... I
> dont even *see* Salmon, Palegoldenrod or anything else there ... and yet
> *those* show properly. It is only the green color that shows wrong and it
> displays as GREY. Not good. I have tried other light green colors but
> the
> only GREEN I can get to work is GREEN and that is too dark. Can someone
> help
> with this? TIA ...
>|||Thanks Kaisa ... I will try that. How can I find the hex codes for the
palette colors in reporting services? For instance what PaleGoldenrod is.
"Kaisa M. Lindahl Lervik" wrote:
> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
> #80ff80.
> Case 0
> Return "#80ff80"
> I would have used hex codes for all colours that are not plain red, yellow,
> blue and green, just to be sure it was rendered OK.
> Kaisa M. Lindahl Lervik
>
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
> >I have a report that needs to have a green cell (based on a condition of
> > course - which is decided in a code function) Here is my function:
> >
> > Public Function SetColor(ByVal N As Double) As String
> >
> > Select N
> > Case 0
> > Return "Salmon"
> > Case 1
> > Return "PaleGoldenrod"
> > Case 2
> > Return "LightGreen"
> > Case 3
> > Return "RoyalBlue"
> > Case Else
> > Return "Transparent"
> >
> > End Select
> >
> > End Function
> >
> > All of the colors work *except* LightGreen. I can even *see* Light Green
> > in the excel color palette (notice the "space" in the excel color name)
> > ... I
> > dont even *see* Salmon, Palegoldenrod or anything else there ... and yet
> > *those* show properly. It is only the green color that shows wrong and it
> > displays as GREY. Not good. I have tried other light green colors but
> > the
> > only GREEN I can get to work is GREEN and that is too dark. Can someone
> > help
> > with this? TIA ...
> >
> >
>
>|||I'm not sure, I thought you could use the .Net object browser, but I didn't
see the hex codes.
System.Drawing.Color has the color enum that you are looking for.
Ah, here's a page:
http://www.opinionatedgeek.com/DotNet/Tools/Colors/default.aspx
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:0260F30C-FB37-4186-8501-E93516FD7C89@.microsoft.com...
> Thanks Kaisa ... I will try that. How can I find the hex codes for the
> palette colors in reporting services? For instance what PaleGoldenrod is.
> "Kaisa M. Lindahl Lervik" wrote:
>> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
>> #80ff80.
>> Case 0
>> Return "#80ff80"
>> I would have used hex codes for all colours that are not plain red,
>> yellow,
>> blue and green, just to be sure it was rendered OK.
>> Kaisa M. Lindahl Lervik
>>
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
>> >I have a report that needs to have a green cell (based on a condition of
>> > course - which is decided in a code function) Here is my function:
>> >
>> > Public Function SetColor(ByVal N As Double) As String
>> >
>> > Select N
>> > Case 0
>> > Return "Salmon"
>> > Case 1
>> > Return "PaleGoldenrod"
>> > Case 2
>> > Return "LightGreen"
>> > Case 3
>> > Return "RoyalBlue"
>> > Case Else
>> > Return "Transparent"
>> >
>> > End Select
>> >
>> > End Function
>> >
>> > All of the colors work *except* LightGreen. I can even *see* Light
>> > Green
>> > in the excel color palette (notice the "space" in the excel color name)
>> > ... I
>> > dont even *see* Salmon, Palegoldenrod or anything else there ... and
>> > yet
>> > *those* show properly. It is only the green color that shows wrong and
>> > it
>> > displays as GREY. Not good. I have tried other light green colors but
>> > the
>> > only GREEN I can get to work is GREEN and that is too dark. Can
>> > someone
>> > help
>> > with this? TIA ...
>> >
>> >
>>|||Thank you Steve ... that is exactly what I needed!!! Between you and Kaisa I
am all set now. Thanks to you both!!
"Steve MunLeeuw" wrote:
> I'm not sure, I thought you could use the .Net object browser, but I didn't
> see the hex codes.
> System.Drawing.Color has the color enum that you are looking for.
> Ah, here's a page:
> http://www.opinionatedgeek.com/DotNet/Tools/Colors/default.aspx
>
>
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:0260F30C-FB37-4186-8501-E93516FD7C89@.microsoft.com...
> > Thanks Kaisa ... I will try that. How can I find the hex codes for the
> > palette colors in reporting services? For instance what PaleGoldenrod is.
> >
> > "Kaisa M. Lindahl Lervik" wrote:
> >
> >> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
> >> #80ff80.
> >>
> >> Case 0
> >> Return "#80ff80"
> >>
> >> I would have used hex codes for all colours that are not plain red,
> >> yellow,
> >> blue and green, just to be sure it was rendered OK.
> >>
> >> Kaisa M. Lindahl Lervik
> >>
> >>
> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> >> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
> >> >I have a report that needs to have a green cell (based on a condition of
> >> > course - which is decided in a code function) Here is my function:
> >> >
> >> > Public Function SetColor(ByVal N As Double) As String
> >> >
> >> > Select N
> >> > Case 0
> >> > Return "Salmon"
> >> > Case 1
> >> > Return "PaleGoldenrod"
> >> > Case 2
> >> > Return "LightGreen"
> >> > Case 3
> >> > Return "RoyalBlue"
> >> > Case Else
> >> > Return "Transparent"
> >> >
> >> > End Select
> >> >
> >> > End Function
> >> >
> >> > All of the colors work *except* LightGreen. I can even *see* Light
> >> > Green
> >> > in the excel color palette (notice the "space" in the excel color name)
> >> > ... I
> >> > dont even *see* Salmon, Palegoldenrod or anything else there ... and
> >> > yet
> >> > *those* show properly. It is only the green color that shows wrong and
> >> > it
> >> > displays as GREY. Not good. I have tried other light green colors but
> >> > the
> >> > only GREEN I can get to work is GREEN and that is too dark. Can
> >> > someone
> >> > help
> >> > with this? TIA ...
> >> >
> >> >
> >>
> >>
> >>
>
>|||I tried using the hex codes and it didnt work. I thought it should too!
What could I be doing wrong? I have a conditional expression in addition to
the function in the cell ... maybe it has something to do with that:
=IIF(IsNothing(Fields!PERCENT.Value), "white", code.SetColor(
Fields!PerformanceValue.Value))
It is not setting all cells to white ... so it isnt the behavior I have seen
written about elsewhere in this newsgroup. I tried "#90EE90" and "#80ff80"
and I still see grey where green should appear. Any other suggestions?
"Steve MunLeeuw" wrote:
> I'm not sure, I thought you could use the .Net object browser, but I didn't
> see the hex codes.
> System.Drawing.Color has the color enum that you are looking for.
> Ah, here's a page:
> http://www.opinionatedgeek.com/DotNet/Tools/Colors/default.aspx
>
>
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:0260F30C-FB37-4186-8501-E93516FD7C89@.microsoft.com...
> > Thanks Kaisa ... I will try that. How can I find the hex codes for the
> > palette colors in reporting services? For instance what PaleGoldenrod is.
> >
> > "Kaisa M. Lindahl Lervik" wrote:
> >
> >> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
> >> #80ff80.
> >>
> >> Case 0
> >> Return "#80ff80"
> >>
> >> I would have used hex codes for all colours that are not plain red,
> >> yellow,
> >> blue and green, just to be sure it was rendered OK.
> >>
> >> Kaisa M. Lindahl Lervik
> >>
> >>
> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> >> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
> >> >I have a report that needs to have a green cell (based on a condition of
> >> > course - which is decided in a code function) Here is my function:
> >> >
> >> > Public Function SetColor(ByVal N As Double) As String
> >> >
> >> > Select N
> >> > Case 0
> >> > Return "Salmon"
> >> > Case 1
> >> > Return "PaleGoldenrod"
> >> > Case 2
> >> > Return "LightGreen"
> >> > Case 3
> >> > Return "RoyalBlue"
> >> > Case Else
> >> > Return "Transparent"
> >> >
> >> > End Select
> >> >
> >> > End Function
> >> >
> >> > All of the colors work *except* LightGreen. I can even *see* Light
> >> > Green
> >> > in the excel color palette (notice the "space" in the excel color name)
> >> > ... I
> >> > dont even *see* Salmon, Palegoldenrod or anything else there ... and
> >> > yet
> >> > *those* show properly. It is only the green color that shows wrong and
> >> > it
> >> > displays as GREY. Not good. I have tried other light green colors but
> >> > the
> >> > only GREEN I can get to work is GREEN and that is too dark. Can
> >> > someone
> >> > help
> >> > with this? TIA ...
> >> >
> >> >
> >>
> >>
> >>
>
>|||I tried it and it actually did not work. I think there is something else I
need to do to fix it in addition. I have a conditional expression deciding
whether to perform the function (because I could have nulls in the cell but I
am replacing them with the words "No Data") So the expression in the data
cell is this (it is a matrix cell)
=IIF(IsNothing(Fields!PERCENT.Value), "No Data",
Sum(Fields!PERCENT.Value)/100 )
and the expression for the background color to be set for this cell is this:
=IIF(IsNothing(Fields!PERCENT.Value), "white", code.SetColor(
Fields!PerformanceValue.Value))
and the Function is this:
Public Function SetColor(ByVal N As Double) As String
Select N
Case 0
Return "Salmon"
Case 1
Return "PaleGoldenrod"
Case 2
Return "LightGreen"
Case 3
Return "RoyalBlue"
Case Else
Return "Transparent"
End Select
End Function
I tried changing the function to return values "#90EE90" etc and I altered
the IIF statement to read "#FFFFFF" instead of "white" ... but that didnt
work - it rendered correctly except didnt export to excel and show green ...
it still showed grey. I did get a warning message "The background color
expression used in textbox â'textbox8â' returned a data type that is not valid"
- textbox 8 is the cell I am using. I know when I have used hex codes for
colors in the past ... I dont believe I enclosed them in quotes in the cell,
but I cant return #90EE90 from my function without putting quotes around it,
can I? What would you do next?
"Kaisa M. Lindahl Lervik" wrote:
> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
> #80ff80.
> Case 0
> Return "#80ff80"
> I would have used hex codes for all colours that are not plain red, yellow,
> blue and green, just to be sure it was rendered OK.
> Kaisa M. Lindahl Lervik
>
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
> >I have a report that needs to have a green cell (based on a condition of
> > course - which is decided in a code function) Here is my function:
> >
> > Public Function SetColor(ByVal N As Double) As String
> >
> > Select N
> > Case 0
> > Return "Salmon"
> > Case 1
> > Return "PaleGoldenrod"
> > Case 2
> > Return "LightGreen"
> > Case 3
> > Return "RoyalBlue"
> > Case Else
> > Return "Transparent"
> >
> > End Select
> >
> > End Function
> >
> > All of the colors work *except* LightGreen. I can even *see* Light Green
> > in the excel color palette (notice the "space" in the excel color name)
> > ... I
> > dont even *see* Salmon, Palegoldenrod or anything else there ... and yet
> > *those* show properly. It is only the green color that shows wrong and it
> > displays as GREY. Not good. I have tried other light green colors but
> > the
> > only GREEN I can get to work is GREEN and that is too dark. Can someone
> > help
> > with this? TIA ...
> >
> >
>
>|||I will report back that I now have it working. I cant tell you what fixed
it ... maybe changing the case of the letters in hex? Thats about all I did.
Here is the expression that works.
=IIF(IsNothing(Fields!PERCENT.Value), "#ffffff", code.SetColor(
Fields!PerformanceValue.Value))
AND the function code:
Public Function SetColor(ByVal N As Double) As String
Select N
Case 0
Return "#fa8072"
Case 1
Return "#eee8aa"
Case 2
Return "#c0ffc0"
Case 3
Return "#4169e1"
Case Else
Return "#ffffff"
End Select
End Function
Thanks for all of your help!! I guess case was important in this CASE ...
pun intended!
"MJT" wrote:
> I tried using the hex codes and it didnt work. I thought it should too!
> What could I be doing wrong? I have a conditional expression in addition to
> the function in the cell ... maybe it has something to do with that:
> =IIF(IsNothing(Fields!PERCENT.Value), "white", code.SetColor(
> Fields!PerformanceValue.Value))
> It is not setting all cells to white ... so it isnt the behavior I have seen
> written about elsewhere in this newsgroup. I tried "#90EE90" and "#80ff80"
> and I still see grey where green should appear. Any other suggestions?
> "Steve MunLeeuw" wrote:
> > I'm not sure, I thought you could use the .Net object browser, but I didn't
> > see the hex codes.
> >
> > System.Drawing.Color has the color enum that you are looking for.
> > Ah, here's a page:
> >
> > http://www.opinionatedgeek.com/DotNet/Tools/Colors/default.aspx
> >
> >
> >
> >
> > "MJT" <MJT@.discussions.microsoft.com> wrote in message
> > news:0260F30C-FB37-4186-8501-E93516FD7C89@.microsoft.com...
> > > Thanks Kaisa ... I will try that. How can I find the hex codes for the
> > > palette colors in reporting services? For instance what PaleGoldenrod is.
> > >
> > > "Kaisa M. Lindahl Lervik" wrote:
> > >
> > >> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
> > >> #80ff80.
> > >>
> > >> Case 0
> > >> Return "#80ff80"
> > >>
> > >> I would have used hex codes for all colours that are not plain red,
> > >> yellow,
> > >> blue and green, just to be sure it was rendered OK.
> > >>
> > >> Kaisa M. Lindahl Lervik
> > >>
> > >>
> > >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> > >> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
> > >> >I have a report that needs to have a green cell (based on a condition of
> > >> > course - which is decided in a code function) Here is my function:
> > >> >
> > >> > Public Function SetColor(ByVal N As Double) As String
> > >> >
> > >> > Select N
> > >> > Case 0
> > >> > Return "Salmon"
> > >> > Case 1
> > >> > Return "PaleGoldenrod"
> > >> > Case 2
> > >> > Return "LightGreen"
> > >> > Case 3
> > >> > Return "RoyalBlue"
> > >> > Case Else
> > >> > Return "Transparent"
> > >> >
> > >> > End Select
> > >> >
> > >> > End Function
> > >> >
> > >> > All of the colors work *except* LightGreen. I can even *see* Light
> > >> > Green
> > >> > in the excel color palette (notice the "space" in the excel color name)
> > >> > ... I
> > >> > dont even *see* Salmon, Palegoldenrod or anything else there ... and
> > >> > yet
> > >> > *those* show properly. It is only the green color that shows wrong and
> > >> > it
> > >> > displays as GREY. Not good. I have tried other light green colors but
> > >> > the
> > >> > only GREEN I can get to work is GREEN and that is too dark. Can
> > >> > someone
> > >> > help
> > >> > with this? TIA ...
> > >> >
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >|||Hmmm, I wonder if it's running into a problem with the IIF(). If you put
the output out as text, rather than trying to set the color, are you getting
the color hex values you expect? If that's working correctly, try not using
the expression and seeing if you can set the background color to the light
green value that isn't working....If that is failing, then you know it's
not the expression and it's a bug or limitation in the excel rendering
engine. I'm using...was using LightGreen ( I swear) and it was rendering
to PDF just fine, not sure about Excel, never tested. I'd try exporting to
PDF and see if that works. If this helps, here's the code I'm using to set
the color of the chart bars.
public static Int32 PickColor(int index)
{
Int32 ReturnVal = Color.Tan.ToArgb();
switch (index)
{
case 0:
ReturnVal = Color.MediumSlateBlue.ToArgb();
break;
case 1:
ReturnVal = Color.MediumVioletRed.ToArgb();
break;
case 2:
ReturnVal = Color.LightYellow.ToArgb();
break;
case 3:
ReturnVal = Color.PaleTurquoise.ToArgb();
break;
case 4:
ReturnVal = Color.Purple.ToArgb();
break;
}
return ReturnVal;
}
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:CE2F86F8-6482-4CB0-A8A7-3D3A8E13FD06@.microsoft.com...
>I tried it and it actually did not work. I think there is something else I
> need to do to fix it in addition. I have a conditional expression
> deciding
> whether to perform the function (because I could have nulls in the cell
> but I
> am replacing them with the words "No Data") So the expression in the data
> cell is this (it is a matrix cell)
> =IIF(IsNothing(Fields!PERCENT.Value), "No Data",
> Sum(Fields!PERCENT.Value)/100 )
> and the expression for the background color to be set for this cell is
> this:
> =IIF(IsNothing(Fields!PERCENT.Value), "white", code.SetColor(
> Fields!PerformanceValue.Value))
> and the Function is this:
> Public Function SetColor(ByVal N As Double) As String
> Select N
> Case 0
> Return "Salmon"
> Case 1
> Return "PaleGoldenrod"
> Case 2
> Return "LightGreen"
> Case 3
> Return "RoyalBlue"
> Case Else
> Return "Transparent"
> End Select
> End Function
> I tried changing the function to return values "#90EE90" etc and I altered
> the IIF statement to read "#FFFFFF" instead of "white" ... but that didnt
> work - it rendered correctly except didnt export to excel and show green
> ...
> it still showed grey. I did get a warning message "The background color
> expression used in textbox 'textbox8' returned a data type that is not
> valid"
> - textbox 8 is the cell I am using. I know when I have used hex codes for
> colors in the past ... I dont believe I enclosed them in quotes in the
> cell,
> but I cant return #90EE90 from my function without putting quotes around
> it,
> can I? What would you do next?
>
> "Kaisa M. Lindahl Lervik" wrote:
>> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
>> #80ff80.
>> Case 0
>> Return "#80ff80"
>> I would have used hex codes for all colours that are not plain red,
>> yellow,
>> blue and green, just to be sure it was rendered OK.
>> Kaisa M. Lindahl Lervik
>>
>> "MJT" <MJT@.discussions.microsoft.com> wrote in message
>> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
>> >I have a report that needs to have a green cell (based on a condition of
>> > course - which is decided in a code function) Here is my function:
>> >
>> > Public Function SetColor(ByVal N As Double) As String
>> >
>> > Select N
>> > Case 0
>> > Return "Salmon"
>> > Case 1
>> > Return "PaleGoldenrod"
>> > Case 2
>> > Return "LightGreen"
>> > Case 3
>> > Return "RoyalBlue"
>> > Case Else
>> > Return "Transparent"
>> >
>> > End Select
>> >
>> > End Function
>> >
>> > All of the colors work *except* LightGreen. I can even *see* Light
>> > Green
>> > in the excel color palette (notice the "space" in the excel color name)
>> > ... I
>> > dont even *see* Salmon, Palegoldenrod or anything else there ... and
>> > yet
>> > *those* show properly. It is only the green color that shows wrong and
>> > it
>> > displays as GREY. Not good. I have tried other light green colors but
>> > the
>> > only GREEN I can get to work is GREEN and that is too dark. Can
>> > someone
>> > help
>> > with this? TIA ...
>> >
>> >
>>|||Steve,
read my post that is a couple of lines up in the thread (2/24) that tells
you I got it working. The only thing I can figure out is it might be the
case I was using. I tried many things ... I took out the IIF statement to
see what would export correctly in hex and for whatever reason it didnt seem
to like #90EE90 for light green but it accepted #C0FFCO ... and I noticed in
reporting services that when I chose a hex code from their custom colors ( I
started with what they had predefined before going to the "web colors" tab)
it made the hex value lower case #c0ffc0 ... and it exported and showed
correctly. So I changed the case of the hex letters in my function and it
seemed to work. Strange. I am snipping your code though. Must be c# ...
doesnt look like vb .net. I love it when people submit examples of code ...
I am just learning and it is so helpful to collect examples. Thanks for your
help! remember to use lower case hex letters ... lol
"Steve MunLeeuw" wrote:
> Hmmm, I wonder if it's running into a problem with the IIF(). If you put
> the output out as text, rather than trying to set the color, are you getting
> the color hex values you expect? If that's working correctly, try not using
> the expression and seeing if you can set the background color to the light
> green value that isn't working....If that is failing, then you know it's
> not the expression and it's a bug or limitation in the excel rendering
> engine. I'm using...was using LightGreen ( I swear) and it was rendering
> to PDF just fine, not sure about Excel, never tested. I'd try exporting to
> PDF and see if that works. If this helps, here's the code I'm using to set
> the color of the chart bars.
> public static Int32 PickColor(int index)
> {
> Int32 ReturnVal = Color.Tan.ToArgb();
> switch (index)
> {
> case 0:
> ReturnVal = Color.MediumSlateBlue.ToArgb();
> break;
> case 1:
> ReturnVal = Color.MediumVioletRed.ToArgb();
> break;
> case 2:
> ReturnVal = Color.LightYellow.ToArgb();
> break;
> case 3:
> ReturnVal = Color.PaleTurquoise.ToArgb();
> break;
> case 4:
> ReturnVal = Color.Purple.ToArgb();
> break;
> }
> return ReturnVal;
> }
>
>
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:CE2F86F8-6482-4CB0-A8A7-3D3A8E13FD06@.microsoft.com...
> >I tried it and it actually did not work. I think there is something else I
> > need to do to fix it in addition. I have a conditional expression
> > deciding
> > whether to perform the function (because I could have nulls in the cell
> > but I
> > am replacing them with the words "No Data") So the expression in the data
> > cell is this (it is a matrix cell)
> >
> > =IIF(IsNothing(Fields!PERCENT.Value), "No Data",
> > Sum(Fields!PERCENT.Value)/100 )
> >
> > and the expression for the background color to be set for this cell is
> > this:
> >
> > =IIF(IsNothing(Fields!PERCENT.Value), "white", code.SetColor(
> > Fields!PerformanceValue.Value))
> >
> > and the Function is this:
> >
> > Public Function SetColor(ByVal N As Double) As String
> >
> > Select N
> > Case 0
> > Return "Salmon"
> > Case 1
> > Return "PaleGoldenrod"
> > Case 2
> > Return "LightGreen"
> > Case 3
> > Return "RoyalBlue"
> > Case Else
> > Return "Transparent"
> >
> > End Select
> >
> > End Function
> >
> > I tried changing the function to return values "#90EE90" etc and I altered
> > the IIF statement to read "#FFFFFF" instead of "white" ... but that didnt
> > work - it rendered correctly except didnt export to excel and show green
> > ...
> > it still showed grey. I did get a warning message "The background color
> > expression used in textbox 'textbox8' returned a data type that is not
> > valid"
> > - textbox 8 is the cell I am using. I know when I have used hex codes for
> > colors in the past ... I dont believe I enclosed them in quotes in the
> > cell,
> > but I cant return #90EE90 from my function without putting quotes around
> > it,
> > can I? What would you do next?
> >
> >
> > "Kaisa M. Lindahl Lervik" wrote:
> >
> >> Try using Hexadecimal colour codes, like #c0ffc0 (quite light green) or
> >> #80ff80.
> >>
> >> Case 0
> >> Return "#80ff80"
> >>
> >> I would have used hex codes for all colours that are not plain red,
> >> yellow,
> >> blue and green, just to be sure it was rendered OK.
> >>
> >> Kaisa M. Lindahl Lervik
> >>
> >>
> >> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> >> news:592E3ADE-1BCD-4729-8782-61B6ACC1A275@.microsoft.com...
> >> >I have a report that needs to have a green cell (based on a condition of
> >> > course - which is decided in a code function) Here is my function:
> >> >
> >> > Public Function SetColor(ByVal N As Double) As String
> >> >
> >> > Select N
> >> > Case 0
> >> > Return "Salmon"
> >> > Case 1
> >> > Return "PaleGoldenrod"
> >> > Case 2
> >> > Return "LightGreen"
> >> > Case 3
> >> > Return "RoyalBlue"
> >> > Case Else
> >> > Return "Transparent"
> >> >
> >> > End Select
> >> >
> >> > End Function
> >> >
> >> > All of the colors work *except* LightGreen. I can even *see* Light
> >> > Green
> >> > in the excel color palette (notice the "space" in the excel color name)
> >> > ... I
> >> > dont even *see* Salmon, Palegoldenrod or anything else there ... and
> >> > yet
> >> > *those* show properly. It is only the green color that shows wrong and
> >> > it
> >> > displays as GREY. Not good. I have tried other light green colors but
> >> > the
> >> > only GREEN I can get to work is GREEN and that is too dark. Can
> >> > someone
> >> > help
> >> > with this? TIA ...
> >> >
> >> >
> >>
> >>
> >>
>
>
Subscribe to:
Posts (Atom)