Tag: SQL Server Reporting Services

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

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’!

Categories
Business Intelligence SQLServerPedia Syndication

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!

Categories
Uncategorized

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.

Categories
Business Intelligence

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?

Categories
Business Intelligence SQLServerPedia Syndication

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 🙂

Categories
Geeky/Programming

SQL Server 2005 Reporting Services – Fix Slow Loading On First Report Load

Setting up a new instance of SQL Server Reporting Services (SSRS), and getting things configured. What I noticed is that the first time I hit the SSRS site, it would take forever to load, then it would be fine as I used it, and then later after no use, I could come back and it would be slow again till it loaded once, then fast.

My first thing to think of was the App Pool settings in IIS, so I checked them..


The first setting on the performance tab of the app pool, “Shutdown worker processes after being idle for (time in minutes)” is checked by default, and set to 20 minutes. Sounds about right, my SSRS instance would get slow after some time of non-use (20+ minutes)

Uncheck that, and you are good to go. Of course if you have tons of usage, then there is no need for changing this, but on a site you might be building up slowly, you would see the effects of this right away.


Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SQL Server Reporting Services: Quick way to get 10 digit year (all the zero's) using String.Format

Dates are fun. See, by default most dates come out like 5/6/2008. But computers, and programs like them formatted as 05/06/2008. That way, all the dates, no matter what month or day, are all the same length, cool huh?

Well, in Reporting Services, if you have a date field coming back in a dataset, and you want to format it as a 10 digit string, there are about 50 different ways to do it. You can use old VBA Left and Mid etc, or you can use String.Format like..

=String.Format(“{0:MM}/{0:dd}/{0:yyyy}”,CDate(Fields!CalendarDate.Value))