Problem Sorting Using Custom Code As The SortExpression
Jul 25, 2007
I have a report that contains a Chart called "SummaryChart" and a table called "DetailsTable"
the data for the summary chart comes from a data set called dataSourceSummary and the data for the details table comes from a data set called dataSourceDetails.
The summary chart presents counts of items broken down by a category and the details table shows the details of the items that make up the counts in summary chart.
I have a situation where I need to sort the data in the table in a non alpha-numeric way. So I have added a user sort item to the table column header and set the SortExpression to something like the following
where SortProvider is a C# class and GetUserSortOrder is a static method
the method essentially finds a row in a database, get the display order for that row and returns it.
I have tested this in another report and it works as expected.
Now finally to the problem:
What happens is when I click on the table heading, values from fields in the dataset for the chart are being passed into my method, followed by values from fields in the dataset for the table and then an exception is thrown.
The only difference between this report and ones that I have tested this on and it works is that this report has two dataregions (Chart and Table) and two datasets.
I have tried setting the ExpressionScope to DetailsTable and the SortTarget to dataSourceDetails but that results in the sort not working at all.
I would have thought that it should only be passed values from the data that it is sorting, especially considering that the data that is being passed in from the chart is a completely different field.
Has anyone else experienced this?
Has anyone got any ideas?
I have a parameter in my report called SortType and depending on the value of the parameter I want to sort by a particular column. I have tried doing this by using a custom function as follows:
Function SortTypeValue() As String If Report.Parameters!SortType.Value = "1" Then Return "=Fields!StockDesc.Value" else Return "=Fields!StockCode.Value" End If End Function
Then in the sorting tab I have entered in the expression field: =Code.SortTypeValue().
However this is not doing anything. The data is just displayed in the order it is taken from the database. Can this be done? If so can anybody please tell me what I am doing wrong?
I've made another topic before concerning this problem, but since it was really confusing, I will made one clearer (it was about orthodromic formula, in case you read it, but the problem change during the topic, so thats why im creating this new one too). I have a stored procedure with custom pagin method inside, and I want to sort my records on a fields I create myself (which will receive a different value for each record.) Now, I want to sort on this temporary field. And since this is a custom paging method I can choose between many page. Now, for the first page, it sorts fine. But when I choose a page above the first one, the sorting is not right (the results all are wrong). So my real question is: is it really possible to sort on a Temporary Field in a custom paging method (because I know I can do it without any problem on a real field from my table, it just doesnt work right when I try on a temporary field). I tried to solve my problem with this little SQL instruction, but it didnt give me any result yet: SELECT TOP 20 PK, test = field_value FROM Table WHERE PK not in (SELECT TOP 10 ad_id FROM Table ORDER BY ?) ORDER BY ? well thanks for taking the time to read this, any help woulb be appreciated.
I have some code that sorts a column logically even if it contains both numbers and text. But I've found that SQL converts certain characters automatically....
Here is the code...
SELECT * FROM TABLE
order by case when isnumeric(fieldname)=1 then right(replicate(' ', 10 ) + convert(varchar,convert(numeric(10),fieldname)),10) else fieldnameend --10 is the length of the field
The issue I've run across is that if my column contains the following strings(5D000,5E000,4E001) it thinks they are numeric then the THEN part of the condition causes a typecasting error....
I found out that any certain combinations of number + E or D + 0. Does anyone know a way to get around this? Try it out select isnumeric('5E0303') will return 1
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.
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?
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?
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) {
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.
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.
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.
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?
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:
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)
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.
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?
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?
HI all, I am having a problem while trying to create a list item through SSRS.The scenario:I have created a SSRS report which has custom code. the report has some parameters. The code accepts the parameters and tries to create a list item in moss. I have added the required references. This works fine as long as I work in VISUAL STUDIO IDE. When I deploy the report to report server it throws an error in the code.Can anybody help me in this...?Regards...
Hello, I am about out of hair from pulling it out. If someone could please show me what I'm doing wrong I would really appreciate it. I have this function for a SSRS 2005 report:
Public Function funAdditions(pFields As Fields) As Double if pFields !FA00902_AMOUNT.Value > 0 and pFields !FA00902_TRANSACCTTYPE.Value = 3 and pFields !FA00902_DEPRTODATE.Value > Parameters!BeginDate.Value and pFields !FA00902_DEPRTODATE.Value <= Parameters!CutOffDate.Value and NOT instr(pFields !FA00902_SOURCDOC.Value, "FACHG")=1 then return pFields !FA00902_AMOUNT.Value end else if pFields !FA00902_TRANSACCTTYPE.Value = 3 and pFields !FA00902_DEPRTODATE.Value > Parameters!BeginDate.Value and pFields !FA00902_DEPRTODATE.Value <= Parameters!CutOffDate.Value and instr(pFields !FA00902_SOURCDOC.Value, "FACHG")=1 then return pFields !FA00902_AMOUNT.Value end End Function
...and after much research cannot figure out the solution to this error:
[rsCompilerErrorInCode] There is an error on line 1 of custom code: [BC30201] Expression expected.
I'm new to SSRS so is there a syntax error I'm missing?
I created a class library which uses a web reference My SSRS report uses the class through its custom code I copied the dll & dll.config of the class library to the VS2005 private assembllies and the report works I copied the dll & dd.config to the reportserver bin and gave the assembly full trust by adding the necessary code group I even added a code group the give the dll.config file full trust as well Yet on Repost server, the report does not work Any ideas I suspect it is a permission problem
OK. I created a few functions in a Public Class called SSRSFunctions. I built it and deployed it to my desktop. What do I do now? Can it be included as standard functions in the function list within SSRS? Do I add a reference to the Class at the solution level? At the report level? If all I do is add a reference at the report level, I may as well add the functions to each report's code window, which is what I have been doing. I'd at least like to add a single reference to my solution if I can't get it added to the function list.
I have a custom assembly which has a strong name, and I have deployed it in the GAC. I have also raised its trust level to Full Trust.
I have added a reference to this assembly from report properties.
when I try to use it, I get the error
[BC30451] name 'myUtil' is not declared.
Here myUtil is the instance name of the my class. (All methods are instance methods in my class and there are no static methods).
I found many people facing this issue, and the solution involved copying of the assembly in some privateassemblies folder. However that should not be the case with me as I have the assembly in the GAC.
I'm trying to create a custom conflict resolver for SQL Server 2000 in C# but i can't seem to find any source code examples of a *.dll where i can actually see what needs to be done.
I created some custom code that will check to see if a file exists on a Windows Share.
Code Snippet Public Function FileExists(ByVal FileFullPath As String) As Boolean Try Dim f As New System.IO.FileInfo(FileFullPath) Return f.Exists Catch e As Exception Return e.Description End Try End Function
Code SnippetSystem.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.CodeAccessPermission.Demand() at System.IO.FileInfo..ctor(String fileName) at ReportExprHostImpl.CustomCodeProxy.FileExists(String FileFullPath) The action that failed was: Demand The type of the first permission that failed was: System.Security.Permissions.FileIOPermission The Zone of the assembly that failed was: MyComputer
I am trying to retrieve and manipulate the value of the stored procedure parameters in the following custom code:
Code Snippet Public Function ShowLoading(ByVal intOption as integer, byVal BasicLoading as string, byVal BasicHealthExtra as string, byVal RiderLoading as string, byVal RiderHealthExtra as string, byVal TotalAmount as string) As string
dim strRider as String dim strCompanyName as string strCompanyName = Fields!CompanyName.Value
select case strCompanyName case "and Plus Rider ": strRider = "Plus Rider" case "and Assist Rider ": strRider = "Assist Rider" end select return strRider
End Function
However, i got this error when I try to preview the report: An error occurred during local report processing, The definition of the report :
'/reportC' is invalid. There is an error on line 5 of custom code: [BC30469] Reference to a non-shared member requires an object reference.
Any help is greatly appreciated. Thanks in advance.
For the above function I added reference to the System.Data . The report works as desired when I execute in the VS 2005 IDE but when I deploy the report on the reporting server and execute it from there the function doesn€™t execute and returns me #Error.
Can any one provide some insight into it to as to how i can resolve this issue.
A simple question I hope. I have got a textbox on a report and I'm trying to populate it by calling a custom assembly. I know I can reference it directly in the textbox (this works) but I am trying to do this from the code block. The following code didn't work:
Protected Overrides Sub OnInit() ReportItems!textbox2.Value = POCCustomAssembly.CustAssembly.Hello() End Sub
The TextBox is called textbox2 and the custom assembly simply returns a string.
I get an error message "The is an error on line 1 of custom code: [BC30469] Reference to a non-shared member requires an object reference".
For setting different languages on the column haeders i need to access a dataset from a custom code function call. I want to pass a parameter to the function that will allow me to find a specific item in the dataset - like a lookup function. I cannot find a way to get access to the entire dataset and to iterate through its components.
Is there any solution? Every hint will be helpful!
I have a custom code that successfully query my database in SSRS 2005 report. However, I am hard coding its connection string (in the custom code) and I want it to be able to use the same connection string as the ones that the report use. This information is available in my registry setting, but if it is possible, I want to avoid that, since I aim for a quite simple solution. If I am able to get the connection string details at the report level or from within the custom code, it would be great.
Anybody have a suggestion? Anything would be really appreciated.
