Matrix - Centering Higher Level Column Group Values In Viewing Area Instead Of Entire Cell Band Width
Dec 28, 2007
I have 2 higher level column groupings of month name and year above my actual date groups. It looks a little weird aligning them left but there is no guarantee that centering them will even allow them to show until I've scrolled right to the middle of the cell width that they occupy.
Is there a feature that comes with, or a well known trick for making them center in the area that is being viewed instead of the potentially very wide cell that they occupy?
What would be an equivalent expression for ISNULL(datafield, 0) for a table/matrix cell? I am using iif( Len().. to find out if there is something in the cell, and displaying zero in the cell if the length of cell item is 0, however am wondering if there is any better/elegant way of doing that?
Since matrices are dynamic in width and/or height, just wondering if there's any good way to center the automatically, since obviously you can't center them effectively manually. Thanks!
I have a parameter (hidden) that gets its value using an expression base on another parameter. When in the designer, the first time when the designer loads I can select the Parameter that controls the child parameter (expression lies in the default value section). The value changes.
When I change the parent parameter again, the value of the child parameter does not seem to change.
How can I make this parameter change automatically when the parent is changed ?
I want to enforce a unique constraint on a column which must be encrypted in MSSQL 2005 using Cell Level Encyption (CLE).
   CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'itsaSECRET!!!3£3£3£!!!'    CREATE CERTIFICATE ERCERT WITH SUBJECT = 'A cert for use by procs'    CREATE SYMMETRIC KEY ERKEY    WITH ALGORITHM = AES_256    ENCRYPTION BY CERTIFICATE ERCERT
[Code] ....
The output makes it obvious why the constraint has 'not' been enforced.
       Email       -------    1 | 0x00703529AF46D24BA863A3534260374E01000000328909B51BA44A49510F24DF31 C46F2E30977626D96617E2BD13D9115EB578852EEBAE326B8F3E2D422230478A29767C    2 | 0x00703529AF46D24BA863A3534260374E01000000773E06E1B53F2C57F97C54370FECBB45B C8A154FEA5CEEB9B6BB1133305282328AAFAD65B9BDC595F0006474190F6482    3 | 0x00703529AF46D24BA863A3534260374E01000000C9EDB1C83B52E60598038D832D34 D75867AB0ABB23F9044B7EBC76832F22C432A867078D10974DC3717D6086D3031BDB
I am using matrix to make a report. I have company name in the row group and user name in the details field. When I run the report I only see the company name once and each user of that company is displayed in a separate row. Then next company name and its users... I wanted to know if there is a way to display company name next to the user name? I know it can be done by using "table" but I can't use table because in this report there are other fields can only be done in a matrix. So, please let me know if there are any settings in matrix which I can use to display my company name next to each user of that company. Please ask me any thing if the question is not clear. Thanks a lot for the help.
I was wondering how can I get the value of System::TaskName of a higher scope when I have a Master Package that have several sequence task, data flow tasks and execute package tasks. For each task inside this Master Package on the Post-Execute event handler I have a script task that logs the execution of each task.
After running this master package I saw in my db that I have a row for every single tasks executed in the process and not only the tasks that exist in the master package. For instance, for simplicity let's say my master package looks like this:
I see two TaskName variables in the Variable window, one with OnPostExec scope and the other with Execute Package 2 for instance. I want to get the value of System::TaskName with Execute Package 2 scope.
I want to see in my db only the tasks in bold. Any ideas of how can I do this? I hope you understand what I'm trying to achieve.
I've read some threads on this topic and all have been solved by installing the SSIS service. This would be fine except for the fact that I already have SSIS installed and working on the server the package is being called from.
I have several scheduled packages that work without error and a few that fail, telling me "Error: ... it requires a higher level edition." Does SSIS need to be installed on the target server as well? Do I need to do a reinstall? Please advise. Thanks.
I am receiving the following error in Sql Job agent when I try to run an SSIS package : The task "Create Excel File" cannot run on this edition of Integration Services. It requires a higher level edition. It then goes on to tell me : The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. I have tried reseting the error count to allow for the "errors" , but it stills fails. The job suceeds in Visual Studio, but not when scheduled in Sql Management Studio. Any suggestions?
I just can not understand why when I add text to a group header the report displays more group row space but when I export to excel the extra space disappears....
Hello Guys, I am working on a matrix report which has several row groups and 1 column group. After execution, the column group wil end up with several columns containg numeric counts. I would like to have the grand total for each "column group" column as a last row on this report. For row groups you can just right click "Subtotal", but that is not possible for column group. Could someone please help me to find a clever way of accomplishing this, please. Thank you so much for your help!
I'll try to make this simple. I'm on SSRS 2005 and I have a report with a matrix object that has one row group and one column group. I need to switch the number format only for values where the column group has a specific value.
For example, here are the records in the table: Customer, Type, Amount Customer1, Revenue, -100 Customer2, Cost, 60 Customer1, Revenue, -200 Customer2, Cost, 125
By default the matrix object shows the following (the total comes from the standard subtotal on the column group): Revenue Cost Total Customer1 -100 60 -40 Customer2 -200 125 -75
But the users need the report to look like this, with all positives (why, oh why?! ): Revenue Cost Total Customer1 100 60 40 Customer2 200 125 75
I was able to use the inscope function to switch the signs of the Total numbers. But now I need to switch the signs of the Revenue column from negative to positive (and vice versa), without affecting the signs of the Cost column. It's strange to me because I CAN switch the signs for a specific row group (changing Customer1's number format, without affecting Customer2's format) using something like this:
But a similar expression specifying a column group value does not work, because the report seemingly doesn't recognize the value of the column group at all no matter what I do:
The other reason why this is strange is that I've done drill-through reports off of matrix objects where specific column group values (the ones clicked on) can be passed into the drill-through report parameters. So it recognizes the column group values upon drill-through, but not for formatting?
How else can I do this? I must be missing something here. Thanks.
I'm trying to read in a flat file (which, admittedly, has one very wide column), and it keeps breaking because of truncation when it tries to read in the file.
I'm developing and sql 2008 view that shows me the last month of sales of every customer and every item sold, the problem is that I need group by customer with the higher Sale Price of every item of the customer, example:
1.If We sold the same item to one customer then it must show just the higher sale price of every item
2.If the same item was sold to the same customer at the same price then it must show just the last sold date record of that item
SELECT TOP (100) PERCENT OrderDate, DebtorNr, DebtorName, ItemCode, Description, Qty, CostPrice, SalePrice FROM dbo.VK_SALE_ORDERS WHERE (OrderDate >= DATEADD(MM, - 1, GETDATE())) ORDER BY DebtorNr, ItemCode
This must be a first, something that works in pdf, but not in html view....
I have 3 interactive sorts on 3 row groupings in a Matrix
When sorting the data in the 'preview' tab of report designer, all works well. On deploying the reports and trying to sort, I find that the report contains huge areas of white space (1 very wide empty column) and no data whatsoever, although the row grouping labels are present. Upon exporting to PDF all is ok.
I have a matrix object in a report that sometimes runs off the side of the page based on the underlying data. Essentially if there are more than 11 columns it stretches out my page.
How can I fix this? Ideally, I would like to show only the Top 11 results but cannot seem to figure out how (or where, or on what data element) to properly set a filter.
I am trying to create a program that transfers tables to flat files. At this point in time, I have suceeded in created one that creates delimited files.
However, I am now trying to create fixed-width files as you can do with the SSIS designer, but programatically.
Is there a way to programatically determine the width of a column from the source table? I can not seem to find any kind of function or member that stores this information or allows me to retrieve it.
I know what I need to change in order to set a width for a column, but I just don't know how to find the width without just asking the user to provide one.
I'm currently working on a report which contains a number of matrix' with subtotals. Each matrix calculates a STDEVP which works just fine and the total shows the values I want.
However, I would like to change the font color of each cell based on the value compared to the total.
For example:
A 4.5 B 7.8 C 3.9 D 5.2 Total 5.7
With the values of A and C being less than the Total, I want to change their color to "Green" and the others to "Red".
I was hopeing I would be able to do something like this. =Iif(ReportItems!matrix1_std.value < xxxxxxxxx,"Green","Red")
Where ReportItems!matrix1_std.value is the values of A-D and xxxxxxxxx would be the "Total"
Is this in some way possible??
I found many expamples for changing the color etc of the totals cell but nothing which tells me how to change the layout of a cell based on the total value.
I created a load of packages on my SQL standard edition. I have now gon eto a Workgroup edition and tried to run the packages but am getting the following error
".....cannot run on this edition of integration services. It requires a higher level edition"
Isit a fact that you cannot run SSIS packages on SQL workgroup edition? Do I have to upgrade to Standard?
A: Id Reason Amount Subject RecordNo 1 Gift 100 first 11 2 Gift Reason 200 second 12 3 Gift Reason 100 first 11
The result that I want is :
Reason Amount Subject ALL 200 first i used the following query
select case when Grouping(B.Subject) <> '' then 'ALL' else B.Subject End as Subject , case when Grouping(B.reason) <> '' then 'ALL' else B.Reason END as Reason, SUM(B.amount) as amount from B where B.RecordNo = 11 group by grouping sets (B.Reason,B.Subject)
but it's giving me 3 rows in return.
I want only one summation row. How can i get that?
I've implemented and tested cell level security on the cube. It's testing certain level conditions, and returns #N/A (as normal) when the user is not supposed to see the cell value. Since I always use .FormattedValue in my reports, works fine in Report Services (and Excel and ProClarity, etc.)
Here's the problem:
When RS parameters encounter this situation, the parameter dataset "breaks" (The following system error occurred: Type mismatch.) This is happening, because the parameter fields (ParameterValue, ParameterCaption, ParameterLevel) are being replaced by #N/A, due to the cell level security. This is happening, because these are actually defined as members, and hence passing through cell level security.
What I need to do is find a way to have these specific members bypass the cell level security, so that the parameter datasets still work. (Failing that, a new way of specifying parameters in MSRS.)
I've tried the following a a cell level security rule, but it doesn't seem to work:
[Measures].CurrentMember is [Measures].[ParameterValue] or [Measures].CurrentMember is [Measures].[ParameterCaption] or [Measures].CurrentMember is [Measures].[ParameterLevel] or [Measures].[Is Visible]
When I created a Table control, I was able to read things like .Color, FormattedValue, etc. from SSAS. This was great, because I could control all existing and future reports' formatting via one change in the cube.
Now, I've decided that a Matrix control is superior for various reasons. However, I notice that I MUST apply an aggregate function to get a cell value...well, it appears that this means there's no longer any way for me to read my SSAS formatting.
I tried =First(Fields!DataValue.FormattedValue), =Max(...), etc.
I also tried it with the color property, etc. Nothing worked.
Are there any workarounds? I'd hate to embed the formatting into each of my 40 reports, because the user is likely to change his/her mind after the fact.
My team is using TFS for work item management and I'd like to provide a week schedule matrix that displays: - weekdays on the x-axis - persons on the y-axis - and the assigned workitems in the cell.
This works fine if there's only one WIT (work item) per day but some WIT's are shorter and thus the resource is assigned more than one a day. My dataset may return something like this:
Date Person WIT 28/3 Mark Task1 28/3 Mark Task2 28/3 Hank Task3 29/3 Mark Task2 29/3 Hank Task3
In this case I'd like the matrix to look something like this, i.e. the two tasks Task1 and Task2 in the same cell: Mark Hank 28/3 Task1 Task3 Task2 29/3 Task2 Task3
I've set up the matrix in the report but I only get the first item per day to show. Mark Hank 28/3 Task1 Task3 29/3 Task2 Task3
The default definition in the cell was "=First(Field!TaskName.Value)", so I figured I'd solved the issue by changing it to "=Field!TaskName.Value", but it didn't.
Hello Friends, I am creating a report in which I want to create group column using two field value. Is it possible to do so? We have a requirement in which we are fetching data from two different hierarchy.
A B C D E F G H I J K L 1 2 3....................4 5 M ............................. N ......................... O ....................... P
The report matrix look like the above one. The elements A,B,C are coming from one hierarchy and D,E,F,G,H,I,J,K are coming from other hierarchy. But i have created one data set to fetch the values for the report. But while creating the column group I am getting both two diff fields so I am not able to use it in single Column group and I want to use only one column group.
I am currently trying to figure out how to change the background colour of a cell when that row happens to be a subtotal
I have included a screen shot of my report design below:
(The link to the image is http://diagnostic.iipuk.co.uk/images/RS_Questions.JPG should the image not be displayed)
When the cell that is circled is on a subtotal row (i.e. is the subtotal of either the first subtotal (which I have called RegionTot) or the second subtotal (which I have called QCTotal) I want the background colour to change to black). This is so the user cannot see the subtotal for that column on the screen.
I have tried using the following expression in the background colour property for that cell (without success!):
The error that comes up states that report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope.
I have searched help and haven€™t managed to find anything!
Any help would be greatly appreciated!
Thanks
Marek Kluczynski Information Manager Investors in People UK
My data is like below:  ClassId ClassName  StudentId  Subject    SubjectId 1        ESL       12        English     20 1        ESL       13        Science     30 1        ESL       12        Social      40 1        ESL       12        Maths       50  Required output: parameters are Subject column valuesÂ
ClassId ClassName  TotalStudents  SubjectIds   1       ESL              1              20, 40, 50 1       ESL                     1              30  When one student takes multiple subjects then count student only once, so in the above data 12 is one student id takes multiple subjects so counted only once. TotalStudents value is 1  I did write below query:  Declare @subjectids string set @subjectids = '20,30,40,50'Â
-- will split @subjectids and store in temp table   Â
select classname, classid, Count(Distinct StudentId) from mytable where SubjectsIds in @subjectIds group by ClassId, ClassName, SubjectId,  but it gives me below output:  Â
ClassId ClassName  TotalStudents  SubjectIds  1       ESL        1              20 1       ESL        1              30 1       ESL        1              40 1       ESL        1              50Â
