April 20, 2012 at 11:37 am
I'm in the process of creating a report to generate server rack elevations from an in-house asset management system, and am running into some report formatting issues I'm hoping there's a solution for.
The data compiled for a page of the report looks like this (in part):
[font="Courier New"]
U__|__Host_Name__|__Manufacturer__|__Product_Name__|__Model__|__Part_#__|__S/N__|
50_|_____________|________________|________________|_________|__________|_______|
49_|_____________|________________|________________|_________|__________|_______|
48_|_SERVER001___|Hewlett-Packard_|Proliant_Server_|_DL585___|413934-L21|721268Z|
47_|_SERVER001___|Hewlett-Packard_|Proliant_Server_|_DL585___|413934-L21|721268Z|
46_|_SERVER001___|Hewlett-Packard_|Proliant_Server_|_DL585___|413934-L21|721268Z|
45_|_____________|________________|________________|_________|__________|_______|
44_|_SERVER002___|Hewlett-Packard_|Proliant_Server_|_DL385___|438825-B21|365268Q|
43_|_SERVER002___|Hewlett-Packard_|Proliant_Server_|_DL385___|438825-B21|365268Q|
42_|_SERVER003___|Hewlett-Packard_|Proliant_Server_|_DL385___|438825-B21|314897V|
41_|_SERVER003___|Hewlett-Packard_|Proliant_Server_|_DL385___|438825-B21|314897V|
40_|_____________|________________|________________|_________|__________|_______|[/font]
and I want it to appear in the report like this:
[font="Courier New"]
U__|__Host_Name______|__Manufacturer___|__Product_Name___|__Model__________|__Part_#_________|__S/N____________|
50_|_________________|_________________|_________________|_________________|_________________|_________________|
49_|_________________|_________________|_________________|_________________|_________________|_________________|
48_|_SERVER001_______|Hewlett-Packard__|Proliant_Server__|_DL585___________|413934-L21_______|721268Z__________|
47_|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|
46_|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|
45_|_________________|_________________|_________________|_________________|_________________|_________________|
44_|_SERVER002_______|Hewlett-Packard__|Proliant_Server__|_DL385___________|438825-B21_______|365268Q__________|
43_|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|
42_|_SERVER003_______|Hewlett-Packard__|Proliant_Server__|_DL385___________|438825-B21_______|314897V__________|
41_|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|<blank/grey_fill>|
40_|_________________|_________________|_________________|_________________|_________________|_________________|
[/font]
I've been able to get the Host Name field to show up correctly by using the "Hide Duplicates" property, but for the other fields (which will repeat on multiple servers), I can't do that. I tried getting around it by using "=(Previous(Fields!HostName.Value) = (Fields!HostName.Value))" as a formula to control the Visibility attribute on the other fields, which does hide the data correctly, but also hides the formatting (which "Hide Duplicates" doesn't).
The grey fill is set for the Host Name field with "=IIf(Fields!HostName.Value = "","White",IIf((Previous(Fields!HostName.Value) = (Fields!HostName.Value)),"DimGray","White"))" as the formula for BackgoundColor, but that only works if I use the "Hide Duplicates" property; if I use the formula for the Visibility attribute, it hides all of the formatting.
Is there any way to copy formatting from one textbox in a table to others in the same row (or is there another way to skin this cat that I don't know of)? The intent is to export this information into a spreadsheet that a team in the data center can use when auditing the contents of the racks (and make corrections on if necessary). I want to show which rows in the rack are filled (thus greyed out) but not repeat the data for all of the rows the server takes up.
Thanks in advance for any help.
Joshua
April 20, 2012 at 12:22 pm
I think I would add Row_Number() to your dataset partitioned by machine, then use the row number of 1 to do your formatting.
April 20, 2012 at 12:34 pm
How would I use that? Wouldn't it increment for blank rows as well? I want to keep blank rows white, but grey out repeats on filled rows.
April 20, 2012 at 2:50 pm
A Row_Number() partitioned and ordered by machine would give you numbers that start at one with every new machine and then increment from there. For example
Server Row#
Server1 1
Server1 2
Server1 3
Server2 1
Server2 2
Server3 1
Server4 1
Server4 2
Server4 3
Server4 4
etc.
If you use a statement that evaluates when the Row# = 1 this could allow you to create whatever formatting you need with regard to visibility, font color, background color etc.
I didn't see a mention of white rows in the initial note, perhaps I missed it. What kind of data are the white rows represented by? "", Null. This may present a problem with Row_Number(). I would need to know a little more.
April 20, 2012 at 3:22 pm
Daniel Bowlin (4/20/2012)
A Row_Number() partitioned and ordered by machine would give you numbers that start at one with every new machine and then increment from there. For exampleServer Row#
Server1 1
Server1 2
Server1 3
Server2 1
Server2 2
Server3 1
Server4 1
Server4 2
Server4 3
Server4 4
etc.
If you use a statement that evaluates when the Row# = 1 this could allow you to create whatever formatting you need with regard to visibility, font color, background color etc.
I didn't see a mention of white rows in the initial note, perhaps I missed it. What kind of data are the white rows represented by? "", Null. This may present a problem with Row_Number(). I would need to know a little more.
Yes, the white rows are ones with no data. The data is generated from a list of the assets and locations (with high and low U location), joined to a "virtual rack" created by cross joining a list of 50 rows with the names of the racks that have equipment in them. Only rows that have equipment have data in those fields; the rest of the "virtual rack" rows returned by the query are ''.
April 22, 2012 at 10:18 pm
Instead of controlling visibility, use formulas for the value being display (either display the text required or empty string) and for the background colour (e.g. either "White" or "Gray")
April 23, 2012 at 7:54 am
happycat59 (4/22/2012)
Instead of controlling visibility, use formulas for the value being display (either display the text required or empty string) and for the background colour (e.g. either "White" or "Gray")
That was my initial attempt. The problem is that some of the rows repeat all fields from the line above and some repeat some fields (but not all) from the line above. I want the rows that have only partial repeats to show all of the data (with a white background) , but the ones that are total repeats to just show gray with no other data. I can get the gray to appear if I use the "Hide duplicates" property, but then the partial repeat rows don't show all of the data.
Because this is for a rack elevation that will be used to audit the hardware in place, each server needs to have the model/part/manufacturer show up even if it's the same as the one above it, I can't just hide all duplicates. I also need to indicate (by graying out) which rows are supposed to be full and which ones empty.
April 23, 2012 at 8:10 am
To me, that simply means that your condition is not simply
Previous(Fields!HostName.Value) = (Fields!HostName.Value)
It is more like
IIF (Previous(Fields!HostName.Value) = (Fields!HostName.Value)) AND Previous(Fields!Manufacture.Value) = (Fields!Manufacturer.Value)) AND Previous(Fields!ProductName.Value) = (Fields!ProductName.Value)) AND...
April 23, 2012 at 8:26 am
happycat59 (4/23/2012)
To me, that simply means that your condition is not simplyPrevious(Fields!HostName.Value) = (Fields!HostName.Value)
It is more like
IIF (Previous(Fields!HostName.Value) = (Fields!HostName.Value)) AND Previous(Fields!Manufacture.Value) = (Fields!Manufacturer.Value)) AND Previous(Fields!ProductName.Value) = (Fields!ProductName.Value)) AND...
Not really. Each server has a different host name, and each host name only applies to a single server. As a result, any change of host name should result in a complete set of data being returned, and if the host name is the same as the previous row, then none of the data will be different (except for the row number); therefore Previous(Fields!HostName.Value) = (Fields!HostName.Value) is sufficient. I can't group by host name, because I need all of the U numbers to show up, and for the rack to be shown in U order.
April 24, 2012 at 8:18 am
I don't think that you should be grouping anything.
Also, I suspect that you are attempting to apply formatting etc at the row level of your report. You should be doing it at the individual cell level with no particular formatting etc being applied to the U field
April 24, 2012 at 8:40 am
happycat59 (4/24/2012)
I don't think that you should be grouping anything.Also, I suspect that you are attempting to apply formatting etc at the row level of your report. You should be doing it at the individual cell level with no particular formatting etc being applied to the U field
I was able to get the report to appear properly by doing the following (in case anyone else has need of this kind of formatting):
* Creating a group with no header or footer on the U field (top level)
* Creating a 2nd level group with no header or footer containing all fields except U (called table1_HostName)
* Setting the Hide Duplicates property for all fields (except U) to use the context of the table1_HostName group
* Setting the values of the fields in the report to "=IIf(Previous(Fields!HostName.Value) = (Fields!HostName.Value),"",[Field being reported]).
* Setting the Background Color property for all fields except U to "=IIf(Fields!HostName.Value = "","White",IIf((Previous(Fields!HostName.Value) = (Fields!HostName.Value)),"DimGray","White"))"
Joshua
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply