passing parameter from vb 6 to crystal report 8

I am trying to pass value to the parameter field which i have created in crystal report 8. the parameter field is of type date. but i get error. the code is as follows.

.Reset
.ReportFileName = App.Path & "\reports\vaccine_flow_ledger.rpt"
.Connect = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=APACCESS"
.DiscardSavedData = True

.ParameterFields(0) = "SDT;" & #1/7/2005#
.ParameterFields(1) = "EDT;" & #1/7/2005#

.Action = 1
End With

Please help me to resolve this problem.Try this

.ParameterFields(0) = "SDT;"# & 1/7/2005 & #"
.ParameterFields(1) = "EDT;"# & #1/7/2005& #"|||I tried that one,but i didn't work.

but

.parameterfields(0) = "sdt;" & "date(2005,1,1);"

works finesql

Passing Multivalue Parameters Through a Report Link

I've created a line chart in my report which lists number of transactions by month. I've also created a report to list transactions by day. My goal is for the user to select a month and link the report to the graph with the selected month divided up into days (in other words, the user wants to see the number of transactions for each day in the selected month).

Anyway, everything is working perfectly except for one thing. In the list of parameters that I am passing to the report for each day, I want to pass a multivalue parameter which contains all the transaction IDs the user selected in creating the report (these are supplied by a multivalue parameter). However, in the parameter dialog box where it asks for a value to send to the awaiting parameter, I do not know how to supply more than one value. If I need to pass one value, it will work fine. However, I would like to do something like:

JOIN(Parameters!TransactionID.value, ",")

for the value of TransactionID, but when I generate the report it is not accepting the values. I'm pretty sure its just a format issue, and I just need to know how exactly I should pass these values.

I already understand that when passing multivalue parameters in a URL you need something like:

TransactionID=1&TransactionID=2

...in order to select multiple values. However, this is a slightly different situation. I'm really running out of ideas, so any help would be much appreciated.

Thank you guys so much

can you please paste your select statement, multivalue parameters are automatically rendered if you have something like this in your select

select * from employees where managerid IN(select managerid from managers)

|||

Have you tried:

SELECT *

FROM Managers

WHERE ManagerID in (@.ManagerParm)

This works in Oracle (except for : instead of @.) and I would be surprised if it did not work in SQL server.

NOTE you will not be able to test it using the !, but it it will work for the report.

|||

I'm sorry I wasn't clear. The project I have is in RS 2005 and uses an analysis services data source and the select statement is entirely in MDX. However, the problem really isn't in the select statement. My problem is that I am creating a report link and I want to pass all the values selected in a multivalue parameter to the other report.

If you want to see reproduce the problem, try these steps:

Create a report with a multivalue parameter. Add a textbox. Right-click and select properties. Click the Navigation tab and select "Jump to Report". Select a valid report that accepts a multivalue parameter. Click the parameters button. In the parameter name column, select the multivalue parameter to receive the values. In the parameter value column, I need to pass the values for the current multivalue parameter. This is the problem I am having.

|||

Please read this related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=163803&SiteID=1

It describes the various scenarios of passing multi value parameters to a drillthrough or subreport. In your case, it should be sufficient to just specify =Parameters!ParameterName.Value to pass all values along.

However, note that if the target report contains a parameterized MDX query and you want to use the passed-in parameter values directly in that query, the parameter values in the main report must represent the UniqueNames (not the caption which is usually the parameter label) - otherwise the target MDX query will most likely not work.

-- Robert

|||Thank you, it works perfectly!!sql

Passing LoginName into SQL query

How do I pass the user's LoginName into the WHERE clause of a SQL query?

I created the following query, but I don't know how to get the user's LoginName and pass its value into Param1. Nothing I try works.

SELECT property.propertyid, property.shortdesc, property.shortdesclink, property.text, property.UserID, [User].UserID AS Expr1, [User].Name FROM property INNER JOIN [User] ON property.UserID = [User].UserID WHERE ([User].Name = @.Param1)

hi, when ever u connecting with sqlserver then

following code will help u in passing parameter

Dim sqlcmd as new sqlcommand

sqlcmd.commandtext =" your query"

Sqlcmd.commandtype= commandtype.text

sqlcmd.parameters.add("@.param1", "loginName")

just add the above lines i hope it will help u.

Passing int parameter to stored procedure question.

Hi all,

I had created a stored procedure "DeleteRow" that can receive a parameter "recordID" from the calling function, however, I received a error msg "Procedure or function deleteRow has too many arguments specified." when run the C# code.
My code is showing below
----------- -------
thisConnection.Open();
SqlParameter param = DeleteRow.Parameters.Add("@.recordI D", SqlDbType.Int, 4);
param.Value = key;
SqlDataReader reader = DeleteRow.ExecuteReader(CommandBeh avior.CloseConnection) //program stop after runing this line
The stored procedure code which is showing below:
--------------------
CREATE PROCEDURE dbo.deleteRow @.recordID INT AS DELETE FROM ShoppingCart WHERE RecordID = @.recordID
Can anyone give me some ideal why this happen.
Thank alot.
wing

I don't know exactly, maybe you made a mistake here:
"@.recordI D",
see the whitespace

|||You should be using ExecuteNonQuery. ExecuteReader is designed to return data, not update.
|||omg, I've overlooked that. :$
You can also use an ExecuteScalar. In contract to ExecuteNonQuery, itwill get the first field of the first row. Usefull when using a returnvalue in a stored procedure...

Passing Formula as a parameter to SQL Query in AddCommand

Hi,

I'am new to Crystal reports,

I created a formula based on that i have to fetch column value from database

for that i wrote a very simple query in SQLCOMMAND

as SELECT <TABLE.CPLUMN> FROM <TABLE>

WHERE <TABLE.OTHERCOLUMN>='@.CRYSTAL REPORT FORMULA'

when closing the commandbox i'am getting error "OLE DB ERROR NO DATABSE RECORDS FOUND"

if i pass a some hardcoded vale(data whic exists in database) then the query is not throwing any error..

Thanks
MikeI don't think you can do that. You can create and use parameters in you query but not formulas. Your query looks really simple, so I have some doubts if it was that necessary to create it in your case.

Why don't you use something like that:

if {TABLE.OTHERCOLUMN}>=here print what you have in your '@.CRYSTAL REPORT FORMULA' then {TABLE.OTHERCOLUMN}

This formula does the same.

Passing default parameters

I have created some reports for our school district and would like to make a
2nd parameters default to what matches the 1st parameter in a drop down list.
Example:
If a teacher selects their school from the parameter drop down listing,
what I am trying to do is so that the teacher does not have to type in their
name and ensure the sysntex is correct, but to have the 2nd parameter default
to all teachers in that school so they could choose from the listing.
I've tried using the school and the teachers in a seperate dataset but when
I then select on the school it shows the school multiple times for each
teacher. I just want the school to appear for it's parameter then the 2nd
parameter would be populated with the teachers names based on the school
selected.
Thanks in Advance
--
Wayne HessThe second parameter should be based on a dataset that accepts the school
and returns the list of teachers.
select distinct firstname + ' ' + lastname as label, teacherid as value from
from schoolteachertable where school = @.SchoolParam
Use this as the source for the second parameter.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Wayne" <Wayne@.discussions.microsoft.com> wrote in message
news:9CB415ED-7050-4073-BACD-836545F0F0E6@.microsoft.com...
>I have created some reports for our school district and would like to make
>a
> 2nd parameters default to what matches the 1st parameter in a drop down
> list.
> Example:
> If a teacher selects their school from the parameter drop down listing,
> what I am trying to do is so that the teacher does not have to type in
> their
> name and ensure the sysntex is correct, but to have the 2nd parameter
> default
> to all teachers in that school so they could choose from the listing.
> I've tried using the school and the teachers in a seperate dataset but
> when
> I then select on the school it shows the school multiple times for each
> teacher. I just want the school to appear for it's parameter then the 2nd
> parameter would be populated with the teachers names based on the school
> selected.
> Thanks in Advance
> --
> Wayne Hess

Passing Date to Stored procedure as variable

Thanks in advance for your help!!!!!
I have created a stored procedure that uses a date range for paramenters. As long as I hardcode the dates in (3/21/03, 3/25/03) I get no errors. As soon as I replace the dates with the variable name and try to run the sp, I get an error message that it can't convert string to date.

It is my plan to call this sp from a web page and pass the date paramenters where they look like this 3/21/03

Here is what I have
@.BegDate Smalldatetime,
@.EndDate Smalldatetime
....
....
Where EntryDate BETWEEN '@.BegDate' AND '@.EndDate'

Could someone please help me understand what I am missing?

Thanks,
Leeyou are searching where EntryDate is between the string '@.BegDate' and the string '@.EndDate' not the values contained in @.BegDate and @.EndDate.

use Where EntryDate BETWEEN @.BegDate AND @.EndDate|||Thanks so much!!!!!! ' '

Passing an array of values for a single parameter

I am using a reportviewer control on my web form and I have created a parameter in my report. I can pass a hardcoded parameter and it works for one values at a time, but I want to send an array of values for the same parameter. For example if I have 5 different fleets of aircaft I might want to see 1,2 or all of the fleet in this report. I am kind of new to SQL reporting any help would be great.

Thanks in advance

you can pass an array but you need to do some processing in the RS designer..under the Data tab. or you can do it all in the stored proc it self by setting up the parameter as varchar and doing the splitting inside..|||

Thanks I will look into RS Designer Data Tab. Is there some sample code available the performs this particular function.

|||check out my blog..there is an article about parsing an array..there is also a link to a better article..it involves using UDFs. It is prbly a better and more efficient approach from the stored proc itself.|||

Could I use a parameter collection to pass an Array of Parameters to a report, if so how would I code that?

Thanks

|||prios did you get this working? If so, could you post your solution? I would also be interested in seeing how you passed a single parameter using the report viewer.
Thanks.|||

I found a solution...

http://odetocode.com/Articles/128.aspx

Passing a string report parameter to a stored proc

I have created a report that uses a SQL stored procedure. I am passing 3
parameters 1 of which is defined in the stored proc as NVARCHAR (10). I have
created 3 report parameters and I am passing them to the stored proc via the
parameters tab for the dataset. In visual studio the closest option to
NVARCHAR I have for the report parameter is string. If I run the sp from the
data area and pass it the params it works fine but when I try to preview the
report and enter the params I get no data (also no error message or anything)
I'm thinking I may have to do some sort of conversion but I'm not sure what
it would be. Any help would be aprrciated.Be sure the store proc parameter is defined in your Report parameters list.
"Jan Shulse" wrote:
> I have created a report that uses a SQL stored procedure. I am passing 3
> parameters 1 of which is defined in the stored proc as NVARCHAR (10). I have
> created 3 report parameters and I am passing them to the stored proc via the
> parameters tab for the dataset. In visual studio the closest option to
> NVARCHAR I have for the report parameter is string. If I run the sp from the
> data area and pass it the params it works fine but when I try to preview the
> report and enter the params I get no data (also no error message or anything)
> I'm thinking I may have to do some sort of conversion but I'm not sure what
> it would be. Any help would be aprrciated.

Passing a session parameter to a report call

Hi

I've created my report with a parameter and a dependent cascading parameter, this all work fine when I preview the .rdl

It also works fine when I access it through my asp.net application.

When accessed the report via my asp,net application the report prompts the user for both parameters, which, when I enter them, also works fine.

I would like to call the report while passing the first parameter to it so that the user only has to select the second parameter, which is dependent on the first.

The first parameter is stored as a session variable, but I cannot figure out how to pass this as a parameter to the report.

I have embedded a report view as:

rsweb:reportviewer id="ReportViewer1" runat="server" processingmode="Remote" width="795px" Font-Names="Verdana" Font-Size="8pt" Height="764px" ShowExportControls="False" ShowFindControls="False" ShowRefreshButton="False" ShowParameterPrompts="true">
<ServerReport ReportPath="/SubSrvs Reports/SubscribedServices" DisplayName="Confirmation Invoices" />
</rsweb:reportviewer>

Is there a way to pass my session value as a parameter? If so where do I do this pls.

I am not using any code behind at the moment.

I have tried passing it in the url of the page I call e.g. Invoices.aspx?ClientId=?? and in thehttp://localhost/reportserver?ClientId=??? but no luck, oh yes I have also tried surpressing the ShowParameterPrompts of the report view, but again no luck

many thanks

string sessParam = Convert.ToString(Session["myparam"]);

RptParameters[0] =
new Microsoft.Reporting.WebForms.ReportParameter("ClientId", sessParam );

this.ReportViewer1.ServerReport.SetParameters(RptParameters);
this.ReportViewer1.ServerReport.Refresh();

Check a basic tutorial over here:

http://www.codeproject.com/sqlrs/ReportViewer2005.asp

HTH,
Suprotim Agarwal

--
http://www.dotnetcurry.com
--

|||

muchas gracias! that worked a treat, used the C# equivalent and placed in the page load event with a !Page.IsPostBack.

also a very good articlehttp://www.codeproject.com/sqlrs/ReportViewer2005.asp

thanks again

Passing a date report parameter on the URL

I'm trying to call a report I created (and deployed to our dev server -
running reporting services 2005), while passing the parameters on the URL.
I think I'm very close, but it doesn't seem to like the format that the date
is in. However, I've tried everything I can think of (surrounding the date
with quotes, double quotes, pound symbols, and every combination of those I
could think of), and yet I'm still getting the error "The value provided for
the report parameter 'StartDate' is not valid for its type.
(rsReportParameterTypeMismatch) "
The URL I'm using is:
http://ssrsdev/ReportServer/Pages/ReportViewer.aspx?/CSR.Reports/UserLogReport&rs:Command=Render&PersonID='12345'&StartDate='06/11/2007'&EndDate='06/15/2007'
The two "date" parameters are obviously set up as date types (especially as
when I browse to the report without passing parameters, it allows me to pick
the dates for each parameter, rather than just allowing text entry).
Any ideas/suggestions?Scott,
Try to not use the ' " or # and just the value like
Start_Date=06/17/2007&End_Date=06/20/2007
Reeves
"Scott Lyon" wrote:
> I'm trying to call a report I created (and deployed to our dev server -
> running reporting services 2005), while passing the parameters on the URL.
> I think I'm very close, but it doesn't seem to like the format that the date
> is in. However, I've tried everything I can think of (surrounding the date
> with quotes, double quotes, pound symbols, and every combination of those I
> could think of), and yet I'm still getting the error "The value provided for
> the report parameter 'StartDate' is not valid for its type.
> (rsReportParameterTypeMismatch) "
>
> The URL I'm using is:
> http://ssrsdev/ReportServer/Pages/ReportViewer.aspx?/CSR.Reports/UserLogReport&rs:Command=Render&PersonID='12345'&StartDate='06/11/2007'&EndDate='06/15/2007'
>
> The two "date" parameters are obviously set up as date types (especially as
> when I browse to the report without passing parameters, it allows me to pick
> the dates for each parameter, rather than just allowing text entry).
>
> Any ideas/suggestions?

Pass variable value to DataReader Source

Dear All,

I have created a DTS Package in Integration Services 2005.

Within the DTS Package declared a variable named xxx and passed a value 1234.

In the control flow i dropped a Data flow task and in the Property Expression Editor of DataFlow Task i defined

Property = [DataReader Source].[sqlCommand]

Expression = Variable name.

Now in the DataFlow Task Canvas dropped DataReaderSource.

How can i pass variable value to the SQLCommand ="Select * from table where name = Variable value.

regards

Sufian

This post explains how to do that...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2009522&SiteID=1

|||

sorry , i have a diffrent problem.

I need to know how can i pass the variable value to the query supplied in the SQLCommand custom properties in datareader source on execution.

the variable datatype is Int32 and i am getting error when i set the expression property in the expression builder (Cannot convert System.Int32 to System.String).

Regards

Sufian

|||

mohd sufian wrote:

the variable datatype is Int32 and i am getting error when i set the expression property in the expression builder (Cannot convert System.Int32 to System.String).

You can't concatenate a string with an integer. You'll have to cast the integer as a string and THEN concatenate.

The cast operator is:

(DT_STR, <length>, <code_page>) variable_value

Code page will probably be 1252.

-Jamie

Pass in encrypted password parameter somehow?

I have multiple databases, one for each client. I created a master report. Then I created links to this report for each client database in separate client-specific folders. I parameterized the connection string so that the server name and the database name are parameters of the linked report. The connection string is: ="data source=" & Parameters!SrvrName.Value & ";initial catalog=" & Parameters!DbName.Value.

The problem is that the security for now is Windows Authentication. (We are in the testing phase). However, my users will be both internal (company) and external (client) users. We do not want to set up database user IDs for each client user. I could create a single SQL account that has read access to all databases, relying on the Reporting Services security. But I would prefer to create separate SQL accounts for each database as the information is sensitive. Then I would probably pass that information in as a parameter to the report as well. Either way, I would have to include the password somehow. But, if I do that, I'm not sure how to include the password parameter. I would like it to be secure and passing it in as a parameter is not very secure.

I would really appreciate some suggestions on how to proceed.

Consider storing the database credentials in the Report Server web.config file instead of passing them as parameters. The ExpressionBasedConnection report in this download shows how this could be done.

Pass fields as array to custom function?

I created a custom function that accepts an array of strings as a input. I need to pass several fields from the dataset to this function as an array. How can I do this?

You would need to create a function in the code window that accepts the values and inserts them into an array.

ex.

Code Snippet

Function CreateArray(ByVal field1 As String, ByVal field2 As String) As Array
Dim ar(1) As String
ar(0) = field1
ar(1) = field2
Return ar
End Function

I haven't tested it, but I think it will work.

Simone

|||

You can use the Split function.

http://msdn2.microsoft.com/en-us/library/6x627e5f(VS.80).aspx

|||That is true but you would still need to create a string to split using the required fields. Depending on the data those fields contain, you would need to be careful if they hold the delimeter used in the split function.

Partitions and Slices without query binding

I have a fairly straightforward cube in SQL 2005 SSAS, with date as one of the dimensions. I created a partition for each year and quarter, and set the slice appropriately. However, in order to create the partition, I had to use a source query, because I got an error about re-using the same table. I then created a single partition that was based off the table and set a slice and reviewed the SQL that SSAS issued, and it did not restrict based on the slice.
My question is: Can you make SSAS (2005) work like SQL 2000 where it automatically generated (albeit not perfectly) a where clause to restrict the partition?
Thanks,
Doug

Hi Doug,

The way I got this to work is by using AMO to clone a base partition, and then update the underlying view (again in AMO by connecting to a SQL Server). So before the partition is processed, the view is updated.

I used Script Task in SSIS to do the AMO.

This is a nice generic solution.

Hope it helps.

Rahil

|||

Rahil,

Any reason that you didn't use the Analysis Services DDL Task in SSIS?

-Jamie

|||

Hi Jamie:

The AS DDL Task in SSIS will not dynamically create a new partition - whereas in the AMO or XMLA script you can achieve that. I guess - that you could argue that we can break that up into two steps and do the partition creation in code and then subsequent processing in the DDL Task.

Thanks.

Suranjan

Partitions and Slices without query binding

I have a fairly straightforward cube in SQL 2005 SSAS, with date as one

of the dimensions. I created a partition for each year and

quarter, and set the slice appropriately. However, in order to

create the partition, I had to use a source query, because I got an

error about re-using the same table. I then created a single

partition that was based off the table and set a slice and reviewed the

SQL that SSAS issued, and it did not restrict based on the slice.

My question is: Can you make SSAS (2005) work like SQL 2000 where

it automatically generated (albeit not perfectly) a where clause to

restrict the partition?

Thanks,

Doug

Hi Doug,

The way I got this to work is by using AMO to clone a base partition, and then update the underlying view (again in AMO by connecting to a SQL Server). So before the partition is processed, the view is updated.

I used Script Task in SSIS to do the AMO.

This is a nice generic solution.

Hope it helps.

Rahil

|||

Rahil,

Any reason that you didn't use the Analysis Services DDL Task in SSIS?

-Jamie

|||

Hi Jamie:

The AS DDL Task in SSIS will not dynamically create a new partition - whereas in the AMO or XMLA script you can achieve that. I guess - that you could argue that we can break that up into two steps and do the partition creation in code and then subsequent processing in the DDL Task.

Thanks.

Suranjan