Sorting a Chart in SSRS

Posted on March 20, 2015 by jeffprom

Sorting a chart in SSRS sounds easy enough, but the sort option isn’t where you might expect to find it. In this example I am using Visual Studio 2012.

First let’s take a look at an unsorted chart. Even if you sort values in your underlying dataset, your chart will likely not be sorted.

One might expect to find sort options by right clicking the chart and going to Chart Properties which will not work. You then might think, of course it must be on the Series Properties. However, that again will not work. So let’s take a look at how to sort the chart. Start by double clicking the chart which will bring up the Chart Data box. Now you will probably expect to find sort options by looking at Value options.. and you would be wrong yet again. Click on the drop down arrow under the Category Groups. Select Category Group Properties.

Here you will find a Sorting option. Click Add and select what you would like to sort on.

Now you can see that we finally have a sorted chart.

SSRS Chart Does Not Show All Labels

Posted on March 2, 2015 by jeffprom

When creating a new chart in SSRS, you may find that not all of the labels are being displayed on either the vertical or horizontal axis. This will happen when the chart tries to determine how many labels it can show using the default setting. Sometimes this isn’t the best solution. For example, as you can see in my chart below there should be plenty of room to show all of the labels but it decides not to.

In this case we definitely want to show all of the labels. To do this we will need to adjust some settings. Double click on the label names on the chart.

On the right hand side of Visual Studio, you should now see the Chart Axis values on the Properties area.

Under the Interval attribute, change the value from ‘Auto’ to 1. This will specify that we want to show labels on every occurrence.

Re-run the report and now you can see that all of the labels are shown.

Bienvenidos al SQL Saturday #189 – Costa Rica

Posted on February 20, 2013 by jeffprom

SQL Saturday #189

I’m excited to announce that I will be speaking at SQL Saturday #189 in beautiful Costa Rica! This event will be held on April 13th, 2013 at the Aurola Hotel Holiday Inn San Jose Downtown.
I will be presenting on the following two topics:

Master Data Services
Does your company have similar data in multiple locations? Not really sure which set of data is accurate? With MDS, Microsoft gives you the ability to have one accurate source of non-transactional data to use with your applications or send to other databases and ETL processes. Leverage data stewards to help manage the data repository to ensure it is up-to-date and accurate. In this session we will cover MDS from start to finish including installation, creating models/entities, working with data, using the Excel add-in, security, hierarchies, views, versions, and business rules. After this session, you should have a good understanding of what MDS does, how to use it, and how to get started with your own MDS project.

Data Quality Services
As the old adage goes, ‘Garbage in, garbage out.’ Stop spending hours manually cleansing your data, and start using DQS! DQS is the new data quality tool provided with SQL Server 2012. By leveraging knowledge bases, you can correct invalid data and improve the quality of your data.

In this session we will cover creating a knowledge base, data cleansing, data matching, and using 3rd party knowledge bases (Reference Data). We will talk about installation, show how to use the DQS tool, and how to integrate DQS into your SSIS packages. After this session you should have a good understanding of what DQS is, how to use it, and how to get started with your own Data Quality initiative.
………………………………….
Last November I was fortunate enough to meet several chapter members at the PASS Summit event in Seattle. I look forward to the opportunity to meet again in San Jose this April.

Check out the SQL Saturday #189 for more information.

Code Mastery (Minneapolis Oct 2nd, 2012)

Posted on September 17, 2012 by jeffprom

Code Mastery Logo

I will be speaking at this years Minneapolis Code Mastery event on October 2nd. I will be presenting on how to use SSRS Reports with SSAS Cubes. This event offers FREE, high-quality technical content on business intelligence and Window 8 development.

Code Mastery Minneapolis is hosted by Magenic with the goal to present attendees with meaningful technical content by the professionals that are using the subject matter every day.

The featured speaker for the Mineapolis event is Magenic CTO and CSLA .NET creator Rockford Lhotka. Along with a great agenda, we’ll make time for you to network and make some great connections. Come spend a day a the Microsoft Technology Center in Edina, MN with some of Magenic’s best and brightest.

Pass this on to your friends and colleagues that may be interested in the event. And feel free to send a Linkedin invite if you’d like to connect with me directly.

WEBSITE LINK: http://codemastery.com/minneapolis/

Hope to see you on October 2nd!

Use JavaScript to open a second SSRS report window

Posted on August 31, 2012 by jeffprom

Within SSRS reports, you may sometimes want to show more details for a given record but don’t necessarily want to add it to the existing report or go to another report. Here is a neat solution. You can add JavaScript to your report and have a second report window pop up with the details! Let’s take a look at how we can do this. First you will need to create two reports; your main report and the second report that will show up on the pop up window with details.

On the first report you will need to decide where you want your user to click to pull up the second report. In our example here, we have a reservation list. We will be clicking on the Customer Name to get more details about that customer.

Report designer

Next, we will go to the text box properties and then the Action tab. Select ‘Go to URL’ and then click on the expression button.

Text box action tab

Here is where the magic happens. Using SSRS URL Access, we will send commands to the report server and pull up the second report.

Javascript to open a second window

Here is the code we will use for our reports.
="javascript:void window.open(" &"'"& Globals!ReportServerUrl &"/Pages/ReportViewer.aspx?" &Globals!ReportFolder &"/Customer+Details&rc:Parameters=False &CustomerID="& Fields!CustomerID.Value &" ','CustomerDetailsWindow','left=370,top=180,width=940,height=300,status=no,menubar=no,scrollbars=no,location=yes'" & ")"

The first line uses a global variable to populate the report server URL and report viewer. The second line puts in a report folder value, and specifies a report name. Our pop up report is called ‘Customer Details.rdl’, but as you can see here we just specify the report name without the .rdl at the end. We will also pass in a parameter option of ‘Parameters=False’ on the second line. This will turn off the parameter drop down box on the controls. We don’t want our report to give the option of selecting a different customer. If you need to pass more than one parameter, simply copy the third line and paste the copy below line three and modify with the new parameter values. In our example, the third line specifies the parameter name (CustomerID) on the second report, and the value from the main report. For this report, we will be passing in (Fields!CustomerID.Value). Finally, on the last line we specify some additional properties that we want the pop up window to have such as the width and height.

We only use one URL parameter value (Parameters=False) in our example. However, there are quite a few more that can help customize your secondary report window. To find more information about parameter values, follow this link:

If you are having a hard time getting your report to work, I suggest first going to your report server URL (http://YourServer/reportserver), browse to your report and try setting your SSRS parameter strings to make sure you have the correct syntax. You should then be able to pull it apart and insert it into your JavaScript expression.

Here is a screenshot of our final solution. When we click on the Customer Name, we get a second report with additional customer information.

Two report windows open

More information on using SSRS URL Access can be found here:
http://msdn.microsoft.com/en-us/library/ms153586

SSRS Page Break After X Number of Records

Posted on May 11, 2012 by jeffprom

In SQL Server Reporting Services you can add a page break after a desired amount of records. Here is how you do it.

1. Add a new Row Group and give it a name.
In our example we named our group:
LimitNumberOfRowsPerPageGroup

SSRS Row Group

2. Go to the properties of the Row Group.

SSRS Row Group Properties

3. Use the following expression to group the row on:
=Floor((RowNumber(Nothing) – 1) / 250)
In this example, 250 is the number of records we want per page.

SSRS Row Group Expression

4. On the Page Breaks tab, check the following:
‘Between each instance of a group’
‘Also at the end of a group’

Page Breaks Tab

Go ahead and run the report.
You should now have 250 records per page.

SSRS Page 1 of 2?

Posted on June 13, 2011 by jeffprom

Starting in reporting services 2008 there is a new feature called On Demand processing.
http://msdn.microsoft.com/en-us/library/bb630400(v=SQL.100).aspx#OndemandProcessing

With this feature reports may be rendered only one page at a time to speed up processing. One drawback to this can be seen on large reports. On your action bar, you may see page 1 of 2?
Here is an example:

One trick to avoid this is to add a textbox in your report header or footer. On the properties of the textbox, set Hidden=True. For the expressoin of the textbox, set it to =Globals!TotalPages.
Now when the report runs, it figures out the total number of pages to put into this textbox. And as a result, it also puts the correct number in your action bar!

Using Images as links in SSRS

Posted on February 10, 2011 by jeffprom

In SSRS you can create drill-through links to other reports from textboxes. But if you really want to jazz things up you could instead add an Image and make it clickable. One catch is that you cannot associate an Image to a dataset to pass down values. How do we overcome this you ask?

There are two methods we can use:
1) If you used parameters in your report, you can easily add these as hyperlink parameters.

2) The trickier one is non-parameter values. To pass down a value from a dataset you need to have the
value available somewhere on your report. If your value may be different for each row, you will need to display it somewhere on your reports’ result set. Then on the Image, Action tab add another parameter and give it a name.

Image Properties and Parameters

For the expression value you would enter =ReportItems!Textbox126.value (or whatever the field name may be on your table)

Image Properties and Parameters

Dynamic MDX

Posted on February 10, 2011 by jeffprom

I recently wrote some dynamic MDX queries for my SSRS reports. I didn’t have much trouble until I ran into a StrToMember function that needed a double quote at the beginning and end. Hmm.. how to overcome that. Turns out you can use some VB .Net characters.

Here is a list of some common ones.
chr(39) = single quote
chr(34) = double quote
chr(10) = new line
chr(13) = carriage return

so for my example I had the following:
STRTOMEMBER("[Time Periods].[Fiscal Month Desc].&["+@FromMonth+", "+@FromYear+"]")

After I did some converting, I came up with this:
STRTOMEMBER(" & chr(39) & "[Time Periods].[Fiscal Month Desc].&[" & Parameters!FromMonth.Value & ", " & Parameters!FromYear.Value & "]" & chr(39) & ")

SSRS Divide by Zero

Posted on May 10, 2010 by jeffprom

In SSRS you often have calculated fields that do division. To avoid having divide by zero errors, you may need to use IIF statements in the expression:
=IIF(Fields!column2.Value = 0, 0, Fields!column1.Value/Fields!column2.Value)
which means if the denominator is 0, show 0, otherwise show the calculated value.

Now for reasons unknown this only works if you are using integers. If you are using decimal values, then you will need to do some more work. The best approach I’ve seen is here, where Robert Bruckner provides a nice solution.

You would go to Report -> Report Properties -> Code and insert the following:
Public Function Divide(ByVal first As Double, ByVal second As Double) As Double If second = 0 Then Return 0 Else Return first / second End If End Function

Now you can do the following in your expression:
=IIF(Fields!column2.Value = 0, 0, Code.Divide(Fields!column1.Value, Fields!column2.Value))

SoCalSQL – Jeff Prom's SQL Server Blog

Sharing knowledge and tips on SQL Server, Azure, Business Intelligence, and Data Management.

Category Archives: SSRS