Multi-Value Parameter Width

Good afternoon,

I've designed a Reporting Services report that has a multi-value parameter. The report works just great. The only issue I'm running into is that the users are complaining that when they want to select the values, within the multi-value parameter box, they have to scroll it to the sides way too much. This also makes them select values by mistake.

Is there away to inscrease the width of the multi-value parameter box?

Best regards.

I guess you can do that by changing the stylesheet provided for controlling the layout and style of report manager. If your clients are using report manager, then read on:

There is a file called ReportingServices.css in Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportManager\Styles.

Edit that in notepad and locate a class called "msrs-multiValueDropDown" and in that class add width property as follows and save the file:

.msrs-multiValueDropDown

{

border: 1px DarkGray solid;

width: 200px;

}

Shyam

|||

That could work, yes. I'll sure try it tomorrow when I get to work.

My only problem with that approach is that it's an horizontal change. All reports will be affected unless theres a way to tell that report, or parameter, to use a specific sheet or value from the default sheet.|||

No, it cannot be done on a report by report basis.

So, can you please mark my post as answer?

Multi-Value Parameter Width

Good afternoon,

I've designed a Reporting Services report that has a multi-value parameter. The report works just great. The only issue I'm running into is that the users are complaining that when they want to select the values, within the multi-value parameter box, they have to scroll it to the sides way too much. This also makes them select values by mistake.

Is there away to inscrease the width of the multi-value parameter box?

Best regards.

I guess you can do that by changing the stylesheet provided for controlling the layout and style of report manager. If your clients are using report manager, then read on:

There is a file called ReportingServices.css in Program Files\Microsoft SQL Server\MSSQL.X\Reporting Services\ReportManager\Styles.

Edit that in notepad and locate a class called "msrs-multiValueDropDown" and in that class add width property as follows and save the file:

.msrs-multiValueDropDown

{

border: 1px DarkGray solid;

width: 200px;

}

Shyam

|||

That could work, yes. I'll sure try it tomorrow when I get to work.

My only problem with that approach is that it's an horizontal change. All reports will be affected unless theres a way to tell that report, or parameter, to use a specific sheet or value from the default sheet.|||

No, it cannot be done on a report by report basis.

So, can you please mark my post as answer?

sql

Multi-Value parameter syntax error

I am new to reporting services...
I've created a report that uses a multi-value parameter based ona dataset
usign a simple select statement. It works fine when a single value is
selected, but when multiple values are selected returns the error Incorrect
Syntax near ','.
How can the report code be modified to pass multiple values with the correct
syntax?See my response to your other posting (which is basically, show us what you
did). My guess is you have an incorrect SQL statement OR you are not going
against SQL Server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"StMaas" <StMaas@.discussions.microsoft.com> wrote in message
news:7342117C-9715-477F-A9F4-E748C2EA3A77@.microsoft.com...
>I am new to reporting services...
> I've created a report that uses a multi-value parameter based ona dataset
> usign a simple select statement. It works fine when a single value is
> selected, but when multiple values are selected returns the error
> Incorrect
> Syntax near ','.
> How can the report code be modified to pass multiple values with the
> correct
> syntax?
>

Multi-Value Parameter

I have a stored procedure im passing into Reporting Services. Only problem is , What do i need to change to allow the user to select more then one value. I already know what to do on the reporting services side, but it keeps erroring with the data source IE my stored procedure. Here's the code:

Code Snippet

SE [RC_STAT]

GO

/****** Object: StoredProcedure [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY] Script Date: 09/05/2007 13:49:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[PROC_RPT_EXPENSE_DETAIL_DRILLDOWN_COPY]

(@.Region int = Null)

AS

BEGIN

SELECT Budget_Reporting_Detail.Budget_Report_Detail_Datetime, Budget_Reporting_Detail.Budget_Report_Detail_Document_Type,

Budget_Reporting_Detail.Budget_Report_Detail_Code, Budget_Reporting_Detail.Budget_Report_Detail_Description,

ISNULL(Budget_Reporting_Detail.Budget_Report_Detail_Amount, 0) AS Actual, Budget_Reporting_Detail.Budget_Report_Detail_Qty,

Budget_Reporting_Detail.Budget_Report_Detail_Responsible, Territory.Name+'('+Code+')' as [Name], Region.Region, Round((Forecast.Budget_Amount/13),2) AS Budget,

Forecast.Budget_Type_Code, Forecast.Budget_Year, Budget_Forecast_Period,

Forecast.SalesPerson_Purchaser_Code

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget_Reporting_Detail AS Budget_Reporting_Detail RIGHT OUTER JOIN

RC_DWDB_INSTANCE_1.dbo.Region AS Region RIGHT OUTER JOIN

(SELECT Budget_Type_Code, Budget_Year, SalesPerson_Purchaser_Code, Budget_Amount

FROM RC_DWDB_INSTANCE_1.dbo.Tbl_Budget

) AS Forecast INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Territory AS Territory INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Sales_Responsible AS Territory_In_Sales_Responsible ON

Territory.Code = Territory_In_Sales_Responsible.Territory_Code INNER JOIN

RC_DWDB_INSTANCE_1.dbo.Tbl_Territory_In_Region AS Territory_In_Region ON Territory_In_Region.Territory_Code = Territory.Code ON

Forecast.SalesPerson_Purchaser_Code = Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code ON

Region.Region_Key = Territory_In_Region.Region_Key ON Budget_Reporting_Detail.Budget_Type_Code = Forecast.Budget_Type_Code AND

Budget_Reporting_Detail.Budget_Year = Forecast.Budget_Year AND

Budget_Reporting_Detail.SalesPerson_Purchaser_Code = Forecast.SalesPerson_Purchaser_Code

WHERE (Region.Region_Key IN( @.Region)) AND (Forecast.Budget_Year = 2007)

END

what am i doing wrong?

You can concatenate the multi values in a comma delimited string and pass the string to the stored procedure. you can create a function in SS to split the string and return a table.

Expression used to populate the parameter

=Join(Parameters! <ParameterName> .Value,", ")

Arrays and Lists in SQL Server

http://www.sommarskog.se/arrays-in-sql.html

AMB

Multi-user issues with SSRS2000

Hi,

At our company we are looking at implementing SQL Server 2000 Reporting Services into our internet application. Having followed your book, and produced a dozen reports, I have found a problem with calling reports in a multi-user environment.

Some of the reports that our customers want can run to over 100 pages which, whilst a large number, is a requirement. I have found that when two people call one of these large reports at the same time; one of the users gets the error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection". Once the worker process begins to render the report on the first user's screen, the report may be refreshed on the second users screen and it renders quite normally. Under normal circumstances the login credentials are valid and identical for both users.

Furthermore, an error 'Server Application Unavailable' is often generated when a number of users are requesting large reports this appears to be caused by the worker process being recycled.

Our production servers are a mixture of Windows 2000 Server (SP4) and Windows 2003 Server Standard Edition (SP1), the version of SQL Server and Report services is 2000 Enterprise Edition, using the latest service packs (SQL Server is SP4, and report services is SP2).

Can you suggest how we can get around these errors.

Regards,

Martin

Because your large report is consuming so much memory during the rendering process, your worker process is probably recycling because it's hitting the max MemoryLimit as defined in machine.config.

The "Login Failed" error message that you mentioned is a symptom of your worker process recycling, coming back up, and then trying to request a report on your behalf...however, because it cycled, it lost your user context (credentials), ergo the "Null" user...

The article below discusses dealing with memory limits / issues with large reports.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;909678

sql

Multi-user errors

Hi,
At our company we are looking at implementing SQL Server 2000 Reporting
Services into our internet application. Having followed your book, and
produced a dozen reports, I have found a problem with calling reports
in a multi-user environment.
Some of the reports that our customers want can run to over 100 pages
which, whilst a large number, is a requirement. I have found that when
two people call one of these large reports at the same time; one of the
users gets the error "Login failed for user '(null)'. Reason: Not
associated with a trusted SQL Server connection". Once the worker
process begins to render the report on the first user's screen, the
report may be refreshed on the second users screen and it renders quite
normally. Under normal circumstances the login credentials are valid
and identical for both users.
Furthermore, an error 'Server Application Unavailable' is often
generated when a number of users are requesting large reports this
appears to be caused by the worker process being recycled.
Our production servers are a mixture of Windows 2000 Server (SP4) and
Windows 2003 Server Standard Edition (SP1), the version of SQL Server
and Report services is 2000 Enterprise Edition, using the latest
service packs (SQL Server is SP4, and report services is SP2).
Can anyone suggest how we can get around these errors.
Regards,
MartinMartin,
It sounds like the problems occur during the processing/collecting of
data and that once that finishes and the reports begin rendering the
conflicts resolve. The first thing that comes to mind is rendering
these reports from snapshot data. Using a snapshot, you schedule the
query that generates your large dataset to run during off peak hours.
The reports can then be rendered immediately from the (already)
collected data rather than both collecting and rendering. This of
course assumes that you don't need up-to-the-moment i.e. realtime data
for the reports otherwise snapshots may not be an option for you.
Another option might be using subscriptions to distribute (push)
multiple copies of the reports rendered from one run of the process.
This might be the way to go if this an instance of one report being
called (pulled) by several users. Again, this assumes relatively
static data.
HTH
toolman
MartinClayton wrote:
> Hi,
> At our company we are looking at implementing SQL Server 2000 Reporting
> Services into our internet application. Having followed your book, and
> produced a dozen reports, I have found a problem with calling reports
> in a multi-user environment.
> Some of the reports that our customers want can run to over 100 pages
> which, whilst a large number, is a requirement. I have found that when
> two people call one of these large reports at the same time; one of the
> users gets the error "Login failed for user '(null)'. Reason: Not
> associated with a trusted SQL Server connection". Once the worker
> process begins to render the report on the first user's screen, the
> report may be refreshed on the second users screen and it renders quite
> normally. Under normal circumstances the login credentials are valid
> and identical for both users.
> Furthermore, an error 'Server Application Unavailable' is often
> generated when a number of users are requesting large reports this
> appears to be caused by the worker process being recycled.
> Our production servers are a mixture of Windows 2000 Server (SP4) and
> Windows 2003 Server Standard Edition (SP1), the version of SQL Server
> and Report services is 2000 Enterprise Edition, using the latest
> service packs (SQL Server is SP4, and report services is SP2).
> Can anyone suggest how we can get around these errors.
> Regards,
> Martin

Multi-Server setup: RS, Sharepoint, new SP2 Webparts

I was reading the SP2 readme and I see this in section 4.1.1:
"To use the Reporting Services SharePoint Web parts, Report Server and
Report Manager must both be installed."
My company's RS server is on it's own box (A). The RS Configuration db
is on it's own box (B). Our intranet (Sharepoint) site is on it's own
box (C). 3 servers total. I want to try out the 2 new web parts. Can I
just copy the the RSWebParts.cab from server A to our Sharepoint server
C and install it or do I actually have to install a full blown
installation of RS plus RS SP2 on that Sharepoint server C? If so, why?
Because I was planning to point the web parts to the reports that exist
on the separate RS server A, or is that not permitted?
Am I going to have to redeploy my reports to C, manage a second
configuration database / report server? I hope not.
Thank you!No, you do not need to install RS on Box C. The parts can be pointed to any
existing RS. The point of the documentation was to let people know they
needed a working RS for the parts to work, not that they needed to all exist
on the same box.
I hope that helps.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dave" <macleary2000@.yahoo.com> wrote in message
news:1134065827.784617.228020@.g44g2000cwa.googlegroups.com...
>I was reading the SP2 readme and I see this in section 4.1.1:
> "To use the Reporting Services SharePoint Web parts, Report Server and
> Report Manager must both be installed."
> My company's RS server is on it's own box (A). The RS Configuration db
> is on it's own box (B). Our intranet (Sharepoint) site is on it's own
> box (C). 3 servers total. I want to try out the 2 new web parts. Can I
> just copy the the RSWebParts.cab from server A to our Sharepoint server
> C and install it or do I actually have to install a full blown
> installation of RS plus RS SP2 on that Sharepoint server C? If so, why?
> Because I was planning to point the web parts to the reports that exist
> on the separate RS server A, or is that not permitted?
> Am I going to have to redeploy my reports to C, manage a second
> configuration database / report server? I hope not.
> Thank you!
>

Multi-Select Parameters in SSRS 2005 - HELP

Hi all â'
Having a problem with SQL 2005 Reporting Services and Multi-Select
parameters. Itâ's seems as though Multi-Select and Allow Nulls are mutually
exclusive. This is a problem as we have some reports that are very complex â'
using very complex stored procedures that allow for this scenario. Now â' put
you hand over your ears for a second â' this works fine in Cryatsl â' the sproc
is set up to accept a null or a list.
WHERE (@.parm is NULL or element = @.parm)
There must be a way around this in SSRS
ThanksTry doing this...
where (@.parm is null or element in (@.parm))
Cheers|||My bad - I mistated the problem - the problem is not in the sproc - it is in
SRSS Designer. It will not let you set both Multi-Select and Allow Nulls on a
parameter - they are mutually exclusive. If you try to set both - the
designer gives an error and will not save your changes. It will on the other
hand - let you set one or the other individually.
Ths sproc workd just fine as we have sent it a list and a NULL value and it
works both ways as intended.
Thanks...
"sullins602" wrote:
> Try doing this...
> where (@.parm is null or element in (@.parm))
> Cheers
>|||Did you try returning a value of null to the dataset that populates the
list for the parameter?
select
null as 'val',
' - All - ' as 'label'
union
select
realval as 'val',
reallabel as 'lable'
from
mytable|||I just ran into the same problem today with a multi-value and null
parameter. I also thought to add null to the dataset, but when I added
the null to the parameter dataset it did not show up in the report
drop-down box.
Could it be possible this is being filtered out?
One way that does work is to use a actual value instead of a null
(assuming int).
SELECT -1 AS Level1Value, ' - None -' AS Level1Label
UNION ALL
<rest of query>
But I would rather have the option to not require a value to be
selected.
Any other ideas on making this work?|||I could try that - the only drawback that I see would be that All would then
be part of the mustiselect list - then what would happen if they select all
plus some of the other values - would they not get a sting like
'NULL','Val1','Val2'....
"sullins602" wrote:
> Did you try returning a value of null to the dataset that populates the
> list for the parameter?
> select
> null as 'val',
> ' - All - ' as 'label'
> union
> select
> realval as 'val',
> reallabel as 'lable'
> from
> mytable
>

Multi-select parameter in SQL Server Rptg Svcs 2005

I am using a beta version of reporting services 2005.
I have created my report parameters as multi-select. However, I'm having
trouble having the query parameters accept the list that the report
parameters passes. (below) I tried replacing the = with in and between and
neither work. It doesn't like the comma between the multiple values. I
can't imagine that they've allowed multi-select report parameters without a
way to pass them cleanly to the query parameters.
Any help would be greatly appreciated. Thank you in advance for any help.
Machelle
Select * from candidate_info
where (recruiting_year in @.p_Year) and
(candidate_type_display in @.p_candidate_type) and
(degree_level in @.p_degree) and
(owner_site_code in @.p_site_owner) and
(offer_job_grade in @.p_grade)Try putting parenthese around the parameter like you normally would if you
were providing a hardcoded list ie
Select * from candidate_info
> where (recruiting_year in (@.p_Year)) and
> (candidate_type_display in (@.p_candidate_type)) and
> (degree_level in (@.p_degree)) and
> (owner_site_code in (@.p_site_owner)) and
> (offer_job_grade in (@.p_grade))
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Machelle" <Machelle@.discussions.microsoft.com> wrote in message
news:3EF55E31-2626-4B24-86FD-97E087999FF8@.microsoft.com...
>I am using a beta version of reporting services 2005.
> I have created my report parameters as multi-select. However, I'm having
> trouble having the query parameters accept the list that the report
> parameters passes. (below) I tried replacing the = with in and between and
> neither work. It doesn't like the comma between the multiple values. I
> can't imagine that they've allowed multi-select report parameters without
> a
> way to pass them cleanly to the query parameters.
> Any help would be greatly appreciated. Thank you in advance for any help.
> Machelle
> Select * from candidate_info
> where (recruiting_year in @.p_Year) and
> (candidate_type_display in @.p_candidate_type) and
> (degree_level in @.p_degree) and
> (owner_site_code in @.p_site_owner) and
> (offer_job_grade in @.p_grade)
>|||I love it when it's a simple answer - thanks Wayne I really appreciate your
taking the time to answer my question.
"Wayne Snyder" wrote:
> Try putting parenthese around the parameter like you normally would if you
> were providing a hardcoded list ie
> Select * from candidate_info
> > where (recruiting_year in (@.p_Year)) and
> > (candidate_type_display in (@.p_candidate_type)) and
> > (degree_level in (@.p_degree)) and
> > (owner_site_code in (@.p_site_owner)) and
> > (offer_job_grade in (@.p_grade))
>
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "Machelle" <Machelle@.discussions.microsoft.com> wrote in message
> news:3EF55E31-2626-4B24-86FD-97E087999FF8@.microsoft.com...
> >I am using a beta version of reporting services 2005.
> >
> > I have created my report parameters as multi-select. However, I'm having
> > trouble having the query parameters accept the list that the report
> > parameters passes. (below) I tried replacing the = with in and between and
> > neither work. It doesn't like the comma between the multiple values. I
> > can't imagine that they've allowed multi-select report parameters without
> > a
> > way to pass them cleanly to the query parameters.
> >
> > Any help would be greatly appreciated. Thank you in advance for any help.
> >
> > Machelle
> >
> > Select * from candidate_info
> > where (recruiting_year in @.p_Year) and
> > (candidate_type_display in @.p_candidate_type) and
> > (degree_level in @.p_degree) and
> > (owner_site_code in @.p_site_owner) and
> > (offer_job_grade in @.p_grade)
> >
>
>

Multi-select Parameter - Single Quote Problem

Hi,
I am using SQL Server 2005 Reporting Services. I am trying to pass a
multi-select string value to my query but having difficulties with
single quotes. My query is:
SELECT * FROM Customers WHERE CustomerID > 200 AND CustomerType IN
(@.CustomerType)
@.CustomerType is a multi-select parameter in my report. The value of
this parameter should be passed as 'Commercial','Residential' BUT I
think it is being passed as 'Commercial, Residential'
Please help.
Thanks
SajjadAre you putting in the single quotes in your selection? You shouldn't.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<sajjad.mussani@.gmail.com> wrote in message
news:1158093243.863677.186330@.d34g2000cwd.googlegroups.com...
> Hi,
> I am using SQL Server 2005 Reporting Services. I am trying to pass a
> multi-select string value to my query but having difficulties with
> single quotes. My query is:
> SELECT * FROM Customers WHERE CustomerID > 200 AND CustomerType IN
> (@.CustomerType)
> @.CustomerType is a multi-select parameter in my report. The value of
> this parameter should be passed as 'Commercial','Residential' BUT I
> think it is being passed as 'Commercial, Residential'
> Please help.
> Thanks
> Sajjad
>|||I am not putting single quote in my selection. My selection is done
using a Multi-select drop down box. What I was trying to say is that,
I think the parameter should be passed with single quotes around each
selection, but it is not.
Sajjad
Bruce L-C [MVP] wrote:
> Are you putting in the single quotes in your selection? You shouldn't.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> <sajjad.mussani@.gmail.com> wrote in message
> news:1158093243.863677.186330@.d34g2000cwd.googlegroups.com...
> > Hi,
> >
> > I am using SQL Server 2005 Reporting Services. I am trying to pass a
> > multi-select string value to my query but having difficulties with
> > single quotes. My query is:
> >
> > SELECT * FROM Customers WHERE CustomerID > 200 AND CustomerType IN
> > (@.CustomerType)
> >
> > @.CustomerType is a multi-select parameter in my report. The value of
> > this parameter should be passed as 'Commercial','Residential' BUT I
> > think it is being passed as 'Commercial, Residential'
> >
> > Please help.
> > Thanks
> > Sajjad
> >|||I don't think that is the issue. I do this all the time. Is your query in a
stored procedure?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sajjad" <sajjad.mussani@.gmail.com> wrote in message
news:1158158039.291968.300150@.m73g2000cwd.googlegroups.com...
>I am not putting single quote in my selection. My selection is done
> using a Multi-select drop down box. What I was trying to say is that,
> I think the parameter should be passed with single quotes around each
> selection, but it is not.
> Sajjad
> Bruce L-C [MVP] wrote:
>> Are you putting in the single quotes in your selection? You shouldn't.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> <sajjad.mussani@.gmail.com> wrote in message
>> news:1158093243.863677.186330@.d34g2000cwd.googlegroups.com...
>> > Hi,
>> >
>> > I am using SQL Server 2005 Reporting Services. I am trying to pass a
>> > multi-select string value to my query but having difficulties with
>> > single quotes. My query is:
>> >
>> > SELECT * FROM Customers WHERE CustomerID > 200 AND CustomerType IN
>> > (@.CustomerType)
>> >
>> > @.CustomerType is a multi-select parameter in my report. The value of
>> > this parameter should be passed as 'Commercial','Residential' BUT I
>> > think it is being passed as 'Commercial, Residential'
>> >
>> > Please help.
>> > Thanks
>> > Sajjad
>> >
>|||> SELECT * FROM Customers WHERE CustomerID > 200 AND CustomerType IN
> (@.CustomerType)
From SQL perspective, this won't work. You cannot pass such parameter. You
must either use dynamic sql ('... cust in ''' + @.cust ...) or parse this
multiparameter into table variable and use this table in subsequent query.
Radim Hampel

Multi-record wrapping textbox?

I know that the subject is badly expressed, but hopefully I can get the
point across...
I'm a total newbie at MS Reporting services and I have a question. I
have a dataset something like this:
Customer Box Item Qty
A 1 X1 50
A 1 X2 50
A 2 X3 75
A 2 X4 25
A 3 X5 15
B 1 X1 35
B 1 X2 65
B 2 X2 10
B 2 X3 10
etc... you get the idea. Each box holds 100 mixed items; each customer
has one or more boxes.
The output report I want should look something like this:
Customer Box Contents
A 001 X1 50 * X2 50
002 X3 75 * X4 25
003 X5 15
Total for customer A: 3 boxes, 215 pieces
B 001 X1 35 * X2 65
002 X2 10 * X3 10 * X4 20 * X5 10 * X6
5
X7 5 * X8 40
003 X9 50
Total for customer B: 3 boxes, 250 pieces
I set it up as a report with two grouping levels. The tricky part is
the "contents" column. I want it to be a textbox that can be up to a
certain width, wrapping if necessary, and containing a string of data
from records. Any way to do that with Reporting Svcs?
If it helps, when I did this with MS Access, I made the wrapping
textbox part as a subreport with columns. I can try that with RS but
it seems like it might be possible another way?
I should also mention that I'm using SQL Server 2000, VB.NET 2003 as my
designer, so perhaps I don't have access to the newest features of 2005.I wrote:
> The tricky part is
> the "contents" column. I want it to be a textbox that can be up to a
> certain width, wrapping if necessary, and containing a string of data
> from records. Any way to do that with Reporting Svcs?
Never mind; I found this blog entry
http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx
that gives a quite serviceable method for getting what I want.

Multiple-step OLE DB operation generated errors

I'm trying to view a report on Report Manager (Reporting Services 2000) that displays Analysis Services (2000) data. I keep getting the following error message:

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

Cannot create a connection to data source 'CubeName'. (rsErrorOpeningConnection) Get Online Help

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

I am using Visual Studio 2003 to build the report and I can successfully view the cube and pull data but when I deploy the report and data source to Report Manager I keep getting this error message. I am not using my credentials for the data source I am using a SQL account that is a sys admin and has access to the cube I am trying to view.

Additional Information: Visual Studio - local machine
SQL Server/Analysis Services - Machine A
Reporting Services - Machine B

See this http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=706585&SiteID=1 is any help.

Multiple-step OLE DB operation generated errors

I'm trying to view a report on Report Manager (Reporting Services 2000) that displays Analysis Services (2000) data. I keep getting the following error message:

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

Cannot create a connection to data source 'CubeName'. (rsErrorOpeningConnection) Get Online Help Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

I am using Visual Studio 2003 to build the report and I can successfully view the cube and pull data but when I deploy the report and data source to Report Manager I keep getting this error message. I am not using my credentials for the data source I am using a SQL account that is a sys admin and has access to the cube I am trying to view.

Additional Information: Visual Studio - local machine
SQL Server/Analysis Services - Machine A
Reporting Services - Machine B

See this http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=706585&SiteID=1 is any help.

multiples charts and tables on the same report from multiple datasets

Hi,
I am trying to use Reporting services toi create a report with a number of
charts and tables from the same datasource but multiple datasets. When I
created a table and dragged the data fields into the column it is
=First(Fields!OrderID.Value, "DataSet1")
It keeps repeating until the end of rows where in the dataset I used top5
SQL what should be the correct way to do it?
Also I am wondering if I can modify the color of and space between bar chart
items.
Thank you in advance,
SunnyRemove "First" from your code, so you end up with =Fields!OrderID.Value.
Also, in the charts, you might have to change your values. At least for me
the values shows up as =Count(Fields!OrderID.Value), and I usually don't
want to have "Count" at all.
You can change the background color of a chart, just right click and choose
properties. (DOn't remeber where, but you'll find it if you look for it.)
DOn't think you can change the spacing, except with just changing the size
of the chart.
Kaisa M. LIndahl
"Sansanee" <sansanee@.nospam.com> wrote in message
news:ufPXgHlNFHA.3704@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am trying to use Reporting services toi create a report with a number of
> charts and tables from the same datasource but multiple datasets. When I
> created a table and dragged the data fields into the column it is
> =First(Fields!OrderID.Value, "DataSet1")
> It keeps repeating until the end of rows where in the dataset I used top5
> SQL what should be the correct way to do it?
> Also I am wondering if I can modify the color of and space between bar
chart
> items.
>
> Thank you in advance,
> Sunny
>

Multiple-resultset stored procedures

Hi,
Is it possible to use multiple-resultset stored procedures with Reporting
Services 2005?
I use SS2005, SP1.
Thanks in advance
Nikola MilicNo it is not. RS only can use one resultset. You could add a parameter and
based on the parameter return the appropriate resultset and then hardcode
that parameter in your report when call the SP. However, that does mean your
SP is called multiple times.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nikola Milic" <hotmnikola@.hotmail.com> wrote in message
news:%23tPpPeB0GHA.2072@.TK2MSFTNGP06.phx.gbl...
> Hi,
> Is it possible to use multiple-resultset stored procedures with Reporting
> Services 2005?
> I use SS2005, SP1.
> Thanks in advance
> Nikola Milic
>

Multiple values contained in query string to be used as parameter in Report. HELP!

Hi,

I'm a bit desperate here so any solutions would be much appreciated. I'm using SQL reporting services 2005 & .NET2.0 & Oracle DB.

I need to pass multiple values in the query string, or any other suggested way, to my report to be used as a parameter. The idea is a user on the web app will do a search & select an item to open a new form.

The user can print the report from here, by pressing a button and being redirected to the report's URL. In this form there will be many (could be alot) of IDs that I will need to pass to the report and the report will use these as a parameter. The problem is that I am passing it at the moment as a comma deliminated string so instead of the SQL doing this : "ID in ('1', '2')", it's doing this: "ID in ('1, 2').

What are my alternatives? Thanks!

RS 2005 supports multi-value report parameters (e.g. http://msdn2.microsoft.com/en-us/library/aa337292.aspx).

If you use the "Oracle" data extension and write your Oracle query with a query parameter like:
select * from emp where ename IN (:EmployeeName)

RS will generate a report parameter (which you need to mark as multi-value report parameter) linked to the query parameter and perform an automatic multi-value query rewrite so that the Oracle query will be executed with correctly generated IN clause contents.

-- Robert

|||Cheers Robert.

Multiple value parameters in SQL Server 2000 Reporting Services.

Hi all,

I need to know how, and if, possible to create a multiple value parameter in SQL Server 2000 Reporting Services. I need this for a client of mine. Any help/tips/etc will be greatly appreciated.

Thank you,

Hi,

Unfortunatelly, there is no official way to push multipy values fo parmeters in SSRS 2000, however, you can hardcode a list with multiply values behind eg: Sales Users as text and "Jim; Bob " ...etc as values and reuse them in the procedure or construct your t-sql string based on parameter with sp_executesql. There is a security risk with the last option.

Regards,

Janos

|||Thanks a lot Janos. I'll see what I can do. Maybe I can convince the client to go to SSRS 2005?

Let's hope.

Thanks anyway,

Multiple users, SourceSafe, and Reporting Services

I am new to the .NET world. I have created a Solution with some Projects in
them for Reporting Services. I have successfually integrated SourceSafe with
my environment and Added the solutions to Sourcesafe.
Now I have a co-worker who needs to begin sharing my solution and Projects.
I have him set up in SourceSafe but can not seem to find a way to get his
environment to "check out" my solution and bind his environment into
SourceSafe like I have mine bound.In the VS IDE, try File>Source Control>Open From Source Control
HTH
"mlapoint" wrote:
> I am new to the .NET world. I have created a Solution with some Projects in
> them for Reporting Services. I have successfually integrated SourceSafe with
> my environment and Added the solutions to Sourcesafe.
> Now I have a co-worker who needs to begin sharing my solution and Projects.
> I have him set up in SourceSafe but can not seem to find a way to get his
> environment to "check out" my solution and bind his environment into
> SourceSafe like I have mine bound.|||Worked like a charm. Thanks!
"Alien2_51" wrote:
> In the VS IDE, try File>Source Control>Open From Source Control
> HTH
> "mlapoint" wrote:
> > I am new to the .NET world. I have created a Solution with some Projects in
> > them for Reporting Services. I have successfually integrated SourceSafe with
> > my environment and Added the solutions to Sourcesafe.
> >
> > Now I have a co-worker who needs to begin sharing my solution and Projects.
> > I have him set up in SourceSafe but can not seem to find a way to get his
> > environment to "check out" my solution and bind his environment into
> > SourceSafe like I have mine bound.

Multiple subreports in a report

I'm trying to generate two reports from one reporting services URL, so
I created a "master report" that simploy contains two sub-reports.
When I run it in the preview browser, one subreport runs, and not the
2nd.
In the RS Manager, both subreports say nothing but "Error: Subreport
could not be shown".
I wonder if it's a datasource problem, or something else?
Thanks,
JackI imagine that it is a datasource issue, if it runs fine in report designer
preview.
I have a report that has 5 subreports and the only problem/change that I had
to make was to make sure that the data source is set properly for all the
subreports. For some reason it looses it occasionally when the reports are
deployed.
"jackfreud" wrote:
> I'm trying to generate two reports from one reporting services URL, so
> I created a "master report" that simploy contains two sub-reports.
> When I run it in the preview browser, one subreport runs, and not the
> 2nd.
> In the RS Manager, both subreports say nothing but "Error: Subreport
> could not be shown".
> I wonder if it's a datasource problem, or something else?
> Thanks,
> Jack
>|||Just had similar issue. Make sure the subreport operates standalone, with the
correct datasource, and proper parameters are passed to the sub report
property on the main report.
"jackfreud" wrote:
> I'm trying to generate two reports from one reporting services URL, so
> I created a "master report" that simploy contains two sub-reports.
> When I run it in the preview browser, one subreport runs, and not the
> 2nd.
> In the RS Manager, both subreports say nothing but "Error: Subreport
> could not be shown".
> I wonder if it's a datasource problem, or something else?
> Thanks,
> Jack
>

Multiple Server Reporting

Hi There,
I got a generic DW question.
Let me preface this
I need to design a report that displays data for 3 branches of same
companies, Branch A ,Branch B and Branch C. The problem is that each Branch
has their own distinct server and database. The schema is identical between
them, just
different data in each.
The report is identical for each Branch, the only different is that the
report needs to pull data from all the three Databases A,B,C to display the
report data for that Company as an aggregated view and Also data for each
branch individually.
How can I design the report or setup the data source.
1) One possible option I guess would be to create a Linked Server and
Distributed view that combines data from identical tables in 3 databases, and
use that view as the datasource in the report.
2) Other option is moving data to the centralized server and report from
that,
which would cause data redundancy both at local branch and at centralized
server and huge vloume of data movement and keeping track of data.
Just looking for what others have done in similar circumstances so I don't
spend multiple days architecting the wrong approach.
Thanks!
S
Centralize data is the better solution.
maybe there is some data to copy, but any query is local, so the response
time is good.
also, you can retrieve data for your reports only instead-of detailed
information.
"ERS Developer" <ERSDeveloper@.discussions.microsoft.com> wrote in message
news:C501637B-D71F-4F72-AF62-B06BBED83A8E@.microsoft.com...
> Hi There,
> I got a generic DW question.
> Let me preface this
> I need to design a report that displays data for 3 branches of same
> companies, Branch A ,Branch B and Branch C. The problem is that each
> Branch
> has their own distinct server and database. The schema is identical
> between
> them, just
> different data in each.
> The report is identical for each Branch, the only different is that the
> report needs to pull data from all the three Databases A,B,C to display
> the
> report data for that Company as an aggregated view and Also data for each
> branch individually.
> How can I design the report or setup the data source.
> 1) One possible option I guess would be to create a Linked Server and
> Distributed view that combines data from identical tables in 3 databases,
> and
> use that view as the datasource in the report.
> 2) Other option is moving data to the centralized server and report from
> that,
> which would cause data redundancy both at local branch and at centralized
> server and huge vloume of data movement and keeping track of data.
> Just looking for what others have done in similar circumstances so I don't
> spend multiple days architecting the wrong approach.
> Thanks!
> S
>
|||Hi S,
the generic question is
'I have distributed data in operational systems, how do I perform
consolidated reporting?'
and the generic answer is:
'bring the data together and integrate it via ODS/DW style processing
system depending on what your needs are. Then, if the data needs to be
re-distributed from the centralised data store back out to various
areas then do that.'
You will almost certainly find that even though the systems are the
same the data in it will be different and will not 'automatically' line
up unless someone forced the user community to use some set of data
standards which almost never happens.
However, with todays line speeds and tools like RS presenting data in
web browsers the need for distributring that data back out has
dramatically reduced. I rarely re-distribute the data back out to so
called 'data marts'...I have been an advocate of 'if the data is
valuable and important it is worth centralising and managing it
properly' for over 20 years now... ;-)....I have seen many people talk
about 'fetch the data from the operational system when it is needed'
come and go....many are still coming along and selling that as 'the
way to do reporting and analysis'.....the folks who go that way
generally regret it very much and end up migrating to an ODS/DW style
solution sooner or later...especially as volumes increase...
Best Regards
Peter Nolan
www.peternolan.com
|||Hi Peter,
Thanks for the clarification always there has been a debate whether or not
to move the data around.Looks like unless we do that no way we can get
efficient reports Let me try the DW.
/
S
"Peter Nolan" wrote:

> Hi S,
> the generic question is
> 'I have distributed data in operational systems, how do I perform
> consolidated reporting?'
> and the generic answer is:
> 'bring the data together and integrate it via ODS/DW style processing
> system depending on what your needs are. Then, if the data needs to be
> re-distributed from the centralised data store back out to various
> areas then do that.'
> You will almost certainly find that even though the systems are the
> same the data in it will be different and will not 'automatically' line
> up unless someone forced the user community to use some set of data
> standards which almost never happens.
> However, with todays line speeds and tools like RS presenting data in
> web browsers the need for distributring that data back out has
> dramatically reduced. I rarely re-distribute the data back out to so
> called 'data marts'...I have been an advocate of 'if the data is
> valuable and important it is worth centralising and managing it
> properly' for over 20 years now... ;-)....I have seen many people talk
> about 'fetch the data from the operational system when it is needed'
> come and go....many are still coming along and selling that as 'the
> way to do reporting and analysis'.....the folks who go that way
> generally regret it very much and end up migrating to an ODS/DW style
> solution sooner or later...especially as volumes increase...
> Best Regards
> Peter Nolan
> www.peternolan.com
>
|||I think the answer depends on how much data, the query performance
requirement, and the frequency of the report. If there is a relatively large
amount of data that would need to be moved, the report is only run once per
month, and the user doesn't really care if the report takes 20 minutes vs. 2
minutes to run, then just setup a partitioned view.
If there are multiple reports that could make use of the localized data,
then the case for building a localized copy of the data would be somewhat
stronger.
"ERS Developer" <ERSDeveloper@.discussions.microsoft.com> wrote in message
news:C501637B-D71F-4F72-AF62-B06BBED83A8E@.microsoft.com...
> Hi There,
> I got a generic DW question.
> Let me preface this
> I need to design a report that displays data for 3 branches of same
> companies, Branch A ,Branch B and Branch C. The problem is that each
> Branch
> has their own distinct server and database. The schema is identical
> between
> them, just
> different data in each.
> The report is identical for each Branch, the only different is that the
> report needs to pull data from all the three Databases A,B,C to display
> the
> report data for that Company as an aggregated view and Also data for each
> branch individually.
> How can I design the report or setup the data source.
> 1) One possible option I guess would be to create a Linked Server and
> Distributed view that combines data from identical tables in 3 databases,
> and
> use that view as the datasource in the report.
> 2) Other option is moving data to the centralized server and report from
> that,
> which would cause data redundancy both at local branch and at centralized
> server and huge vloume of data movement and keeping track of data.
> Just looking for what others have done in similar circumstances so I don't
> spend multiple days architecting the wrong approach.
> Thanks!
> S
>