| Author |
Topic |
|
opensourcederry
Starting Member
12 Posts |
 Posted - 2012-07-12 : 10:33:31
|
| Hi all,I am trying to link through from a matrix with summed numerical values to the row level detail that make-up up the summed numbers. I am trying to show the detail in a sub report. I have a link to other report setup as an action on the text box containing the summed item.Imagine I have a simple matrix.On rows on left I am group by Year and month.Across top I have countries, UK, Ireland, USA etc etc.In the middle data section I have a number which indicates orders placed. This number will show totals at year level or month level, whatever user is looking at.Can anyone tell me if it is possible to click on the summed number say 15, and then go to a sub report which shows details of the 15 orders that make up the number 15?I can use sub reports ok and I can link to reports ok via a text box actions and passing parameters until I am blue in the face but I cannot for the life of me link through to the sub report and get the required data for the 15 orders.It only appears to work when there is a single group on the row axis. For example if I was grouping by Year only on the rows and showing countries across the top then I would simply pass the year and country parameter to my subreport and it shows the correct amount of details records. So if I had 120 totals orders in 2011 for the USA then click ing on the number 120 opens the sub report with the correct 120 orders.However, if I group by more than one item, in this case Year and then Month then ssrs seems to have problems showing the correct data at the top grouping.So imagine the 120 for 2011 is made up on 10 orders per month with 10 as the total for each month. If I drill down to the number 10 for any month and click on it it shows the 10 orders ok. However, if I click on the 120 value for all over 2011 then I do not get the full 120 orders shown, it only shows some of them. In this example I am passing year, month and country parameters to the sub report.Any ideas?Thanks in advance,osd |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-12 : 11:06:30
|
you can get the details by passing all of required parameters. for totals pass only required parameters like in your case for 120 pass only year and country parameters no month. And for your detailed report all parameters should be optional and should have default value of All. when you pass nothing for a parameter from matrix it will take All as value and in your query behind bypass the filter condition in this case Month = @Monthparameter when @Monthparameter = 'All'so filter will look like below in sql for details report(Country=@country OR @Country='All')AND (Month = @month OR @Month = 'All')AND (Year = @year OR @year='All') ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
opensourcederry
Starting Member
12 Posts |
Posted - 2012-07-12 : 15:57:24
|
quote:
Originally posted by visakh16
you can get the details by passing all of required parameters. for totals pass only required parameters like in your case for 120 pass only year and country parameters no month. And for your detailed report all parameters should be optional and should have default value of All. when you pass nothing for a parameter from matrix it will take All as value and in your query behind bypass the filter condition in this case Month = @Monthparameter when @Monthparameter = 'All'so filter will look like below in sql for details report(Country=@country OR @Country='All')AND (Month = @month OR @Month = 'All')AND (Year = @year OR @year='All') ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Thank you Visakh for your reply.I had tried something similar to your approach but still no joy.I have noticed some peculiar behaviour from SSRS and I am wondering if it's a bug.First of all the drill through to the sub report works fine at the bottom group level of Month. It's the parent group of year that seems to be causing the problem.So, in my example above imagine that the year sales total for 2011 is 120 orders and clicking on the + beside 2011 shows the lower group of 12 months with 10 beside each one. Clicking on any of the 12 x 10 values opens the sub report and shows the 10 correct rows for that relevant month. However, clicking on the 120 parent group value which is the total for the year opens the sub report ok but does not show 120 records, instead it shows the records from the first item in the month group it comes across, eg January.However, there is another twist. If the total in the year parent group for 2011 was 120 and the 120 was actually comprised and limited to 120 orders within a single month child group group, eg March, then clicking on the 120 value in the year parent group shows the 120 sales orders ok in the sub report.So the issue seems to be that if the parent group total is made up from >1 child group branches then the sub report at parent level does not show the full quota of records.thanks, osd |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-12 : 17:25:54
|
| how are you passing values for navigating report? have you set parameter values separately?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
opensourcederry
Starting Member
12 Posts |
Posted - 2012-07-12 : 18:21:50
|
quote:
Originally posted by visakh16
how are you passing values for navigating report? have you set parameter values separately?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Hi Visakh,No parameters in the matrix report, just a select that produces a data set. The data set includes year, month and country.In the detail sub report I have 3 x parameters set up - year, month and country. To link reports I right click in the middle field (the text box that contains the summed values) on matrix, text box properties, action, go to report, browse for report and set the 3 parameters in the detail report to the same fields in the matrix. So @year in subreport is driven by year in the matrix etc.Thank you. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-12 : 20:13:38
|
| does matrix have a grouping? is issue while navigating from subtotal?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
opensourcederry
Starting Member
12 Posts |
Posted - 2012-07-13 : 06:23:49
|
quote:
Originally posted by visakh16
does matrix have a grouping? is issue while navigating from subtotal?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
Hi Visakh,Yes the Matrix has 2 groups on the rows Year x Month and one group on columns, countryThe year total is sum of all the month values beneath it.Is it possible to test for the grouping level in ssrs? Logically it seems that at Year grouping level I need to pass Year and Country to the sub report whereas at the month grouping level I need to pass Year month and country.thank you,osd |
|
|
|
opensourcederry
Starting Member
12 Posts |
Posted - 2012-07-16 : 07:30:22
|
| Hi all,Managed to get this resolved via a work around.I was building the matrix by choosing Insert - Matrix in Report Builder 3.This let me drag year and month to the row, country to the column and sales order count to the data field.However, for some reason in design mode the Insert matrix method keeps all the row groups on one row, even if there is more than one and this leaves only a single data cell.What I did instead was build the matrix manually via inserting a table and then adding 2 x row groups for Year and Month and a column group for countryCrucially this placed each row on it's own physical row, not the same one which meant I had now a data field for each intersection so I just placed a Go To Report action in each field and changed the parameters as required.Thanks for all your help, much appreciated.osd |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-16 : 10:03:31
|
| well..you should have specified you were using report builder. I thought you were trying to create report using standard ssrs report type project. anyways glad that you sorted it out------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Drill to detail from matrix