Point Labels not displaying consistently on Bar Graphs

When doing Bar Graphs in Reporting Services, it’s nice to use point labels to show the values of your data fields. To do this it’s quite easy :

• Right click on your data value field and select properties ( or on the chart properties box go to the Data tab , select a value and click Edit )
• Select the Point Labels Tab and click Show Point Labels.
• Choose the data label and the formatting style.

You can even customise the font attributes and point label positioning. However I stumbled accross a problem recently where if the values of the bars were significantly different from each other , some bars would not display the point labels.

The first thing I tried was to remove the Auto option and position the point label myself manually. This didn’t really help. I then tried adding Side Margins to the chart in order to make it display properly. This is useful if you sometimes have only a few range values and the chart does not quite look right. For example , if you have a chart of sales per month and you only have a months worth of data in the database, you get one large ugly bar the width of the chart. Adding side margins will always add some space on either side and make it look much nicer. The upshot is that adding the side margins to my chart cured the problem of the point labels not rendering on certain bars !! Go figure that one out. To add them in do the following :

• Go to the chart properties window
• Select your Axis and select Side Margins

Now the thing to remember here is that if you want margins on the left and right you have to enable it on the Y-Axis, and for the Top and Bottom do it on the X-Axis. ( I would have thought it would be the other way around ). So the default charts in Reporting Services do have a lot of functionality – you just need to play with all the properties to see what’s really possible. Of course , we’re getting Dundas charts in RS 2008 , and that’s something to really look forward to…..:)

Landscape printing in Reporting Services

I always get asked about printing in Reporting Services. The 2 issues are always

 

1) Printing in Landscape in Reporting Services

2) After creating reports , when printed or exported to PDF you get blank pages.

 

Lets talk about the blank pages first. What I’ve found when authoring reports , is that when you are creating portrait reports ( reports that should either print on a normal A4 page , or be exported to a PDF file ) make sure that the report width is not more than 16 cm’s. Sometimes a report author has a table or matrix that is short enough , but still gets the extra blank page. Thats because the report width is greater than 16 cm’s. What you need to do is remove all the extra space at the right of the report. This is normally the cause of the "Extra Blank Pages" that you see when you print or export to PDF.

The above illustrates a report where the actual table is less than 16 cms but the report width is not. You will still get extra blank pages when you print this or export to PDF. Make sure therefore that there is no extra space after the table on the right ( if not needed ).

The other way to control the report width is on the Report Properties Tab.

This is a useful window, lots of properties to set here

Which brings me to the Landscape Printing option. To set your report to always print / export in Landscape mode, you need to go into the report properties box , select the Layout tab and inverse the Width and Height ie. The normal settings are 21cm for Page Width and 29.7cm for Page Height. Swop these around and you should get a Landscape Report.

The above image shows the same report exported to PDF after the change is made. Easy as pie…….

Deploying RS onto an internet facing environment

As you know , Reporting Services works very well within an intranet environment, in part to the integrated security. But what if you want to deploy RS onto a site that’s internet facing ?

 

You need to study your network architecture carefully to make sure that what you want to do is feasible. Most companies have only the web servers accesible from the outside and normally the Reporting Services server is behind the firewall and won’t be acessible to the end user directly.

 

So URL access to the reports won’t work. Now I know that many people, when writing ASP.NET applications. use URL access to call Reporting Services reports , and although this is quick and easy, get to learn more about the Reporting Services Web Service. It is a powerful ally !!

 

Your 2 options therefore are :

 

1) To use the Report Viewer control to display the reports ( and if you are using subreports there are some tricks you need to perform )

2) Use the Web Service to render the report to the bytestream. Once you do this you could simply display as HTML or PDF ( PDF is better for printing ). Make sure you set the MIME type in the latter case so that IE will know that it’s getting a PDF document.

More on Dates in Reporting Services – DateDiff

It seems like you always have to perform comparisons with dates to do conditional formatting in Reporting Services. Many of you will know the DateDiff function :

DateDiff("d",Fields!Date1.Value,Fields!Date2.Value)

You can also  :

• Use Parameters or static fields instead of fields from the dataset ( This applies to most functions )
• Change the "d" ( which means compare days ) to "M" or "yyyy"

If it doesn’t work , remember that the values you are comparing must be date values. Use "CDate" to convert strings to Date values.

And remember , when you want compare a date to todays date , getdate() won’t work, since this is VB Script not SQL !! Use the Now() command as follows :

DateDiff("d",Fields!Date1.Value,Now())

If the first date is smaller than the second date , the difference will be a positive number. You need to remember this since normally your expression for conditional formatting is normally something like :

=iif(DateDiff("d",Fields!CalendarDate.Value,Now())>30,"White","Black")

Working with Dates in Reporting Services

As with any other technology ( eg. SQL , C# ), you always find people running into problems with processing of dates. I was asked today by a colleague "How to I format dates when using the Date Picker and sending it through to a stored procedure" ? Let’s have a look ….

 

1) The FormatDateTime command

 

This is pretty easy to use, but maybe a bit limiting. You can specify 1 of 4 formats using the command arguments. Let’s say we have selected a date such as 10th April 2007 , our results will be as follows :

Command	Result
FormatDateTime(Parameters!Date.Value,1)	Tuesday, April 10, 2007
FormatDateTime(Parameters!Date.Value,2)	4/10/2007
FormatDateTime(Parameters!Date.Value,3)	12:00:00 AM
FormatDateTime(Parameters!Date.Value,4)	00:00

…but the better way to do it would be to use …

2) The Format command and specify the exact format you require. For example…

Command	Result
Format(Parameters!Date.Value,"dd-MM-yyyy")	10-04-2007
Format(Parameters!Date.Value,"dd/MM/yyyy")	10/04/2007
Format(Parameters!Date.Value,"MMM-dd-yyyy")	Apr-10-2007
Format(Parameters!Date.Value,"MMM-dd-yy")	Apr-10-07

So 3 M’s give you "Apr" ….anyway this is quite useful if you’re looking for Day/Month/Year , since the system will default to MM/DD/YYYY.

Using this you should be able to display the date format you want , or send through a particular format to a Stored Proc.

EDITED 22/08/2007 : If the Format Command doesn’t work , try converting the value to a date , eg.

Format(Cdate(Parameters!Date.Value),"dd-MM-yyyy")

Using Report Builder with Analysis Services

Report Builder is taking off , and I know of a few companies now deploying it. I think most people think of it as a Reporting tool for use with a  relational data source, but you can also create a Report Model off a SQL Analysis Services datasource. Here’s how …..

 

You can create the model using SQL Management Studio or Report Manager , but NOT Visual Studio. This means that the generated model cannot be refined further once created. A bit of a limitation at the moment , but I think it will change in the next release. Anyway , open up  Management Studio

 

1) Connect to Reporting Services

 

2) Navigate to your data sources. Right click on a data source that you have already that connects to Analysis Services , and select "Generate Model". I created one that connects to the Adventure Works cube on my local machine.

 

3) A dialog box will pop up as shown below. Set the path to create the model , typically in your "Models" folder, and give it a name.

That should do it. Now when you open up Report Builder , you’ll see the model available on the right hand pane.

Now here’s a few things to remember.

1) Since you generated the model automatically , you cannot edit the entity names etc. afterwards to provide friendly names. Thus you will have to design the cube with user friendly names for the model to be generated accordingly.

2) Your parent-child hierachies are not exposed. Your Dimensions are created as Entities , with Members created as Fields. The good thing is that you still can get a drill down report with the correct heirachy if you create a matrix report and drag the Members in the correct order on either the row group or column group. This is shown below.

3) Calculated Measures and KPI’s that do not have an associated Measure Group will not appear in the Model.

For the full list , go to the following URL :

http://msdn2.microsoft.com/en-us/library/aa337350.aspx

Well , I hope you guys find this useful. Just remember that Report Builder is NOT designed to be a slicing and dicing tool. But it still is great to give your users some functionality in terms of reporting off your OLAP sources.