Dynamic Sorting Using Parameters in SSRS

Steve Novoselac has done an outstanding job of explaining how to set up your SSRS reports so your users can dynamically sort their reports. This reference document was very easy to follow and within minutes I was able to achieve the results as described.

Steve Novoselac's Tech Blog (stevienova.com)

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…

View original post 319 more words

Sometimes you just gotta find a string…

“Search entire SQL Server database and all tables for a specified string

Having a neatly organized datalayer, perhaps a great ORM will make any developers life easier. But there are times when the best architecture simply isn’t good enough to answer a simply question like: “Are there any occurences of ‘a-specific-peace-of-text’ in the database?”. Sometimes you just need to look through all the tables and views to answer this.”

Check out Hanssens write up here on how to accomplish this…

Crystal Reports: “Continued from previous page.”

Have you found yourself needing to tell consumers of your Crystal Report that the group at the top of that page was a continuation from the group at the bottom of the previous page? You can do so very easily and here is how…

1. Create a new formula field called “Continued” or whatever you may choose to call it.
2. Paste in the code below into the Formula Workshop:

If InRepeatedGroupHeader Then "Continued from previous page."

3. Test the code (by clicking the X2 button) and click “Save and Close” to return to your report.
4. Drag the new field into the group header.

You can modify the text output found between the quotes to make it display the wording that is most appropriate for your application.

That’s all it takes. It is a simple solution that means the world to some report consumers out there. In my opinion, it shows a higher level of attention to detail. It’s like dotting the i’s and crossing the t’s. The little details do matter.

Good luck and stay safe!

Crystal Reports: Show All Headers on Drill Down

Have you found yourself drilling down into suppressed data in Crystal Reports only to realize there is nothing identifying the report or what data is in each column? You can fight with adding a second group header and suppressing it via conditional logic. That will work, and in some cases it will be necessary to meet your desired result, but there is a simpler way with a broader stroke.

Crystal Reports has a feature which can be enabled called “Show All Headers On Drill Down”. This feature will do exactly as it says. It will show all headers as you drill down into your data. For me, on my most recent report, this was enough for me to meet my needs.

To enable this feature, go to File > Report Options > enable check box for “Show All Headers On Drill Down”.  After you click “OK”, you should be able to see your header when you drill down into your data.

Today’s shout out goes to DBlank who posted on CrystalReportsBook.com here which guided me down this path.

Good luck and safe reporting!

SQL: Understanding Database Fragmentation

It is no secret that I am new to supporting, maintaining, and monitoring SQL databases.  I have been a sponge recently trying learn all that I can.  I figure that maintenance plans were the most basic yet essential place to start.  As many of you already know there is a tremendous amount of technical information out there, but I found I was spending a lot of time bouncing from page to page wishing it would break it down the very basics and spoon feed it to me.  I got a copy of “Brad’s Sure Guide to SQL Server Maintenance Plans” by Brad McGehee, from Amazon, as a place to start.  He also offers the book in a downloadable pdf on his blog.

This book is a very good place to start if you have technical skills but not specifically in SQL maintenance.  He really details every step to set up basic maintenance plans and send email reports of the results.  It is exactly where I needed to start.

Let me back up a little… I learned several weeks ago through the grapevine from some coworkers that an important database was showing as very fragmented.  A sweet tool by SolarWinds told us that, but I couldn’t find an entry-level, beginners method to handle the fragmentation.  Apparently I wasn’t Googling the right terms so I felt overwhelmed.  As any good and wise technical person knows, you don’t just run any script from the internet on your database. 🙂

Today I spent the day reading Brad’s book and figuring out how to address our fragmentation issue.  Through a variety of resources, I’ve learned to setup database mail, reorganize the indexes, update statistics, rebuild the indexes, identify the duration for the rebuild, then how to schedule it run weekly or even nightly.  (Note: I learned reorganizing and rebuilding but learned not to do them back to back as well as to run update statistics with reorganize but not rebuild.)  I write up all of this as a way to share some of the cool stuff that I used to see the issue, measure the issue, address the issue, and proactively manage it with a recurring solution.

Resource 1: Brad’s Sure Guide to SQL Server Maintenance Plans

Resource 2: Index Fragmentation Findings: Part 1, The Basics by Brent Ozar

Resource 3: Index Fragmentation Findings: Part 2, Size Matters by Brent Ozar

Resource 4: SQL query on the sys.dm_db_index_physical_stats object where I used information written by Arshad Ali’s Blog and also MSDN.


Select database_id
,object_id
,index_id
,partition_number
,index_type_desc
,alloc_unit_type_desc
,index_depth
,index_level
,avg_fragmentation_in_percent
,avg_fragment_size_in_pages
,page_count

From sys.dm_db_index_physical_stats (db_id(N'database_name'), NULL, NULL, NULL , 'DETAILED') IPS --replace database_name with the name of your database

Where page_count > 8
and avg_fragmentation_in_percent <> 0

This query took several minutes to run on my 42 GB database so don’t expect a rapid response.  I was able to take this out to Microsoft Excel to really analyze the data and sort by avg_fragmentation_in_percent field.  I was able to see substantial change in the statistics after performing a rebuild.  I learned that the page count matters.  If the page count is small then high fragmentation is necessary, and there isn’t really anything that you can do about it.  That is why in the code above I actually filtered out when the page_count was less than 8.  I also don’t want to wade through all of the additional rows of results where the avg_fragmentation_in_percent was equal to zero.   On Monday, I will incorporate all of this stuff into a maintenance plan on a recurring schedule.

I hope that this information makes sense and helps you in your journey.

Thank you.  Be safe.

 

Crystal Reports: Display File Name without Path

From time to time you will want to display the file name of a Crystal Report in the report so that you can find the report faster to work on it.  Crystal Reports has a built-in Special Field called File Path and Name.  It works and displays the file name but also the full UNC path of where the file is located.  The whole path is too much information to publish to anyone who may view the report and in some cases takes up too much real estate on the page.  Here is how you get around that.

The first thing to do is to create a new Formula Field and name it FileNameOnly or something descriptive to help you or one of your coworkers quickly identify the field and its purpose.  When the new field prompts you for the formula, paste in of the options below.

 

Option 1: Show only the file name and the file’s extension:

 

filename [ InstrRev ( filename , '\') + 1  to Length (FileName) ]

 

Option 2: Show the file name without the file’s extension:


filename [ InstrRev ( filename , '\') + 1  to Length (FileName) - 4 ]

 

You might find that you want to display more or less than that what is visible with the code snippets above, so be sure to play around with the values and massage it so the output is to your liking.

Take care and be safe.