SSRS - Implementing Drill Through action in the same RDL

Scenario:

           User wants a SSRS Chart report which will display the Product Category wise Sales in the initial view. On clicking of the bar chart, the report should display the Product wise Sales of that selected Category.

          We can enable this drill through action in a single RDL as given below.

Note :  I haven't given the SSRS chart creation steps detaisl by considering that the reader knows how to create SSRS Chart reports. 

To implement the above said requirement, we can do some tweaks in the SQL query if the report data set and achieve the same.

The given below sample query can be used in the data set, which will return the Category wise sales if the in parameter (@ProdCategory) value is not available in the Category table.

if exists(

select 'x' from dbo.Dim_Category  where Category=@ProdCategory

    )

begin

select ProductName as Item ,SalesAmt as Sales_Amt from dbo.Dim_Category where Category=@ProdCategory

end

else

begin

select Category as Item,sum(SalesAmt) Sales_Amt from dbo.Dim_Category group by Category

end

Follow the given below steps:

-In the above created Dataset, @ProdCategory is parameter and its default value is 'a' initially.

-Create a Chart report and drag the field Item in to the category field and Sales_Amt into the Data field of the chart.

-In the Action pane write an expression to Go to this same report in the Chart series

-Pass the Field ‘Item’ as a parameter to the report

-On clicking on the Category that Category will be taken as a new parameter. And new result set for the selected Category (Products under the selected Category) will be produced in the same report.

-Now, when the report initially gets opened, the default parameter will be passed to the data set and the value will not be available in the Dim_category table, and the report will generate Category wise Sales Amount in the Chart.

-If the user clicks on the Category series, then the selected Category will be passed as an in parameter to eth data set and the selected categories Products will be returned by the data set and the chart will provide the Product wise Sales amount. 

SSRS : New Line Expression

SSRS : New Line Expression

       If you want to display the text in a new line at the text box, the following expression can be used.

="Line 1 " + VBCRLF + "Line 2"

Ex : =Fields!AddressLine1 + VBCRLF + Fields!AddressLine2 + VBCRLF + Fields!AddressLine3

OP:
 Address1
 Address2
 Address3

Note: If VBCRLF is used first or last part the expression, it has no impact on result. If you want a line in the first or last, then use one more extra VBCRLF.

SSRS Custom code to convert the Integer values into “Days:HH:MM” format

To Convert the Integer values into “Days:HH:MM” format:

Write the following VB.Net code in Custom Code area (Right click on report -> Report Properties -> Code)

Function MinToDDHHMM(iValues  as integer) as String

        Dim mDay,  iHour As Integer

        Dim mMinute As Integer

 If mValue >= (24 * 60) Then

            mDay = mValue \ (24 * 60)

            mValue = mValue Mod (24 * 60)

 End If

 If mValue >= 60 Then

            mHour = mValue \ 60

            mValue = mValue Mod 60

  End If

mMinute = mValue

MinToDDHHMM = IIF(mDay>9,iDays, "0" & mDay) &  ":" & IIF(mHour>9,mHour, "0"&mHour) & ":" &

                                                        IIF(mMinute>9 ,mMinute,"0" &mMinute)

End Function

Now, use the following value expression “ =Code.MinToDDHHMM(Fields!TotalMinutes.Value) in the report area.

(Considered that the field “TotalMinutes” contains the minute value to be converted into Days:HH:MM format.)

Result : You will get the minute value “1505” as “01:01:05”

SSRS Custom Code to Convert minutes into HH:MM format

To Convert the Integer values into “HH:MM” format in SSRS:

Write the following VB.Net code in Custom Code area (Right click on report -> Report Properties -> Code)

Function ToHHMM(Minutes As Integer) As String

Dim RetValue As String

Dim min As Integer

RetValue = "0000:00"

If Minutes < 0 Then Minutes = Minutes * -1

RetValue = Fix(Minutes / 60)

min = Minutes Mod 60

If min < 10 Then

RetValue = RetValue + ":0" + CStr(min)

Else

RetValue = RetValue + ":" + CStr(min)

End If

ToHHMM = RetValue

End Function

Now, use the following value expression “ =Code.ToHHMM(Fields!TotalMinutes.Value) in the report area.

(Considered that the field “TotalMinutes” contains the minute value to be converted into HH:MM format.)

Result : You will get the minute value “185” as “03:05”

SSRS : Displaying Parameter values as a Label in the report

We can just show the Parameter value in the text box by writing simple expression as

=Parameters!ParamName.value

But If the Parameter is a Multi value parameter, we have to write Join expression to display multiple selected values. Ex:  

                    = Join(Parameters!ParamName.Value, “,”)

 This will give you comma separated list of parameter values

In this method, if the number selected values are more, the text box will grow up the report might not look nice. If we want to show “ALL”, or <Some message> when ‘Select All’ is selected in the report parameter, we can use the following expression(Assuming that the values for the parmaters are supplied from the data set “DataSet_ParamName”)

=IIF(Sum(Fields!ID.Value, “DataSet_ParamName”)=Parameters!ParamName.Count, ”Al””,Join(Parameters!ParamName.Value, “,”))

We can also change the same expression to display some message when the selected Parameters count is greater than some values.

=IIF(Parameters!ParamName.Count >=2, ”Selected More than 2”,Join(Parameters!ParamName.Value, “,”))

If the selected parameter count is greater than 2 then it will show the message as as “Selected More than 2”.

SSRS - Serial number for the group column

                We were developing a report which contain a group (grouped by Product) and the group contain a detail row in it. We wanted to show Serial Number for the Products, we trying to use RowNumber function in SSRS but it didn’t come up as expected.  Finally we found the solution from Web which was written by Bilal Hani- a Microsoft Community Contributor as how to “add serial number to grouping column in SSRS Tablix”.

1. Open the report properties (right click on blank area outside the layout and click properties) or (Menu-->Report-->Report Properties)

2. Click on the code pane on the left

3. In the code window enter below script

Dim Counter as integer=0

public function getGroupCounter() as Integer

 Counter=Counter+1

 return Counter

end function

4. Go to the properties of the group (based on which group you need the group serial number)

5. Click the variables tab on the left

6. Click on Add and set Name as GroupCountValue and set the expression (in the expression window) as =Code.getGroupCounter, click ok and close the window.

7. Now in the textbox where you want to display the group number, set the expression as =Variables!GroupCountValue.Value

                Now the report will return the Serial Number for each Product as Expected. !!

Repeat Header / Keep Header Visible in Tables in RS 2008

               When I tried to set the properties like ‘Repeat header rows on each page’ or ‘Keep header rows visible while scrolling’ -  it doesn’t work in SSRS 2008. Through the searches, I found the following solution for it.

            In the grouping pane of the report page, click on the small triangle and select "Advanced Mode"

It will give you a list of static members in the selected Tablix.
Select the corresponding static and press F4 to see its properties

In the properties window :
- set KeepWithGroup to After
- set RepeatOnNewPage to True for repeating headers
- set FixedData to True for keeping headers visible 

Now click to preview the report...the selected static (Header Row) will be displayed in each page of the report. !!