I have a table with a Labor Category Group containing 5 categories (1,2, 3, 4 and 5). I need to add a subtotal for categories 1, 2 and 3. I know how to add the subtotal for all 5 but not for a subset of them. How do I do this?
Thanks
Dean
Hi,
I am using a matrix to report, and there is 2 rowgroups and onde column group. It looks like this:
T1 T2 T3 T4
type1 A 9 3 2 4
B 6
type2 A 1 2 3
B 2 4 1 7
But I need to sum only 2 columns of the report (columns T2 and T3):
T1 T2 T3 T4 T2+T3
type1 A 9 3 2 4 5
B 6 6
C
type2 A 1 2 3 2
B 2 4 1 7 5
C 8
And I do not know how to create this sum column, only the default sobtotal column of a matrix.
I tried to do this sum in my datasource, but with no success too.
I also tried to use a table, but the columns must be create automatically from database data and I couldn't do it.
Another issue is: how to make line C appear even with only null or zero numbers?
Any ideas?
I am new with reporting services, so please be clear and detailed.
Thanks a lot!
Hello Friends,
I have created a report using SSRS and in that report I am using group rows in one of the matrix. When I tried to display the subtotal of that group row by using the SSRS in-built feature the subtotal column is coming at the last of the matrix columns but the subtotal its showing is wrong . Its just giving the value of the first columns value.
Can anyone help me on this issue.
Thanks & Regards
Shivanandan Gupta
What I want to accomplish is that at design time the designer can enter a value for some custom property on my custom task and that this value is accessed at executing time.
View 10 Replies View RelatedWe have a table with 2 columns 'OrderNo' and 'Amount' as below
ORDERNO | AMOUNT
1D1ZX000 | 9262.5
1D1ZX001 | 9000.0
1D1ZX001 | 9000.0
1D1ZX002 | 10000
1D1ZX003 | 1000
1D1ZX003 | 200.50
1D1ZX003 | 100.50
1D1ZX004 | 500.0
1D1ZX004 | 1000
1D1ZX004 | 2000
1D1ZX004 | 1000
as per my client requirement we need subtotal of 'Amount' group by 'OrderNo'. column so am writing a select statement with WHERE condition and I would like to have another column called SUBTOTAL in the result set (select statement result) with subtotals for that order Number as below
ORDERNO | AMOUNT | SubTotal
1D1ZX000 | 9262.5 | 9262.5
1D1ZX001 | 9000.0 | 18000
1D1ZX001 | 9000.0 | 18000
1D1ZX002 | 10000 | 10000
1D1ZX003 | 1000.0 | 3001
1D1ZX003 | 2000.5 | 3001
1D1ZX003 | 1000.5 | 3001
1D1ZX004 | 500.00 | 4500
1D1ZX004 | 1000.0 | 4500
1D1ZX004 | 2000.0 | 4500
1D1ZX004 | 1000.0 | 4500
Is there a way to expand the RDL to add custom nodes?
I read here there is a <Custom> element that can be put inside the <Report> node, but it does not work. I get all kinds of Scheme errors.
http://msdn2.microsoft.com/en-us/library/ms153972.aspx
I get the following errors when I add <Custom> element inside <Report>, which is acceptable according the the MSDN link above.
Deserialization failed: The element 'Report' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition' has invalid child element 'Custom' in namespace 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition'. List of possible elements expected: 'http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinitionescription http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Author http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:AutoRefresh http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinitionataSources http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinitionataSets http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Body http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:ReportParameters http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition:Code http://schemas.microsoft.com/sqlserver/reporting/2005/... Line 3, position 4.
Does anyone know how to get this to work, or know where there might be some information about this online? I am coming up dry.
Thanks.
Hi,
I was trying the SSIS programming samples,that comes with installation of SQL Server 2005, I want to load the ChangeCase DLL in the toolbox.
The readMe file gives the direction. It does not work. It says you will see component in once you see DataFlow Task button( clicking Choose Items in the tools box).
I cannot load the custom component to the toolbox, Can I get some help.
Thank you,
Hi,
I am interested in adding custom buttons to the parameters toolbox (under "View Report"). I'm trying to provde a way for users viewing the report to go "Back" or up a level (in the drill through reports). Does anyone have any information on this topic, or know if it's even possible?
Thanks in advance,
-Brandon
Hi,
I am building a custom component have a IDTSCustomProperty90 property that can take the value 'True' or 'False'.
Depending on its setting, I want to include (or not include) a column in the output.
Any advice on how to go about doing this (with some sample code) would be much appreciated!
Here's how I'm declaring the property in ProvideComponentProperties()
IDTSCustomProperty90 IncludeErrorDesc = ComponentMetaData.CustomPropertyCollection.New();
IncludeErrorDesc.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE;
IncludeErrorDesc.Name = "Some Name";
IncludeErrorDesc.TypeConverter = typeof(Boolean).AssemblyQualifiedName;
IncludeErrorDesc.Value = Convert.ToBoolean(false);
Thanks in advance
-Jamie
I would like to add additional string functions and other types of functions to the expression builder in SQL Server Integration Services. Right now the list of functions is relatively limited to such things as FINDSTRING, RIGHT, LEN, etc.
Is this possible?
Matt
I'm building a custom transform component. I want to mark some input columns as keys for deduplicating. In a similar way to the provided Sort component, I want to check those columns and allow pass-throughs (or not) for the others - so next to each input column name I need two checkboxes (1:use for dedupe; 2:include in output if 1 not checked). If a column is checked for use in the dedupe, I want some other attributes to be shown indicating how it will be used. How do I display the checkboxes to let users select which columns to include for deduplication, and then how do I add further attributes underneath (copying the Sort component's look) for selection?
Thanks in advance for guidance and pointers on this.
Hi all,
I saw a couple of other posts here on this topic, but none quite got to my issue.
I'm trying to add error output to a custom source component (not a script, a custom component). The samples all seem to deal with transform components, and my issues seem to be unique to source components.
I have the following code related to error handling ...
Public Overloads Overrides Sub ProvideComponentProperties()
...
Dim output As IDTSOutput90 = ComponentMetaData.OutputCollection.New
output.Name = OUTPUTCOLUMNNAME
output.ExternalMetadataColumnCollection.IsUsed = True
ComponentMetaData.UsesDispositions = True
output.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed
output.ErrorOrTruncationOperation = "Something got truncated or blew up"
Dim errorOutput As IDTSOutput90 = ComponentMetaData.OutputCollection.New
errorOutput.Name = ERRORCOLUMNNAME
errorOutput.IsErrorOut = True
...
End Sub
Public Overloads Overrides Sub ReinitializeMetaData()
Dim output As IDTSOutput90 = ComponentMetaData.OutputCollection(OUTPUTCOLUMNNAME)
Dim outColumn As IDTSOutputColumn90 = output.OutputColumnCollection.New
outColumn.Name = strName
outColumn.SetDataTypeProperties(DataType.DT_I4, 0, 0, 0, 0)
Dim metadataColumn As IDTSExternalMetadataColumn90 = output.ExternalMetadataColumnCollection.New
metadataColumn.Name = outColumn.Name
metadataColumn.DataType = outColumn.DataType
metadataColumn.Precision = outColumn.Precision
metadataColumn.Length = outColumn.Length
metadataColumn.Scale = outColumn.Scale
metadataColumn.CodePage = outColumn.CodePage
outColumn.ExternalMetadataColumnID = metadataColumn.ID
outColumn.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed
outColumn.ErrorOrTruncationOperation = "Something Truncated!"
outColumn.TruncationRowDisposition = DTSRowDisposition.RD_NotUsed
Dim errorOutput As IDTSOutput90 = ComponentMetaData.OutputCollection(ERRORCOLUMNNAME)
Dim errorColumn As IDTSOutputColumn90 = errorOutput.OutputColumnCollection.New
errorColumn.Name = outColumn.Name
errorColumn.SetDataTypeProperties(DataType.DT_I4, 0, 0, 0, 0)
...
End Sub
The confusions I have are:
a) the stock advanced properties editor (I haven't provided a custom one) doesn't seem to "realize" that I have an error output, so provides no method to configure. I am believing it would need to know which Output columns can have their error/truncation redirected. I'd have thought setting ErrorRowDisposition on my output column would have been enough to trigger this ??
b) since I don't have any means of configuring, not surprisingly, when I try to connect my error output, designer complains that, "Ths error output cannot receive any error rows. This occurs for several reasons: Input columns or output columns are not yet defined. Error handling is not supported by the component. Error handling is not configured for the component."
c) UsesDispoistions would seem to be appropriate only for a transform component
Thanks for reading, and appreciate any help or pointers.
Bill
A month or so ago I instigated this thread- http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=243117&SiteID=1 which talked about how to conditionally add a column to my component depending on the value of a custom property. If the custom property is TRUE then the column should appear in the output (and vice versa).
Bob Bojanic said I should use the SetComponentProperty() method to do this and that is working pretty well. However, it bothers me that SetComponentProperty() could be called, the column will then be added, and then the package developer could press 'Cancel'. In this instance the value of my custom property would be inconsistent with the presence of the extra column.
How do you get around that?
Thanks
Jamie
Hi All,
I am new to SSIS. I am working in adding SSIS components programmatically. I have added Data flow task, Lookup transformation, OLEDB Source and OLEDB Destination.
Now, i am facing problems in adding Custom Script tranformation component programatically. Please help me out.
Venkatesh.
My report has two groups, company and error type for each company.
Company1
Functional Error
Data Error
Other Error
My goal is to show the subtotals for each error types when I show Company's subtotal/total; in the group 1 footer area.
Total for Functional Error:
Total for Data Error:
Total for Company1:
Please help me in figuring out how to do that.
Thank you in advance for your help.
I am trying to add a custom dll(IncrementTask avalilable in SQL Server DTS Samples)
as an executable(Task) to an SSIS package.
The following console application consumes the Custom dll:
using System;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.FileSystemTask;
using Microsoft.SqlServer.Dts.Samples;
using Microsoft.Samples.SqlServer.Dts;
namespace Microsoft.SqlServer.Dts.Samples
{
class Program
{
static void Main(string[] args)
{
Package p = new Package();
// Add a File System task to the package.
//This Line of code works fine.
//TaskHost execPkghost = (TaskHost)p.Executables.Add("STOCKQLTask");
//This line throws the following error: Object reference not set to an instance of an object.
TaskHost execPkghost = (TaskHost)p.Executables.Add("Microsoft.Samples.SqlServer.Dts.IncrementTaskUI,IncrementTaskCS,
Version=1.0.0.0,Culture=Neutral,PublicKeyToken=b23be0feabed1355");
TaskHost thFileSystemTask = execPkghost as TaskHost;
// Iterate through the package Executables collection.
Executables pExecs = p.Executables;
foreach (Executable pExec in pExecs)
{
TaskHost taskHost = (TaskHost)pExec;
Console.WriteLine("Type {0}", taskHost.InnerObject.ToString());
}
Console.Read();
}
}
}
I am getting a runtime error that "object reference has not been set to an instance of the object".
Any help on this is higly appreciated.
We install SQL Express 2005 with a custom named instance. Since a named instance uses dynamic ports, how can I add this named instance to the Windows Firewall exception list? Previously with MSDE 2000 we installed as default, then I added port 1433 to the Firewall exception list.
Is there a way to install SQL Express to a static port (programmatically)? Or, is there a better method, like adding the SQL custom named instance service to the firewall exception list?
I am writing my first custom SSIS task and I can see that, if I put a public property into the task, I see that property in the standard Properties window. If I add a property of type String, I can put a value in that property, in the task code, and when I instantiate the task in a package, I can see the value I entered.
To try and get a drop down list property in the Properties window, I declared a property of type Combobox, and indeed a drop down list appears in that property.
My problem is trying to get values in that property. I have used the test items:
   Property.Items.add("Fred")
   Property.Items.Add("Jim")
But I do not see the values in the drop down list. All I do see is one item with a value of "(none)".
I have created a custom library(CodeLibrary) which internally references the dlls Microsoft.TeamFoundation.Client and Microsoft.TeamFoundation.WorkItemTracking.Client.
I added this custom Dll codelibrary.dll to my SSRS report. and the expression of one of the field asÂ
=codelibrary.codefunction.GetValue(1000) Â
codefunction is the class and GetValue is the method.
When I preview the report, I get the error "Error while loading code module:
'CodeLibrary,Version=1.0.0.0,Culture=neutral,PublicKeyToken=null'. Could not load file or assembly 'CodeLibrary,Version1.0.0.0, Culture=neutral,PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified."
I am using VS2013, I have placed the custom library DLL in the path
C:Program FilesMicrosoft Visual Studio 12.0Common7IDEPrivateAssemblies
I have tested the custom library with a WPF application and it works fine.
I am not able to figure out what is causing this error.
Hi,
I am creating a custom transformation component, and a custom user interface for that component.
In
my custom UI, I want to show the custom properties, and allow users to
edit these properties similar to how the advanced editor shows the
properties.
I know in my UI I need to create a "Property Grid".
In
the properties of this grid, I can select the object I want to display
data for, however, the only objects that appear are the objects that I
have already created within this UI, and not the actual component
object with the custom properties.
How do I go about getting the properties for my transformation component listed in this property grid?
I am writing in C#.
Hi,
I've created a Custom Data Flow Component and added some Custom Properties.
I want the user to set the contents using an expression. I did some research and come up with the folowing:
Code Snippet
IDTSCustomProperty90 SourceTableProperty = ComponentMetaData.CustomPropertyCollection.New();
SourceTableProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
SourceTableProperty.Name = "SourceTable";
But it doesn't work, if I enter @[System:ackageName] in the field. It comes out "@[System:ackageName]" instead of the actual package name.
I'm also unable to find how I can tell the designer to show the Expression editor. I would like to see the elipses (...) next to my field.
Any help would be greatly appreciated!
Thank you
Hi,
I'm trying to enable Expression for a custom property in my custom data flow component.
Here is the code I wrote to declare the custom property:
public override void ProvideComponentProperties()
{
ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
RemoveAllInputsOutputsAndCustomProperties();
IDTSCustomProperty90 prop = ComponentMetaData.CustomPropertyCollection.New();
prop.Name = "MyProperty";
prop.Description = "My property description";
prop.Value = string.Empty;
prop.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
...
}
In design mode, I can assign an expression to my custom property, but it get evaluated in design mode and not in runtime
Here is my expression (a file name based on a date contained in a user variable):
"DB" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + "\" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + ".VER"
@[User::varCurrentDate] is a DateTime variable and is assign to 0 at design time
So the expression is evaluated as: "DB189912189912.VER".
My package contains 2 data flow.
At runtime,
The first one is responsible to set a valid date in @[User::varCurrentDate] variable. (the date is 2007-01-15)
The second one contains my custom data flow component with my custom property that was set to an expression at design time
When my component get executed, my custom property value is still "DB189912189912.VER" and I expected "DB200701200701.VER"
Any idea ?
I am writing a custom task that has some custom properties. I would like to parameterize these properties i.e. read from a varaible, so I can change these variables from a config file during runtime.
I read the documentation and it says if we set the ExpressionType to CPET_NOTIFY, it should work, but it does not seem to work. Not sure if I am missing anything. Can someone please help me?
This is what I did in the custom task
customProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
In the Editor of my custom task, under custom properties section, I expected a button with 3 dots, to click & pop-up so we can specify the expression or at least so it evaluates the variables if we give @[User::VaraibleName]
Any help on this will be very much appreciated.
Thanks
Is there any way or option to get the all columns of dataset added to table when we add a table in data region. It will take lot of time to add one by one and also there are chances to add one column ore than once.
View 7 Replies View RelatedHow can something like this be done in reporting services? I'm having trouble to get the totals, because if I do Sum(Fields!Yes.Value) + Sum(Fields!No.Value) it will just give me the overall total (133).
Please help guys. I tried with the matrix but it always breaks or just doesn't want to give me the subtotals.
Description
Yes
No
Subtotal
Studies
50
22
72
Works
28
33
61
€¦
€¦
€¦
€¦
I want to create a subtotal for my results in my table, for example:
=Fields!Animals.Value =Fields!Result.Value
Dogs 3
Cats 4
bird 6
SubTotal 13
If i use =Sum(Fields!Result.Value) i have a error #Error, i don't know how do ir, Someone can help me?
Thank you a lot!
Hello,
I have a matrix with subtotal.
I want to hide or show the subtotal by a parameter.
The header of the subtotal is not a problem since it have the "visibility" property.
The problem is that when I'm clicking on the green rectangle to go to the subtotal area itself - It doesn't have the "visibility" property.
So I don't see the header but I see the subtotal date itself.
Any idea on how to resolve this?
Thanks.
I need to total filtered values each time the filter criteria changes.
So lets say I change the month to JAN then in addition to the subtotals for each category I need to get a Grand Total.
Thanks much.
SELECT Location, SUM(month_est) AS SubTot_Month_Est, SUM(actual) AS SubTot_Actual, mnth, yr
FROM dbo.meetings
GROUP BY Location, mnth, yr
Hi,WITH SQL SERVER QUETY, I have output likePRJ item qty=================P1 I1 10P1 I1 20P1 I2 10P2 I2 10P2 I3 10================I WANT TO MANIPULATE IT LIKE THISPRJ item qty=================P1 I1 30P1 I2 10---------------P1 * 40---------------P2 I2 10P2 I3 10---------------P2 * 20---------------===============PRJ * 60===============SHOW DATA AND SUBTOTAL AND GRAND TOTAL IN ONE RESULTSETCAN ANYBODY HELPTHANKS IN ADV.T.S.NEGI
View 2 Replies View Related
hi,
i am using SQL server reporting services 2000. in that i am using a matrix and one column group and a one row group. i want to calculate total of row and column group and i insert a subtotal at for row and column group.
butits calculation wrong total just picking first row and first column in total.
Application
ABC
DEF
123
456
Total
stat
17
15
3
6
17
Config
15
0
12
0
15
CSP - IMS
11
10
0
0
11
Portalm
10
8
8
0
10
Total
17
15
3
6
17
can anyone help me in this regards.
Regards,
Faisal Saleem
Hi
I have the following matrix
CA AZ
No surplus 11 5
Surplus 12 10
Zotal 100 50
Totlal 123 65
I want that only (No surplus and Surplus) sum include in total Can I apply This condition on Subtotal.
thanks in advance
Hi Everyone,
I am trying to get something like this using matrix in reporting services
SE
CP
CG
Total
Qty
Value
Qty
Value
Qty
Value
Qty
Value
GST
Net
7/07/2006
124
233
552
525
141
2544
563
4215
321
4536
8/07/2006
1245
211
11
142
555
1444
1987
1854
124
1978
The problem is, in the total group, the GST value and Net ( Net = Value + GST) can't added into the total group in the matrix.
Can somebody help... Thanks a lot.
Is there a way to make the subtotal column appear on every page when the matrix spans multiple pages?
View 1 Replies View Related