Hello,
We have used SSAS and SSRS to develop a BI reporting suite for our data
warehouse and would like to deploy this into our customer base, but at the
same time protect our investment.
How do we control or limit the number of users that can use our cubes and
reports? We would like to control user numbers by a licence arrangement -
either concurrent or named - but fear once a user has our developments
installed they only need to increase their MS licences and away they go...
Any help and advice appreciated.
StephenYou could do the following for named users.
1. Have your own routine that authorizes. Put the user names into a table.
Then somewhere else have the total number allowed encrypted.
2. In your reports (or even better in stored procedures) use the User!UserID
global parameter and verify the user. You could do this in a custom assembly
too. You could put a nag message on each report.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Stephen Lonsdale" <StephenLonsdale@.discussions.microsoft.com> wrote in
message news:4AE7D682-F6D1-4E60-8641-03EC900EA69D@.microsoft.com...
> Hello,
> We have used SSAS and SSRS to develop a BI reporting suite for our data
> warehouse and would like to deploy this into our customer base, but at the
> same time protect our investment.
> How do we control or limit the number of users that can use our cubes and
> reports? We would like to control user numbers by a licence arrangement -
> either concurrent or named - but fear once a user has our developments
> installed they only need to increase their MS licences and away they go...
> Any help and advice appreciated.
> Stephen
Showing posts with label reporting. Show all posts
Showing posts with label reporting. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
How to connect to Analysis Service Database through reporting service?
Hello,
In our project, we would like to use the same data source for our analysis service database cubes and for our reporting service reports.
I created the analysis service project first, deployed successfully. When trying to setting up the data source in the report model project, I selected the "create a data source based on another object", and then selected the "create a data source based on an analysis service project". However, there is no analysis service project to select, and no browse button to see where the reporting service is looking for analysis service project either.
I have tried creating a new analysis service project with data source views, cubes, dimensions and all the stuff, but still cannot see the analysis service project in the drop down box to be selected for my reporting model project data source.
As I am fairly new to the reporting service, I'm sure I'm missing something, but couldn't find much information in the help or on the web. Any suggestion would be much appreciated.
Thank you very much,
Annie
To create a model based on an Analysis Services cube, use Report Manager. First create a data source which points to the cube, then click "Generate Model" on the data source properties page. AS models can't be edited. Full details are in Books Online, under "Reporting Services How-To Topics".
If you just want to create a report which uses the same data source as your cube, you do not need a report model project. Instead create a report project, add a new report, and create a report data source which uses the same connection settings as the cube data source.
-Albert
|||Can you tell me how to open up the report manager?|||http://<your server name>/reports|||It doesn't work for http://localhsot/reportssqlHow to connect to Analysis Service Database through reporting service?
Hello,
In our project, we would like to use the same data source for our analysis service database cubes and for our reporting service reports.
I created the analysis service project first, deployed successfully. When trying to setting up the data source in the report model project, I selected the "create a data source based on another object", and then selected the "create a data source based on an analysis service project". However, there is no analysis service project to select, and no browse button to see where the reporting service is looking for analysis service project either.
I have tried creating a new analysis service project with data source views, cubes, dimensions and all the stuff, but still cannot see the analysis service project in the drop down box to be selected for my reporting model project data source.
As I am fairly new to the reporting service, I'm sure I'm missing something, but couldn't find much information in the help or on the web. Any suggestion would be much appreciated.
Thank you very much,
Annie
To create a model based on an Analysis Services cube, use Report Manager. First create a data source which points to the cube, then click "Generate Model" on the data source properties page. AS models can't be edited. Full details are in Books Online, under "Reporting Services How-To Topics".
If you just want to create a report which uses the same data source as your cube, you do not need a report model project. Instead create a report project, add a new report, and create a report data source which uses the same connection settings as the cube data source.
-Albert
|||Can you tell me how to open up the report manager?|||http://<your server name>/reports|||It doesn't work for http://localhsot/reportsFriday, March 23, 2012
How to connect Oracle Data Source for Reporting Service
I have the problem to connect Oracle data for my Report designer.
1st I used ODBC, it does not work, and my reference book "MS SQL server 2000 Reporting service step by step" tells me that I must select Oracle data provide instead of the generic ODBC.
I created a new data source and after I enter the userId/pswd and click "Test Connection" button, it is OK. But when I click "run" icon to try, it gives me the following error messages:
An error occurred while executing the query, ORA-00936:Missing expression
Additiona information:
ORA-00936:missing expression(system.Data.OracleClient)
My version of reporting service is 2005. The setting of "Connection Properties" as follows:
Data Source: Oracle (OracleClient) Server name: creonpra Log On to the database: UserId/Pswd= qmprodviewer/*********
I have very less knowledge about Oracle. I do not know Oracle Client software's coverage. I checked my program and only find Oracle 8i of "Net8 Assistant", "Net8 Config Assistant" and Oracle ODBC test" on my desktop PC.
Please help me to resolve the problem
Hi,
This looks like a query syntax error.
Did you use the query builder to create your Oracle query?
If you paste your query in a new database project can you execute it?
I would recommand to use the database project or Toad to debug the query, then when it is working, paste it back to your report dataset.
Philippe
|||Yes definitely a syntax problem. Along with the other suggestion of trying it in the Oracle also try simple expressions first like...
select * from Table1
etc.
If your still stuck past the Syntax here and I can have a look at it since I connect primarily to Oracle DB's using the Oracle connection.
Craig
Monday, March 19, 2012
How to configure Reporting service for SMTP server located remotly.
Hi
I have one problem which i am not able to solve regardig the reporting
service SMTP services.
One of our client is using the SQL server 2000 along with Microsoft
Reporting Services. They need to configure SQL Reporting services for
sending reports through emails using subscription services. They don't
have any SMTP server located in their local network.
They are using external SMTP server for sending & receiving mails. How
to configure the RSReportServer.config file for the same? If we refer
documentation available in Reporting Service Books Online, it states
that we can configure using SMTPAuthenticate settings by using
"SENDUSERNAME" & "SENDPASSWORD".
ms-help://MS.RSBOL80.1033/rsadmin/htm/arp_configserver_v1_4bzl.htm
"SendUsing"
Specifies which method to use for sending messages.
To send report subscriptions through e-mail using a local POP3 server
hosted on a Windows 2003 server, you must set the value to 1.
1=sends a message using the local SMTP service pickup directory.
2=sends the message using the network SMTP service.
"SMTPAuthenticate "
Specifies an integer value that indicates the kind of authentication to
use when sending messages to an SMTP service over a TCP/IP connection.
0=no authentication.
1=basic authentication. Credentials are passed in clear text using
either sendusername and senduserpassword, or postusername and
postuserpassword fields.
2= NTML (NT LanMan) authentication. The security context of the current
process is used to authenticate the service.
If We Refer the MSDN online it states SMTPAuthenticate authorization is
not
supported.
http://msdn2.microsoftcom/ms157273.aspx
SendUsing
Specifies which method to use for sending messages. Valid values
include the following:
1=Sends a message from the local SMTP service pickup directory.
2=Sends the message from the network SMTP service.
SMTPAuthenticate
Specifies an integer value that indicates the kind of authentication to
use when sending messages to an SMTP service over a TCP/IP connection.
Valid values include the following:
0=No authentication.
1= (not supported).
2= NTLM (NT LanMan) authentication. The security context of the Report
Server Windows service is used to connect to the network SMTP server.
Please help me in matter to solve the problem. We are unable to send
mails using reporting service by using the settings stated in the
Reporting Service Books Online. Please solve the following problems.
1. Whether it is possible to use SMTP server outside the network or
not?
2. If yes what is the method of configuring SMTP services?
3. How can we send username & password to authenticate the user?
4. Send us sample RSReportServer.config if possible for the same.
Thanking youYou might want to start with checking if the firewall is allowing traffic
out of this server.
On your server, try using telnet to check for traffic.
Open command prompt.Try to type what I write after the (type)...
(Type) Telnet your-mail-server 25
reply 220 your-mail-server Microsoft ESMTP MAIL Service, Version:
5.0.2195.6713
ready at Mon, 24 Oct 2005 15:43:27 +0200
(Type) HELO your-domain
250 your-mail-server Hello [10.61.120.21]
(Type) MAIL FROM: you@.youradress.com
250 2.1.0 you@.youradress.com... Sender OK
(Type) RCPT TO: someone.else@.youradress.com
250 2.1.5 someone.else@.youraddress.com
(Type) DATA
354 Start mail input; end with <CRLF>.<CRLF>
(Type) THis is my message!
.
250 2.6.0 your-mail-server> Queued mail for delivery
Type quit to end the telnet session.
If you can't send an e-mail by telnet, your reporting services server
probably can't either.
Kaisa M. Lindahl Lervik
"msinghindia" <msinghindia@.gmail.com> wrote in message
news:1140695688.842888.246030@.f14g2000cwb.googlegroups.com...
> Hi
> I have one problem which i am not able to solve regardig the reporting
> service SMTP services.
> One of our client is using the SQL server 2000 along with Microsoft
> Reporting Services. They need to configure SQL Reporting services for
> sending reports through emails using subscription services. They don't
> have any SMTP server located in their local network.
> They are using external SMTP server for sending & receiving mails. How
> to configure the RSReportServer.config file for the same? If we refer
> documentation available in Reporting Service Books Online, it states
> that we can configure using SMTPAuthenticate settings by using
> "SENDUSERNAME" & "SENDPASSWORD".
> ms-help://MS.RSBOL80.1033/rsadmin/htm/arp_configserver_v1_4bzl.htm
> "SendUsing"
> Specifies which method to use for sending messages.
> To send report subscriptions through e-mail using a local POP3 server
> hosted on a Windows 2003 server, you must set the value to 1.
> 1=sends a message using the local SMTP service pickup directory.
> 2=sends the message using the network SMTP service.
> "SMTPAuthenticate "
> Specifies an integer value that indicates the kind of authentication to
> use when sending messages to an SMTP service over a TCP/IP connection.
> 0=no authentication.
> 1=basic authentication. Credentials are passed in clear text using
> either sendusername and senduserpassword, or postusername and
> postuserpassword fields.
> 2= NTML (NT LanMan) authentication. The security context of the current
> process is used to authenticate the service.
>
> If We Refer the MSDN online it states SMTPAuthenticate authorization is
> not
> supported.
> http://msdn2.microsoftcom/ms157273.aspx
> SendUsing
> Specifies which method to use for sending messages. Valid values
> include the following:
> 1=Sends a message from the local SMTP service pickup directory.
> 2=Sends the message from the network SMTP service.
> SMTPAuthenticate
> Specifies an integer value that indicates the kind of authentication to
> use when sending messages to an SMTP service over a TCP/IP connection.
> Valid values include the following:
> 0=No authentication.
> 1= (not supported).
> 2= NTLM (NT LanMan) authentication. The security context of the Report
> Server Windows service is used to connect to the network SMTP server.
> Please help me in matter to solve the problem. We are unable to send
> mails using reporting service by using the settings stated in the
> Reporting Service Books Online. Please solve the following problems.
> 1. Whether it is possible to use SMTP server outside the network or
> not?
> 2. If yes what is the method of configuring SMTP services?
> 3. How can we send username & password to authenticate the user?
> 4. Send us sample RSReportServer.config if possible for the same.
> Thanking you
>|||Thnks for early reply.
I followed your steps. When type RCPT TO: mail@.domain.com gives the
following error.
"Relaying Denied...proper authentication required."
Please ! It will be great if you could let me know the reasons behind
the error.
Thanks in Advance
Manjeet|||Relaying is often used for spamming - someone sends out loads of email from
a mail server they don't own. So a lot of email providers will configure
their server to not allow relaying.
You might want to check with the owners of your smtp server what settings
you should use, and how to authenticate proberly remotely.
Kaisa M. Lindahl Lervik
"msinghindia" <msinghindia@.gmail.com> wrote in message
news:1140704270.023492.174060@.j33g2000cwa.googlegroups.com...
> Thnks for early reply.
> I followed your steps. When type RCPT TO: mail@.domain.com gives the
> following error.
> "Relaying Denied...proper authentication required."
> Please ! It will be great if you could let me know the reasons behind
> the error.
> Thanks in Advance
> Manjeet
>|||I have checked with the owners of the server. They can not enable
relaying since it will create problem of SPAM mails as server is live
on internet. So i can not send mails without authorization. So i need
to send username & password to SMTP server when Reporting Server send
mails? But how to do that '
If i refer documents of reporting service for rsconfig file it says
using "sendusername" and "senduserpassword". But it is not working.
When i refer MSDN online it says these is not Supported.
Please any body know how to authorize SQL Reporting Service for sending
mails'
Thanks in advance.
I have one problem which i am not able to solve regardig the reporting
service SMTP services.
One of our client is using the SQL server 2000 along with Microsoft
Reporting Services. They need to configure SQL Reporting services for
sending reports through emails using subscription services. They don't
have any SMTP server located in their local network.
They are using external SMTP server for sending & receiving mails. How
to configure the RSReportServer.config file for the same? If we refer
documentation available in Reporting Service Books Online, it states
that we can configure using SMTPAuthenticate settings by using
"SENDUSERNAME" & "SENDPASSWORD".
ms-help://MS.RSBOL80.1033/rsadmin/htm/arp_configserver_v1_4bzl.htm
"SendUsing"
Specifies which method to use for sending messages.
To send report subscriptions through e-mail using a local POP3 server
hosted on a Windows 2003 server, you must set the value to 1.
1=sends a message using the local SMTP service pickup directory.
2=sends the message using the network SMTP service.
"SMTPAuthenticate "
Specifies an integer value that indicates the kind of authentication to
use when sending messages to an SMTP service over a TCP/IP connection.
0=no authentication.
1=basic authentication. Credentials are passed in clear text using
either sendusername and senduserpassword, or postusername and
postuserpassword fields.
2= NTML (NT LanMan) authentication. The security context of the current
process is used to authenticate the service.
If We Refer the MSDN online it states SMTPAuthenticate authorization is
not
supported.
http://msdn2.microsoftcom/ms157273.aspx
SendUsing
Specifies which method to use for sending messages. Valid values
include the following:
1=Sends a message from the local SMTP service pickup directory.
2=Sends the message from the network SMTP service.
SMTPAuthenticate
Specifies an integer value that indicates the kind of authentication to
use when sending messages to an SMTP service over a TCP/IP connection.
Valid values include the following:
0=No authentication.
1= (not supported).
2= NTLM (NT LanMan) authentication. The security context of the Report
Server Windows service is used to connect to the network SMTP server.
Please help me in matter to solve the problem. We are unable to send
mails using reporting service by using the settings stated in the
Reporting Service Books Online. Please solve the following problems.
1. Whether it is possible to use SMTP server outside the network or
not?
2. If yes what is the method of configuring SMTP services?
3. How can we send username & password to authenticate the user?
4. Send us sample RSReportServer.config if possible for the same.
Thanking youYou might want to start with checking if the firewall is allowing traffic
out of this server.
On your server, try using telnet to check for traffic.
Open command prompt.Try to type what I write after the (type)...
(Type) Telnet your-mail-server 25
reply 220 your-mail-server Microsoft ESMTP MAIL Service, Version:
5.0.2195.6713
ready at Mon, 24 Oct 2005 15:43:27 +0200
(Type) HELO your-domain
250 your-mail-server Hello [10.61.120.21]
(Type) MAIL FROM: you@.youradress.com
250 2.1.0 you@.youradress.com... Sender OK
(Type) RCPT TO: someone.else@.youradress.com
250 2.1.5 someone.else@.youraddress.com
(Type) DATA
354 Start mail input; end with <CRLF>.<CRLF>
(Type) THis is my message!
.
250 2.6.0 your-mail-server> Queued mail for delivery
Type quit to end the telnet session.
If you can't send an e-mail by telnet, your reporting services server
probably can't either.
Kaisa M. Lindahl Lervik
"msinghindia" <msinghindia@.gmail.com> wrote in message
news:1140695688.842888.246030@.f14g2000cwb.googlegroups.com...
> Hi
> I have one problem which i am not able to solve regardig the reporting
> service SMTP services.
> One of our client is using the SQL server 2000 along with Microsoft
> Reporting Services. They need to configure SQL Reporting services for
> sending reports through emails using subscription services. They don't
> have any SMTP server located in their local network.
> They are using external SMTP server for sending & receiving mails. How
> to configure the RSReportServer.config file for the same? If we refer
> documentation available in Reporting Service Books Online, it states
> that we can configure using SMTPAuthenticate settings by using
> "SENDUSERNAME" & "SENDPASSWORD".
> ms-help://MS.RSBOL80.1033/rsadmin/htm/arp_configserver_v1_4bzl.htm
> "SendUsing"
> Specifies which method to use for sending messages.
> To send report subscriptions through e-mail using a local POP3 server
> hosted on a Windows 2003 server, you must set the value to 1.
> 1=sends a message using the local SMTP service pickup directory.
> 2=sends the message using the network SMTP service.
> "SMTPAuthenticate "
> Specifies an integer value that indicates the kind of authentication to
> use when sending messages to an SMTP service over a TCP/IP connection.
> 0=no authentication.
> 1=basic authentication. Credentials are passed in clear text using
> either sendusername and senduserpassword, or postusername and
> postuserpassword fields.
> 2= NTML (NT LanMan) authentication. The security context of the current
> process is used to authenticate the service.
>
> If We Refer the MSDN online it states SMTPAuthenticate authorization is
> not
> supported.
> http://msdn2.microsoftcom/ms157273.aspx
> SendUsing
> Specifies which method to use for sending messages. Valid values
> include the following:
> 1=Sends a message from the local SMTP service pickup directory.
> 2=Sends the message from the network SMTP service.
> SMTPAuthenticate
> Specifies an integer value that indicates the kind of authentication to
> use when sending messages to an SMTP service over a TCP/IP connection.
> Valid values include the following:
> 0=No authentication.
> 1= (not supported).
> 2= NTLM (NT LanMan) authentication. The security context of the Report
> Server Windows service is used to connect to the network SMTP server.
> Please help me in matter to solve the problem. We are unable to send
> mails using reporting service by using the settings stated in the
> Reporting Service Books Online. Please solve the following problems.
> 1. Whether it is possible to use SMTP server outside the network or
> not?
> 2. If yes what is the method of configuring SMTP services?
> 3. How can we send username & password to authenticate the user?
> 4. Send us sample RSReportServer.config if possible for the same.
> Thanking you
>|||Thnks for early reply.
I followed your steps. When type RCPT TO: mail@.domain.com gives the
following error.
"Relaying Denied...proper authentication required."
Please ! It will be great if you could let me know the reasons behind
the error.
Thanks in Advance
Manjeet|||Relaying is often used for spamming - someone sends out loads of email from
a mail server they don't own. So a lot of email providers will configure
their server to not allow relaying.
You might want to check with the owners of your smtp server what settings
you should use, and how to authenticate proberly remotely.
Kaisa M. Lindahl Lervik
"msinghindia" <msinghindia@.gmail.com> wrote in message
news:1140704270.023492.174060@.j33g2000cwa.googlegroups.com...
> Thnks for early reply.
> I followed your steps. When type RCPT TO: mail@.domain.com gives the
> following error.
> "Relaying Denied...proper authentication required."
> Please ! It will be great if you could let me know the reasons behind
> the error.
> Thanks in Advance
> Manjeet
>|||I have checked with the owners of the server. They can not enable
relaying since it will create problem of SPAM mails as server is live
on internet. So i can not send mails without authorization. So i need
to send username & password to SMTP server when Reporting Server send
mails? But how to do that '
If i refer documents of reporting service for rsconfig file it says
using "sendusername" and "senduserpassword". But it is not working.
When i refer MSDN online it says these is not Supported.
Please any body know how to authorize SQL Reporting Service for sending
mails'
Thanks in advance.
How to configure new development workstation to use VS2005 with SSRS?
Just getting started using SSRS (SQL Server Reporting Services) 2005 in production and I've got what should be a simple question.
How do I configure a development workstation to be able to develop SSRS reports which will run on a networked server running SQL Server 2005?
Here is my problem...
I installed VS2005 (Visual Studio Pro 2005) and SQL Server 2005 workstation components (with SQL Server SP2) on the workstation but the "Business Intelligence Projects", "Business Intelligence Wizards", and "Report Projects" are missing from the New > Project types. How do I get them added?
I can access the network SQL Server and the http://<Server>/Reports from the workstation.
On the SQL Server I can develop & deploy reports using BIDS (Business Intelligence Development Studio), on the server.
But on the workstation I cannot create a new Report Project nor can I open an existing reporting project (rptproj) or solution. (I get the error that rptproj is not intalled and that I should install the associated program...which is what?)
WORKSTATION:
Windows Vista Business with all the latest updates.
Visual Studio Pro 2005 with SP1 & Vista SP1 Update installed.
SQL Server Workstation Components (and Reporting Services components) & SP2 update installed.
NETWORK SERVER:
Windows Server 2003 StandardSQL Server 2005 Standard with SP2 & Reporting Services installed.
This should be a simple question, but I'm suprised that it is not working after installing what seemed like the appropriate components from the SQL Server 2005 disk.
Any suggestions on how to get the SSRS projects to show up and work in the Visual Studio 2005 on the development workstation?
Thanks!
-Erik
Within the SQL 2005 install program you have to go through an advanced install and select the development components to be installed as I believe they do not get installed by default.
|||I used the "Advanced" option when I installed.
But I just went back again to see it. Reporting Services is greyed out (I assume because it is already installed).
Now when I try though, I get the following messages and cannot install it.
Name: Microsoft SQL Server 2005 Tools Express Edition
Reason: Your upgrade is blocked. For more information about upgrade support, see the "Version and Edition Upgrades" and "Hardware and Software Requirements" topics in SQL Server 2005 Setup Help or SQL Server 2005 Books Online.
Build version check:
Your upgrade is blocked because the existing component is a later version than the version you are trying to upgrade to. To modify an existing SQL Server component, go to Add or Remove Programs in Control Panel, select the component you would like to modify, and then click Change/Remove.
TITLE: Microsoft SQL Server Setup
None of the selected features can be installed or upgraded. Setup cannot proceed since no effective change is being made to the machine. To continue, click Back and then select features to install. To exit SQL Server Setup, click Cancel.
For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=SQLSetup90&EvtType=28108
BUTTONS:
OK
So I'm inclined to think the Reporting Services components are already installed. Is there something I can check to verify if that is true? If not, why is it greyed out? In the advanced section I can check the Reporting Services component, but it won't let me install it, as it looks from above that setup believes it is already installed. Is there perhaps a registry key I need to manually delete, if it failed to install but left some key indicating it is installed? (It doesn't seem like this should be so hard to get going.)
Any other suggestions?
Thanks!
-Erik
|||In what order did you install VS Pro 2005 and SQL (including RS components)?
It sounds as though when you installed the RS components, the installation didn't recognize that VS was there, so it didn't integrate itself with VS. Or, if RS came first, that when when you installed VS it didn't "see" the RS components on the box.
I'm going to guess this is a Vista-related problem. Something didn't have permissions to look at something else, in the registry or wherever, when it installed and missed a stitch as a result.
I guess it could also be a mismatched edition problem. IOW, the Express version of the components don't talk to the Pro version of Studio. Studio Pro comes with the Reporting Services stuff (see http://msdn2.microsoft.com/en-us/vstudio/aa700921.aspx) so probably the SQL components install would think it's already there.
Two things to check:
Is it possible (check your Start menu carefully) that there are *two* shortcuts to different VS versions? (this happened to me once but I couldn't tell you why)
Since your Visual Studio version is Pro, can you try adding features to it to get the Reporting (or Business Intelligence) stuff to show up, rather than trying to do it through the SQL install?
|||Order of Installation:
Visual Studio 2005 Professional Edition first. (Used Custom Install and chose ALL features.)
Installed the VS 2005 SP1 update and the VS 2005 SP1 Update for Vista.
Then since I still couldn't see the SSRS projects, I installed SQL Server workstation components. (Advanced install to ensure SSRS features were included...but it seems the SSRS items were perceived as already being there.)
|||>>Permissions:
I have Admin rights on the box, so if there is a permission issue, it is not for lack of admin rights.
<,
I'm sorry, Erik, but you may be making an assumption here that is inappropriate on Vista. When you did the install, did you Run As Administrator? It doesn't matter that you have Admin rights on the box in some cases unfortunately.
>>Are the SSRS items a different VS install that I have to download and install from some MS download website? Like an add-on feature?
I didn't think so, with the Pro version, but that is not the version I have so I don't know for sure <s>. At work where I only have the Visual Web Developer Express and VB Express versions, I do have the BI components installed as a separate item from a separate add-on download. I think I got it from here http://download.microsoft.com/download/4/4/D/44DBDE61-B385-4FC2-A67D-48053B8F9FAD/SQLServer2005_ReportAddin.msi
I guess the problem might be that the SQL Server Express version that got installed with VS is the problem? There is an update to SQL Server Express with "Advanced Services" that installs "a limited version of Reporting Services" http://www.microsoft.com/downloads/details.aspx?familyid=4C6BA9FD-319A-4887-BC75-3B02B5E48A40&displaylang=en
Do you have this?
I'm sorry I can't help you from my own experience, my edition here is "Team Edition for Developers".
But I just checked with my husband, who has the Pro version on his laptop -- he says he installed the BI pieces from SQL Server Developer Edition on top of Visual Studio install. So clearly he didn't get it from Pro.
>L<
|||If you are running VS2005 pro with Vista and trying to run reporting services there are two layer of issues, one correct features for reporting services in IIS 7 and two correct permissions to run VS2005 for web development in Vista because IIS main use is web development. The first link covers needed IIS 7 configuration for reporting services and the second covers permission and IIS issues in Vista by the Web development team at Microsoft.
One more thing VS2005 pro comes with a free developer edition you can install that as a named instance locally do the correct configuration for remote connection in configuration manager and surface area configuration tool and just use the production to run the stored procs and copy your code over to production as needed. There are about four files to copy the RDL and the related solution files you will see them under reports in your project, it makes development much easier. The only issue will be to not use Enterprise edition specific features in your development since production is standard. Hope this helps.
http://blogs.msdn.com/bwelcker/archive/2007/03/19/candy-apple-grey-installing-ssrs-and-windows-vista.aspx
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2086062&SiteID=1
|||Hmmm...still not working. (Thanks for the helpfull links though.)
I enabled IIS as indicated in the first link. Then I opened VS2005 Pro and still did not find the Business Intelligence projects or Reporting Services project types listed when selecting new > project. Next I tried opening an existing *.rptproj project which I had already developed on the SQL Server in it's Business Intelligence Development Studio and it gave me the following error on the development workstation,
"The application for project 'L:\<path>\<filename>.rptproj' is not installed. Make sure the application for the project type (.rptproj) is installed."
It does not appear that enabling the IIS helped or made a difference. I still cannot open an rptproj project and I cannot create a new Reporting Services project type on the development workstation. Did I miss something?
Any other suggestions or ideas are greatly appreciated.
Thanks!
-Erik
|||I did not add that because I assumed you will take my advice about installing the developer edition locally because I run the most expensive VS2005 called Team Suites but I still need to install the developer edition of SQL Server before getting BI development related features because I think BI development outside of the VS2005 database developer edition require SQL Server 2005 local installation. Why the VS2005 for BI development does not include the .NET languages, that is the reason to do database development in Team Suites I had to download a separate Addon.
http://msdn2.microsoft.com/en-us/vstudio/aa700921.aspx
That already installed when I did the full install of VS2005 Pro. In fact, during the install of the SQL Server 2005 workstation components, it asked if I wanted to install the workstation components to the SQLExpress instance or to the default instance. (I chose the default instance.)
I feel like I'm missing something obvious here. Please let me know if you see what I'm missing.
Still welcoming suggestions and associated questions. 
Thanks!
-Erik
|||(That already installed when I did the full install of VS2005 Pro. )
No VS2005 just install Express I was telling you to install the developer edition locally and enable all the features needed for remote connection in in configuration manager go to sql server services and turn on reporting services, browser services and in sql native client configuration go to client protocols and enable both named pipes and tcp/ip. Then go to surface area configuration tool to surface area configuration for services and connection in database engine click on remote connection and enable both local and remote connection and click on using both tcp/ip and named pipes. When you have enabled all you can do reporting services development.
|||I'm not at all arguing with you, but am I understanding this correctly?
So, if I want to use Visual Studio 2005 Professional on a development workstation to make SQL Server Reporting Services reports which will be hosted and used on a networked SQL Server 2005 Standard server, I have to also purchase and install SQL Server 2005 Developer edition to install on the development workstation?
Is that correct?
Is there no other way to use VS2005 Pro to build SSRS reports which will be run on the SQL Server 2005 Standard server? If the answer is no, then can anyone suggest an alternative?
If the goal is just to be able to develop the SSRS reports on the development workstation and deploy them for use on the SQL 2005 Server, then what do I need to do that? Are there any other alternatives? If there are additional scenarios, I'd appreciate it if someone (MICROSOFT?) could list the available software scenarios that would enable someone to be able to develop SSRS reports on a development workstation, for deployment and use on a SQL 2005 Standard Server. Is there a link somewhere that explains this? I couldn't seem to find it spelled out well anywhere.
For comparison... If I have Business Objects Enterprise or Crystal Reports Server, I know I need Crystal Reports Pro or Developer, etc. on the development workstation for developing the reports -- which will be deployed to the server. I realize that with SSRS there is more than one tool that can develop the reports, but I've not seen it clearly explained what tools a development workstation needs in order to write SSRS reports for deployment and use on the SQL 2005 (Standard) Server.
Can someone please enlighten me? Multiple scenarios would be appreciated too.
Thanks much!
-Erik
|||On the server:
Windows server
SQL Server
IIS
.net
Reporting services
(of course, the above could be split into a multiple server deployment)
On the developer workstation:
windows
.net
Visual Studio
Report designer
So the question is, what is report designer? It's a plug in that adds report design capabilities to Visual Studio. How do you install it? You get the cd that has reporting services, and run a custom install. Part of the install is a choice to install the client tools. You do not need sql server / reporting services on the developer workstation, you just need the sql server / reporting services cd to do the install of the client tools.
Of course, if you wanted another answer, you could always install BIDS (business intelligence design studio, it's also on the sql server cds) intead of VS and report designer, as it essentially is BOTH in one package. That's for folks who want to do report design but NOT fun things like asp.net, and who also don't want to pay the licensing fees for a full copy of Visual Studio.
Mike G.
|||Your assumption is not correct because VS2005 pro comes with free SQL Server developer edition but it comes locked down like the Express you just need to use the steps I posted to get it to work like the standard and Enterprise. One more thing the developer edition comes with no deployment restriction so it is under $40 on th web, no RDBMS vendor gives you that option.Monday, March 12, 2012
How to configure Email Delivery to send out message in Plain/Text format?
Hi, guys,
I have an application which only accept Plain/Text format email, but the
email sent out by Reporting Services Email Delivery was wrap in HTML format.
Is it possible to configure Email Delivery to send out Plain/Text format
message?
Many thanks!
David ZengThe reports server should populate both the plain text and html parts of the
message. Are you saying that you can not have the html portion present? If
so then RS does not support this. You could have RS drop the files to a
local share and then have an app which stripped out the html part before
sending the message on.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Zeng" <dzeng@.pembrooke.com> wrote in message
news:#K9xIQqpEHA.3424@.TK2MSFTNGP12.phx.gbl...
> Hi, guys,
> I have an application which only accept Plain/Text format email, but the
> email sent out by Reporting Services Email Delivery was wrap in HTML
format.
> Is it possible to configure Email Delivery to send out Plain/Text format
> message?
> Many thanks!
> David Zeng
>|||Daniel,
That's right! I do not want the HTML portion present.
Are there any ways to configure Reporting Services to export report into CSV
format without header?
I am in the middle of one urgent project. I very much appreciate your help!
Thanks,
David Zeng
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:uBIS04vpEHA.3716@.TK2MSFTNGP10.phx.gbl...
> The reports server should populate both the plain text and html parts of
the
> message. Are you saying that you can not have the html portion present?
If
> so then RS does not support this. You could have RS drop the files to a
> local share and then have an app which stripped out the html part before
> sending the message on.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "David Zeng" <dzeng@.pembrooke.com> wrote in message
> news:#K9xIQqpEHA.3424@.TK2MSFTNGP12.phx.gbl...
> > Hi, guys,
> >
> > I have an application which only accept Plain/Text format email, but the
> > email sent out by Reporting Services Email Delivery was wrap in HTML
> format.
> > Is it possible to configure Email Delivery to send out Plain/Text format
> > message?
> >
> > Many thanks!
> > David Zeng
> >
> >
>|||Daniel,
That's right! I do not want the HTML portion present.
Are there any ways to configure Reporting Services to export report into CSV
format without header?
I am in the middle of one urgent project. I very much appreciate your help!
Thanks,
David Zeng
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:uBIS04vpEHA.3716@.TK2MSFTNGP10.phx.gbl...
> The reports server should populate both the plain text and html parts of
the
> message. Are you saying that you can not have the html portion present?
If
> so then RS does not support this. You could have RS drop the files to a
> local share and then have an app which stripped out the html part before
> sending the message on.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "David Zeng" <dzeng@.pembrooke.com> wrote in message
> news:#K9xIQqpEHA.3424@.TK2MSFTNGP12.phx.gbl...
> > Hi, guys,
> >
> > I have an application which only accept Plain/Text format email, but the
> > email sent out by Reporting Services Email Delivery was wrap in HTML
> format.
> > Is it possible to configure Email Delivery to send out Plain/Text format
> > message?
> >
> > Many thanks!
> > David Zeng
> >
> >
>|||No, you can not change the way the Viewer renders the report on export, nor
set any device info for subscriptions. You could write a Delivery extension
fairly quickly that would call the renderer with what ever device info you
want. Then just have it drop it to a file share. Would this solve your
problem?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Zeng" <dzeng@.pembrooke.com> wrote in message
news:#nukQ2wpEHA.3244@.tk2msftngp13.phx.gbl...
> Daniel,
> That's right! I do not want the HTML portion present.
> Are there any ways to configure Reporting Services to export report into
CSV
> format without header?
> I am in the middle of one urgent project. I very much appreciate your
help!
> Thanks,
> David Zeng
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:uBIS04vpEHA.3716@.TK2MSFTNGP10.phx.gbl...
> > The reports server should populate both the plain text and html parts of
> the
> > message. Are you saying that you can not have the html portion present?
> If
> > so then RS does not support this. You could have RS drop the files to a
> > local share and then have an app which stripped out the html part before
> > sending the message on.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "David Zeng" <dzeng@.pembrooke.com> wrote in message
> > news:#K9xIQqpEHA.3424@.TK2MSFTNGP12.phx.gbl...
> > > Hi, guys,
> > >
> > > I have an application which only accept Plain/Text format email, but
the
> > > email sent out by Reporting Services Email Delivery was wrap in HTML
> > format.
> > > Is it possible to configure Email Delivery to send out Plain/Text
format
> > > message?
> > >
> > > Many thanks!
> > > David Zeng
> > >
> > >
> >
> >
>
I have an application which only accept Plain/Text format email, but the
email sent out by Reporting Services Email Delivery was wrap in HTML format.
Is it possible to configure Email Delivery to send out Plain/Text format
message?
Many thanks!
David ZengThe reports server should populate both the plain text and html parts of the
message. Are you saying that you can not have the html portion present? If
so then RS does not support this. You could have RS drop the files to a
local share and then have an app which stripped out the html part before
sending the message on.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Zeng" <dzeng@.pembrooke.com> wrote in message
news:#K9xIQqpEHA.3424@.TK2MSFTNGP12.phx.gbl...
> Hi, guys,
> I have an application which only accept Plain/Text format email, but the
> email sent out by Reporting Services Email Delivery was wrap in HTML
format.
> Is it possible to configure Email Delivery to send out Plain/Text format
> message?
> Many thanks!
> David Zeng
>|||Daniel,
That's right! I do not want the HTML portion present.
Are there any ways to configure Reporting Services to export report into CSV
format without header?
I am in the middle of one urgent project. I very much appreciate your help!
Thanks,
David Zeng
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:uBIS04vpEHA.3716@.TK2MSFTNGP10.phx.gbl...
> The reports server should populate both the plain text and html parts of
the
> message. Are you saying that you can not have the html portion present?
If
> so then RS does not support this. You could have RS drop the files to a
> local share and then have an app which stripped out the html part before
> sending the message on.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "David Zeng" <dzeng@.pembrooke.com> wrote in message
> news:#K9xIQqpEHA.3424@.TK2MSFTNGP12.phx.gbl...
> > Hi, guys,
> >
> > I have an application which only accept Plain/Text format email, but the
> > email sent out by Reporting Services Email Delivery was wrap in HTML
> format.
> > Is it possible to configure Email Delivery to send out Plain/Text format
> > message?
> >
> > Many thanks!
> > David Zeng
> >
> >
>|||Daniel,
That's right! I do not want the HTML portion present.
Are there any ways to configure Reporting Services to export report into CSV
format without header?
I am in the middle of one urgent project. I very much appreciate your help!
Thanks,
David Zeng
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:uBIS04vpEHA.3716@.TK2MSFTNGP10.phx.gbl...
> The reports server should populate both the plain text and html parts of
the
> message. Are you saying that you can not have the html portion present?
If
> so then RS does not support this. You could have RS drop the files to a
> local share and then have an app which stripped out the html part before
> sending the message on.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "David Zeng" <dzeng@.pembrooke.com> wrote in message
> news:#K9xIQqpEHA.3424@.TK2MSFTNGP12.phx.gbl...
> > Hi, guys,
> >
> > I have an application which only accept Plain/Text format email, but the
> > email sent out by Reporting Services Email Delivery was wrap in HTML
> format.
> > Is it possible to configure Email Delivery to send out Plain/Text format
> > message?
> >
> > Many thanks!
> > David Zeng
> >
> >
>|||No, you can not change the way the Viewer renders the report on export, nor
set any device info for subscriptions. You could write a Delivery extension
fairly quickly that would call the renderer with what ever device info you
want. Then just have it drop it to a file share. Would this solve your
problem?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"David Zeng" <dzeng@.pembrooke.com> wrote in message
news:#nukQ2wpEHA.3244@.tk2msftngp13.phx.gbl...
> Daniel,
> That's right! I do not want the HTML portion present.
> Are there any ways to configure Reporting Services to export report into
CSV
> format without header?
> I am in the middle of one urgent project. I very much appreciate your
help!
> Thanks,
> David Zeng
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:uBIS04vpEHA.3716@.TK2MSFTNGP10.phx.gbl...
> > The reports server should populate both the plain text and html parts of
> the
> > message. Are you saying that you can not have the html portion present?
> If
> > so then RS does not support this. You could have RS drop the files to a
> > local share and then have an app which stripped out the html part before
> > sending the message on.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "David Zeng" <dzeng@.pembrooke.com> wrote in message
> > news:#K9xIQqpEHA.3424@.TK2MSFTNGP12.phx.gbl...
> > > Hi, guys,
> > >
> > > I have an application which only accept Plain/Text format email, but
the
> > > email sent out by Reporting Services Email Delivery was wrap in HTML
> > format.
> > > Is it possible to configure Email Delivery to send out Plain/Text
format
> > > message?
> > >
> > > Many thanks!
> > > David Zeng
> > >
> > >
> >
> >
>
how to configure database settings from config file?
Hi guys,
I am very new to Reporting Services, so please bear with me here. I am
curious about how we can configure the database server, database name, and
login data using data from a properties file so that when Reporting Services
and the Report Server start up, they have all the data they need to connect
to the appropriate database. Has anyone tried this yet? If so how would it
be possible?
Thanks a bunch,
Carolyn
cvoREMOVE_ALL_CAPS@.REMOVE_ALL_CAPSacornsys.comPlease take a look at RSConfig.exe tool. You should be able to provide these
settings as command line parameters to this tool.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Carolyn Vo" <cvoREMOVE_ALL_CAPS@.REMOVE_ALL_CAPSacornsys.com> wrote in
message news:OYdWxHGhEHA.3320@.TK2MSFTNGP11.phx.gbl...
> Hi guys,
> I am very new to Reporting Services, so please bear with me here. I am
> curious about how we can configure the database server, database name, and
> login data using data from a properties file so that when Reporting
Services
> and the Report Server start up, they have all the data they need to
connect
> to the appropriate database. Has anyone tried this yet? If so how would
it
> be possible?
> Thanks a bunch,
> Carolyn
> cvoREMOVE_ALL_CAPS@.REMOVE_ALL_CAPSacornsys.com
>|||You may find the following posting useful:
http://prologika.com/blog/archive/2004/08/20/161.aspx
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
---
"Carolyn Vo" <cvoREMOVE_ALL_CAPS@.REMOVE_ALL_CAPSacornsys.com> wrote in
message news:OYdWxHGhEHA.3320@.TK2MSFTNGP11.phx.gbl...
> Hi guys,
> I am very new to Reporting Services, so please bear with me here. I am
> curious about how we can configure the database server, database name, and
> login data using data from a properties file so that when Reporting
Services
> and the Report Server start up, they have all the data they need to
connect
> to the appropriate database. Has anyone tried this yet? If so how would
it
> be possible?
> Thanks a bunch,
> Carolyn
> cvoREMOVE_ALL_CAPS@.REMOVE_ALL_CAPSacornsys.com
>
I am very new to Reporting Services, so please bear with me here. I am
curious about how we can configure the database server, database name, and
login data using data from a properties file so that when Reporting Services
and the Report Server start up, they have all the data they need to connect
to the appropriate database. Has anyone tried this yet? If so how would it
be possible?
Thanks a bunch,
Carolyn
cvoREMOVE_ALL_CAPS@.REMOVE_ALL_CAPSacornsys.comPlease take a look at RSConfig.exe tool. You should be able to provide these
settings as command line parameters to this tool.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Carolyn Vo" <cvoREMOVE_ALL_CAPS@.REMOVE_ALL_CAPSacornsys.com> wrote in
message news:OYdWxHGhEHA.3320@.TK2MSFTNGP11.phx.gbl...
> Hi guys,
> I am very new to Reporting Services, so please bear with me here. I am
> curious about how we can configure the database server, database name, and
> login data using data from a properties file so that when Reporting
Services
> and the Report Server start up, they have all the data they need to
connect
> to the appropriate database. Has anyone tried this yet? If so how would
it
> be possible?
> Thanks a bunch,
> Carolyn
> cvoREMOVE_ALL_CAPS@.REMOVE_ALL_CAPSacornsys.com
>|||You may find the following posting useful:
http://prologika.com/blog/archive/2004/08/20/161.aspx
--
Hope this helps.
---
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
http://www.prologika.com
---
"Carolyn Vo" <cvoREMOVE_ALL_CAPS@.REMOVE_ALL_CAPSacornsys.com> wrote in
message news:OYdWxHGhEHA.3320@.TK2MSFTNGP11.phx.gbl...
> Hi guys,
> I am very new to Reporting Services, so please bear with me here. I am
> curious about how we can configure the database server, database name, and
> login data using data from a properties file so that when Reporting
Services
> and the Report Server start up, they have all the data they need to
connect
> to the appropriate database. Has anyone tried this yet? If so how would
it
> be possible?
> Thanks a bunch,
> Carolyn
> cvoREMOVE_ALL_CAPS@.REMOVE_ALL_CAPSacornsys.com
>
how to configue the reportserver?
hi,
I have recently start learning reporting service!
A problem is that when I try to run the report,
a error message like "report server can not be found" always occur
how can i solve it?
thx
There is probably something wrong in the configuration of your report server. Did you do a default install of report server? Were there any errors showing in the configuration tool? Is there anything useful in the log file?how to conditionally get the data
Hi,
In Reporting Service, how to conditionally get the data? Let's say, I don't want the line
when the Status field is "inactive" and Eff_Date<"1/1/2006"
I conditionally hided the Visibility of this line. But when i tried to get the subtotal, this line's Quantity info still was included into the subtotal.
I tried to use the Filter box of the dataset to do it, but don't know how.
Can anybody please help me out?
Thanks a lot.
What kind of datasource are you using?Friday, March 9, 2012
How to Concatinate results to display results in a list from Left - Right instead of Top -
Hi,
I am creating a report using SQL Server Reporting Services 2000. My
report displays values in datatable. Say, the table looks like this
EmpName Emp# Address Phone#
..... ... ...... ...
I want all phone # for an employee to be displayed in a single row.
This can be acheived by displaying phone numbers in a group footer
(group by emp#). But I want all phone# to be displayed horizontally
(Left - Right) not vertically (Top - Bottom).
Instead of displaying results like this
EmpName Emp# Address
..... ... ......
Phones
###
###
###
###
I want to display results like below -
EmpName Emp# Address Phone#
..... ... ...... ...
Phones ### , ### , ####, ###, ###, ###
..... ... ...... ...
Phones ### , ###
..... ... ...... ...
Phones ### , ###, ### , ###
How can I concatinate the phone# ? I tried using a sub report in the
footer to retrive all phone# for an employee but how to concatinate
them ?
I tried to query like this
SELECT @.STRCON = EmpPhone + ', ' FROM EMPLOYEE WHERE EMPID = ###
But I'm not sure about the length of the return value (may be more than
8000 characters).
So is there any way in concatinating values in Report ? Or is there any
way in acheiving this in a Listbox or Table ?
Regards,
ChiroI think Chris Hays has something that will work for you.
http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx
Steve MunLeeuw
"Chiro" <chirangv@.hotmail.com> wrote in message
news:1161268089.076044.227180@.e3g2000cwe.googlegroups.com...
> Hi,
> I am creating a report using SQL Server Reporting Services 2000. My
> report displays values in datatable. Say, the table looks like this
> EmpName Emp# Address Phone#
> ..... ... ...... ...
> I want all phone # for an employee to be displayed in a single row.
> This can be acheived by displaying phone numbers in a group footer
> (group by emp#). But I want all phone# to be displayed horizontally
> (Left - Right) not vertically (Top - Bottom).
> Instead of displaying results like this
> EmpName Emp# Address
> ..... ... ......
> Phones
> ###
> ###
> ###
> ###
> I want to display results like below -
> EmpName Emp# Address Phone#
> ..... ... ...... ...
> Phones ### , ### , ####, ###, ###, ###
> ..... ... ...... ...
> Phones ### , ###
> ..... ... ...... ...
> Phones ### , ###, ### , ###
>
> How can I concatinate the phone# ? I tried using a sub report in the
> footer to retrive all phone# for an employee but how to concatinate
> them ?
> I tried to query like this
> SELECT @.STRCON = EmpPhone + ', ' FROM EMPLOYEE WHERE EMPID = ###
> But I'm not sure about the length of the return value (may be more than
> 8000 characters).
> So is there any way in concatinating values in Report ? Or is there any
> way in acheiving this in a Listbox or Table ?
> Regards,
> Chiro
>|||Hi,
Thank You.
I was able to acheive this as follows -
1. Add a Matrix control. Add Phone# in column grouping of matrix.
2. Add a ListBox control. Include Matrix inside the Listbox.
3. Edit details of Listbox to add a group expression
=RowNumber(Nothing) / 15. (15 is number of columns to be displayed)
4. Add a Matrix column group expression as your Listbox group
expression.
=RowNumber("list1_Details_Group"). Now your matrix should contain 2
group expressions. (1 for Phone # and other for controling no. of
columns).
Regards,
Chiro
Steve MunLeeuw wrote:
> I think Chris Hays has something that will work for you.
> http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx
> Steve MunLeeuw
> "Chiro" <chirangv@.hotmail.com> wrote in message
> news:1161268089.076044.227180@.e3g2000cwe.googlegroups.com...
> > Hi,
> > I am creating a report using SQL Server Reporting Services 2000. My
> > report displays values in datatable. Say, the table looks like this
> >
> > EmpName Emp# Address Phone#
> > ..... ... ...... ...
> > I want all phone # for an employee to be displayed in a single row.
> > This can be acheived by displaying phone numbers in a group footer
> > (group by emp#). But I want all phone# to be displayed horizontally
> > (Left - Right) not vertically (Top - Bottom).
> > Instead of displaying results like this
> > EmpName Emp# Address
> > ..... ... ......
> > Phones
> > ###
> > ###
> > ###
> > ###
> > I want to display results like below -
> > EmpName Emp# Address Phone#
> > ..... ... ...... ...
> > Phones ### , ### , ####, ###, ###, ###
> > ..... ... ...... ...
> > Phones ### , ###
> > ..... ... ...... ...
> > Phones ### , ###, ### , ###
> >
> >
> > How can I concatinate the phone# ? I tried using a sub report in the
> > footer to retrive all phone# for an employee but how to concatinate
> > them ?
> > I tried to query like this
> > SELECT @.STRCON = EmpPhone + ', ' FROM EMPLOYEE WHERE EMPID = ###
> > But I'm not sure about the length of the return value (may be more than
> >
> > 8000 characters).
> > So is there any way in concatinating values in Report ? Or is there any
> >
> > way in acheiving this in a Listbox or Table ?
> > Regards,
> > Chiro
> >
I am creating a report using SQL Server Reporting Services 2000. My
report displays values in datatable. Say, the table looks like this
EmpName Emp# Address Phone#
..... ... ...... ...
I want all phone # for an employee to be displayed in a single row.
This can be acheived by displaying phone numbers in a group footer
(group by emp#). But I want all phone# to be displayed horizontally
(Left - Right) not vertically (Top - Bottom).
Instead of displaying results like this
EmpName Emp# Address
..... ... ......
Phones
###
###
###
###
I want to display results like below -
EmpName Emp# Address Phone#
..... ... ...... ...
Phones ### , ### , ####, ###, ###, ###
..... ... ...... ...
Phones ### , ###
..... ... ...... ...
Phones ### , ###, ### , ###
How can I concatinate the phone# ? I tried using a sub report in the
footer to retrive all phone# for an employee but how to concatinate
them ?
I tried to query like this
SELECT @.STRCON = EmpPhone + ', ' FROM EMPLOYEE WHERE EMPID = ###
But I'm not sure about the length of the return value (may be more than
8000 characters).
So is there any way in concatinating values in Report ? Or is there any
way in acheiving this in a Listbox or Table ?
Regards,
ChiroI think Chris Hays has something that will work for you.
http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx
Steve MunLeeuw
"Chiro" <chirangv@.hotmail.com> wrote in message
news:1161268089.076044.227180@.e3g2000cwe.googlegroups.com...
> Hi,
> I am creating a report using SQL Server Reporting Services 2000. My
> report displays values in datatable. Say, the table looks like this
> EmpName Emp# Address Phone#
> ..... ... ...... ...
> I want all phone # for an employee to be displayed in a single row.
> This can be acheived by displaying phone numbers in a group footer
> (group by emp#). But I want all phone# to be displayed horizontally
> (Left - Right) not vertically (Top - Bottom).
> Instead of displaying results like this
> EmpName Emp# Address
> ..... ... ......
> Phones
> ###
> ###
> ###
> ###
> I want to display results like below -
> EmpName Emp# Address Phone#
> ..... ... ...... ...
> Phones ### , ### , ####, ###, ###, ###
> ..... ... ...... ...
> Phones ### , ###
> ..... ... ...... ...
> Phones ### , ###, ### , ###
>
> How can I concatinate the phone# ? I tried using a sub report in the
> footer to retrive all phone# for an employee but how to concatinate
> them ?
> I tried to query like this
> SELECT @.STRCON = EmpPhone + ', ' FROM EMPLOYEE WHERE EMPID = ###
> But I'm not sure about the length of the return value (may be more than
> 8000 characters).
> So is there any way in concatinating values in Report ? Or is there any
> way in acheiving this in a Listbox or Table ?
> Regards,
> Chiro
>|||Hi,
Thank You.
I was able to acheive this as follows -
1. Add a Matrix control. Add Phone# in column grouping of matrix.
2. Add a ListBox control. Include Matrix inside the Listbox.
3. Edit details of Listbox to add a group expression
=RowNumber(Nothing) / 15. (15 is number of columns to be displayed)
4. Add a Matrix column group expression as your Listbox group
expression.
=RowNumber("list1_Details_Group"). Now your matrix should contain 2
group expressions. (1 for Phone # and other for controling no. of
columns).
Regards,
Chiro
Steve MunLeeuw wrote:
> I think Chris Hays has something that will work for you.
> http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx
> Steve MunLeeuw
> "Chiro" <chirangv@.hotmail.com> wrote in message
> news:1161268089.076044.227180@.e3g2000cwe.googlegroups.com...
> > Hi,
> > I am creating a report using SQL Server Reporting Services 2000. My
> > report displays values in datatable. Say, the table looks like this
> >
> > EmpName Emp# Address Phone#
> > ..... ... ...... ...
> > I want all phone # for an employee to be displayed in a single row.
> > This can be acheived by displaying phone numbers in a group footer
> > (group by emp#). But I want all phone# to be displayed horizontally
> > (Left - Right) not vertically (Top - Bottom).
> > Instead of displaying results like this
> > EmpName Emp# Address
> > ..... ... ......
> > Phones
> > ###
> > ###
> > ###
> > ###
> > I want to display results like below -
> > EmpName Emp# Address Phone#
> > ..... ... ...... ...
> > Phones ### , ### , ####, ###, ###, ###
> > ..... ... ...... ...
> > Phones ### , ###
> > ..... ... ...... ...
> > Phones ### , ###, ### , ###
> >
> >
> > How can I concatinate the phone# ? I tried using a sub report in the
> > footer to retrive all phone# for an employee but how to concatinate
> > them ?
> > I tried to query like this
> > SELECT @.STRCON = EmpPhone + ', ' FROM EMPLOYEE WHERE EMPID = ###
> > But I'm not sure about the length of the return value (may be more than
> >
> > 8000 characters).
> > So is there any way in concatinating values in Report ? Or is there any
> >
> > way in acheiving this in a Listbox or Table ?
> > Regards,
> > Chiro
> >
Friday, February 24, 2012
How to combine Multiple Dataset result in each row of the report
Hi,
I need to produce a report in MS SQL Server Reporting Service 2005 which has some date fields & need to pass these date fields to find out the no_of_User on that date from another database. For example
Itemid
Availabilty_Start_Dt
Availabilty_End_Dt
User_On_St_DT (this info is in another DB & need to pass Availabilty_Start_Dt )
User_On_ED_DT (this info is in another DB & need to pass Availabilty_Start_Dt )
I am facing two problem here. Since hereis two different dataset needed how can I combine the dataset result to produce each row of the report.
REally appreciate if anyone can show me some light on it.
Regards,
write a strored procedure in the backend and try to write the two queries and combine the results and store in a temp table.
use the stored procedure in the reporting services. Backend is the best solution for it.
Sunday, February 19, 2012
How to clean machine for SQL2005 Reporting Services RTM
hi all,
I am trying to get Reporting Services to install on a machine that previously had the September CTP install on it.
The SQL Server part of the install has been fine and all seems to be operational as expected but the Reporting Services fails to install with the following errors:
"An instance of the same name is already installed on this computer..."This is thrown out of the ValidateInstanceName func in the MSI. I am telling it to use the Default Instance as there is only one SQL instance running (to my knowledge).
How do I clean up the machine so that it does not think another instance is present? I have tried MSIZAP and that has not helped. I have removed references from IIS, COM+, file system and registry.. but there must be some I am missing. Does anyone know what ValidateInstanceName is actually looking at/for?
Thanks
PaulIf in doubt..
Uninstall SQL Server 2005 and remove every reference to SQL (as far as possible) from the registry..
Fixed the issue.
How to Choose Method to Transfer Data from Production to Reporting database
My current project now is how to synchronize (data transfer) between
Production Database & Reporting Database using DTS, but I have no idea
how to do it and using which the best method. Need to know, that two
database have different structure tables - of course - and have a
thousand records inside, and our transaction working 24 hours, so
that's imposible to delete all records at Reporting Database and
replace with new data. The method that I've considered now is :
[vbcol=seagreen]
- 0 : haven't transfered at all
- 1 : the record have been modified
- 2 : have transfered to Reporting database
So I only need delete all records at Reporting Services which match
record at Production have flag 1, and then transfered all records that
have flag 0 and 1.
But the weakness of my method is how to synchronize for deleted
records? Should I save the primary key of deleted records, or is there
any other method?
Please give me suggestion/comment for my method.
Thanks
To determine what records have been deleted from MyTable in the Reporting
Database, you can left join MyTable from the Reporting Database (RD) back to
the Production Database (PD) on the primary key (pkey) and select
RD.MyTable.pkey where PD.Mytable.pkey is null.
Now, to delete these records from the Reporting version of MyTable:
delete from RD.MyTable where pkey in (the above subquery)
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120701842.102486.317180@.f14g2000cwb.googlegr oups.com...
> My current project now is how to synchronize (data transfer) between
> Production Database & Reporting Database using DTS, but I have no idea
> how to do it and using which the best method. Need to know, that two
> database have different structure tables - of course - and have a
> thousand records inside, and our transaction working 24 hours, so
> that's imposible to delete all records at Reporting Database and
> replace with new data. The method that I've considered now is :
> - 0 : haven't transfered at all
> - 1 : the record have been modified
> - 2 : have transfered to Reporting database
> So I only need delete all records at Reporting Services which match
> record at Production have flag 1, and then transfered all records that
> have flag 0 and 1.
> But the weakness of my method is how to synchronize for deleted
> records? Should I save the primary key of deleted records, or is there
> any other method?
> Please give me suggestion/comment for my method.
> Thanks
>
|||I understand what you mean, but I've hundred records, so using that
query, I think is still have slow performance.
|||Actually, I would expect the query portion to run within a few seconds, if
the two tables are joined using an indexed column.
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120784708.699397.48280@.f14g2000cwb.googlegro ups.com...
> I understand what you mean, but I've hundred records, so using that
> query, I think is still have slow performance.
>
Production Database & Reporting Database using DTS, but I have no idea
how to do it and using which the best method. Need to know, that two
database have different structure tables - of course - and have a
thousand records inside, and our transaction working 24 hours, so
that's imposible to delete all records at Reporting Database and
replace with new data. The method that I've considered now is :
[vbcol=seagreen]
- 0 : haven't transfered at all
- 1 : the record have been modified
- 2 : have transfered to Reporting database
So I only need delete all records at Reporting Services which match
record at Production have flag 1, and then transfered all records that
have flag 0 and 1.
But the weakness of my method is how to synchronize for deleted
records? Should I save the primary key of deleted records, or is there
any other method?
Please give me suggestion/comment for my method.
Thanks
To determine what records have been deleted from MyTable in the Reporting
Database, you can left join MyTable from the Reporting Database (RD) back to
the Production Database (PD) on the primary key (pkey) and select
RD.MyTable.pkey where PD.Mytable.pkey is null.
Now, to delete these records from the Reporting version of MyTable:
delete from RD.MyTable where pkey in (the above subquery)
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120701842.102486.317180@.f14g2000cwb.googlegr oups.com...
> My current project now is how to synchronize (data transfer) between
> Production Database & Reporting Database using DTS, but I have no idea
> how to do it and using which the best method. Need to know, that two
> database have different structure tables - of course - and have a
> thousand records inside, and our transaction working 24 hours, so
> that's imposible to delete all records at Reporting Database and
> replace with new data. The method that I've considered now is :
> - 0 : haven't transfered at all
> - 1 : the record have been modified
> - 2 : have transfered to Reporting database
> So I only need delete all records at Reporting Services which match
> record at Production have flag 1, and then transfered all records that
> have flag 0 and 1.
> But the weakness of my method is how to synchronize for deleted
> records? Should I save the primary key of deleted records, or is there
> any other method?
> Please give me suggestion/comment for my method.
> Thanks
>
|||I understand what you mean, but I've hundred records, so using that
query, I think is still have slow performance.
|||Actually, I would expect the query portion to run within a few seconds, if
the two tables are joined using an indexed column.
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120784708.699397.48280@.f14g2000cwb.googlegro ups.com...
> I understand what you mean, but I've hundred records, so using that
> query, I think is still have slow performance.
>
How to Choose Method to Transfer Data from Production to Reporting database
My current project now is how to synchronize (data transfer) between
Production Database & Reporting Database using DTS, but I have no idea
how to do it and using which the best method. Need to know, that two
database have different structure tables - of course - and have a
thousand records inside, and our transaction working 24 hours, so
that's imposible to delete all records at Reporting Database and
replace with new data. The method that I've considered now is :
[vbcol=seagreen]
- 0 : haven't transfered at all
- 1 : the record have been modified
- 2 : have transfered to Reporting database
So I only need delete all records at Reporting Services which match
record at Production have flag 1, and then transfered all records that
have flag 0 and 1.
But the weakness of my method is how to synchronize for deleted
records? Should I save the primary key of deleted records, or is there
any other method?
Please give me suggestion/comment for my method.
ThanksTo determine what records have been deleted from MyTable in the Reporting
Database, you can left join MyTable from the Reporting Database (RD) back to
the Production Database (PD) on the primary key (pkey) and select
RD.MyTable.pkey where PD.Mytable.pkey is null.
Now, to delete these records from the Reporting version of MyTable:
delete from RD.MyTable where pkey in (the above subquery)
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120701842.102486.317180@.f14g2000cwb.googlegroups.com...
> My current project now is how to synchronize (data transfer) between
> Production Database & Reporting Database using DTS, but I have no idea
> how to do it and using which the best method. Need to know, that two
> database have different structure tables - of course - and have a
> thousand records inside, and our transaction working 24 hours, so
> that's imposible to delete all records at Reporting Database and
> replace with new data. The method that I've considered now is :
>
> - 0 : haven't transfered at all
> - 1 : the record have been modified
> - 2 : have transfered to Reporting database
> So I only need delete all records at Reporting Services which match
> record at Production have flag 1, and then transfered all records that
> have flag 0 and 1.
> But the weakness of my method is how to synchronize for deleted
> records? Should I save the primary key of deleted records, or is there
> any other method?
> Please give me suggestion/comment for my method.
> Thanks
>|||I understand what you mean, but I've hundred records, so using that
query, I think is still have slow performance.|||Actually, I would expect the query portion to run within a few seconds, if
the two tables are joined using an indexed column.
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120784708.699397.48280@.f14g2000cwb.googlegroups.com...
> I understand what you mean, but I've hundred records, so using that
> query, I think is still have slow performance.
>
Production Database & Reporting Database using DTS, but I have no idea
how to do it and using which the best method. Need to know, that two
database have different structure tables - of course - and have a
thousand records inside, and our transaction working 24 hours, so
that's imposible to delete all records at Reporting Database and
replace with new data. The method that I've considered now is :
[vbcol=seagreen]
- 0 : haven't transfered at all
- 1 : the record have been modified
- 2 : have transfered to Reporting database
So I only need delete all records at Reporting Services which match
record at Production have flag 1, and then transfered all records that
have flag 0 and 1.
But the weakness of my method is how to synchronize for deleted
records? Should I save the primary key of deleted records, or is there
any other method?
Please give me suggestion/comment for my method.
ThanksTo determine what records have been deleted from MyTable in the Reporting
Database, you can left join MyTable from the Reporting Database (RD) back to
the Production Database (PD) on the primary key (pkey) and select
RD.MyTable.pkey where PD.Mytable.pkey is null.
Now, to delete these records from the Reporting version of MyTable:
delete from RD.MyTable where pkey in (the above subquery)
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120701842.102486.317180@.f14g2000cwb.googlegroups.com...
> My current project now is how to synchronize (data transfer) between
> Production Database & Reporting Database using DTS, but I have no idea
> how to do it and using which the best method. Need to know, that two
> database have different structure tables - of course - and have a
> thousand records inside, and our transaction working 24 hours, so
> that's imposible to delete all records at Reporting Database and
> replace with new data. The method that I've considered now is :
>
> - 0 : haven't transfered at all
> - 1 : the record have been modified
> - 2 : have transfered to Reporting database
> So I only need delete all records at Reporting Services which match
> record at Production have flag 1, and then transfered all records that
> have flag 0 and 1.
> But the weakness of my method is how to synchronize for deleted
> records? Should I save the primary key of deleted records, or is there
> any other method?
> Please give me suggestion/comment for my method.
> Thanks
>|||I understand what you mean, but I've hundred records, so using that
query, I think is still have slow performance.|||Actually, I would expect the query portion to run within a few seconds, if
the two tables are joined using an indexed column.
"Resant" <resant_v@.yahoo.com> wrote in message
news:1120784708.699397.48280@.f14g2000cwb.googlegroups.com...
> I understand what you mean, but I've hundred records, so using that
> query, I think is still have slow performance.
>
Subscribe to:
Posts (Atom)