Hello,
I'd like to use Globals!ReportName and User!Language in custom code like this:
Function Localize(ByVal key As String) As String
Return ReportUtil.Localizer.Localize(Globals!ReportName, key, User!Language)
End Function
If I preview the report I get an error message saying that there is error on line 2 of custom code.
Is it possible to reach report variables in custom code?
Thanks!
Werner
i am using Microsoft Sql Server 2008 Report buider 3.0. and i generated report and that report how to use custom variables.
View 2 Replies View RelatedHello,
When I run my report from within visual studio 2005 it generates just fine.
However, when I run the report from the reporting services local web site I get the following error. What do I need to do to fix this (temporarily turning off .net security uusing caspol didn't work).
An error occurred while executing OnInit: Request for the permission of type 'System.Security.Permissions.EnvironmentPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed
I have a balance sheet report developed and working. What I need to do is add the ability to click (or double-click) on a number and have a separate report (drill-down) open with the detail that makes up that number. I've researched drill-through reports, sub reports, etc. but can't find a way to do what I want.
I'm now thinking that I need to add custom code in the report properties, code window to do this. I'm hoping that there is a VB method I can use to call this report and pass a parameter. Pseudo-code for this function would look something like this: RunReport("DrillDownReport", "Parameter").
This seems like it should be pretty easy but I don't know what the function is in VB for running an SSRS report (if there is one).
Is this possible or am I barking up the wrong code tree?
Hi all,
I am trying to use the custom code in the report but I don't think I am understanding how this is being used.
I have a function to get starting months for a report parameter.
The function is below:-
--------------------------------------------------------------
Shared Function GetStartingMonths() as String
dim strDefault as string
dim CurrentMonth as String
Dim SqlString as String
'strDefault = Month(Now) & "/1/" & Year(Now)
CurrentMonth = "5/1/2002"
Do While CDate(CurrentMonth) <= Now
SqlString = SqlString + "Select " & CurrentMonth & " as value, " & MonthName(Month(CurrentMonth)) & " " & Year(CurrentMonth) & " as MonthYear"
CurrentMonth = dateadd("m",1,CDate(CurrentMonth))
if Cdate(CurrentMonth) = Now then
Exit Do
else
sqlString = SqlString & " Union "
end if
Loop
return SqlString
End Function
---------------------------------------------------------------
what i am trying to do here, and hopefully produce a sql string that would fill my dataset of dates and their representation.
In the dataset, I had put the following expression
=Code.GetStartingMonths()
However, I can't seem to get the parameter to display the dates. shows up as disabled in my report.
Am I doing something wrong here or is there a better way to doing this ?
Additionally, I was wondering whether there is a better SQL code that would achieve the same thing I am doing ?
thanks !
Bernard Ong
Hi All,
I want to get the Font,Background colors of the report item(Textbox) changed in Runtime,According to the value in the Textbox. The idea is to get this done by using the Same Custom code for getting both the Foreground,Background colors, I thought to implement this with flag, But I dont know how to do this..
Reporting experts ..please help me with this issue.Thanks in Advance
-Mahendra
I wrong a custom code in my report - Public shared Function Myfunc and in the header textbox - I tried to reference this code = Code.Myfunc.... the Myfunc gives an error Unrecognized identifier.The code is written in the report properties = Code Tab.
View 4 Replies View Related
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.
Hello,
I found one post from the past asking this question, but didn't see an answer, so I am hoping someone can shed some more light on it:
I am writing a custom log provider in C#. Is it possible give this custom log provider access to the variables collection (via a variabledispenser object)? First glance at the log provider classes would tell me that this is not possible, though I would like confirmation. Also, if I am correct in thinking it is not possible, what was the reason for leaving this out?
Thanks!
Hi Everyone,
I created a script task that uploads a file to a HTTPs site. The script task works well and now I want to turn this script task into a custom component...
I'm having problems on how to call the variables I have in the package, I already locked them for reading, but how am I suppose to access them and how can I put their values into strings so I can easily call them in the WebClient.UploadFile method?
Also, I am very new to custom tasks in SSIS.. and would appreciate if anyone of you can send me something (code, link, etc.) of a very simple custom task without any form or properties, that I can try to study.
Basically, what I want my custom task to do is,
1. Read the varialbes in the package.
2. use the variables values as input in my WebClient.UploadFile("varURLstring", "PUT", "varFilePath")
as you can see, my custom task is really simple... but I'm a noob here and I don't know where to go.
Thanks in advance,
Kervy
Hi,
Given that Task.Validate() exposes the package's VariableDispenser, but LogProviderBase.Validate() doesn't...
http://msdn2.microsoft.com/fr-fr/library/microsoft.sqlserver.dts.runtime.task.validate.aspx
http://msdn2.microsoft.com/fr-fr/library/microsoft.sqlserver.dts.runtime.logproviderbase.validate.aspx
...I guess that simply means that I can't access a package's variables within a custom log provider? Can anyone comment/confirm? Any other options/routes to achieving the same..?
We live in hope,
Tamim.
As far as I can make out it is not possible to access the variables collection from a custom log provider.
I'm interested to know what the rationale for this is. Why aren't the variables available for logging purposes?
Thanks
Jamie
[Microsoft follow-up]
I am interested in determining if package variables can be read from a custom log provider. I am able to pass the values into the custom log provider using an expression in a custom connection manager. My intention is to used the values of these variables upon package failure so I can capture the context of the failure. Unfortunately, if the value of the variable is changed at runtime, this update is not reflected in the custom log provider.
Is it possible to get direct access to the package variables from the custom log provider?
Thanks,
Marc
Hi there,
View 4 Replies View RelatedHi
I am developing custom dataflow component ,I need to get the of variables of the current package in the component , how can i get it?
Thanks
Mani
Hi,
I'm trying to simplify the deployment process of my project. I already had some troubles with the config files but lets say I solved that issue. I'm going to read a flat file and set the variables of my packages from this file. I was thinking to use a Script Task to do that but I will need to copy this task in every package (I have at least 30). So if I want to make some change this will be painful.
Then, I came up with the idea of creating a Custom Task called Config File Task. I'm working on this but I got stuck trying to get the variables from the package that is running my Config File Task.
This is the code I had in the Script Task:
Dim streamReader As New StreamReader(Dts.Variables("ConfigFilePath").Value.ToString)
Dim line As String
Dim lineArray As String()
Dim variableName As String
Dim variableValue As String
Dim readConfigurations As Boolean = False
While (streamReader.Peek() <> -1)
line = streamReader.ReadLine()
If line = "[CONFIGURATIONS]" Then
readConfigurations = True
ElseIf line = "[/CONFIGURATIONS]" Then
readConfigurations = False
Else
If readConfigurations And line <> "" Then
lineArray = line.Split("|".ToCharArray())
variableName = lineArray(0).Trim()
variableValue = lineArray(1).Trim()
If Dts.Variables.Contains(variableName) Then
Dts.Variables(variableName).Value = variableValue
End If
End If
End If
End While
Dts.TaskResult = Dts.Results.Success
All I want to do is set the variables that exists in each package from the config file. In my UI Class (ConfigFileTaskUI.cs) I can have access to the variables via the TaskHost which is passed as an argument of Initialize() method.
Any thoughts? I'd really appreciate some help!
P.S. I've been working on this for 2 days!
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!
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.
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
Dear Friends,
I am having 2 Tables.
Table 1: AddressBook
Fields --> User Name, Address, CountryCode
Table 2: Country
Fields --> Country Code, Country Name
Step 1 : I have created a Cube with these two tables using SSAS.
Step 2 : I have created a report in SSRS showing Address list.
The Column in the report are User Name, Address, Country Name
But I have no idea, how to convert this Country Code to Country name.
I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]
Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.
Thanks in advance.
Regards
Ramakrishnan
Singapore
28 March 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
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
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;
}
}
}
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
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 RelatedI'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?
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
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
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
Hello all,
I have been struggling trying to read and/or write package level variables from within my custom task. I'd like to be able to get and set values from within the Execute method of my custom task. I have searched this forum and the books online and can't seem to find the answer. I thought maybe I could use an expression on my task (mapping the package variable to a custom task public property) but that doesn't seem to be working for me. I also would have thought I could use the VariableDispenser object from within my task but the collection is empty. I have 3 package level variables configured and can't seem to find a way to access them (with intentions of getting/setting). Could someone point me to a good doc or provide an example that may accomplish this? Thanks!
(I'm using package level variables as a means of passing simple information between tasks that are not using a DB, if there is a better way I'm open to suggestions.)
Jay_G
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 RelatedIs 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.
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.