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

Expression Editor On Custom Properties On Custom Data Flow Component

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

Expression Issue With Custom Data Flow Component And Custom Property

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

Custom Task - Custom Property Expression

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

Custom Code (Embedded Code) Question

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

Sort Expression As Store Procedure Parameter?

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

Table Items Custom Sort

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

Reporting Services :: Custom Sort Column Groups

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

Evaluate Expression In Custom Component

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

Custom Components - How To Evaluate A Property As An Expression?

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

Is Adding Custom Expressions Possible In The Expression Builder?

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

Changing Sort / Code Page When Upgrading

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

How To Protect From SQL Injection In ASP.NET And SQL 2005 For Custom Query Expression?

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

Building Expression Like Properties In Custom SSIS Tasks

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

Custom Task: How To Access/modify The Expression Collection Of A Package

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

Assign Expression Value Using Code

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

Enabling Expression Builder For Custom SSIS DataFlow Source Component

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

Using Custom Code

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

Custom Code....

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

Custom Code

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

What Custom Code Can Access

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

Is It Possible To Do A Redirect In The Custom Code?

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

Custom Code Question

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

Writing A Custom Code Example

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

Nested Ifs In Custom Code

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

Custom Code For A Parameter

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

Grouping And Custom Code

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

Bug With InScope() And Some Custom Code

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

Custom Code Spaces Being Cut Off

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

How Can I Throw A Custom Error Code?

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

Custom Code...For The Sake Of Argument

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

Execute Query Within Custom Code

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

Build Parameter From C#/Custom Code

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







Copyrights 2005-15 www.BigResource.com, All rights reserved