Accessing sub totals in a matrix

SQL Server Reporting Services

Index ‹ SQL Server

- Previous
  - 1
    - To find ratio between two columns Ok I will tell my scenario clearly, so that u can help me better. I have a table like this: A/cNO: description: Amount: date: 10000 aaa 50 05/02/06 .... ... ... .... .... ... ... .... 10999 Tot_Assets 1200 20000 bbb 60 07/12/06 .... .... ... ... .... ... ... .... 20999 Tot_liability 1500 ..... Now, I want to find the ratio between Tot_Assets & Tot_liability for every year. The Tot_Assets is sum of (10000 to 10999) & Tot_liability is sum of (20000 to 20999). Also I want to plot this ratio in a chart against Year. The query which I wrote was: SELECT DISTINCT DATEPART(yy, gle.[Posting Date]) AS Year, CASE WHEN (gle.[G_L Account No_] BETWEEN 10000 AND 10999) THEN SUM(gle.Amount) ELSE 0 END AS 'Assets', CASE WHEN (gle.[G_L Account No_] BETWEEN 20000 AND 20999) THEN SUM(gle.Amount) ELSE 0 END AS 'Liabilities' FROM [The ProSure Group, Inc_$G_L Entry] AS gle INNER JOIN [The ProSure Group, Inc_$G_L Account] AS gla ON gle.[G_L Account No_] = gla.No_ GROUP BY DATEPART(yy, gle.[Posting Date]), gle.[G_L Account No_] But I am not getting the expected result, where did I go wrong Expecting a positive reply.... Tag: Accessing sub totals in a matrix
  - 2
    - SP1 Installation note Just after it started a small dialog box popped up. It basically said the installation failed with the following error -- and there was nothing following it!! What I did is when the send an error report to Microsoft popped up - I looked at the contents and followed its link to c:\windows\hotfix\hotfix.log The contents (which are below) contained a reference to a support file on e: which is the USB drive. Hope that helps. Bob 02/23/2007 15:14:11.531 ================================================================================ 02/23/2007 15:14:11.562 Hotfix package launched 02/23/2007 15:14:11.578 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion 02/23/2007 15:14:11.593 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files 02/23/2007 15:14:11.625 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion 02/23/2007 15:14:11.656 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files 02/23/2007 15:14:12.031 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion 02/23/2007 15:14:12.062 Successfully read registry key: CommonFilesDir, string value = C:\Program Files\Common Files 02/23/2007 15:14:12.078 Successfully opened registry key: SOFTWARE\Microsoft\Windows\CurrentVersion 02/23/2007 15:14:12.125 Successfully read registry key: ProgramFilesDir, string value = C:\Program Files 02/23/2007 15:14:12.140 Local Computer: 02/23/2007 15:14:12.171 Target Details: E1705 02/23/2007 15:14:12.187 commonfilesdir = C:\Program Files\Common Files 02/23/2007 15:14:12.218 lcidsupportdir = e:\f4724c27d81193cdbb4c1bf458\1033 02/23/2007 15:14:12.250 programfilesdir = C:\Program Files 02/23/2007 15:14:12.281 supportdir = \\E1705\e$\f4724c27d81193cdbb4c1bf458 02/23/2007 15:14:12.296 supportdirlocal = e:\f4724c27d81193cdbb4c1bf458 02/23/2007 15:14:12.328 windir = C:\WINDOWS 02/23/2007 15:14:12.343 winsysdir = C:\WINDOWS\system32 02/23/2007 15:14:12.375 02/23/2007 15:14:12.437 Enumerating applicable products for this patch 02/23/2007 15:14:18.953 The patch installation could not proceed due to unexpected errors 02/23/2007 15:14:18.984 02/23/2007 15:14:19.015 Product Status Summary: 02/23/2007 15:14:19.109 Hotfix package closed Tag: Accessing sub totals in a matrix
  - 3
    - SSIS Package permissions?? Yes, but i have impersonation on, should the user not be impersonated to the system So i would be processing the cube like myself instead of IIS user How can i do this then Best Regards, Tag: Accessing sub totals in a matrix
  - 4
    - How do I restore sql server database primary data back .mdf Hello guys My server crashed but luckily I was not able to get back my files with help of recovery software. Now, all I have from the database are just sql server database primary data .mdf and sql server databaseTransaction Log Files .ldf . I need to restore these data back to sql server. Please could someone tell me how to restore these two file types back to my sql sever 2007 database Thanks netboy Tag: Accessing sub totals in a matrix
  - 5
    - Table-value Function or View And they can serve different purposes. A TVF can provide a higher logic within, whereas a view can be optimized in a better way from the optimizer as it will have more predictable results. But it really depends on the scenario, it’s like asking "How can I get better from A to B, walking, by bike or by car " :-) Jens K. Suessmeyer --- http://www.sqlserver2005.de --- Tag: Accessing sub totals in a matrix
  - 6
    - how can we do Row based table tracking The key question here is how you define second row. I guess you have a column which you can order the data on to establish what is the second row. Assuming you do, the you could either: 1) dump all your rows into a temp table with an identity column and then select from that - my prefered option 2) use a derived table Code Block create table table1 ( col1 int , col2 int , col3 int ) insert into table1 select 1 , 2 , 1 union all select 5 , 3 , 2 union all select 4 , 4 , 3 union all select 7 , 4 , 4 -- we want the 2nd row based upon the dataset ordered by col3 in descending order -- option 1 declare @table table ( incID int identity ( 1 , 1 ), col1 int , col2 int , col3 int ) insert into @table select col1 , col2 , col3 from table1 order by col3 DESC select col1 , col2 , col3 from @table where incID = 2 -- option 2 select top 1 * FROM ( select top 2 col1 , col2 , col3 -- vary the number of rows returned here to get different row numbers from table1 order by col3 DESC ) AS dt order by col3 ASC drop table table1 HTH! Tag: Accessing sub totals in a matrix
  - 7
    - SQL Reporting Services Justify text in a textbox Close, but not really what I want. I have text that needs to be justified. (For example, in Microsoft Word you can Justify the text - Justify means that both the left and right margins will be aligned when typing. For some reason most people don't understand what Jusftiy means since we usually just do Left Justify). SRS does not have the Justify option that Word does. My end users demand that the letters (my SQL Report) use Justified format, not just left-justified. Tag: Accessing sub totals in a matrix
  - 8
    - Creating a Query for Notification We want to be able to cache the data for our propertydetails web page (a real estate web site) Right now the stored procedure for that page is not a good candidate for query notification. Is it possible to have a trigger on a Property table and when the lastupdated field is changed then go and update the web cache data for that propertydetails page (i believe that this is Broker Services). Tag: Accessing sub totals in a matrix
  - 9
    - looping to drop temp tables Here it is, Code Snippet declare @n as nvarchar ( 3 ), @table as nvarchar ( 10 ) set @n = 1 while @n <= 5 begin set @table = '#temp' + @n if object_id ( 'tempdb..#temp' + @n ) is not null exec ( 'drop table ' + @table ) set @n = @n + 1 end Tag: Accessing sub totals in a matrix
  - 10
    - Column count max for Merge replication still 246? Madhu, Can I replicate a table more than 264 columns in Transactional replication. During my initial setup, the publisher did not allow me to publisher a table with more than 246 columns. Thank you. Edwin Tag: Accessing sub totals in a matrix
  - 11
    - rsExecutionNotFound ! I have been frequetly visiting your blogs with a solution for rsExecutionNotFound. I used johngaallard's script to extend the accesstimeout(to 6000 ) eventhough that was not the base of the problem. My Reports run in less than 15 sec (max). I have enabled caching for about 12 hours ( there are nightly updates) using [Cache a temporary copy of the report. Expire copy of report after a number of minutes option]) The reports are rendered using a ReportViewerControl in an ASP.Net2.0 web application.(The report is rendered as PDF and then shown in another IFrame using Adobe plugin).The ASP application session timeout is 100mts. The problem i am facing is that after sometime, the rsExecutionNotfound starts occuring for some report with a specific set of parameters. No amount of rerunning the report will stop. The same report with different parameters renders fine. The only way to stop this is to set the cache option 'Do not cache temporary copies of this report'. If i set ''Do not cache temporary copies of this report'' there are no errors. I am doing caching mainly to bring down report rendering from 15 sec to 2-3 secs. I am using PDF format . I am now thinking of working around by trying to catch the Exception that occurs and trying to re-render the report. If this is the only way, how can i do it. I had fought fiercely to use SSRS and now I am in a fix Any help is appreciated. Thanks, Saj Tag: Accessing sub totals in a matrix
  - 12
    - Programmatically create a Report History Snapshot Thanks for your prompt reply. Exec addevent 'ReportHistorySchedule' , 'B3A10200-FA1B-4298-8689-88B8ACFDBCFB' (which is what the SQL Agent job does) assumes that a history generation schedule for the report already exists, and doesn't work if no such schedule has been created. How can I simulate the Click of the New Snapshot button which MANUALLY generates a History snapshot, regardless of whether a schedule to generate History Snapshots exists or not. Thanks again for your comments. Sam. Tag: Accessing sub totals in a matrix
  - 13
    - User of the Authorized Windows Group Failed to Login Thanks for the script. I will try to run it when I am at the server. The Windows User is not disabled. When I add the windows user manually as login in SQL Server, this user can login succesfully. I also have different case. I add Domain Groups to Windows Group, and then add this Windows Group to SQL Server as login. I try to login using member of Domain Groups, but fail to login. When I add the Domain Groups directly to SQL Server, the member of that Domain Group is able to login. The problem is I have quite number of Domain Groups to be added to SQL Server with same role. How can I add this Domain Group to Windows Group and authorized to SQL Server using the Windows Group Tag: Accessing sub totals in a matrix
  - 14
    - SQL 2000 Database upgrade to 2005 HI Hemantgiri Many thanks for the info, I knew my old memory was right - I shall remember your points when I upgrade thanks again Regards Martin Tag: Accessing sub totals in a matrix
  - 15
    - Proactive caching notification error I have had the same error. After a lot of analysis I have come to the following conclusions: There are 2 places in a Data Source View where you can store Data Source information - at the root and optionally at the DataTable level. In many scenarios you need to have data coming from multiple data sources In a normal build SSAS uses the Data Table data source for connecting to the source database However, when using ProActive caching it is using the Data Source information (i.e. database connection string) from the root level of the Data Source View - hence if you have Data Tables from numerous datasources in the same Data Source View SSAS will attempt to use the root data source. If this does not match the data source used in the Data Table then an error will occur. I can't find a way round this at the moment. I have tried to use a different Data Source View for some of my dimensions - but it seems that you can only use one Data Source View for a cube using BIDS. I am going to try and edit the cube defintion file manually to see if there is a way of adding dimensions from other Data Source Views as a workaround. In conclusion, this looks like a bug / restriction of SSAS itself. Tag: Accessing sub totals in a matrix
  - 16
    - Connecting to SQL Express after Unattended Install You know, there are two "allow remote connections" thing. One is in the SQL Server Properties\Connections and the other one is in SQL Server Surface Area Configuration. Be sure they are set correctly. Tag: Accessing sub totals in a matrix
- Next
  - 1
    - SQL Server Installed, now what? There's actually ton's of free SQL Server training material and lab to learn SQL Server from HTTP://WWW.SQLCOMMUNITY.COM Actual link is: http://sqlcommunity.com/SQLResources/SQLServerLab/tabid/75/Default.aspx Pete Tag: Accessing sub totals in a matrix
  - 2
    - installtion program dies when installing Native Client Can you check C:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\Log\Summary.txt and post the error message Also go inside the subfolder files and look for _snac.log Thanks Vishal Tag: Accessing sub totals in a matrix
  - 3
    - BCP on client machine Thanks for your reply. Can anybody help me out in downloading the third party data dump tool. I heard about freetds, thats only for linux. Any help is appreciated. -Anand Tag: Accessing sub totals in a matrix
  - 4
    - foodmart database? BlackStallion wrote: Where can i find the foodmart sample database for SQL Analysis Services 2005. thanks in advance. All in here: http://blogs.conchango.com/jamiethomson/archive/2006/04/02/3423.aspx -Jamie Tag: Accessing sub totals in a matrix
  - 5
    - SQL Server 2005 JDBC Driver v1.1 and SSL Thanks very much. I'll keep my eyes peeled for a new release. Trev. Tag: Accessing sub totals in a matrix
  - 6
    - Loading excel file(.xls) into sqlserver (ASP.net web application)(ASP.net framework 1.0) i need visual basic(VB) codings....lol... sorry for this.... Tag: Accessing sub totals in a matrix
  - 7
    - compact sqlce database Thanks. How can get the newer SQLCE engine Can that be run at desktop without a device I have quite a bit corrupted databases whih is a disaster now! Tag: Accessing sub totals in a matrix
  - 8
    - Database design - best practices Does anyone know the location of an offical (Microsoft if possible) document that shows the best practices for databse design For instance what to call tables now schema's exist Should it be Countries LookCountry Geography.Country Geography.Countries What about Views and Stored procedures prefix with VW_ sp_ or some alternative Thanks Tag: Accessing sub totals in a matrix
  - 9
    - How do can I do this? thanks a lot, but I have only colA and colB, the "x)" in my post was only for being more clear about it. Tag: Accessing sub totals in a matrix
  - 10
    - OLE DB Source to Flat File Destintation using Fixed Width Columns - determining source column width Hi theddern, Have you looked into the system provided views under the "master" database in SQL Server 2005 There's one callsed sys.columns. I'm not really sure but, I think with a SELECT statement against that view you'd get what you need. :) HTH Tag: Accessing sub totals in a matrix
  - 11
    - date picker format If you have a parameter of datetime, and the report language setting is user!langauge, and the datetime in IE is not coming out as expected, then you do not have you local regional settings set up correctly. Tag: Accessing sub totals in a matrix
  - 12
    - Data disappearing during Run time Unfortuantly I can't find this the only one similar is "FAQ: My data doesn't get saved in Visual Studio" however this isn't the problem, in my program I have a save button wich updates the database, when I click this the data saves to the database but disapears from view, however if I close the application and re open it the data that I'd previously saved is nw there Tag: Accessing sub totals in a matrix
  - 13
    - Old problem, but... Cannot open user default database. Login failed. Login failed for user 'NT AUTHORITY\NETWORK SERVICE'. You should not post like that, it is not helpful. Can anyone help thanks Tag: Accessing sub totals in a matrix
  - 14
    - Data Conversion Tag: Accessing sub totals in a matrix
  - 15
    - Results from Aggregate() are wrong I don't think mine is related. I don't get an error, just invalid results. I suppose it would help if I can repro in Adventureworks. I was hoping somebody would say that Aggregate() used as a slicer in a WHERE clause is not defined to return valid results or something like that. Tag: Accessing sub totals in a matrix
  - 16
    - Inconsistent Linked Server Query Results Hello, I have a linked server named 'Charlie_File' to an Excel Workbook that I set up in SQLServer 2005 Management Studio. The workbook is on my local C drive. Sometimes, I get the results back that I expect when I run the following query; SELECT * FROM OPENQUERY ( Charlie_file , 'SELECT * FROM [Feb$]' ) Sometimes, on subsequent runs of the above query, I get the following message; Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for linked server "Charlie_file" reported an error. The provider did not give any information about the error. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "Charlie_file". There seems to be about a minute or so of a delay before the query will run correctly on subsequent attempts. Is there a connection issue here where a connection blocks subsequent attempts to select the data within a specific time span Thank you for your help! cdun2 Tag: Accessing sub totals in a matrix

DotNet_Guy

Hi,

How can I access the subtotal cells/values from each of the columns in Matrix and use them for calculations on other places in the report

Thanks.

Re: Accessing sub totals in a matrix

DotNet_Guy

Any body

How can I access the sub-totals of a Matrix as I need to use them in calculations in another place on the report How can I programatically access the matrix report item's cell or text box values, particularly the sub totals

Thanks,

Re: Accessing sub totals in a matrix

DotNet_Guy

Does any one know how the sub total field can be accessed in the matrix

Re: Accessing sub totals in a matrix

Aayush Goyal

Right click on your Column Group Header to acces the Column Subtotal. Same with the Row Group header for the Row Subtotal. I don't think you can access the Subtotal value in other places on the report though.

-Aayush

Re: Accessing sub totals in a matrix

DotNet_Guy

Well, I meant how to access the contents of a subtotal in a running matrix for use in computation in another column/text box. How to access a particular sub total cell value, and say, subtract it from the cell value of another matrix column at corresponding level and display that in a third column.