Report: same

Hi There

We created a report from an Informix database stored procedure (with parameters). When we execute the stored procedure in the data page, it returns correct result set with column headings blank. In the datasets page, the field names are: ID, ID_1, ID_2, …

When I preview a report created from the dataset, all the fields shows the same value as field1 (ID). I have no idea on what happened, why it happened, and how to resolve it? Could you please help?

Thanks in advance.

Jane

It's been awhile since I have run into this, so apologies if this isn't exactly right. I believe this occurs when Reporting Services can't get the column metadata from the database, or when all the columns have the same base name (the table.column name may be unique, but the column name is not). So the mapping of values to columns doesn't work correctly.

The workaround for us was to make sure the database query aliased all columns to a unique name.

|||

Thanks for your reply.

You are right, our problem is Reporting Services can't get the column metadata from the stored procedure return result. In the data page, we select Text, and type:

execute procedure proc_name (?)

Because we use stored procedures instead of queries, not sure how to set the column names. Any ideas? Thanks a lot.

Cheers

Jane

Report/query parameter default values

I am using a SQL stored procedure for a report. The sproc has a variable in it (@.itemnumber) to be supplied by the user at run time. In the report parameters within Visual Studio Designer, I am trying to set a default value for the parameter of % so that all items will be returned unless the user enters a specific item number. In the Visual Studio preview this works but once the report is deployed to the web server, the default value does NOT show up. Please advise.

thanks!

Martha

Hi Martha,

SQL Server 2005 Books Online contains an example that takes you through

exactly what it sounds like you are trying to accomplish. Please

take a look at it at:

SQL Server 2005 Books Online->SQL Server 2005

Tutorials->Reporting Services Tutorials->Using a Dynamic Query in

a Report

It shows you how to build a dynamic query to return all records when no parameter is passed through.

If you have further problems after looking at that, please continue posting.|||

Only problem is that we are still on SQL RS 2000. Have not gone to 2005 as yet. Would the information you refer to work on 2000?

thanks!

|||Well let's try to tackle it a different way. Is your query

dynamic and includes a union with your default value that you want to

show all? For example:

SELECT 0 AS DepartmentID, 'All' AS Name

UNION

SELECT DepartmentID, Name

FROM HumanResources.Department

ORDER BY Name|||No, it's a non-queried default. I'm simply trying to set it to %. The variable is a string (varchar) in the sproc. In the Visual Studio preview it works, just not on the web once deployed.|||

hi,

iam also not able to use the dynamic query in repoting services,in sql server 2005.

i am trying from the following path

SQL Server 2005 Books Online->SQL Server 2005 Tutorials->Reporting Services Tutorials->Using a Dynamic Query in a Report-->Lesson 6: Updating the Employees Dataset with a Dynamic Query

as given in the boks online the following query

="SELECT c.firstname, c.lastname, e.title, d.departmentID " &

"From HumanResources.EmployeeDepartmentHistory D " &

"INNER JOIN HumanResources.Employee E " &

"ON D.EmployeeID = E.EmployeeID " &

"INNER JOIN Person.Contact C " &

"ON E.ContactID = C.ContactID " &

Iif(Parameters!Department.Value = 0, "", "WHERE D.DepartmentID = " & Parameters!Department.Value) &

"ORDER BY C.LastName" but am getting error from the if clause,so how to use the dynamic query for the above so.please do replysekhar|||

I am not worked on dynamic queries but i suggest you can use stored procedures with a parameter. Create a stored procedue and pass your parameter to stored procedure.

Report/query parameter default values

I am using a SQL stored procedure for a report. The sproc has a variable in it (@.itemnumber) to be supplied by the user at run time. In the report parameters within Visual Studio Designer, I am trying to set a default value for the parameter of % so that all items will be returned unless the user enters a specific item number. In the Visual Studio preview this works but once the report is deployed to the web server, the default value does NOT show up. Please advise.

thanks!

Martha

Hi Martha,
SQL Server 2005 Books Online contains an example that takes you through exactly what it sounds like you are trying to accomplish. Please take a look at it at:
SQL Server 2005 Books Online->SQL Server 2005 Tutorials->Reporting Services Tutorials->Using a Dynamic Query in a Report
It shows you how to build a dynamic query to return all records when no parameter is passed through.
If you have further problems after looking at that, please continue posting.
|||

Only problem is that we are still on SQL RS 2000. Have not gone to 2005 as yet. Would the information you refer to work on 2000?

thanks!

|||Well let's try to tackle it a different way. Is your query dynamic and includes a union with your default value that you want to show all? For example:
SELECT 0 AS DepartmentID, 'All' AS Name
UNION
SELECT DepartmentID, Name
FROM HumanResources.Department
ORDER BY Name
|||No, it's a non-queried default. I'm simply trying to set it to %. The variable is a string (varchar) in the sproc. In the Visual Studio preview it works, just not on the web once deployed.|||

hi,

iam also not able to use the dynamic query in repoting services,in sql server 2005.

i am trying from the following path

SQL Server 2005 Books Online->SQL Server 2005 Tutorials->Reporting Services Tutorials->Using a Dynamic Query in a Report-->Lesson 6: Updating the Employees Dataset with a Dynamic Query

as given in the boks online the following query

="SELECT c.firstname, c.lastname, e.title, d.departmentID " & "From HumanResources.EmployeeDepartmentHistory D " & "INNER JOIN HumanResources.Employee E " & "ON D.EmployeeID = E.EmployeeID " & "INNER JOIN Person.Contact C " & "ON E.ContactID = C.ContactID " & Iif(Parameters!Department.Value = 0, "", "WHERE D.DepartmentID = " & Parameters!Department.Value) & "ORDER BY C.LastName" but am getting error from the if clause,so how to use the dynamic query for the above so.please do replysekhar|||

I am not worked on dynamic queries but i suggest you can use stored procedures with a parameter. Create a stored procedue and pass your parameter to stored procedure.

Report/query parameter default values

I am using a SQL stored procedure for a report. The sproc has a variable in it (@.itemnumber) to be supplied by the user at run time. In the report parameters within Visual Studio Designer, I am trying to set a default value for the parameter of % so that all items will be returned unless the user enters a specific item number. In the Visual Studio preview this works but once the report is deployed to the web server, the default value does NOT show up. Please advise.

thanks!

Martha

Hi Martha,

SQL Server 2005 Books Online contains an example that takes you through

exactly what it sounds like you are trying to accomplish. Please

take a look at it at:

SQL Server 2005 Books Online->SQL Server 2005

Tutorials->Reporting Services Tutorials->Using a Dynamic Query in

a Report

It shows you how to build a dynamic query to return all records when no parameter is passed through.

If you have further problems after looking at that, please continue posting.|||

Only problem is that we are still on SQL RS 2000. Have not gone to 2005 as yet. Would the information you refer to work on 2000?

thanks!

|||Well let's try to tackle it a different way. Is your query

dynamic and includes a union with your default value that you want to

show all? For example:

SELECT 0 AS DepartmentID, 'All' AS Name

UNION

SELECT DepartmentID, Name

FROM HumanResources.Department

ORDER BY Name|||No, it's a non-queried default. I'm simply trying to set it to %. The variable is a string (varchar) in the sproc. In the Visual Studio preview it works, just not on the web once deployed.|||

hi,

iam also not able to use the dynamic query in repoting services,in sql server 2005.

i am trying from the following path

SQL Server 2005 Books Online->SQL Server 2005 Tutorials->Reporting Services Tutorials->Using a Dynamic Query in a Report-->Lesson 6: Updating the Employees Dataset with a Dynamic Query

as given in the boks online the following query

="SELECT c.firstname, c.lastname, e.title, d.departmentID " &

"From HumanResources.EmployeeDepartmentHistory D " &

"INNER JOIN HumanResources.Employee E " &

"ON D.EmployeeID = E.EmployeeID " &

"INNER JOIN Person.Contact C " &

"ON E.ContactID = C.ContactID " &

Iif(Parameters!Department.Value = 0, "", "WHERE D.DepartmentID = " & Parameters!Department.Value) &

"ORDER BY C.LastName" but am getting error from the if clause,so how to use the dynamic query for the above so.please do replysekhar|||

I am not worked on dynamic queries but i suggest you can use stored procedures with a parameter. Create a stored procedue and pass your parameter to stored procedure.

Report works fine on VS.NET 2003, get error when uploaded

Hello,

I created a report and use stored procedure to retrieve data. The report works perfect on VS.NET 2003 IDE. But when I upload the .rdl file to the report server, it doesn't work. The error msg I got is:

Reporting Services Error

---

An error has occurred during report processing. (rsProcessingAborted)Get Online HelpQuery execution failed for data set 'dsTransTime'. (rsErrorExecutingCommand)Get Online HelpProcedure 'usp_RPT_Procedure_Name' expects parameter '@.Parameter1', which was not supplied.

---

Anyone know the problem?
Thanks,Finally, I make it works. But I still don't know how I made it.
I cannot find a similiar errors from google. So everything I did is based on guess. Becasuse the error msg shows the report cannot take the parameter, I removed the parameter and re-created it again. Also, I use refresh button on the Data view to refresh the dataset.

Sometime I feel waired about Reporting services.

Report won;t run with new paramters

I have a report that works just fine and has for some time. I had to make a
change and added a new parameter to the stored procedure. I updated the
report and republished it. It sees the parameter but doesn't seem to be
using it. I even ran SQL Profiler and it definitely is not calling the
stored procedure with the new parameter. I checked the RDL and it does know
about the parameter. I am just confused.
Stacey LevineTry deleting the report from the report server and redeploying it.
Andy Potter|||That did not work.. but..here is what I had to do to fix it. I had to go to
the data tab in visual studio and run the data set (stored procedure) and
enter the new parameters. Then I rebuilt and redeployed and it started
working. Not sure why.. but this did it.
Thanks.
Stacey
"Potter" <drewpotter@.gmail.com> wrote in message
news:1136925208.341658.254200@.g44g2000cwa.googlegroups.com...
> Try deleting the report from the report server and redeploying it.
> Andy Potter
>|||If you add a parameter to a database object, you must either refresh or
execute the dataset in order for the MSRS to know about the new
parameter. This forces RS to execute its GetParameters() method for
the command. This parameter will then be listed on the Parameters tab
of the dataset (and similarly, in the RDL). The GetParameters() is not
called when you Preview the report.
The alternative is to manually enter the new parameter on the Parameter
tab of the dataset.
Andy Potter

Report Wizard

I am trying to use a stored proc in the report wizard with parameters
Exec usp_AgentSessionSummary @.StartDate, @.EndDate, @.ProjectID, @.AgentID
I get the error below. I want to use the wizard for ease, how do I get
around the error?
There is an error in the query. Implicit conversion from data type
sql_variant to datetime is not allowed. Use the CONVERT function to run this
query.You could try first hard coding the parameters, use the wizard and then
switch it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"JW" <JW@.discussions.microsoft.com> wrote in message
news:E1ADCFEF-E23F-41F7-8327-BB5B10C11008@.microsoft.com...
>I am trying to use a stored proc in the report wizard with parameters
> Exec usp_AgentSessionSummary @.StartDate, @.EndDate, @.ProjectID, @.AgentID
> I get the error below. I want to use the wizard for ease, how do I get
> around the error?
> There is an error in the query. Implicit conversion from data type
> sql_variant to datetime is not allowed. Use the CONVERT function to run
> this
> query.

report when was last used the stored procedure/view- in a DB

hello,
is there a possibilitie to get the date when a stored procedure / view was
used
The problem is that in a database there are a lot of views - which where
created in a period of last 2 years from diffren user which had access to th
e
database. Now i want to have a kind of report for all views / stored
procedures from a database - to see if this where used in the last 3 month.
If no we can than make a backap and delete this.
best regardsYou could do the stored procedures by either using Profiler and auditing
whats executed against the SQL Server and then pull out the procs used, or
you could write into each stored procedure a write to an audit table
something like...
insert sp_run_log ( procname ) values ( 'myprocname' )
Then you can check through the log after a period of time - you would need
to run every piece of logic in yoru application though.
I can't think of anyway you could do a view, you could perhaps trap the SQL
again using profiler at the statement level and do string searches for them.
Hope that helps.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:07E773A8-9A95-4CB5-B839-72AA796BF2CE@.microsoft.com...
> hello,
> is there a possibilitie to get the date when a stored procedure / view
> was
> used
> The problem is that in a database there are a lot of views - which where
> created in a period of last 2 years from diffren user which had access to
> the
> database. Now i want to have a kind of report for all views / stored
> procedures from a database - to see if this where used in the last 3
> month.
> If no we can than make a backap and delete this.
> best regards|||hello Tony,
thanks for yur help, the idea to write in a audit table is great.
For the view ..... have you any link / information / step by step .. how
to realize the " trap the SQL using profiler at the statement level ..."
thanks
"Tony Rogerson" wrote:

> You could do the stored procedures by either using Profiler and auditing
> whats executed against the SQL Server and then pull out the procs used, or
> you could write into each stored procedure a write to an audit table
> something like...
> insert sp_run_log ( procname ) values ( 'myprocname' )
> Then you can check through the log after a period of time - you would need
> to run every piece of logic in yoru application though.
> I can't think of anyway you could do a view, you could perhaps trap the SQ
L
> again using profiler at the statement level and do string searches for the
m.
> Hope that helps.
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:07E773A8-9A95-4CB5-B839-72AA796BF2CE@.microsoft.com...
>
>

Report Viewer Information

Does anyone know of a good tutorial to learn how to use the Report Viewer with ASP.NET pages? I will just be stored procedures, and I haven't used a reporting tool before. I tried to make up one just to try it out, but I kept getting table errors. It also referenced the file locally, which I don't think is right for what I need. I am converting Access Reports to the ASP.NET site that will replace the Access application, and I need a report that can be accessed from remote computers though the side. Please help if you have any ideas because I couldn't find much information about this.

try here:

http://www.gotreportviewer.com/

|||I tried that site before, and didn't have too much luck with it, but I was able to figure out some of the basics on my own. Does anyone know how to make it so that the report will be converted horizontally on the PDF file when exported with the report viewer control?

Report Viewer authentication

Hello,

I am trying to create a simple ASP page to navigate in the reports stored in the report server. If I work in debug mode everything is fine. The page is working and If I call for a report I can see it perfectly. If I publish the report on my test machine (Windows XP Sp2 so IIS 5.1) I see that IE is connecting me with the ASP net user and not the correct one (myself) that is authorized to see the reports. Anybody knows how to solve it?

Thank you!

Andrea Pagliano

If you need the interactive user identity to be propagated to the Report Server, in your app web.config set <indentity impersonate="true"/>. This won't work if the Report Server is ona different machine though unless you enable Kerberos. There are other options as explained here.|||

Thank you very much! That did it!

Thanks again

Andrea Pagliano

Report Viewer and Stored Procedure

I'm trying to build a report using the Report Viewer and a stored procedure. My sproc has paramerters and uses a #temp table. I have been able to get all the way to the point of adding fields to the RDLC , however, there is no table or fields in my dataset. Do I need to do something special that I'm missing to build the dataset with a sproc that uses a #temp table? My parameters are provided by fields on the aspx page where the Report Viewer is located.

I appreciate any help.

You should be able to execute the proc under the Data tab and see the results and make sure your proc is returning some data before you even start off building the report. So you konw what fields are available.sql

Report timeout after 60 minutes

I'm running a long report from Reporting Services web site. When I run
the stored procedure by itself, it executes with no problem, taking
about 3 hours to complete. When I execute the report itself, it runs
for exactly 60 minutes and after that I get "The page can not be
displayed" message. I played around with session timeouts and report
execution timeout setting both of these to 5 hours, however that didn't
give any positive results. I'm beginning to think that it maybe some
IIS issue. I also increased the connection timeout in IIS to 5 hours.
Does anyone have any clues?
I'm running this on Windows 2000, IIS 5.
Thank you.
-IKI solved the problem myself. Looks like IE version 5.x and higher,
have 60 minute time-out limit.
A quick registry setting and a restart fixes the problem.
1. Add a ReceiveTimeout DWORD value with a data value of (<number of
seconds>)*1000 in the following registry key:
HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet
Settings
For example, if you want the timeout duration to be 8 minutes, set the
ReceiveTimeout data value to 480000 (<480>*1000).
2. Restart your computer.
You can find more info at:
http://support.microsoft.com/kb/q181050/
-IK|||hi IK,
one clarification.
need to set this on the server or on the client.
i am adjusting this setting on a comp connecting to the server and checking
this out.
would be quite a relief to get round this one.
thanks for your note,
SamK
"IK" wrote:
> I solved the problem myself. Looks like IE version 5.x and higher,
> have 60 minute time-out limit.
> A quick registry setting and a restart fixes the problem.
> 1. Add a ReceiveTimeout DWORD value with a data value of (<number of
> seconds>)*1000 in the following registry key:
> HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Internet
> Settings
> For example, if you want the timeout duration to be 8 minutes, set the
> ReceiveTimeout data value to 480000 (<480>*1000).
> 2. Restart your computer.
>
> You can find more info at:
> http://support.microsoft.com/kb/q181050/
> -IK
>sql

Report taking forever to render

Hi All,

This question pertains to sql server reporting services 2005.

I have a report in which i am using a stored procedure to populate the dataset. However if i run that report in the IDE or the browser it takes forever to run and nothing happens even after 30 min.

I thought something was wrong with the stored procedure so i ran it separately in the SQL server management and it runs in about 25 secs.

I am clueless why it is behaving like this.

Any suggestion will be appreciated.

HI,abhi81 :

Here are a few things to check:
- Make sure that you're report project Datasource is setup correctly
(i.e., has the correct connection string, etc).
- Make sure that if you are passing parameters to your stored
procedure/query, that you tie them correctly in the dataset (via the
Data tab -> Edit Dataset button [...] -> Parameters tab).
- If you are using report parameters, make sure that your datasets are
not erroring out for those parameters.
- Lastly, make sure that you have the latest service pack for SQL
Server.

And you can try to use a simply store procedure and make sure it is not the problem related to your comlicated one.
Hope this helps.

Report subscription only works 80% of the time.

Hi,

I have setup report subcription with stored credential using a valid domain account and "Use as Window Credentials..." checked. Everything tested fine and works properly when the report is viewed in a web application. The problem is this report renders fine in the browser 80% of the time and occasionally it gives me Logon failure error:

An error has occurred during report processing (rsProcessingAborted)

Cannot impersonate user for data source 'Pinnacle'. (rsErrorImpersonatingUser)

Logon failed. (rsLoginFailed)

Logon failed: unknown user name or bad password. (Exception from HRESULT: 0x8007052E)

Since the report is working 80% of the time and the user domain account hasn't changed. I am confused why I am getting bad user account error. Please help!

Thanks.

-SouBee

This sounds like a network connectivity issue - if the security authority is not available when we try to login the data source credential user, you'll see behavior like this. At the time the subscription fails, are you able to connect to the data source as the same credentialed user?

-Lukasz

Report subscription only works 80% of the time.

Hi,

I have setup report subcription with stored credential using a valid domain account and "Use as Window Credentials..." checked. Everything tested fine and works properly when the report is viewed in a web application. The problem is this report renders fine in the browser 80% of the time and occasionally it gives me Logon failure error:

An error has occurred during report processing (rsProcessingAborted)

Cannot impersonate user for data source 'Pinnacle'. (rsErrorImpersonatingUser)

Logon failed. (rsLoginFailed)

Logon failed: unknown user name or bad password. (Exception from HRESULT: 0x8007052E)

Since the report is working 80% of the time and the user domain account hasn't changed. I am confused why I am getting bad user account error. Please help!

Thanks.

-SouBee

This sounds like a network connectivity issue - if the security authority is not available when we try to login the data source credential user, you'll see behavior like this. At the time the subscription fails, are you able to connect to the data source as the same credentialed user?

-Lukasz

Report snapshot generation failed on MDX script

Hi,

I am running RS 2005 and Analysis Services 2005. I was able to get RS to connect to the OLAP database (via stored credential) when generating snapshot. However, it failed when processing MDX script in the report dataset. The same report works fine in browser and VStudio, but only failed during snapshot or cache generation. Below is the error message and the affected MDX script. Any help would be appreciated!

An error has occurred during report processing. (rsProcessingAborted) Get Online Help

Get Online Help

Query execution failed for data set 'PU_Floorcovering'. (rsErrorExecutingCommand) Get Online Help

Incorrect syntax near '{'. Incorrect syntax near 'Business Type'. Incorrect syntax near 'Facility'. Incorrect syntax near 'Production Units Reporting UOM'.

WITH SET [UOM] AS

' ([Raw Materials Source UOM].[Source UOM].ALLMEMBERS - [Raw Materials Source UOM].[Source UOM].[All]) *

([Raw Materials Reporting UOM].[Reporting UOM].ALLMEMBERS - [Raw Materials Reporting UOM].[Reporting UOM].[All]) *

([Production Units Source UOM].[PU Source UOM].ALLMEMBERS - [Production Units Source UOM].[PU Source UOM].[All]) *

([Production Units Reporting UOM].[PU Reporting UOM].ALLMEMBERS - [Production Units Reporting UOM].[PU Reporting UOM].[All])'

SELECT NON EMPTY

{

[Measures].[Weight], [Measures].[Weight per Production Unit Converted]

} ON COLUMNS,

NON EMPTY

{

(([Time].[Year].ALLMEMBERS - [Time].[Year].[All]) *

([Raw Materials].[Category Type].ALLMEMBERS - [Raw Materials].[Category Type].[All]) *

([Raw Materials].[Category].ALLMEMBERS - [Raw Materials].[Category].[All]) *

[UOM])

}

having not IsEmpty([Measures].[Weight]) And ([Measures].[All Facilities Production Unit Converted], [Production Units].[UOM]) > 0

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM

(

SELECT ( { [Business Type].[Business Type].&[{11116DDD-FD7B-46AA-A4D8-73492E220001}]}) ON COLUMNS

FROM

(

SELECT ({[Raw Materials Reporting UOM].[Reporting UOM].&[Lb]}) ON COLUMNS

FROM

(

SELECT ({[Production Units Reporting UOM].[PU Reporting UOM].&[Yd2]}) ON COLUMNS FROM [Pinnacle]

)

)

)

WHERE

(

[Facility].[LocationType].&[1],

[Facility].[Initiative - InitiativeFacility].[Initiative].&[{11136DDD-FD7B-46AA-A4D8-73492E220002}]

)

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

- SouBee

It was a mistake on my part. The data source was set to MS SQL Server, instead it should be MS SQL Analysis Services.

Thanks.

SouBee

Report Services - capture userid of person running the report

I have a report which executes a stored procedure when the report is opened. What function will capture the identity ( name or user id ) of the person opening the report and therefore executing the procedure?

At what point in the stack are you trying to get the userid? Within the SP, or within the report expressions?

From within the report, you can use User!UserId. If you want to get the login that is currently executing the SP from within the SP, then you can use system_user (http://msdn2.microsoft.com/en-us/library/ms179930.aspx).

|||More specifics please|||

the first parameter of the report is UserId, string, hidden

the available value is label UserId with value =User!UserID. The default value is non-queried =User!UserID

the data source is: exec ReportBase.dbo.proc_TestUser '01/01/2006','01/02/2006',@.UserID

the procedure is:

-- =============================================

-- Create basic stored procedure template

-- =============================================

use ReportBase

GO

-- Drop stored procedure if it already exists

IF EXISTS (

SELECT *

FROM INFORMATION_SCHEMA.ROUTINES

WHERE SPECIFIC_SCHEMA = N'dbo'

AND SPECIFIC_NAME = N'proc_TestUser'

)

DROP PROCEDURE dbo.proc_TestUser

GO

CREATE PROCEDURE dbo.proc_TestUser

@.Date1 as datetime,

@.Date2 as datetime,

@.UserID as varchar(100)

AS

-- drop table ReportBase.dbo.t_UserTest

/*

create table ReportBase.dbo.t_UserTest (

[first_date] [datetime] NULL ,

[second_date] [datetime] NULL ,

[user_name] [varchar](100) NULL )

*/

insert into ReportBase.dbo.t_UserTest

select @.Date1, @.Date2, @.UserID

select * from ReportBase.dbo.t_UserTest

go

Report Services - capture userid of person running the report

I have a report which executes a stored procedure when the report is opened. What function will capture the identity ( name or user id ) of the person opening the report and therefore executing the procedure?

At what point in the stack are you trying to get the userid? Within the SP, or within the report expressions?

From within the report, you can use User!UserId. If you want to get the login that is currently executing the SP from within the SP, then you can use system_user (http://msdn2.microsoft.com/en-us/library/ms179930.aspx).

|||More specifics please|||

the first parameter of the report is UserId, string, hidden

the available value is label UserId with value =User!UserID. The default value is non-queried =User!UserID

the data source is: exec ReportBase.dbo.proc_TestUser '01/01/2006','01/02/2006',@.UserID

the procedure is:

-- =============================================

-- Create basic stored procedure template

-- =============================================

use ReportBase

GO

-- Drop stored procedure if it already exists

IF EXISTS (

SELECT *

FROM INFORMATION_SCHEMA.ROUTINES

WHERE SPECIFIC_SCHEMA = N'dbo'

AND SPECIFIC_NAME = N'proc_TestUser'

)

DROP PROCEDURE dbo.proc_TestUser

GO

CREATE PROCEDURE dbo.proc_TestUser

@.Date1 as datetime,

@.Date2 as datetime,

@.UserID as varchar(100)

AS

-- drop table ReportBase.dbo.t_UserTest

/*

create table ReportBase.dbo.t_UserTest (

[first_date] [datetime] NULL ,

[second_date] [datetime] NULL ,

[user_name] [varchar](100) NULL )

*/

insert into ReportBase.dbo.t_UserTest

select @.Date1, @.Date2, @.UserID

select * from ReportBase.dbo.t_UserTest

go

Report Service output suppressing duplicate info

Hello,
Here is the situation. I have a table with a fieldname called VAL. I
call a stored procedure to return 4 records from the table.
Ok, the 4 records's field name VAL all have the same value , "Horray".
I created a report with a table and only want to display the VAL column.
I was expecting to see 4 rows of 'Horray' but I only see one row. Is there
place in the Report Service to turn off suppressing duplicate values?
product is Sql Server Reporting Service 2000.
Thanks
SteveOn the table, select your val column textbox , right click for properties you
can see "Hide duplicate" so this does the things..
Amarnath
"steve kwon" wrote:
> Hello,
> Here is the situation. I have a table with a fieldname called VAL. I
> call a stored procedure to return 4 records from the table.
> Ok, the 4 records's field name VAL all have the same value , "Horray".
> I created a report with a table and only want to display the VAL column.
> I was expecting to see 4 rows of 'Horray' but I only see one row. Is there
> place in the Report Service to turn off suppressing duplicate values?
> product is Sql Server Reporting Service 2000.
> Thanks
> Steve
>
>|||hi steve
here's another solution
right click the text box, click properties, click on visibility tab. select
Expression radio button and click on the function button. enter this
expression there
=(Previous(Fields!PK_Field1.Value) = Fields!PK_Field1.Value)
replace PK_Field1 with the field name that determines the duplicate value
for you. repeat the same step for all the text boxes that where you want to
suppress the duplicates.
this way, if you had a report like this
field1val 1234 abcd
field1val 1234 xyz
field2val 3988 alsdk
you'll see this output:
field1val 1234 abcd
xyz
field2val 3988 alsdk
hope it helps
"steve kwon" wrote:
> Hello,
> Here is the situation. I have a table with a fieldname called VAL. I
> call a stored procedure to return 4 records from the table.
> Ok, the 4 records's field name VAL all have the same value , "Horray".
> I created a report with a table and only want to display the VAL column.
> I was expecting to see 4 rows of 'Horray' but I only see one row. Is there
> place in the Report Service to turn off suppressing duplicate values?
> product is Sql Server Reporting Service 2000.
> Thanks
> Steve
>
>

Report Server Stored Credentials

I am getting the standard "Subscriptions cannot be created because the credentials used to run the report are not stored -".I tried to store them in the Stored Data Source in the Visual Studio Project and then deployed. I cannot find in any documentation a reference where to go to STORE CREDENTIALS for a Report Server subscription. I would really appreciate any help on this. I did look at the existing threads, but they don't specifically address where to create the credentials. Thanks for any help you can provide.

Hello Gary,

Using Report Manager, drill down to your report. Click the properties tab, then select 'data sources' on the left side of the page. Make sure the 'A custom data source' is the radio button selected, then click the radio button for 'Credentials stored securely in the report server'. Type in your credentials to run the report and if it's a domain account, then check the box for 'Use as Windows credentials when connecting to the data source'. Click Apply at the bottom and you should be able to create a subscription.

Hope this helps.

Jarret

|||Where is this in the documentation? I searched Books on Line and MSDN and got NADA. What a brilliant person you are. Maybe smarter than the richest man in the world (BG), how bout that. Thanks and thanks again.|||

Many thanks for your kind words Gary.

In BOL, just search for 'stored credentials', it also has a way to set the stored credentials via a RS connection in Management Studio.

Jarret