//
you're reading...
Database, IT-Center, SQL Server

SQL Server 2005 : Drilldown Reporting Service

The solution to these challenges is to understand some subtle techniques when using the Reporting Services report designer. This discussion will demonstration three specific techniques. All of the materials needed for this demonstration are included so you can try this yourself. The techniques are the following:

  • Technique #1 – The Standard “Drilldown”
  • Technique #2 – The Reverse “Drilldown”
  • Technique #3 – Conditional Initial Visibility

Follow along and you’ll be a drill down Pro in no time at all.

Sample Data

The following examples use a fictitious table about the availability of fruit from different suppliers. If you want to follow along with your own system, create a database called zVisibilityLab. Once created use this script to create the table.

Technique #1 – The Standard “Drilldown”

Setup a new report to use the VISIBILITY_EXAMPLE_TABLE as a datasource. Use the following query:

SELECT ProductCode, ProductName, ProductColor, QuantityAvailable, SupplierName, SupplierRegion
FROM VISIBILITY_EXAMPLE_TABLE

Setup a standard table. When you are done it should look something like this:

The preview will look like:

Next add a grouping for Region.

Select the Supplier Region field from the dropdown.

I have also merged all cells in the group header, deleted the group footer and added border styles of “solid” for everything. When you are done it may look something like this:

Now for the Drilldown

Check the properties of the group header textbox. Note the “Name” property. It should be something like “SupplierRegion_1” (or something close to that).

Now we have to get to the Details Grouping properties. (There’s something about that that doesn’t make sense, but go with me here.) There are two ways (that I know of) to get there. 1) Right click on the details line and select Edit Group (even though it’s a details line), or 2) select the entire table, right-click, select Properties, then the Groups tab, then Details Grouping. Both these methods bring you to the same place.

Then click on the Visibility tab. Click Hidden, check the Visibility can be toggled… checkbox and select the SupplierRegion_1 textbox.

When you are done your preview should look like this:

Expand some of the times:

____________________________________________________________

Technique #2 – The Reverse “Drilldown”

But let’s say this is a small table and you want display the records expanded to open by default. There are two additional things to add to the above technique to make this work.

1) On your Visibility tab (of the Details Grouping), change the Initial Visibility to Visible.

After this, try out a preview. You will notice that the +/- signs do not work the way you would expect or want.

2) To reverse this, right click on the SupplierRegion_1 (group header, not the group details anymore) textbox properties. Select the Visibility tab, then change the Initial appearance of the toggle… to Expanded (-).

This will correct the +/- sign and you are all set to provide all details by default with the ability to collapse groupings for readability.


Technique #3 –  Conditional Initial Visibility

Now let’s say you want to open some groups but not others based on a certain condition. (How about by the number of records?). For the purposes of demonstration, I have added the following expression to the last cell in the group heading line.

=CountRows("table1_Group1")

This will display the number of rows for each group.

For the sake of this discussion, let’s say that we want to display the records of a group only if the number is 4 or less.

In the Visibility tab of the Group Details section, under Initial Visibility instead of  selecting “Visible” or “Hidden”,  select  Expression and enter the following in the Expression field (or use the Expression Editor button).

=IIF(CountRows("table1_Group1")<=4,False,True)

*NOTE: Important – Make sure you use the “False” and “True” keywords. In many other cases, the IIF statement uses the keywords from the normal options (i.e. ‘solid’ &  ‘none’  for BorderStyle). In this case, you cannot use “hidden” & “visible”.

Once done, the report operates fine, but we have to enter a similar (but opposite) expression to fix the +/- signs again. To fix this, right click on the SupplierRegion_1 (group header, not the group details anymore) textbox properties. Select the Visibility tab, then change the Initial appearance of the toggle… to Expression and enter the following:

=IIF(CountRows("table1_Group1")<=4,True,False)


You see, the table conditionally expands where row counts are <=4. You have to click the “+” sign to expand groups of 5 or more.

_____________________________________________________________
See Also : http://www.mono-software.com/blog/post/Mono/139/Drill-down-in-SQL-Server-Reporting-Services-2008/

http://www.mssqltips.com/sqlservertip/1329/drill-down-techniques-in-ssrs-with-the-visibility-property/

 

 

 

 

About berbagisolusi

Berbagi merupakan sebuah bentuk simbol keikhlasan untuk membantu dan menolong, sedangkan solusi adalah cara menyelesaikan masalah. Setiap manusia pasti mengalami masalah, tetapi kita tidak perlu mengalami masalah yang sama jika orang lain pernah mengalami dan kita tahu hal tersebut.

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 9 other followers

May 2012
M T W T F S S
« Apr   Jun »
 123456
78910111213
14151617181920
21222324252627
28293031  

Archives

Web Statistic

Blog Stats

  • 149,580 hits
%d bloggers like this: