Its Not Report Parameters (ITS REPORTING SERVICES I THINK)

i am trying to generate a report based on 3 parameters

age, location, ethnciity

every thing works fine in data and layout tab,

when i run the preview tab, it give me the option to input paramaters and then when i hit veiw report, it shows processing report.... (indefinite) time.

i tried executing the query in data tab, it takes less than a sec.

any ideas?

am i doing somethign wrong in parameters?

Raja,

I'm going on the assumption you are using SQL as your datasource, If so, you can start a SQL profiler on the database you're using.

In the profiler, you can watch the query statement from SQL reporting service being executed, It will reveal exactly what is being passed in your parameter. You can copy this statement and execute to a new query to help you debug your issue.

I hope that this helps.

Ham

|||

its some weard problem.

its hitting the dbase and i double checked it in the profiler.

i dont knows what happening. i deleted the data cache files in the folder and tried rerun again..

nothing works.

data tab works 1000 times fine.

|||

What happens when you take the statement from the profiler and run it in a new query window. Does that execution of the statement still takes a long time?

Ham

|||

Hi,

I once had sort of the same problem. I didn't set the datatype of the parameters correctly which caused problems when viewing the report.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Yes,

I checked it with the QAnalyzer, Profiler, ReportingServices Data Tab.

everything takes less than a sec to execute the 15000 records.

This is one thing i noticed now after some weird research.

when i hit the preview tab and input parameters its not showing anything (i mean it shows report processinng..... )

but now when i deploy it to the server and try to view it. ITS WORKING

Where the h... is the problem. ?

|||any one have an email id of any of the webcast instructors who have instructed for RS before...|||any help?

Iterate through list of parameters

Hello,
I want to concatenate a string like
=Parameters!Para1.Label + ": " + Parameters!Para1.Value + ", "
+ Parameters!Para2.Label + ": " + Parameters!Para2.Value + ", "
+ Parameters!Para3.Label + ": " + Parameters!Para3.Value + ", "
+ ...
But this should be universal for all reports and universal for
single/multivalue parameters (where JOINs would be necessary).
So my question:
Is it possible to iterate through the list of parameters in custom code or
in an assembly to return such a string in a single, report-independant way?
Kind regards,
R."Ralph Watermann" wrote:
> Hello,
> I want to concatenate a string like
> =Parameters!Para1.Label + ": " + Parameters!Para1.Value + ", "
> + Parameters!Para2.Label + ": " + Parameters!Para2.Value + ", "
> + Parameters!Para3.Label + ": " + Parameters!Para3.Value + ", "
> + ...
> But this should be universal for all reports and universal for
> single/multivalue parameters (where JOINs would be necessary).
> So my question:
> Is it possible to iterate through the list of parameters in custom code or
> in an assembly to return such a string in a single, report-independant way?
> Kind regards,
> R.
>
>
sure you can ... here is a function I used in a previous report :
function createparameter(ByVal param as object) as object
dim strNewParam() as string
dim countery as int32
dim x as object
for each x in param
countery = countery + 1
next
redim strNewparam(countery + 1)
dim counterx as int32
counterx = 0
for each x in param
strNewParam(counterx) = x
counterx = counterx + 1
next
strNewParam(counterx) = ""
return strNewParam
end function
enjoy
Peter|||Ralph,
I have used the Join function to concatenate the selected values of my
multivalue parameters like this:
=Join(Parameters!Para1.Label + ": " + Parameters!Para1.Value, ", ")
if you need to wrap anything in quotes you will need to modify, say add
quotes around the whole thing:
=Join("'" + Parameters!Para1.Label + ": " + Parameters!Para1.Value, "', '")
Hope this helps
Reeves
"Ralph Watermann" wrote:
> Hello,
> I want to concatenate a string like
> =Parameters!Para1.Label + ": " + Parameters!Para1.Value + ", "
> + Parameters!Para2.Label + ": " + Parameters!Para2.Value + ", "
> + Parameters!Para3.Label + ": " + Parameters!Para3.Value + ", "
> + ...
> But this should be universal for all reports and universal for
> single/multivalue parameters (where JOINs would be necessary).
> So my question:
> Is it possible to iterate through the list of parameters in custom code or
> in an assembly to return such a string in a single, report-independant way?
> Kind regards,
> R.
>
>|||Hello Peter,
thank you very much for your help. Maybe I do not understand your code, but
I can't see the solution to my problem.
So I try to explain in more detail:
I wan't to print out the filters a user selected for displaying a report,
containing all filter descriptions and values. And so I think I need to
write a function like
function concatenateparameter(ByVal param as object) as string
but I do not know how to specify the object "param" which has to consist auf
all available parameters and their descriptions.
Kind regards,
Ralph
"Peter De Rop" <PeterDeRop@.discussions.microsoft.com> schrieb im Newsbeitrag
news:507E6A77-B3E6-4B26-8171-D5CB69764E68@.microsoft.com...
>
> "Ralph Watermann" wrote:
>> Hello,
>> I want to concatenate a string like
>> =Parameters!Para1.Label + ": " + Parameters!Para1.Value + ", "
>> + Parameters!Para2.Label + ": " + Parameters!Para2.Value + ", "
>> + Parameters!Para3.Label + ": " + Parameters!Para3.Value + ", "
>> + ...
>> But this should be universal for all reports and universal for
>> single/multivalue parameters (where JOINs would be necessary).
>> So my question:
>> Is it possible to iterate through the list of parameters in custom code
>> or
>> in an assembly to return such a string in a single, report-independant
>> way?
>> Kind regards,
>> R.
>>
> sure you can ... here is a function I used in a previous report :
> function createparameter(ByVal param as object) as object
> dim strNewParam() as string
> dim countery as int32
> dim x as object
> for each x in param
> countery = countery + 1
> next
> redim strNewparam(countery + 1)
> dim counterx as int32
> counterx = 0
> for each x in param
> strNewParam(counterx) = x
> counterx = counterx + 1
> next
> strNewParam(counterx) = ""
> return strNewParam
> end function
> enjoy
> Peter

Issue with Render Stream method in Reporting Services 2005

Hi,
In reporting services 2000 we had provision to pass report parameters
to Render Stream method. Now i am not able to do this in Render Stream
method of report execution api in RS 2005. I need to get the stream in
the form of html based on the parameters passed (e.g;in RS 2000 i used
to do like this...image = rs.RenderStream(reportPath, "HTML4.0",
streamID,historyID, devInfo, values, out optionalString, out
optionalString);). Can any one please tell me how to do this in
Reporting services 2005?Hello,
I have the same issue too, but I got the StreamNotFoundException. Here is my
steps:
1. call render() method. (a ref to streamID is returned)
2. call renderstream() method with above streamID.
Got the exception StreamNotFoundException.
I have no idea why the exception occurs.
The exception is pretty much the same as
http://support.microsoft.com/?kbid=913363
Anyone got an idea?
Thanks
Tai
"Arthas" wrote:
> Hi,
> In reporting services 2000 we had provision to pass report parameters
> to Render Stream method. Now i am not able to do this in Render Stream
> method of report execution api in RS 2005. I need to get the stream in
> the form of html based on the parameters passed (e.g;in RS 2000 i used
> to do like this...image = rs.RenderStream(reportPath, "HTML4.0",
> streamID,historyID, devInfo, values, out optionalString, out
> optionalString);). Can any one please tell me how to do this in
> Reporting services 2005?
>

Issue with populating the parameters

Hi,
I am using crystal report 11.My dynamic parameter displays company codes (4 digits).While populating, company codes are displayed as seperated with commas.But i need to display full 4 digits without commas.
For example:- Database value : 2345 Dynamic parameter displays as 2,345. How to avoid this issue ?Try to use formula having the code totext(field).
or goto format field and dormat it not to have commas

Issue with dataset from stored procedure

I am having an issue with a dataset in SSRS2005. Basically, this is a
simple dataset from a stored procedure, with two char parameters.
I just picked the stored procedure name and let the BI Studio pick all
the default settings. Then I try to preview the data. The BI studio
locks up. This happens every time.
The stored procedure works fine in SSMS. It returns one simple small
data set. It takes 6 seconds to run in SSMS. So I decided to set the
timeout to 30 seconds in the dataset. It times out and gives me the
expected timeout error message. If I set the timeout to 60 seconds, it
locks up - no timeout error message.
If I choose any other similarly structured stored procedure, no
problems occur. So I tried slightly modifying this procedure, but no
luck.
If I watch the procedure in sql profiler, the procedure is started, but
it doesn't complete until I kill BI studio.
Any ideas?hey, scott
when you say you "try to preview the data", do you mean you execute the
query from the data tab in the Report Designer, or do you actually go
to the Preview tab and run the report?
If you mean the latter, then I suggest you take a look at the report
layout, and make sure you're not creating some kind of bottleneck by
doing heavy grouping or formatting.
If you mean the former, I'd encourage you to take a look at the
properties of you data source (shared or in-report) and make sure you
are giving it the correct connection string, and authentication method
(windows/sql/none) and credentials.
Regards,
Thiago Silva
Scott.Stonehouse@.gmail.com wrote:
> I am having an issue with a dataset in SSRS2005. Basically, this is a
> simple dataset from a stored procedure, with two char parameters.
> I just picked the stored procedure name and let the BI Studio pick all
> the default settings. Then I try to preview the data. The BI studio
> locks up. This happens every time.
> The stored procedure works fine in SSMS. It returns one simple small
> data set. It takes 6 seconds to run in SSMS. So I decided to set the
> timeout to 30 seconds in the dataset. It times out and gives me the
> expected timeout error message. If I set the timeout to 60 seconds, it
> locks up - no timeout error message.
> If I choose any other similarly structured stored procedure, no
> problems occur. So I tried slightly modifying this procedure, but no
> luck.
> If I watch the procedure in sql profiler, the procedure is started, but
> it doesn't complete until I kill BI studio.
> Any ideas?|||Sorry, I mean the former. I am doing the preview from the data tab.
I know there is nothing wrong with the connection strings or anything.
I started commenting out fields in the stored procedure, and there are
three fields in particular that appear to be causing the problem.
Again, these look fine when I run it in SSMS. In each of those fields,
I did a coalesce, so I thought maybe that was related, but if I remove
the coalesce, nothing changes.
Then I tried putting the full stored procedure query into the dataset
as text instead of a stored procedure. Everything works. It was cut &
paste, so I know it's the same.
I would just leave it like this, but I have several reports running
from the same procedure. I don't want to have to edit every report
when it's time to make a change to the query.

Issue incorporating third parameter in report

I'm having issues incorporating a third parameter into my existing 2 parameters. I currently have them all defined and referenced in all queries, but the problem is with the criteria I think. I can get 2 of them to work together either 1&2 or 2&3 (by removing the other one), but not 1,2&3 at the same time. I'm using or conditions. Is there something I'm missing? thanks.

Like I said all my datasets referrence all of these parameters 1,2&3. On another note, when I'm in the preview area and I choose a value from the third parameter and the 2nd and click view, the screen just blinks (nothing will retrieve unless I choose a parameter for 1).

Also, when I deploy it on the web server and I select values for 2&3 and click view, it says to select a value for 1. I went back into my criteria for each dataset and check the parameters and they all look correct. Here is a sample of the condition I'm using for each dataset I'm retrieving:

where (X=1 and Y=2) or (X=3 and Y=2). Logically thinking, this is what I think it should be.

Thanks in advance for the help!

Hi,

Set the parameters to allow null values and change your query:

="select some_columns from your_table where some_conditions and " & iif(Parameters!Var1.Value is Nothing,"( col3=" & Parameters!Var3.Value & " and col2=" & Parameters!Var2.Value & " )" ,"( col1=" & Parameters!Var1.Value & " and col2=" & Parameters!Var2.Value & " )" )

To preview if this works as expected just copy the statement to a textfield and verify it looks like you want it to look
|||

I keep getting unclosed quotation mark near the character string ')' somewhere on the second line.

To be more specific, Here is the exact way may query looks:

SELECT CONVERT(text, CASE WHEN fra.percent_planned_complete LIKE '[0-9].[0-9][0-9]~%' ESCAPE '~' OR
fra.percent_planned_complete LIKE '[0-9][0-9].[0-9][0-9]~%' ESCAPE '~' OR
fra.percent_planned_complete = '100.00%' THEN cast(cast(ROUND(CAST(stuff(fra.percent_planned_complete, len(fra.percent_planned_complete), 1, '')
AS DECIMAL(5, 2)), 0) AS int) AS varchar(30)) + '%' ELSE fra.percent_planned_complete END) AS FORMATTED_Planned_PCT_COMPLETE,
CONVERT(text, Percent_Complete_To_Date + '%') AS FORMATTED_PCT_COMPLETE, PROJECTMANAGER, REPORTDATE, CAPITALPROJECTNUMBER,
PROJECTNAME
FROM FRA
WHERE (PROJECTNAME = @.proj_name) AND (REPORTDATE = @.report_month) OR
(CAPITALPROJECTNUMBER = @.cap_num) AND (REPORTDATE = @.report_month) and " & iif (Parameters!proj_name.Value is null,"( REPORTDATE=" & Parameters!@.report_month.Value & " and CAPITALPROJECTNUMBER=" & Parameters!@.cap_num.Value & " )" ,"( PROJECTNAME=" & Parameters!@.proj_name.Value & " and REPORTDATE=" & Parameters!@.report_month.Value & " )" ).

Also when I run just:

"SELECT REPORTDATE, CAPITALPROJECTNUMBER,
PROJECTNAME
FROM FRA
WHERE (PROJECTNAME = @.proj_name) AND (REPORTDATE = @.report_month) OR
(CAPITALPROJECTNUMBER = @.cap_num) AND (REPORTDATE = @.report_month) and " & iif (Parameters!proj_name.Value is null,"( REPORTDATE=" & Parameters!@.report_month.Value & " and CAPITALPROJECTNUMBER=" & Parameters!@.cap_num.Value & " )" ,"( PROJECTNAME=" & Parameters!@.proj_name.Value & " and REPORTDATE=" & Parameters!@.report_month.Value & " )" ).

I get maximum length is 128 and incorrect syntax by the Select

I keep trying to resolve this, but can't seem to do it.