Wednesday, February 24, 2010

Upcoming SQL Lunch Webinars

If you haven’t attended a SQLLunch, I suggest that you put this upcoming lunch on your schedule. Brad McGehee, SQL Server MVP, will be presenting an Introduction to Graphical Execution Plans. Brad has over 15 years of SQL Server Experience and over 6 years of training.

clip_image001

Add To Outlook: Add To Calendar

Date and Time: 2/25/2010 11:30:00 AM CST

Topic: Introduction to Graphical Execution Plans

Topic: #11-Introduction to Graphical Execution Plans
One of the black arts of being a DBA is learning how to read and interpret graphical execution plans. This is an important skill, as knowing how to read and interpret graphical execution plans can help the DBA tune queries for optimal performance. Without this crucial knowledge, the DBA won’t be able identify potential query problems, nor understand how to fix them. In this short, introductory level session, which is designed for DBAs with little or no knowledge of query execution plans, you will learn why execution plans are important, how they are created, learn about the different execution plan formats, discover different ways to produce them, find out about the difference between estimated and actual execution plans, and learn the basics of how to read them. Because this is an introductory session, after attending, I highly recommend you download this free eBook to learn more about execution plans: “SQL Server Execution Plans” by Grant Fritchey. http://www.sqlservercentral.com/articles/books/65831/ This session assumes attendees have a basic foundation of DBA knowledge. After attending the session, attendees will be able to immediately put what they have learned into practice.

Upcoming Lunch

clip_image003

Add To Outlook: Add To Calendar

Date and Time: 3/1/2010 11:30:00 AM CST

Topic: Zero to Cube in 30 Minutes

BIO: Adam Jorgensen, MBA, MCDBA, MCITP: BI has over a decade of experience leading organizations around the world in developing and implementing enterprise solutions. His passion is finding new and innovative avenues for clients and the community to embrace business intelligence and lower barriers to implementation. Adam is also very involved in the community as a featured author on SQLServerCentral, SQLShare, as well as a regular contributor to the SQLPASS Virtual User Groups for Business Intelligence and other organizations. He regularly speaks at industry group events, major conferences, Code Camps, and SQLSaturday events on strategic and technical topics.


Topic: #14-Zero to Cube in 30 Minutes
Don’t think you can learn to build a cube in Analysis Services on your lunch break? Come join BI Guru Adam Jorgensen     as he takes you through the ins and outs of building a simple cube in under 30 minutes! Bring your questions for Q&A afterwards.

Talk to you soon,

Patrick LeBlanc, founder www.SQLLunch.com and www.TSQLScripts.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.

Monday, February 22, 2010

SSRS 2008 – Adding a Totals Row

During a recent client engagement I was asked if there was a quick way to add a row containing totals for each corresponding column in the details row from a Tablix item. The tablix contained about 18 columns, and the person was entering an expression that would sum the value of the field in the corresponding detail rows. Fortunately, SSRS 2008 provides a very simple method that will perform this task.

Assuming that you have a report that contains a Tablix item you can perform the following steps to quickly add a totals row to your report.

1. In the Row Groups Panels, right-click the item labeled Details:

clip_image002

2. Choose Before or After, this will depend on where you want to the totals to appear in the report.

3. Once you make the selection a new row will be added to your report. It will contain an expression for each column in the details that sums the value of each column.

clip_image003clip_image005

It’s just that simple. As always if you have any questions or concerns regarding this post, please feel free to email me pleblanc@pragmaticworks.com

Talk to you soon,

Patrick LeBlanc, founder www.SQLLunch.com and www.TSQLScripts.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.

Friday, February 19, 2010

Reporting Services Switch Function

Reporting Services Switch Function

Recently a client requested to create a Reporting Services expression that emulated the CASE WHEN statement from T-SQL. The expression would use the selected parameter value to dynamically set the Go to report value of an Action under the properties of a textbox. In other words, if a user chose a parameter value of Report1 the go to report value would be set to Report1, if the user chose Report2 value would be set to Report2, so on and so forth.

I looked at several varying expression methods to solve this problem including the IIF method, but I eventually settled for the SWITCH function because it offered the most flexibility. One small limitation was that the value was only updated when the report was refreshed. If a user changed the value of the parameter, he or she would be required to click the view report button to refresh the value. So how do you use the switch function? To specify the value for this example drop a text box on your report, right-click it and choose Text Box Properties. The following dialogue box will appear:

image

Choose Action from the list of available choices in the right pane. Ensure that the Go to report radio button is selected. Then click the expressions button next to the Specify a report drop down list. The expression dialogue window will appear.

image

In the Set expression for ReportName textbox you would type an expression similar to the following:

=SWITCH

(

Parameters!WhichReport.Value="Report2", "Report2",

Parameters!WhichReport.Value="Report3", "Report3",

Parameters!WhichReport.Value="Report4", "Report4"

)

The SWITCH function should be used when you have three or more conditions to test. When evaluated to true the function returns the first value in the series of available conditions.

I welcome any comments, criticisms or suggestions regarding this post. How would you have solved this problem? I have attached a small report project that simulates my approach. As always if you have any questions regarding this post please feel free to email me at pleblanc@pragmaticworks.com.

Talk to you soon,

Patrick LeBlanc, founder www.SQLLunch.com and www.TSQLScripts.com.

Visit www.BIDN.com, Bring Business Intelligence to your company.