Sort Expression - Custom Code
Apr 28, 2008
Hi,
I have a report that needs sorting on a column that is calculated. however, the calculation is so complex (iterative calculations) i've had to do that within my custom code. I am getting the results back well and good but i cannot perform a sort based on that.
My problem is: how do i write a custom sort expression function that will direct the column to sort my table based on say the value(s) returned from that function. I want for example:
Code Snippet
Public Function mySortExpression(byVal myIDorWhatever as Something) as CustomSortableExpression
' Perform some calculation
' Return some value(s)/array or anything that the column can appreciate and sort the table accordingly with
End Function
then in say the Interactive Sort Sort Expression, i'll have the code:
Code Snippet
=code.MyClass.mySortExpression(myID)
I hope i've made myself clear enough.
Cheers
View 3 Replies
ADVERTISEMENT
Aug 14, 2007
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
View 6 Replies
View Related
Apr 2, 2007
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 ?
View 5 Replies
View Related
Aug 16, 2006
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
View 3 Replies
View Related
Oct 16, 2007
Hi all,
Could someone tell me if custom code function can capture the event caused by a user? For example, onclick event on the rendered report?
Also, can custom code function alter the parameters of the report, or refresh the report?
Thanks.
View 2 Replies
View Related
Jul 23, 2005
Can I pass a sort expression into a store procedure as a parameter? Iwant to do sth like the following but do not know if it's possible.Maybe I need some tricks to work around?CREATE Procedure SelectAllRoles(@SortExpression varchar(20))ASIF @SortExpression <> ''THENSELECT Id, Name, Name2, LastUpdatedFROM [Role]ORDER BY @SortExpressionELSESELECT Id, Name, Name2, LastUpdatedFROM [Role]GOThanks!Zhu Ming
View 1 Replies
View Related
Feb 15, 2007
hello
i am currently trying to port a "Business Objects" report to SQL Server Reporting Services.
in B.O., there is a possibility to define the order of the table values by previewing the table values and moving them up or down.
Example : the dataset contains data for 3 items, so in the Custom Sort window, I will see these 3 items and I can order them by moving them up or down in a listbox.
is this possible to do in Reporting Services ?
View 9 Replies
View Related
Jun 3, 2015
Is it possible to do custom sort for a Column Group?
i.e. if the columns are coming out as A Â B Â C, is it possible to change it B Â C Â A Â (or anything else).Â
View 5 Replies
View Related
Dec 12, 2007
Is there a way to evaluate an expression (like the derived column component) in a custom component? If so where should I look first? Is there an example?
An extremely simple sample is to put in an expression and evaluate one column and then add that to another column to create a new column. i.e. newcolumn = column1 + column2.
I realize that the derived column allows me to do this but I'm trying to figure out if it is possible to do this in a custom component without having to build my own expression evaluator.
Thanks!
-Thames
View 5 Replies
View Related
Apr 11, 2008
Hi all,
I'm wondering if anyone's accomplished this before - I've been unable to find a whiff of info on how to do this so far.
I'm creating a custom component that I'd like to give a "Derived Column" type of ability to. By that, I mean I'd like to populate a property of my component with an expression (including references to input columns, package variables and functions) and be able to evaluate it at runtime - per row processed by the component.
I would also appreciate any information as to how to provide the interface to allow the user to build such an expression as well - is there a UI function in SSIS I can call to pop up the "expression builder"?
Thanks!
View 6 Replies
View Related
Mar 22, 2008
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
View 6 Replies
View Related
Jul 26, 2000
Any experience with upgrading from 6.5 to 7.0 and changing sort order / code page in the process? Does this cause problems or matter? We're trying to go with a standard and the servers I need to upgrade aren't at that code page standard.
Thanks..
View 2 Replies
View Related
Oct 3, 2006
How to Protect From SQL Injection in ASP.NET and SQL 2005 for custom query expression?In my project, I allow user to custom query expression through UI, such asstring queryCondition=' sale>20 and sale <100'string queryCondition=' createDate>"10/10/2005"'string queryCondition='Fullname like "%Paul%" '...I construct SQL based the queryCondition string, such as string mysql='select * from mytable where '+queryConditionI know it's very dangerous because of SQL Injection, but it's very convenient for user to custom query expressionCould you tell me how to do? many thanks!
View 13 Replies
View Related
Oct 22, 2007
Ive been using SSIS for a month or two and now find I need to create some custom tasks to perform some performance logging. in the the overloaded ProviderComponentProperties section I am trying to create a property which has the same look as the Expressions properties you find elsewhere (Little + on the left and a group of sub properties when expanded).
Ive have played with creating a IDTSCustomPropertyCollection90 collection then adding my sub properties to it but I cant seem to then add my new collection to the ComponentMetaData.CustomPropertiesCollection.
Im assuming the Expressions parameter is a collection added to the properties collection but I cant figure out how. Any help would be much appreciated.
View 4 Replies
View Related
Feb 6, 2007
Hi,
I created a Custom Task which it has a Property called ConfigFilePath. I'm overriding the Validate() method from Task. I want to throw an error if my property ConfigFilePath is empty and if the expression for this property is empty. So far, I can check if the property is empty but I don't see how I can access the Expressions Collection of my Custom Task.
Any thoughts? I'd appreciate your help.
View 5 Replies
View Related
Feb 7, 2007
I am trying to use a variable to set an attribute value of an SSIS task but I keep running into the 4000 character limit of the string variable. Not sure why the variable which is of .NET type String has this limit when it doesn't when you are in a .NET environment. Regardless, can anyone provide some sample code that I could use to do this in a script task? I am trying to set the QueryString property of the Data Mining Query Task. All help would be appreciated.
Thank you in advance.
View 5 Replies
View Related
Mar 13, 2007
Hi,
I have implemented a custom source component that can be used as the data source in the Data Flow task.
I have also created a custom UI for this component by using the IDtsComponentUI .
But my component does not have the capability of setting the custom properties via the DTS Variables using the Expression Builder.
I have looked around for samples on how to do this, but I can only find samples of how to do this for custom Control Tasks, i.e. IDtsTaskUI.
My question is, How can implement the Expression Builder in my custom Source component + custom Source UI. Or do you know of any samples which I can look at.
Thank you,
Jameel.
View 1 Replies
View Related
May 17, 2007
I am trying to use custom code on a report which is:
Public Function Percentage(decValueOne As Double, decValueTwo As Double) As Object
If (decValueOne = 0 Or decValueTwo = 0) Then
Percentage = 0
Else
Percentage = (decValueOne - decValueTwo)
End If
End Function
In my textbox I have:
=Code.Percentage(SUM(Fields!PreviousYTDExpenseAmount.Value, "Template_OutputData_Sales"), SUM(Fields!PreviousYTDExpenseAmount.Value, "Template_OutputData_CGS"))
When I run the report in design mode, I get the "#Error" on that report field.
Any help with why this is happening would be greatly appreciated!
View 2 Replies
View Related
Mar 21, 2007
Hey guys I have a problem I am trying to solve using custom code but I am open to other solutions.
Situation: I have multiple reports that the user can see. Example (Move In Corp. report, Move In Region report,Move In Division report, Mid Year Corp. report, Mid Year Region report,Mid Year Division report etc.) I have a stored procedure that returns all the information needed based upon the UserID being passed from the report to the stored procedure.
Goal: When the user logs in I want to be able to foward the user to a specific report based upon their userid (global in RS). I currently have three textboxes(Move In, Mid Year, Year End) with navigation enabled to jump to another report. The report to which the user is passed to is determined by an expression which is something like this for the Move In text box =IIF(Fields!orgtypeid.Value = 1,"Move In Corp","Move In Region"). The orgtypeid.value is returned by the stored procedure. I want to be able to forward the user a specific Move In report (Move In Corp, Move In Region, etc) without having the user select the textbox.
Any suggestions are greatly appreciated.
View 3 Replies
View Related
Oct 24, 2007
Hello, this is probably a pretty easy one. I have made a report, and added custom code in the code tab on the report properties. But when I type code.whatever in a text box expression my function does not show. Does anyone know what I am doing wrong?
Thanks
View 3 Replies
View Related
Jun 8, 2007
I am trying to find ways around the limitation of not being able to resize columns based on the text input. I am dynamically setting what values a column is getting from my query. I am wondering if I can access the table (and thus, the columns) from within the custom code.
For example:
Public Function Whynot() As Integer
Report.table1.TableColumn1.Width = ....
table1.TableColumn.Width = ....
Return 0
End Function
Neither of these seem to work, anyone else have any ideas?
Thanks
View 2 Replies
View Related
Jun 11, 2007
Is it possible to do a redirect to an arbitrary page in by using custom code?
eg.
Public sub myRedirect()
response.redirect(http://www.microsoft.com)
End sub
/Alex
View 1 Replies
View Related
Feb 21, 2008
I developed a ConnectionString assembly in custom code a while back. Now it won't work. I've changed boxes since. My question is do I have to have SQL Server and Reporting Services on my development machine to run Custom Code assemblies?
I put my dll in the PublicAssemblies folder. I see that I need to put my dll in C:Program FilesMicrosoft SQL Server80ToolsReport Designer but I don't have that folder.
When I try and set the ConnectionString to "=ReportingLibrary.ReportingLibraryFunctions.ConnectionString(User!UserID)", I get "Unrecognized Identifier." I checked the reference and it was still there from when the project was working before.
Code Snippet
using System;
using System.Security.Permissions;
using System.Configuration;
namespace ReportingLibrary
{
public class ReportingLibraryFunctions
{
public static string ConnectionString(string userName)
{
System.Data.SqlClient.SqlClientPermission oPerm = new System.Data.SqlClient.SqlClientPermission(PermissionState.Unrestricted);
oPerm.Assert();
string catalog;
string query;
string connectstring;
string datasrc = String.Format("Data Source={0};", ConfigurationManager.AppSettings["DataSource"]);
string userpwd = String.Format("User ID={0};Password={1};", ConfigurationManager.AppSettings["DBUsr"], ConfigurationManager.AppSettings["DBPwd"]);
query = "SELECT TOP 1 MyAcctKey FROM dbo.MyUserAcct WHERE UserName=@username;";
connectstring = String.Format("{0}Initial Catalog=MyMaster;", datasrc);
System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(String.Format("{0}{1}", datasrc, userpwd));
connection.Open();
System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(query, connection);
command.Parameters.Add("@username", System.Data.SqlDbType.VarChar);
command.Parameters["@username"].Value = userName;
catalog = String.Format("MyDb_{0};", command.ExecuteScalar());
connectstring = String.Format("{0}Initial Catalog={1}", datasrc, catalog);
connection.Close();
return connectstring;
}
}
}
View 9 Replies
View Related
Oct 19, 2007
I have been stuck in a problem for a while now. I am trying to count the number of groups. I tried soo many things and nothing worked . Now I want to try to write a function that does the counting for me.
Can somebody write me an example of a cutom code that can be called from expression.
So my custome code should be something like that
Public int CountFun(int counter)
{
Return counter= counter +1;
}
Also how do I call this method from my expression?
i am going to call it from a field inside the groups, so each time the group is implemented i add one to the counter.
thanks
View 4 Replies
View Related
Sep 19, 2007
Is it not possible to have nested ifs in a custom code function? I keep getting an error message when I try it.
View 1 Replies
View Related
Oct 3, 2006
I'm using a GAC-installed assembly as part of a Reporting Services
(2005) report. The code does not need any permissions beyond execution.
All it does it take today's date and calculate last week's start and
end date. It's all just datetime manipulations. All methods are static.
In
the VS2005 report designer, I can do everything fine. The code runs as
expected and defaults the parameters to the right date.
When I
upload the rdl to the Report Server, I get the message "Error during
processing of €˜Start€™ report parameter.
(rsReportParameterProcessingError) ". If I override the "Start"
parameter, it doesn't give me that error anymore. Start should be the
Sunday of the previous week.
I have deployed the signed assembly to the GAC on the report server as well as the bin folder for reporting services. Because it doesn't need anything beyond execution, I shouldn't have to modify any config files, right? I have already restarted IIS & the Reporting Services service.
What am I missing?
View 10 Replies
View Related
Jun 25, 2007
Hello everyone,
I've got an issue where I want to sum the group values and not the details, the reason is because I am hiding duplicate records. Here's how my Layout is setup.
TH
GH1 (hidden)
GH2 (hidden)
Det (hidden)
GF2 =Code.AddValue(Fields!Quantity.Value * Fieds!Cost.Value)
GF1 =Code.ShowAndResetSubTotal()
TF =Code.GrandTotal
I have the following in my Code window.
Dim Public SubTotal as Decimal
Dim Public GrandTotal as Decimal
Function ShowAndResetSubTotal() as Decimal
ShowAndResetSubTotal = SubTotal
SubTotal = 0
End Function
Function AddValue(newValue as decimal) as Decimal
SubTotal += newValue
GrandTotal += newValue
AddValue = newValue
End Function
This gives me incorrect results and I can't figure out why. Here's how it shows on my report:
Part Number
Quantity
Cost
Regular Subtotal Method
Using Custom Code
Part 1
4,000
1.49
$5,947.20
Customer 1
$11,894.40
$0.00
Part 2
10
1.01
$10.07
Customer 2
$50.34
$5,947.20
Part 3
1
0.44
$0.44
Part 4
6,050
0.25
$1,530.41
Part 5
0
1.25
$0.00
Part 6
0
1.23
$0.00
Customer 3
$42,851.86
$10.07
Part 7
16,250
0.24
$3,922.59
Customer 4
$19,612.94
$1,530.85
Part 8
17,250
0.38
$6,544.82
Part 9
27,225
0.20
$5,380.20
Customer 5
$66,891.69
$3,922.59
Grand Total
$141,301.23
$0.00
The issues brought up from the duplicates is shown in the "Regular Subtotal Method" column (there are 2 detail records for Customer 1-Part 1, which is why it is doubled). I can't use a distinct on the SQL query because there are other fields (not shown) on the report that are different.
As you can see, the GF1 (Customer #) shows the subtotal from the previous group, and the Table Footer (Grand Total) shows 0. Why is this?
Jarret
View 2 Replies
View Related
May 30, 2006
Hi guys,
i was developing some custom code to do a running total in a matrix, and i have noticed some odd behaviour with the InScope function. I am doing year on year reporting, so i have two row groups on my matrix: the first is on month (matrix2_Calendar_Month), the second on year (matrix2_Calendar_Year).
I needed to total the number of days covered by the months i was reporting on, so i wrote some very standard code to do this, along with an expression in that column of the matrix:
=IIf(
InScope("matrix2_Calendar_Year"),
CStr( Round( Sum(Fields!Sales.Value / (24 * Code.AddDays( CStr(Fields!Calendar_Month.Value), CInt(Fields!Calendar_Year.Value))), 2)) ,
Code.getBounds()
)
Code.AddDays() calculates and returns the number of days in the month of that year on that row. Code.getBounds simply returns the lower and upper bounds of the array, plus its contents (so i can inspect them). This is what is returned in the report:
Month / Year
Sales
Capacity
% Capacity
Avg $/h
February
2006
3842
7706
49.86%
2.86
2007
0
0
0.00%
0
March
2006
4949
8692
56.94%
3.33
2007
0
0
0.00%
0
April
2006
5160
8154
63.28%
3.58
2007
0
0
0.00%
0
May
2006
3309
8348
39.64%
2.22
2007
0
0
0.00%
0
Total
17259
32900
52.46%
0-8*28,28,31,31,30,30,31,31,28
If you look at the output in the total row, you will see that Code.AddDays() has been called one extra time at the end, with Feb 2006 as its parameters, thus adding an extra 28 days to the running total. Why is Code.AddDays called on the total row, when i should be out of the scope of both the row groups? (Note: this happens for whichever row group i use in the InScope check in the expression).
Here is the custom code used for all this:
Dim numDays()
Public Function AddDays(ByVal month As String, ByVal year As Integer) As Integer
Dim thisMonth As String
Dim upper As Integer
upper = 0
On Error Resume Next
upper = UBound(numDays) + 1
ReDim Preserve numDays(upper)
thisMonth = CStr(year) & "-" & month & "-01"
numDays(upper) = DateDiff("d", CDate(thisMonth), DateAdd("m", 1, CDate(thisMonth)))
AddDays = numDays(upper)
End Function
Public Function TotalDays() As Integer
Dim lower As Integer
Dim upper As Integer
lower = 0
upper = 0
On Error Resume Next
lower = LBound(numDays)
upper = UBound(numDays)
TotalDays = 0
Dim ii As Integer
For ii = lower To upper
TotalDays = TotalDays + CInt(numDays(ii))
Next
End Function
public function getBounds() as string
getBounds = Cstr(LBound(numDays)) & "-" & CStr(UBound(numDays)) & "*" & Join(numDays, ",")
end function
sluggy
View 5 Replies
View Related
May 6, 2008
I have a custom C# assembly that my report is calling with embedded code that is called from fields in a reporting services table. The value comes back fine, but I need some of the values to be indented, so I have tried all of the following and every time the spaces are cut off. Spaces work when not using embedded code.
I tried the following in the table cell
=Space(3)+Code.<<String Function Name>>
and I tried
=" "+Code.<<String Function Name>>
Then I tried the code in the assembly and in the embedded code with the same result. No matter where I put the spaces they get cut off. Any ideas? I tried characters other than spaces and they came back fine.
Thanks,
Adam
View 5 Replies
View Related
Jun 26, 2006
I'm trying to run a DTS package, and cant find an easy direct way to run it. It seems like the easiest solution would be to throw a custom error- then the server notices the error and runs a custom job, and the custom job runs the DTS. This is a roundabout way, but seems like it would be the simplest solution.Anyways, how do I throw the error in my code? Do I just write a throw 3829 statement? Also maybe this is a very bad way to do this- any suggestions?
View 2 Replies
View Related
Oct 3, 2006
Is it possible to write custom code that achieves the same thing as an aggregate function?
ex: Calculating the sum of a group? (with out using the sum( field!one.value) function)
if I had a group that grouped on name.
Name Number
header Fields!FirstName.Value ************
details Fields!Number.Value
footer
Can i write a custom code function (or functions) that will get the sume of the numbers in that group.
Thanks in advance.
View 7 Replies
View Related
Apr 2, 2008
Hi,
Is it possible to connect to a database and fetch data from it from within custom code inside a report?
Appreciate any help.
Regards,
Asim.
View 4 Replies
View Related
Jun 6, 2007
Hello,
Is it possible to add a multivalued parameter to a report from C#? I already have a C# .dll which I reference in the report. If so, could someone give some hints on how to do it?
As I searched the net for an answer to this, I came across ReportExecutionService. It looks like it's what I want, but I cant find the dll to reference in order to used this class. Does anyone know?
If it is not possible to do this from C#, then can someone please give an example on how to do it from Custom Code?
Thanks
/Peter
View 1 Replies
View Related