Thursday, December 17, 2009

SSRS:Master-Detail Reports...

In SSRS 2008 there are different ways to create Master detail reports. One of the strategies adopted to Master Detail was to use the Javascript Windows.Open function. For this function the URL string has to be built, in case the detail report requires parameters, the values for the parameters need to be passed and built as well. For example if there is a Column called SalesPerson, the users wants to click on the sales person's name and view the details of his sales transactions. In SSRS , in the tablix control right click on the cell containing the Salesman name. Choose the Option TextBox Expression.

This opens up a window, on the left there is a option Action, click on this. You will be presented with a list of options, choose Go to URL, below that there will be a textbox with a button on to the right, click on it.

This will open the expression window where you will type:
="javascript:void(" & Code.GoToDetail("XYZ","Manager",Fields!SalesmanId.Value,Code.ShowParameterValues(Parameters!as_accttype),Code.ShowParameterValues(Parameters!as_city)) & ",'_Blank','Toolbar=no resizable=yes'))"

In the above example the calls a function called GoToDetail which is defined in the Code Section of Report Properties. In the GotoDetail function, one would build the URL String to access the detail report. The URL construct would like:

URL = "'" & ReportPath & ReportName & "&rs:Command=Render&rs:Format=" & RenderFormat & "&rc:Parameters=false" & as_Type & as_Section & as_accttype & as_city & "'"

The URL String returned from the function. When the user runs report, under the SalesmanID column the icon would change to pointing finger. When the user clicks on the SalesmanID value it would open the Detail Report. In case one has dual monitors the master and the detail report can be compared side by side.

No comments:

Post a Comment