Tag Archives: SSRS

ReportBuilder Icon

Dynamic Sorting Using Parameters in SSRS

The other day, someone requested that a report in SSRS be sorted differently by default. While that might make sense if everyone wants it that way, more than likely you might have people that want a report sorted differently by default. How to do it?

There are probably a few ways, but this is what I did.

First, I added two parameters. “SortByDefault” and “SortOrder”

The “SortByDefault” will be a drop down of your columns you want to sort by for your dataset (or group, or table/tablix)

The “SortOrder” is simply Asc (1 to N, A to Z) and Desc (N to 1, Z to A)

Now, here is how mine look:

SortByDefault (I have two columns I want to allow sorting by, PointsLeft and StackRank):

SortOrder:

Now comes the fun stuff: Making it work.

Make sure you remove any “ORDER BY” in your dataset (you don’t have to but this makes it easier).

I also have every column in the report set up for interactive sorting based on the column header/column it shows, but not sure that is necessary here, I just wanted to put that out there just in case

You want to get to your sorting options. So in my case, I have a tablix, so get to your tablix property window and the sorting option:

Now you can see, my “Sort By” and “Then By” are expressions. It is kind of weird here. Also you can’t set expressions for “Asc” or “Desc” so what I had to do was trick it somewhat.

the first is to handle the asc option:

=IIF(Parameters!SortOrder.Value=”Asc”,Fields(Parameters!SortByDefault.Value).Value,0)

the second is to handle the desc option

=IIF(Parameters!SortOrder.Value=”Desc”,Fields(Parameters!SortByDefault.Value).Value,0)

You can see, some magic. If the order by is XYZ then use the field, otherwise 0. If you notice from the screenshot, first one is A to Z (Asc) and the second one is Z to A (Desc). So we are basically telling SSRS to sort by the param or not based on the order by option and it chooses the right order by (ASC/DESC). I think this was easier in SQL 2000 SSRS :)

Well now you should be able to test your report and try to sort orders. What I did next is make my params hidden. The defaults are what I wanted for the existing report (Order By PointsLeft DESC), and what I did next is create a linked report and set the hidden parameters int he report options in Report Manager to (Order By Stack Rack, ASC)

Now I have one report, with hidden sorting params and I can create linked reports with different sort options without having to create a new report. I could add all columns to the choices, or even let users choose as parameters (but they already have interactive sorting in this case).

Happy Report Buildin’!



ReportBuilder Icon

SSRS Report – No Page Breaks For You!

One thing I usually run into when creating SQL Server Reporting Services (SSRS) Reports is this: You have a smallish size dataset back, maybe somewhere between 50 and 150 rows, but if it hits that row limit on the page break, you get 3 records on the next page. Annoying. What I usually do for reports like this is just make them all one big page. How? Pretty simple actually. Set the “Interactive Height” to 0 on the report.

Open up Report Builder (or BIDS) and get to your “Report” Properties, and then under “Interactive Size” set the height property to zero. Thats it.

Once you make this minor change, you report will just list your row with no page breaks. Pretty useful for that report with 3 more rows than a page!



MADPASS Monthly Meeting April 27th (SSRS!)

The third meeting for the Madison Area SQL Server Users Group (MADPASS) is tonight. The talk is about SQL Server Clustering for High Availability.

Meetings are always FREE and food is provided.  Details about the next meeting can be found below.
In an ongoing attempt to make the content presented useful to our members we start each meeting with a short Q&A session.  Unless noted in the meeting notes the schedule for each meeting will be:
5:30 – 5:45 – General Q&A session & Announcements
5:45 – 6:15 – Short Presentation
6:15 – 6:45 – Food & Networking
6:45 – 7:45 – Main Presentation
7:45 – 8:00 – Door Prizes & Q&A Session

 

Reporting Services 201: The Next Level

You have mastered the art of linking a dataset to a table in SQL Server Reporting Services. You have solid, reliable reports that you and others depend on. Learn how to take them from "meh" to "wow", using features such as tablix, lists, images, and charts.

Date:

Wednesday, April 27th, 2011  5:30 PM

Speaker:

Jes Schultz BorlandJes Schultz Borland is a Database Administrator at Kimberly Clark. She holds an AAS – Programmer/Analyst degree. She has worked with SQL Server since 2007, focusing on Reporting Services and day-to-day administration. She is an active member of PASS, the PASS WIT virtual chapter, and MADPASS. She is also an avid tweeter, blogger and runner.

Location:

2310 Crossroads Drive
Madison, WI 53718

Park behind the building and walk in main entrance.
Our room will be straight ahead on the first floor.

Live Meeting:
Click here to enter the meeting.

Add Meeting to Calendar:

Click here to add the meeting to your calendar.

We will also be talking about upcoming elections. Cool prizes. Beer. Pizza, and more.



Agile: Creating an SSRS Burndown Chart Part 3

In the previous 2 parts (see Part 1 and Part 2) of this series I showed you how to get your data ready, and how to get your report started and your Datasets and parameters where you need them. In this part, we will get the graph functional, and in the next part, we will make it pretty.

Start by adding title to your report “Agile Burndown”, then add a Line Chart to your report. Make it somewhat big, delete the Chart Title and Axis Titles,  and remove the “Details” from the Category Groups. You should have something that looks like this:

 

image_thumb15

Now to get the data on and finish it off!

Drag your values over to your Chart Data Values area like this:

image_thumb[17]

One thing we need to tweak, and this is on the PointsLeft Value. Right click on the PointsLeft series and go to “Series Properties”. To the right of the Value field, click the Fx button (for Expression Functions).

We need to change this series to not write out anything to the graph if there are no points greater than today. Why? If you don’t do this, your graph line for PointsLeft will drop off to zero for dates in your sprint after the current day, and we don’t want this. This is what the expression should be:

 

=IIF(Sum(Fields!PointsLeft.Value)=0 And Fields!Date.Value > DateTime.Now,Nothing,Sum(Fields!PointsLeft.Value))

 

Pretty cool, your graph should actually work now and function as a working burndown chart. But of course we need to pretty it up! Look for the next and final post soon.



Agile: Creating an SSRS Burndown Chart Part 2

In the previous post in this series, Agile: Creating an SSRS Burndown Chart Part 1, I explained what data you would need to prepare to create an SSRS Burndown Chart (Sprint_Dates, Stories, Story_History). In this part of the series I will explain how to get a basic burndown report in SSRS.

First, fire up Report Builder 3.0 and create a new report (if the wizard pops up, just pick “Blank Report”). You need to add a Data Source to your report. In my example, I am just using a database on my localhost called Agile, so I connect to that and create a report Data Source.

image

 

We then need to add 3 Datasets to the report. (Burndown, Sprints, and CurrentSprint), and one parameter (Sprint) and we can then format our report.

 

Sprints (this will be a dropdown of Sprints for a user to choose from)

image

CurrentSprint (this will get the current sprint based on what day we view the report, default param for the Sprint parameter we will create)

image

 

For the Burndown, do the same thing, but since the query is so large, no screenshot, just the query:

;WITH DayHistory AS 
(
SELECT 
	 bd.[DATE]
	,bd.PointsScheduled
	,bd.PointsLeft
	,bd.PointsScheduled - ((ROW_NUMBER() OVER (ORDER BY bd.[DATE]) - 1) * (CAST(bd.PointsScheduled AS DECIMAL(15,6))/10.0)) AS 'Goal'
	,ROW_NUMBER() OVER (ORDER BY bd.[DATE]) AS [DayNumber]
FROM (
	SELECT tot.Sprint,tot.LogDate AS [DATE], 
		CASE WHEN SUM(tot.PointsScheduled) = 0 THEN (SELECT SUM(Points)
		FROM dbo.Stories st 
		WHERE Sprint = 'Sprint01') ELSE SUM(tot.PointsScheduled) END AS 'PointsScheduled', 
		SUM(tot.PointsLeft) AS 'PointsLeft'
	FROM (		
			-- Get History for the Current Sprint
			SELECT Sprint,LogDate,SUM(Points) AS 'PointsScheduled', SUM(PointsLeft) AS 'PointsLeft'
			 FROM 
				 dbo.Story_History st 
				WHERE Sprint = @Sprint
			GROUP BY Sprint,LogDate
			UNION
			-- Get the Current Day		
			SELECT	Sprint AS 'Sprint',CAST(GETDATE() AS DATE) AS 'LogDate',
				SUM(Points) AS 'PointsScheduled',
				SUM(PointsLeft) AS 'PointsLeft'
				FROM dbo.Stories
				WHERE Sprint = @Sprint
			GROUP BY Sprint
			UNION
			-- Get zero's for all days in sprint to round out our dataset
			SELECT 'Sprint01' AS 'Sprint',WorkDate,0,0 
			FROM dbo.Sprint_Dates
			WHERE Sprint = @Sprint
		) tot
	GROUP BY tot.Sprint,tot.LogDate			
) bd
)
SELECT
	 a.[DATE]
	,ISNULL(b.PointsScheduled, a.PointsScheduled) AS [PointsScheduled]
	,ISNULL(b.PointsScheduled, a.[PointsLeft]) AS [PointsLeft]
	,ISNULL(b.PointsScheduled, a.[Goal]) AS [Goal]
FROM DayHistory a
	LEFT OUTER JOIN DayHistory b
		ON a.DayNumber = b.DayNumber - 1
			AND b.DayNumber = 2
ORDER BY DATE

 

This query is where all the magic happens. First, you need to get your story point values for the days, from your history, and also from the current day, you also need to get all days for that sprint with zero’s so that your graph will have all days and not just days with burndown. The CTE around the main query calculates the burndown by day so you end up with 4 columns, Date, PointsScheduled, PointsLeft, Goal

Now that you have your Datasets, we need to create a parameter, and then the graph!

Create a new parameter called “Sprint”, and set up the available values. Remember the Dataset we created to get all the sprints? Here is where you use it, like this:

image

Next, we want to setup the default values. Remember the query to get the “Current Sprint” – that is used to set our default.

image

Once you have that all setup, it is time to build the graph!

We are really close to having a working report here, and check back for part 3 of the series to get the graph working correctly, and part 4 for beautification!



Agile: Creating an SSRS Burndown Chart Part 1

The burndown chart. A must have for any ScrumMaster and Agile team. What it should show you is the rate at which you are “burning” down story points.

image

As you can see from the chart above, 3 lines. Red is your “points scheduled”, Green is the “goal” and blue is “points left”. While it is easy enough to create this chart and track the burndown manually in Excel, many teams after using Excel turn towards other systems to track their points and sprints. Right now I have one team using Unfuddle, one team using TFS, there are others that use this chart that use Footprints and really you can use whatever, and this chart can be built off of any database as long as it has the right data.

First, you need a table with your stories in it. You need to have some key columns – Sprint, Points and PointsLeft.

CREATE TABLE [dbo].[Stories](
	[Sprint] [VARCHAR](50) NULL,
	[Points] [INT] NULL,
	[PointsLeft] [INT] NULL,
	[StoryId] [INT] NOT NULL,
	[StoryText] [VARCHAR](MAX) NULL
) ON [PRIMARY]

Now you may have others, like StoryId, StoryText, Assignee, etc but we aren’t concerned about those for this chart.

You then need at least 2 or tables, and a SQL job. 1 table to hold your Sprint and Dates and one to hold your “Story History”

 

CREATE TABLE [dbo].[Sprint_Dates](
	[Sprint] [VARCHAR](50) NOT NULL,
	[WorkDate] [DATE] NOT NULL
) ON [PRIMARY]
 
CREATE TABLE [dbo].[Story_History](
	[LogDate] [DATE] NOT NULL,
	[Sprint] [VARCHAR](50) NOT NULL,
	[Points] [INT] NULL,
	[PointsLeft] [INT] NULL
) ON [PRIMARY]

 

You will need a SQL Agent Job to run at 11:55 PM to capture the history, which should run this query:

 

INSERT INTO dbo.Story_History (LogDate,Sprint,Points,PointsLeft)
SELECT CAST(GETDATE() AS DATE),Sprint,SUM(Points),SUM(PointsLeft)
FROM dbo.Stories
GROUP BY Sprint

 

Remember you might not need all 3 tables, just the history and dates. You can get your actual stories off of wherever your stories are stored in the database. Now that you have your data in place, you can get ready to write the actual report! Look for the next part in this series.



Looking to Hire…

I have been managing two different groups @ Trek -  (Business Intelligence and .NET Software Development) for a while now, and we have some openings we are trying to file, so that is why I am putting this out here.

First role, looking to hire a Microsoft .NET Windows Forms developer, or someone with web experience looking to get into Windows Forms and eventually WPF/Silverlight, and also Windows Services. C# is the language.

Second role, looking for a Microsoft Business Intelligence Developer/DBA – SSAS/SSIS/SSRS, DBA experience preferred. Working on cubes, and ETL’s and reports and DBA stuff.

Shoot me an email at steve_novoselac@trekbikes.com with your resume and info.



SSRS Report Creation Checklist

You can whip out reports in SSRS (SQL Server Reporting Services) very quickly with the report builders (2.0 and 3.0 are money).

But what should you remember to do each time, or information to get?

  1. Where does the data come from (GL, Sales, etc) – we could use a cube or datawarehouse, or staging, or Other system, etc.
  2. Report Name (on Report Manager)
  3. Short description of the purpose – to appear under report name on report manager
  4. Should it go into an existing folder, or a new folder, or the user’s folder?
  5. Who should have access?
  6. How should it be consumed? Email (and what, excel, pdf, web archive, etc), To a file share, User Ran?
  7. What parameters should be available? a. What are the defaults?
  8. Can we get a rough mockup of how it should look? a. Can we get an existing report (if avail, crystal, or excel, etc)
  9. Does it need to print on a 8×11 page?
  10. How often are you going to run it? (Hourly,Daily, Weekly, Monthly, ad-hoc)
  11. Is it going to be informational, or used to export data and manipulate? (if export – is there something else we can do?)
  12. If tabular data, does it need to be sortable?
  13. Do snapshots need to be taken? a. How many do we save? b. How often to take them? c. Do you need ability to delete them?

you get the idea, the list could go on and on. So creating that report is simple, but actually getting and doing all the things necessary to get it done “correctly” is more time consuming.

What other things can you think of for the list?



Microsoft Live Labs Pivot Viewer – Rich Internet Application

So, I previously blogged about using PivotViewer in your Web Applications, but you can also just consume Pivot collections using the “Pivot” tool from Microsoft Live Labs

You can download it here

What does this tool offer? Well first it has a library/homepage of collections you can browse

You can do some slicing and dicing on a collection of Presidents, or athletes, or Sports Illustrated covers. This tool and technology really fasinates me. It is “Business Intelligence” but in a different way – it is based on “objects” (images) instead of “metrics”. I like it.

What are some cool things I think this could be used for? Company Directory? Online Catalog? Beer selection at Eddie’s? the list goes on and on..

Additionally there is now an add in for SQL Server Reporting Services and SharePoint 2010 you can download here

Once I have an environment in which I can test that set up, I will blog about it.



SSRS DataSet Filter – “Like” Operand

Figured out today that in SSRS, if you add a filter to your dataset, and you want to use “Like”, that the operand for everything is * instead of % like you might be used to in SQL, sometimes it is just the little things :)