|
|
-
Question:
How can I repeat a group header on each page, but have it say "MyGroup (continued)" on all but the first page?
Answer:
Since pagination is determined long after the report has been fully processed, there's no way for the body of the report to reference any page information. As a result, we're going to have to resort to a trick wherein something in the page header can be made to look as if it is the group header.
The following assumes you are using a table and have no table header but can be readily adapted to other situations.
Step 1: Turn off RepeatOnNewPage on your group header
Step 2: Duplicate the group header information in a hidden column
You're going to need the group header text to always be present on the page, even when the group header itself isn't present. The easiest way is to add a hidden column to the table and then copy the group header textbox into the detail row in that column. Make careful note of the names of both the group header textbox (which I'll call GroupHeader) and the copy (which I'll call GroupCopy)
Step 3: Copy the first GroupHeader and first GroupCopy to the page header
In the page header, add two textboxes with the following expressions:
=First(ReportItems!GroupHeader.Value)
=First(ReportItems!GroupCopy.Value)
Make careful note of the names of both these textboxes (which I'll call FirstGroupHeader and FirstGroupCopy). Run the report and notice the behavior: FirstGroupHeader is the same as FirstGroupCopy only if it appears at the top of the page. Mark both textboxes as Hidden=True
Step 4: Copy the GroupCopy textbox into the page header and add "continued"
In the page header, add a textbox with the following expression:
=First(ReportItems!GroupCopy.Value) + " (continued)"
Align and size the textbox to match the GroupHeader textbox exactly. Now when you run the report, you'll see both the normal group header and the Continued group header
Step 5: Conditionally hide the Continued header textbox
Ideally, the next step would be to set the Hidden expression on the Continued header textbox to this:
=ReportItems!FirstGroupHeader.Value=ReportItems!FirstGroupCopy.Value
Unfortunately, this won't work due to a slightly overzealous publishing restriction against using multiple report item references within a single expression in a page header item [which was intended to prevent potentially bad things like Sum(textbox1/textbox2)].
However, we can bypass this publishing restriction by passing in the report items collection to a custom function.
Add the following to the report's Code block:
public function HideHeader(Items as ReportItems) as Boolean
return Items("FirstGroupHeader").Value = Items("FirstGroupCopy").Value
end function
Now set the Hidden property of the Continued header to:
=Code.HideHeader(ReportItems)
Note: In the event you will always page break at the end of a group, there is a much simpler approach. You can skip steps 3 and 5 and instead set the Hidden property of the Continued header to:
=Not(ReportItems!GroupHeader.Value Is Nothing)
A full working sample of continued headers on subsequent pages is attached.
|
-
Question:
How can I get print-quality output from my custom report item?
Answer:
Custom report items have slightly different plumbing than native report items. Of particular note are native charts. These are generated during report rendering, which means it is possible for us to vary both the type (raster or vector) and the DPI of the image based on the needs of the renderer. For example, we currently use 300 DPI raster for PDF, vector for print and Winforms and 96 DPI raster for HTML and Excel. Custom Report Items, on the other hand, are generated during processing. This means only raster images can be used (since they must work with all renderers, including those which do not support vector images) and only a single DPI can be chosen for all rendering targets.
Custom Report Items should therefore generate a raster image of sufficiently high DPI as to be high quality when printed. A default setting of 300 DPI should be sufficient for most applications. However, since the renderers don't currently pay attention to DPI, this will result in the image being three times the intended size. To account for this, you'll need to set the Sizing property of your generated image to FitProportional so that the image is rendered at its originally intended size:
m_MyImage.Sizing = Microsoft.ReportingServices.ReportRendering.Image.Sizings.FitProportional;
In some future version when we add native vector graphics support (EMF or SVG), this will become a non-issue for custom report items. At that time, a CRI could generate a single vector image which would be automatically converted by the Rendering Object Model into a raster image of appropriate DPI if only raster images are supported by the target renderer.
I've attached an updated Custom Report Item sample which illustrates working with variable DPI.
|
-
Question:
How can I enable users to select "All" as the value of a multivalue query parameter?
Answer:
Since multivalue parameters cannot be null, you cannot use the approach previously described here. Fortunately, a simple modification of that approach to use a non-null "ALL" token is all that is required.
Step 1: Start with a report that already has a parameterized query and Valid Values queries.
Step 2: Edit your base query to account for "All".
Whever you have something like this:
... AND Field IN (@Parameter) ...
Replace it with this:
... AND (Field IN (@Parameter) OR 'ALL' IN (@Parameter)) ...
Step 3: Edit your Valid Values queries to insert an explicit "All" and separate ids from labels
For example, for this original query:
SELECT name FROM productcategory
Change it to this:
SELECT 'ALL' as ID, 'All' as Label
UNION ALL
SELECT name as ID, name as Label FROM productcategory
Step 4: Update the report parameters
Set the default value of each parameter to ALL
Change the valid values settings to account for the new field names (if needed).
Step 5 (Optional): Update Valid Values queries to account for "All".
If you have hierarchical parameters, in some cases, you may want the dependent parameters to be independently selectable when the parent parameter's value is "All". In this case, repeat step 2 for each of your Valid Values queries. Note: Don't do this if you have extremely large numbers of possible values, as performance will suffer and your users wouldn't be able to nagivate an excessively long list anyhow.
A full working sample of multivalue "All" parameters is attached.
|
-
Question:
How can I reset my page number back to 1 every time I get a group break?
Answer:
Resetting the page number on group breaks isn't natively supported, but it can be achieved by tracking group breaks in a shared variable and subtracting off the page offset of the first page of the group from the current page number.
Step 1: Make sure there's a textbox in the report which contains the group expression
Step 2: Add shared variables to track the current group and page offset
Shared offset as Integer
Shared currentgroup as Object
Step 3: Add a custom function to set the shared variables and retrieve the group page number
Public Function GetGroupPageNumber(group as Object, pagenumber as Integer) as Object
If Not (group = currentgroup)
offset = pagenumber - 1
currentgroup = group
End If
Return pagenumber - offset
End Function
Step 4: Use the function in the page header or footer
=Code.GetGroupPageNumber(ReportItems!Category.Value,Globals!PageNumber)
Note: Because this uses static variables, if two people run the report at the exact same moment, there's a slim chance one will smash the other's variable state (In SQL 2000, this could occasionally happen due to two users paginating through the same report at the same time, not just due to exactly simultaneous executions) If you need to be 100% certain to avoid this, you can make each of the shared variables a hash table based on user ID (Globals!UserID).
A full working sample of page number reset on group is attached.
|
-
A currently undocumented feature of SQL 2005 Reporting Services is the ability to develop custom report items for embedding in reports. The documentation will be available in the web doc update around the time of the release. But for those of you interested in playing around with this in the mean time, I've put together a sample that shows pretty much everything you need to know to build one.
There are two controls you'll need to build when creating a Custom Report Item:
First, there’s the runtime control. Based on the presence of a CustomReportItem element in the report (RDL), the report processing engine processes the data and hands the resulting CustomReportItem object (part of the Rendering Object Model) to the runtime control (ICustomReportItem). The runtime control then creates a standard ReportItem to hand back in to the Rendering Object Model. Note: Currently the only type of report items supported is Image.
The second component is the design time control, which provides a plug-in to the Visual Studio-based Report Designer. It consists of a design surface, an activated design space (good for drag-and-drop of fields), integration with the VS property browser and hooks for a custom editor and other custom actions. Using this control, you use the report designer object model to construct a custom report item object within the report definition.
There's also a third optional component which allows you to "upgrade" an existing report item to your custom report item. It just takes an XML node for the original report item and returns an XML node for the custom report item.
The full sample can be downloaded here:
http://www.msnusers.com/ReportingServicesSleazyHacks/Documents/PolygonsCRI.zip
(You'll need to register here first for access: http://www.msnusers.com/ReportingServicesSleazyHacks/join)
For instructions on where to obtain the official sample, see: http://msdn.microsoft.com/en-us/library/ms345265.aspx
|
-
Question:
I have variable sized images I want to display in a table column. How can I center these images without stretching them to fill the table cell?
Answer:
While there is no automatic centering behavior for the Image control, you can simulate this by writing an expression for PaddingLeft and PaddingTop to result in a centered image.
Step 1: Clip, don't Fit
Set the Sizing property of the Image control to Clip. In a table cell, this is the only setting that won't resize your image. (If your image gets automatically resized, the calculations below won't work.)
Step 2: Give yourself room to grow
Make your column and row large enough to accomodate your largest image so that the image won't clip.
Step 3: Turn off CanGrow
For all other cells in the row, turn off CanGrow, so ensure long textual contents won't cause the row to increase in size (thereby invalidating the calculations below).
Step 4: Reference System.Drawing
To determine the size of the image, you'll need to add a reference to the System.Drawing assembly in your report. This is in the Report Properties dialog on the References Tab.
Step 5: Calculate padding
To determine the size of the image, you'll need to load the image from your field into a memory stream and from there load it into a .Net Image object. Since fields are of type Object, you'll need to cast it to a byte array before loading it into a memory stream.
ImageWidthInPixels = System.Drawing.Image.FromStream(new System.IO.MemoryStream(CType(Fields!Photo.Value,Byte()))).Width
From there, you can calculate the half of the difference between the width of the image and the width of the column.
Padding = (ColumnWidthInInches-ImageWidthInPixels/DPI)/2
DPI is typically 96
The padding property takes a size, which is a string including the size and the units.
PaddingLeft = CStr(Round(Padding,2)) & "in"
Here's the expression fully expanded:
=CStr(Round((3-System.Drawing.Image.FromStream(new System.IO.MemoryStream(CType(Fields!Photo.Value,Byte()))).Height/96)/2,2))&"in"
Now just do the same thing for PaddingTop and you've got a fully centered image.
A full working sample of centered images is attached.
|
-
Question:
How can I get a green-bar effect (alternating colors) in a matrix?
Answer:
For a green-bar table, you can simply use a background color expression like this: =iif(RowNumber(Nothing) Mod 2,"Green","White")
However, there is currently no GroupNumber() function on which to base a green-bar calculation in a matrix.
GroupNumber can be (mostly) simulated by using the RunningValue function to get a running distinct count of group expression values.
However, the trickiest part of green-bar in a matrix is the fact that some matrix cells may contain no data at all. This makes the group number calculation incorrect for empty cells.
To work around this, you need to effectively calculate the group number in the row header and then use that value inside the data cells.
Step 1: Add a (fake) inner row grouping
Select the innermost row grouping in your matrix. Right-click and select Insert Group.
For the group expression, group on a constant, such as =1
Step 2: Calculate the name of the color in the inner row grouping header
In the Value property of the newly created grouping header, add a calculation for the desired color based on a running value of a count distinct of the containing group expression.
For example: =iif(RunningValue(Fields!Country.Value,CountDistinct,Nothing) Mod 2, "AliceBlue", "White")
Note: If you have more than one row grouping, you may need to do the count distinct on the combination of all group expressions, like this:
=iif(RunningValue(Fields!Country.Value & CStr(Fields!Year.Value),CountDistinct,Nothing) Mod 2, "AliceBlue", "White")
Step 3: Set the background color of the inner row grouping header to =Value
Step 4: Set the background color of the matrix data cell to the value of the inner row grouping header
For example: =ReportItems!ColorNameTextbox.Value
Step 5: Set the background color of the outer row grouping header
You'll need to use the same expression here that you used for the Value of the inner row grouping header.
Step 6: "Cloak" the inner row grouping header (so it looks like part of of the outer grouping header)
Set the right border style of the outer grouping header to None.
Set the left border style of the inner grouping header to None.
Set the font weight of the inner grouping header to 1 pt.
Set the font color of the inner grouping header to =Value.
Set the CanGrow property of the inner grouping header to False.
Drag the inner grouping header to be as narrow as possible.
Optional: Hand-edit the RDL to set the width of the inner grouping header to 0in.
A full working sample of green-bar matrix is attached.
|
-
Question:
How can I enable users to select "All" as the value of a query parameter?
Answer:
The simplest way is to modify your query to accept nulls and map "All" to null.
Step 1: Start with a report that already has a parameterized query and Valid Values queries.
Step 2: Edit your base query to account for nulls.
Whever you have something like this:
... AND Field = @Parameter ...
Replace it with this:
... AND (Field = @Parameter OR @Parameter is NULL) ...
Step 3: Edit your Valid Values queries to insert an explicit "All" and separate ids from labels
For example, for this original query:
SELECT name FROM productcategory
Change it to this:
SELECT name as ID, name as Label FROM productcategory
UNION
SELECT Null as ID, 'All' as Label
Step 4: Update the report parameters
Mark each parameter as nullable
Change the valid values settings to account for the new field names (if needed).
Step 5 (Optional): Update Valid Values queries to account for nulls.
If you have hierarchical parameters, in some cases, you may want the dependent parameters to be independently selectable when the parent parameter's value is "All". In this case, repeat step 2 for each of your Valid Values queries. Note: Don't do this if you have extremely large numbers of possible values, as performance will suffer and your users wouldn't be able to nagivate an excessively long list anyhow.
Update
|
-
Question:
Does Reporting Services support horizontal tables (fixed rows and dynamic columns)?
Answer:
There is no native "horizontal table" report item, but simple horizontal tables can be simulated using Matrix.
Step 1: Add a matrix to your report
Step 2: Add static rows to the matrix
Right-click in the Data cell and select "Add Row". Repeat for the number of fixed rows you want.
Step 3: Drag fields into the Data cells
You'll notice that the design tool automatically wraps your field reference in the "First" aggregate (e.g. =First(Fields!City.Value)). Since you're doing this in the context of a matrix, the design tool is ensuring that the expression is meaningful even in the context of a subtotal or if the matrix is showing aggregated data rather than detail data. Since you're going to show detail data without subtotals, you technically could remove the aggregate expression (but don't, since then you'll start getting warnings which you'll need to ignore).
Step 4: Add a column grouping
Right-click on the column header and select "Edit Group". Enter this for the group expression: =RowNumber(Nothing). This will cause the matrix to give you one column per row of data. Since horizontal tables can end up rather wide, you probably want your table wrap around to the next "line" after a specific number of columns.
Step 5: Put the table into a list
Add a list to your report and drag the table into it
Step 6: Group by a number of rows
Right-click on the list and select Properties. Then click on Edit Details Group.
Enter this for the group expression: =Ceiling(RowNumber(Nothing)/3)
This will cause the list to group on every three rows. So you'll get a separate table for every three rows.
Step 7: Adjust the group expression in the matrix
Edit the column group expression in your matrix and change the RowNumber argument to be the list group name.
For example: =RowNumber("list1_Details_Group")
A full working sample of horizontal table is attached.
|
-
Question:
How can I give my users the ability to dynamically select fields on which to group within a report?
Answer:
The key to dynamic grouping in a report is this: Practically everything in a report can be based on an expression. From grouping to column headers to column and row visibility... since all of it is expression-based, it can be based on parameters supplied by the user.
Step 1: Build your report with static grouping.
It will be easier to start with a standard report with non-dynamic grouping and modify it from there.
Step 2: Define the grouping parameters.
For each dynamic group, create a parameter for the field name on which to group. Add a valid values list containing the names of the fields on which you want to allow grouping. If you want grouping to be optional, also include null (with a label like "None").
Step 3: Change your group expressions to make them based on the parameters
The trick here is the indexer into the Fields collection. Normally, you have static group expressions like this: =Fields!Year.Value.
But you can refer to items in the Fields collection using an alternate string-based syntax like this: =Fields("Year").Value.
Since the field name is just a string, that means you can use any string subexpression instead. In particular, you can use the parameter:
=Fields(Parameters!FirstFieldName.Value).Value
If you want to allow for optional grouping, this is slightly more complicated:
=iif(Parameters!Group1.Value is Nothing,1,Fields(iif(Parameters!Group1.Value is Nothing, "Year",Parameters!Group1.Value)).Value)
If the parameter value is Nothing, you can just group on a constant (like 1).
Notice the second iif embedded within the Fields collection indexer expression. This is needed because you'll get an error if you try to access the Fields collection with a null indexer [Remember: Visual Basic evaluates all arguments of all functions, so the outer iif wouldn't be enough to stop the Fields collection from erroring if the parameter value is null]. If the parameter value is Nothing, the third argument to the iif will return the value of the Year field, but it won't be used for anything.
Step 4: Change the column headings and report data to be based on the parameters
Before:
Customer Year
=Fields!Customer.Value =Fields!Year.Value
After:
=Parameters!Group1.Value =Parameters!Group2.Value
=Fields(Parameters!Group1.Value).Value =Fields(Parameters!Group2.Value).Value
Note: Again, if you're allowing for optional grouping, you'll need to make things slightly more complex:
=Fields(iif(Parameters!Group1.Value is Nothing, "Year", Parameters!Group1.Value)).Value
Step 5: Hide columns and rows for optional grouping
If you're allowing for optional grouping, you'll need to hide the unneeded columns and rows. You can do this by setting the Hidden property of the corresponding column and row to: =Parameters!Group1.Value is Nothing
A full working sample of dynamic grouping is attached.
|
-
I'm the guy who gets to take the credit and/or blame for designing the Report Definition Language (RDL).
This is my warehouse for RDL tips, tricks, sleazy hacks and answers to frequently asked questions.
Whenever I get around to it.
The reporting services newsgroup: microsoft.public.sqlserver.reportingsvcs
I post topics here based on common or interesting questions I answer on the newsgroup so I can direct people here if I've answered it before.
|
|
|
|
