Hi All,
As the thread title state, I was wondering if it is possible to alternate the list table row colors between 2 colors. Just wanted to make it more presentable when viewing.
Regards,
Fadzli
I need to print in RED if there's atleast 1 or more consistency error and need a heading color of the table (Caption) how to do it.
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H1>DB Corruption Report:</H1>' +
N'<table border="1">' +
N'<tr><th>DBName</th><th>MessageText</th>' +
[code]....
Hi All,
I have a table containing 21 columns. I want to alternate the color on the rows, to make it more radable and nocer to the eye. As such I set the BackgroundColor property on the entire TableRow (so it is applied to every textbox) to the following:
Code Snippet
=iif(RowNumber(nothing)Mod 2,"LightGray","White")
In theory it should work. I checked a few of the texboxes to make sure everything was okay and indeed the BackgroundColor property on each one of them was properly set to the above expression.
When I tried to build the report, I was prompted by 21 errors (one per textbox) stating the following:
[rsCompilerErrorInExpression] The BackgroundColor expression for the textbox 'FIELDNAME' contains an error: [BC30455] Argument not specified for parameter 'TruePart' of 'Public Function IIf(Expression As Boolean, TruePart As Object, FalsePart As Object) As Object'.
Obviously the TruePart parameter has been specified ("LightGray" in my expression), so what could be wrong??
Thanks in advance for the help
Idelso
Hi
I was able to generate rows with alternating colors by using the below exp. but it only works fine with table report.
=iif(RowNumber(Nothing) Mod 2, "Lavender", "White")
Please help me to get alternating row colors in a matrix report.
Regards
Durai
1)What is alternating way for this query.
2)What is mean by sql injection ?
3)Is it safe to use exec in stored procedure?
kindly answer ASAP
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: Suyog Dabhole
-- Create date: 27/9/2007
-- Description: View all history data for detail invoicing report
-- =============================================
ALTER PROCEDURE [dbo].[procGetAllDetailInvoice]
@Date nvarchar(25)=null, --Date format like (10 Aug 2007) no timeing send by BAL
@MonthDate nvarchar(25)=null, --From BAL sended date is of format 01-MON-YYYY(01 Aug 2007)(default day is 01)
@FromDate nvarchar(25)=null, --date format is like DD MON YYYY(12 Aug 2007)
@ToDate nvarchar(25)=null, --date format is like DD MON YYYY(12 Aug 2007)
@WeekDays nvarchar(55)=null , --By default 0 is send for All days and we are using 1 for SUNDAY ,2 FOR MONDAY like ...UPTO 7 FOR SATURDAY
@AllRooms bit=null,
@SingleRoom nvarchar(10)=null,
@ByGroup nvarchar(10)=null,
@byAreaFrom nvarchar(10)=null,
@byAreaTo nvarchar(10)=null
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Query nvarchar(MAX)
SET @Query='SELECT DISTINCT tblChargeSummaryHistory.MonthYearStamp,
tblRooms.RoomNumber, tblMinibars.MinibarDisplayName, tblChargesDetailHistory.ChargeDateTimeStamp, tblChargesDetailHistory.Quantity,
tblChargesDetailHistory.ProductCategoryName, tblChargesDetailHistory.ProductName,
''Mode'' =CASE
WHEN tblChargesDetailHistory.Source =0 THEN ''Charge''
ELSE ''Adjustment''
END,
''Source'' =CASE
WHEN tblChargesDetailHistory.Source =0 THEN ''Minibar''
ELSE tblChargesDetailHistory.UserName
END,
''PMS Interface''=CASE
WHEN (tblChargesDetailHistory.FlagChargetobeSent=1 and tblChargesDetailHistory.FlagChargeSenttoPMS=1 and tblChargesDetailHistory.PMSInterfaceStatus=1)
THEN ''OK''
ELSE ''No Interface''
END,
tblChargesDetailHistory.SellingPriceWithoutTax AS ''Price(Exc.Tax)'',
tblChargesDetailHistory.Tax1 + tblChargesDetailHistory.Tax2 + tblChargesDetailHistory.Tax3 + tblChargesDetailHistory.Gratuity AS ''Taxes & Gratuity'',
tblChargesDetailHistory.SellingPriceWithoutTax + (tblChargesDetailHistory.Tax1 + tblChargesDetailHistory.Tax2 + tblChargesDetailHistory.Tax3 + tblChargesDetailHistory.Gratuity)
AS ''Price(Inc.Tax)''
FROM tblChargeSummaryHistory INNER JOIN
tblMinibars ON tblChargeSummaryHistory.MinibarID = tblMinibars.MinibarID INNER JOIN
tblChargesDetailHistory ON tblMinibars.MinibarID = tblChargesDetailHistory.MinibarID INNER JOIN
tblRooms ON tblChargeSummaryHistory.RoomID = tblRooms.RoomID AND tblMinibars.RoomID = tblRooms.RoomID AND
tblChargesDetailHistory.RoomID = tblRooms.RoomID'
IF(@Date IS NOT NULL)
BEGIN
SET @Query=@Query+' WHERE tblChargeSummaryHistory.MonthYearStamp<'''+@Date+' 11:59:59 PM '' and tblChargeSummaryHistory.MonthYearStamp>'''+@Date+' 12:00:00 AM '''
END
ELSE IF(@MonthDate IS NOT NULL)
BEGIN
SET @Query=@Query+' WHERE DATEPART(year,tblChargeSummaryHistory.MonthYearStamp)=DATEPART(year,'''+@MonthDate+''') and
DATEPART(month,tblChargeSummaryHistory.MonthYearStamp)=DATEPART(month,'''+@MonthDate+''')'
IF(@Weekdays IS NOT NULL)
BEGIN
SET @Query=@Query+' AND DATEPART(weekday,tblChargeSummaryHistory.MonthYearStamp) in('+@WeekDays +')'
END
END
ELSE IF(@FromDate IS NOT NULL AND @ToDate IS NOT NULL)
BEGIN
SET @Query=@Query+' WHERE tblChargeSummaryHistory.MonthYearStamp between '''+@FromDate+' 12:00:00 AM '' and '''+@ToDate+' 11:59:59 PM '''
IF(@Weekdays IS NOT NULL)
BEGIN
SET @Query=@Query+' AND DATEPART(weekday,tblChargeSummaryHistory.MonthYearStamp) in('+@WeekDays +')'
END
END
IF(@AllRooms IS NOT NULL)
BEGIN
SET @Query=@Query+' '
END
ELSE IF(@SingleRoom IS NOT NULL)
BEGIN
SET @Query=@Query+' and tblRooms.RoomID=(select DISTINCT RoomID from tblRooms where RoomNumber='''+@SingleRoom+''')'
END
ELSE IF(@ByGroup IS NOT NULL )
BEGIN
SET @Query=@Query+' and tblRooms.RoomID IN (SELECT RoomID FROM tblRoomGroups WHERE GroupID=(SELECT DISTINCT GroupID FROM tblGroups WHERE GroupName='''+@ByGroup+''' )) '
END
ELSE IF(@byAreaFrom IS NOT NULL AND @byAreaTo IS NOT NULL)
BEGIN
SET @Query=@Query+' and tblRooms.RoomID IN (SELECT DISTINCT RoomID FROM tblRooms WHERE RoomNumber>='+@ByAreaFrom+' AND RoomNumber<='+@ByAreaTo+' )'
END
exec(@Query)
SET NOCOUNT OFF;
END
I am trying to get alternating colors on group headers.
The rownumber() doesn't work; that only seems to be the count of rows in the group.
Does anyone have any great ideas for this?
Thanks!
BobP
Does anyone know if it's possible int sql 2000 reporting to change the color of an alternating item, similar to a datagrid in asp.net?
View 1 Replies View RelatedHello,
Im trying to embedd a picture into my detail table rows to have a dynamic bar graph happening with data from a column called winbacks.
1.So Ive created the embedded 'bar' image
2. Ive inserted the image into the row and under the image properties i have put under the padding
=144 * (1.0 - Fields!Winbacks.Value / Max(Fields!Winbacks.Value, "dataset1")) & "pt"
This part is working....and the image is stretching accordingly to scale.
I would now like to put in a condition to use a different image OR write some text in the row field if the Winback.Value is the highest in the table for that row...ie write ''top gun'' on top off the bar image
Im not sure if you can write text and have the image appearing but this would be ideal...
so Iam instead trying to create an image with the text and when the winback value is the highest use that image ELSE use the normal image.
Can someone please help with how to do this
thanks
Hi All,
I like RS for SQL server.
However, I am surprised (and frustrated) how limited the chart capabilities are; perhaps we have been spoiled by Excel...
Anyway, is there any way to change the color of the chart bars programatically.
I can select different schemas (excel, pastel, grayscale, etc) but I need to go a little bit further, is that much to ask?
Oh, by the way, we can't mix chart styles, can't we?
Need to show a chart and a line in the same report...
I appreciate your help on this, hopefully any answers to my questions might also help with other RS users with the same needs,
Thanks,
L
Hi,
I know the title is not really that specific but I have a question about the use of templates.
When you copy a package (template) you allways have to generate a new id for it to be unique. But when you copy the package the tasks (etc.) will not get an new id and you can't even change it because it is readonly. So two different packages can have tasks which has the exact same id!!
When you debug in VS if you run two packages in parrallel the one that first exits will become green and VS will automatically make all tasks that has this exact id green - making it seem like the task in another package has finished while it may not have started yet at all.
So the question is: beside the strange behavour with the color can this identical id problem be a real problem causing other strange behavours that is more serious than just a change in color?
Has others experienced this behavour?
Is there a way to apply .css or Skin files to all reports. Changing colors in each and every page is a little cumbersome.
Thanks in advance
Hi all,
Hopefully someone can clarify for me. I'd like to use custom colors for a chart that I have created. I've seen some code examples but I'm not really sure what to do with them. I've been using reporting services for like 2 days and I don't know xml or vb script, so I apologize for the probably stupid question. Thank you in advance.
-Keith
I am using RGB HEX #s in my report for some colors. It shows up fine when rendering to a browser, however when exporting to Excel my custom colors don't come over. I've tried creating a report.xlt file with my custom colors in it, but when I export to Excel it uses the standard Excel palette instead of the custom palette in my report.xlt.
Any ideas on how to retain my custom colors when exporting to Excel?
I have been asked to derive a bar charts in SSRS. Each bar would represents a different category and each bar should come with different colors. No. of categories might vary every time, it is not fixed. How to achieve this dynamically?
View 2 Replies View RelatedI need some help regarding Custom Colors implementation for Charts in the Reporting Services.
What we are trying to do is to make every color for every Series(see example below) to be a customized Corporate color.
For example each of the series on the Chart should be in specific Custom Color:
Series 1 - "LightBlue"
Series 2 - "LightYellow"
Series 3 - "DarkBrown"
All I found from MSDN is how to do it in the Chart Properties in a Code section (right click on the report outside the chart -> Properties -> Code tab -> paste the Custom Code ).
And then to use it in the Chart color definition as:
Code Snippet=Code.GetColor( Series# )
But we are trying to do it in a separate C# class that would be updated only in one place, instead of changing 30 reports each time something is changed. So that it could be generic.
The C# class:
Code Snippet
namespace CustomColorPalette
{
public class CustomPalette
{
public CustomPalette()
{
}
public static string GetColor(string GroupingValue)
{
int groupingValue = int.Parse(GroupingValue);
string[] colorPalette = {"Green", "Blue", "Red"};
if (groupingValue < colorPalette.Length)
{
return colorPalette.GetValue(groupingValue).ToString();
}
else
{
return "Black"; // if # of Series is more than defined - all undefined will be = Black color
}
}
}
}
I tried 2 different ways:
1.
Then I put a code into a Custom Code section of a report:
Code Snippet
Public Function GetColor(SeriesNumber as String)
Return CustomColorPalette.CustomPalette.GetColor(SeriesNumber)
End Function
And in the Chart Custom Color settings I put:
Code Snippet=Code.GetColor( Fields!SeriesNumber.Value )
And after compilation I get an error:
Error 303 [rsCompilerErrorInCode] There is an error on line 2 of custom code: [BC30451] Name 'CustomColorPalette' is not declared. c:developmentvoyagerreportsvoyager convert reports.rootvoyager convert reportsReport_007D.rdl 0 0
2.
Second way I tried:
Then I put a code into a Custom Code section of a report:
Code Snippet
Dim X As CustomColorPalette.CustomPalette
Protected Overrides Sub OnInit()
X = new CustomColorPalette.CustomPalette()
End Sub
And in the Chart Custom Color I put:
Code Snippet=Code.X.GetColor( Fields!SeriesNumber.Value )
And after compilation I am getting an error like this:
Error 302 [rsCompilerErrorInExpression] The BackgroundColor expression for the chart €˜chart2€™ contains an error: [BC30456] 'X' is not a member of 'ReportExprHostImpl.CustomCodeProxy'. c:developmentvoyagerreportsvoyager convert reports.rootvoyager convert reportsReport_007D.rdl 0 0
Is there any workaround? Or all I can do is to make the Custom Colors manually in each report, and if something need to be changed - I will need to change it in each report separately
Thanks,
Alex
I have a chart that presently has two groupings on the x-axis: Year & Qtr. I'd like the Year labels to be one color, and the Qtr labels to be a different color. (All year labels should be Black, all Qtr labels should be Blue). I can see that an expression can be written to handle this dynamically, but I'm banging my head trying to figure it out...
Any help?
Thanks in advance,
Pete
Hello,
Maybe anyone have done that before?
I have table where i store SOURCE_TABLE_NAME and DESTINATION_TABLE_NAME, there is about 120+ tables.
i need make SSIS package which selects SOURCE_TABLE_NAME from source ole db, and loads it to DESTINATION_TABLE_NAME in destination ole db.
I made such SSIS package. set ole db source data access mode to table or view name variable.
set ole db destination data access mode to table or view name variable. set to variables defoult values (names of existing tables)
but when i loop table names is changed, it reports error, that can map columns, becouse in new tables is different columns.
how to solve that problem?
I have a Matrix with dynamic columns. I am grouping Matrix on one column group. I am trying to have a different color for every group. I hvae so far, three groups. How can I do that? A note, there is no data for every column group in every row.
View 7 Replies View RelatedI have created a linked report in which I have two screen:
First Screen: Clicking on the bar of particular country the details of that particular country should be visible (the second screen)
Second Screen: When I clicked on a bar. All the count values are very well correct. In first chart Count of values under one bar "Pending decision"(yellow) was 337 for a country and in second screen its 337 again(shown in light green bar).
<!--[if !vml]--><!--[endif]-->
Problem: My problem is to make it sure that the colors that the first screen is having for particular status (as shown in the legend) should remain same in second screen also. For example for a status say "pending decision" the chart is using yellow color, so in screen two as well it must be shown with yellow color and not with lany other color. Can anyone help me in this context. Is there any way to customize colors used by bar chart. Please note that the question is not about using appropriate color scheme(palette) the question is how to "capture/ Re-use/ customize/ pass as a parameter" the colors used by a bar chart in SQL Server 2005 Reporting Services.
I have a column bar chart which displays values for each month. As per the requirement, January column must be blue, February must be orange, March must be green, April must be pink. These 4 colors would repeat for the remaining months.
View 5 Replies View RelatedI have a database that has 370 tables that match %_REF_% naming. All of these tables have a column call ds_name.
I need to get a list of all values of ds_name for each table.
Hi All,
I want to get the Font,Background colors of the report item(Textbox) changed in Runtime,According to the value in the Textbox. The idea is to get this done by using the Same Custom code for getting both the Foreground,Background colors, I thought to implement this with flag, But I dont know how to do this..
Reporting experts ..please help me with this issue.Thanks in Advance
-Mahendra
I have a report in which I have assigned custom colors with a function to one of my stacked column charts. When I run it in the report builder, the colors are correctly placed, but as soon as I upload the report to the server and view the report from there, the colors get swapped. I have these custom colors applied to other stacked column charts in the report and they work perfectly fine. The only difference between this chart and my other charts is that it contains two category groups.
EDIT: Alright, I have gotten it to display the correct colors by using a function with the colors I wanted in reverse. I have two values, "Upcoming" which is displayed in red, and "Outstanding" which is displayed in blue. If the chart has only upcoming values, it will display in blue rather than red, whereas if I have upcoming and outstanding values, they will display in their correct colors. why this is occurring?
I am facing some issue in rendering my SSRS report(2012 version) to excel sheet with color codes. i am using conditional formatting to display the colors. So what is happening is ,take the below expression as an example, for the id  = 0, we are not getting white back ground, we are getting some other colors. But the same thing is working fine in SSRS 2008R2.Â
=IIF(MAX(Fields!id.Value=0,
= 0, "WHITE","BLUE")
We mark cells in  a report using color names like red, pink, and transparent in the fill expression. In the browser it works correctly. When exported to Excel the colors go to the wrong cells. This worked correctly in SSRS 2008.Â
View 9 Replies View RelatedI have 3 SSRS 2014 (Dev, UAT and Prod). I would like to change background colors of each environment and customize the title 'SQL Server Reporting Services' to ' SSRS Development'.
I prefer to implement both, a background color change and a title change. The reason for this is to clarify to end users which environment they are working with.
Where can I make those minimal changes in SSRS 2014.
i need to give list all table in my database plaese give me qurity?
thanks a lot
Hello,
If you need to have a liste of tables starting with 'WI', under Oracle for example, with a simple select of a system view (with a filter : table_name like 'WI%') you have the result.
Under SQL Server, how to do this simply (sp_tables ?) ?
Thanks in advance
Hi Group!I would like to get a list of all the table names from a database, cananybody please tell me how to do that? Many thanks!
View 4 Replies View RelatedHi,
i want to get the list of column i have in a table using sql statment
can someone send me example?
thanks
Anybody know any method/script I can use to see a list of table name
and table size (in bytes, not rows) within a database?
I know I can see the size of any particular table in "Table info" tab or using "sp_spaceused" procedure. But I can't generate a report with a list of table name and table size using these methods.
Any idea? Thanks.
John
SELECT Item.Unit_price
FROM Item
ORDER BY Item.Unit_price DESC
How can I display the Top10 only? thanks.
Hi guys, i'm sort of stuck.
I have a list of names, all i have to do is check to see if any of them are in a table, if they are they get deleted. The unique field of the table is title and it is what i have. I don't really know how i am meant to do this. I thought of creating a new table, placing my values into it and comparing to the already existing table, but surely their is a simple way?