InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 11:18:58 GMT20http://www.sqlservercentral.com/Forums/Topic539847-1347-1.aspxHi dave-dj,the SSAS article has been published under [url=http://www.sqlservercentral.com/articles/Linked+Server/63867/]http://www.sqlservercentral.com/articles/Linked+Server/63867/[/url]Take careMartinSat, 25 Jun 2011 05:27:31 GMTMartin Cremerhttp://www.sqlservercentral.com/Forums/Topic539847-1347-1.aspxInteresting and helpful. ThanksFri, 24 Jun 2011 10:50:48 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic539847-1347-1.aspxnot tried it yet, but reading through it seems very interesting and useful.:-DI'd be interested to know about the SSAS article when its done.,Fri, 24 Jun 2011 04:42:11 GMTdave-djhttp://www.sqlservercentral.com/Forums/Topic539847-1347-1.aspxHi Martin,Thanks for your input. this is really helpful. I am stuck with an issue and unable to find any information. Hope you can provide me with some suggestions.My issues is Data Source :SSASReport Type :Matrix (Drill Down)I have a report Year :2010 Jan Feb March April Avg(last2 months)Account Sub Acc1 1 2 1 2 (2-1)=1 Sub Acc2 2 1 1 1 0Account2 Sub Acc2 3 3 1 1 0 Sub Acc 1 4 4 1 3I am able to create the report create totals , sub totals etc, my issue is the population or calculation of the last column “Avg” last column depends on the parameter (EndMonth), it is always EndDatevalue –EndDate-1.value.Is there a way that I can get to that columnI tried to first display the last 2 columns but could not get to that. I tried=IIF((Format(CDate(Fields!MonthFirst.Value), "yyyyMM"))=Parameters!ToDimDateYearMonth.Value,0,IIF(IsNothing((Fields!Orders.Value)), 0,(Fields!Orders.Value)))Is there a way to get the last column.Any suggestions are highly appreciated.ThanksDeviMon, 10 Jan 2011 17:35:28 GMTapondurihttp://www.sqlservercentral.com/Forums/Topic539847-1347-1.aspxGreat article ! But how I would handle if I need to calculate subtotals for the noncalulated fields? For example if you would need to drill down to Customer Type for every month? Then if I add a subtotal, calculated fields will get duplicated. Mon, 27 Jul 2009 13:26:09 GMTvcaponehttp://www.sqlservercentral.com/Forums/Topic539847-1347-1.aspxHi Sergio,I published the SSAS-article on SQLServerCentral a while ago. You should find it here: [url=http://www.sqlservercentral.com/articles/Linked+Server/63867/]http://www.sqlservercentral.com/articles/Linked+Server/63867/[/url]Concerning SQL Server 2008:The Reporting Services 2008 has a new control called "tablix" ("[u]tabl[/u]e" and "matr[u]ix[/u]") which should behave much better than the matrix. However, I have not tried the example of my article within SQL Server 2008 yet.Kind regards,MartinSat, 18 Apr 2009 15:36:38 GMTMartin Cremerhttp://www.sqlservercentral.com/Forums/Topic539847-1347-1.aspxHello!Great article. Really helpfull... Do you know is there is any new feature on SQL Server 2008 that would add this functionality?Any way, If you would be so kind to share the solution with SSAS.Please, send me a briefing about it, if you would not write an article!sergioblum@hotmail.comThank you very very much!Mon, 13 Apr 2009 16:20:37 GMTSergio Adriano Blumhttp://www.sqlservercentral.com/Forums/Topic539847-1347-1.aspx[quote][b]Matteo (7/24/2008)[/b][hr]Another way to do this using more of the native function of the Matrix would be to create a grouping column, where one group value is tied to the Articles sold and another group value tied to the Target information. Group 01 is the Articles sold.Group 02 is the target.The data set would look like:grp Name Month #Sold== ==== ===== ====01 Franz 200801 1001 Franz 200803 701 Franz 200804 1202 Franz Target -20 ([b]Trick:[/b]note negative Target value is need to make the "math" ok when sub totalling)...Matteo[/quote]Matteo,as a simplification of my approach your idea has the benefit of being easier to understand and maintain. It has its limitations, however:* When using groups you cannot decide to have a total on the first group only (in the example: on grp=01 but not on grp=02). Reporting Services will add a total column for both 01 and 02.* You are limited to sums (Totals). My approach is more generic (and therefore more complex) and can be used for any arithmetic operation (average, multiply sales figures by price, ...).Therefore it depends on the actual situation which way is the better way to go.RegardsMartinThu, 24 Jul 2008 12:42:00 GMTMartin Cremerhttp://www.sqlservercentral.com/Forums/Topic539847-1347-1.aspxMartin,First of all Thank you for really good article with perfect example to show the functionality. I fully support your approch of stored functions/stored procedures to generate the result set with the calculation and just use basic functions of SSRS to display the data especially, as you wrote, while exporting report to excel. Again it was a very good article....Keep it on.....:)Thu, 24 Jul 2008 10:46:41 GMTHarit Gohelhttp://www.sqlservercentral.com/Forums/Topic539847-1347-1.aspxThis is a great article, and I must apologize -- this is the first time I went to rate an article, and I meant to give it 5 stars. Unfortunately my unfamiliarity with the process led to only crediting it with one star.:blush: If the editor could change that vote I would appreciate it.:(Thu, 24 Jul 2008 09:28:38 GMTnotquitexenahttp://www.sqlservercentral.com/Forums/Topic539847-1347-1.aspxExcellent. If it looks like you'll have something ready and the "posting/publishing" is delayed, can you e-mail something in the interim. :)Thu, 24 Jul 2008 09:11:30 GMTbteaguehttp://www.sqlservercentral.com/Forums/Topic539847-1347-1.aspxGreat article ...Thu, 24 Jul 2008 08:53:43 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic539847-1347-1.aspxHello bteague,I hope to have that article written within the next week.But I do not know how long it takes for the article to be published.MartinThu, 24 Jul 2008 08:34:51 GMTMartin Cremerhttp://www.sqlservercentral.com/Forums/Topic539847-1347-1.aspxAnother way to do this using more of the native function of the Matrix would be to create a grouping column, where one group value is tied to the Articles sold and another group value tied to the Target information. Group 01 is the Articles sold.Group 02 is the target.The data set would look like:grp Name Month #Sold== ==== ===== ====01 Franz 200801 1001 Franz 200803 701 Franz 200804 1202 Franz Target -20 ([b]Trick:[/b]note negative Target value is need to make the "math" ok when sub totalling)Then just set up totalling on your matrix report to provide sub totals per the group value.Special Notes:1) In order for the final total to come out correctly the Target data needs to go in as a negative number2) You would have to have a special format option when displaying the "target" data to not show the negative3) You would still need to use the techniques, described in the article, for proper sequencing and column headers and such on the final report.Advantages:- Your data set only has raw data in it, rather than mixing raw data and summary data. - Much Simpler T-SQl statements - Lets the matrix list do what it does best.Regards AllMatteoThu, 24 Jul 2008 08:31:23 GMTMatteo-317581http://www.sqlservercentral.com/Forums/Topic539847-1347-1.aspxReally a clever way. I've been using the addition of an extra table with similar grouping as the matrix one but then I always lost the ability to export and also on sorting capability (as is that is pretty limited in a matrix report). Thanks.Thu, 24 Jul 2008 08:26:31 GMTrjv_rnjnhttp://www.sqlservercentral.com/Forums/Topic539847-1347-1.aspxWhen do you think you'll have the SSAS article in a draft form?Thu, 24 Jul 2008 08:21:25 GMTbteaguehttp://www.sqlservercentral.com/Forums/Topic539847-1347-1.aspxWow Martin, This is a great article. Totals columns, 13 week averages, % change -- all of these have been a critical part of reporting that I felt matrix tables would be well suited for if one could only add extra columns. This really pushes our reporting capabilities ahead by light years!MarkThu, 24 Jul 2008 06:25:27 GMTmark-786476http://www.sqlservercentral.com/Forums/Topic539847-1347-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/Reporting+Services/63415/">Reporting Services: Adding extra columns / rows to a matrix</A>[/B]Wed, 23 Jul 2008 22:52:05 GMTMartin Cremer