Mapping of SQL Server data types to Integration Services Data Type

Does anyone know of any cross-references between SQL Server data types and the new data types introduced with SQL Server Integration Services?

For example, Integration Services has "DT_DATE", "DT_DBDATE", "DT_DBTIME" and "DT_DBTIMESTAMP". So far, if I have a SQL Server datetime column, the only Integration Services type I have been able to use is "DT_DBTIMESTAMP". There must be a way to map the datetime type to "DT_DATE", "DT_DBDATE" and "DT_DBTIME", but there no easy to use reference for this.

Please post a link to the resources if you know of one.

Thanks.We are just in the process of refining such a topic for the around-RTM Web refresh of Books Online. Copying and pasting HTML out of BOL is usually a disaster, but I'll give it a try, below. It will look more user-friendly when it appears in BOL! This topic has not been fully edited and tech reviewed - use at your own risk.

-Doug

Mapping Data Types in the Data Flow

While moving data from sources through transformations to destinations, a data flow component must sometimes convert data types between the SQL Server 2005 Integration Services (SSIS) types defined in the DataType enumeration and the managed data types of the Microsoft .NET Framework defined in the System namespace. In addition, a component must sometimes convert one Integration Services data type to another before that type can be converted to a managed type.

Note: The mapping files in XML format that are installed by default to C:\Program Files\Microsoft SQL Server\90\DTS\MappingFiles are not related to the data type mapping discussed in this topic. These files map data types from one database version or system to another (for example, from SQL Server 2000 to SQL Server 2005, or from SQL Server 2005 to Oracle), and are used only by the SQL Server Import and Export Wizard.

Mapping between Integration Services and Managed Data Types

Sometimes a data flow component must convert data types between the SQL Server 2005 Integration Services (SSIS) types defined in the DataType enumeration and the managed data types of the Microsoft .NET Framework defined in the System namespace. The following table lists the conversions that are currently performed by the BufferTypeToDataRecordType and the DataRecordTypeToBufferType methods of the PipelineComponent class. Other Integration Services data types not listed here cannot be converted to managed types.

Caution: Developers should use these methods of the the PipelineComponent class with caution, and may want to code data type mapping methods of their own that are more suited to the unique needs of their custom components. The existing methods do not consider numeric precision or scale, or any other properties closely related to the data type itself. Microsoft may modify or remove these methods, or modify the mappings that they perform, in a future version of Integration Services.

Integration Services Data Type Managed Data Type DT_WSTR System.String DT_BYTES Array of System.Byte DT_DBTIMESTAMP System.DateTime DT_NUMERIC System.Decimal DT_GUID System.Guid DT_I1 System.Byte DT_I2 System.Int16 DT_I4 System.Int32 DT_I8 System.Int64 DT_BOOL System.Boolean DT_R4 System.Single DT_R8 System.Double DT_UI1 System.Byte DT_UI2 System.UInt16 DT_UI4 System.UInt32 DT_UI8 System.UInt64

Converting Integration Services Data Types to Fit Managed Data Types

Sometimes a data flow component must also convert one Integration Services data type to another before that type can be converted to a managed type. The following table lists the conversions that are currently performed by the ConvertBufferDataTypeToFitManaged method of the PipelineComponent class.

Caution: Developers should use these methods of the the PipelineComponent class with caution, and may want to code data type mapping methods of their own that are more suited to the unique needs of their custom components. The existing methods do not consider numeric precision or scale, or any other properties closely related to the data type itself. Microsoft may modify or remove these methods, or modify the mappings that they perform, in a future version of Integration Services.

Original Data Type Converted Data Type DT_DECIMAL DT_NUMERIC DT_DATE DT_DBTIMESTAMP DT_BOOL DT_I4 DT_TEXT DT_WSTR DT_STR DT_WSTR DT_IMAGE DT_BYTES

See Also

Reference

BufferTypeToDataRecordType
DataRecordTypeToBufferType
ConvertBufferDataTypeToFitManaged

|||This is a good start, thanks. I'll be mindful of the risks in using it.

Ken|||Let me clarify in particular that the sentence, "Other Integration Services data types not listed here cannot be converted to managed types." (already rewritten since that build of BOL) means "...by using these methods." That is, the API methods mentioned in that paragraph.|||

If you interested in mapping of tinyints have a look at my post

http://www.sqljunkies.com/WebLog/simons/archive/2006/02/24/tinyint_in_SSIS.aspx

sql

Mapping of SQL Server data types to Integration Services Data Type

Does anyone know of any cross-references between SQL Server data types and the new data types introduced with SQL Server Integration Services?

For example, Integration Services has "DT_DATE", "DT_DBDATE", "DT_DBTIME" and "DT_DBTIMESTAMP". So far, if I have a SQL Server datetime column, the only Integration Services type I have been able to use is "DT_DBTIMESTAMP". There must be a way to map the datetime type to "DT_DATE", "DT_DBDATE" and "DT_DBTIME", but there no easy to use reference for this.

Please post a link to the resources if you know of one.

Thanks.We are just in the process of refining such a topic for the around-RTM Web refresh of Books Online. Copying and pasting HTML out of BOL is usually a disaster, but I'll give it a try, below. It will look more user-friendly when it appears in BOL! This topic has not been fully edited and tech reviewed - use at your own risk.

-Doug

Mapping Data Types in the Data Flow

While moving data from sources through transformations to destinations, a data flow component must sometimes convert data types between the SQL Server 2005 Integration Services (SSIS) types defined in the DataType enumeration and the managed data types of the Microsoft .NET Framework defined in the System namespace. In addition, a component must sometimes convert one Integration Services data type to another before that type can be converted to a managed type.

Note: The mapping files in XML format that are installed by default to C:\Program Files\Microsoft SQL Server\90\DTS\MappingFiles are not related to the data type mapping discussed in this topic. These files map data types from one database version or system to another (for example, from SQL Server 2000 to SQL Server 2005, or from SQL Server 2005 to Oracle), and are used only by the SQL Server Import and Export Wizard.

Mapping between Integration Services and Managed Data Types

Sometimes a data flow component must convert data types between the SQL Server 2005 Integration Services (SSIS) types defined in the DataType enumeration and the managed data types of the Microsoft .NET Framework defined in the System namespace. The following table lists the conversions that are currently performed by the BufferTypeToDataRecordType and the DataRecordTypeToBufferType methods of the PipelineComponent class. Other Integration Services data types not listed here cannot be converted to managed types.

Caution: Developers should use these methods of the the PipelineComponent class with caution, and may want to code data type mapping methods of their own that are more suited to the unique needs of their custom components. The existing methods do not consider numeric precision or scale, or any other properties closely related to the data type itself. Microsoft may modify or remove these methods, or modify the mappings that they perform, in a future version of Integration Services.

Integration Services Data Type Managed Data Type DT_WSTR System.String DT_BYTES Array of System.Byte DT_DBTIMESTAMP System.DateTime DT_NUMERIC System.Decimal DT_GUID System.Guid DT_I1 System.Byte DT_I2 System.Int16 DT_I4 System.Int32 DT_I8 System.Int64 DT_BOOL System.Boolean DT_R4 System.Single DT_R8 System.Double DT_UI1 System.Byte DT_UI2 System.UInt16 DT_UI4 System.UInt32 DT_UI8 System.UInt64

Converting Integration Services Data Types to Fit Managed Data Types

Sometimes a data flow component must also convert one Integration Services data type to another before that type can be converted to a managed type. The following table lists the conversions that are currently performed by the ConvertBufferDataTypeToFitManaged method of the PipelineComponent class.

Caution: Developers should use these methods of the the PipelineComponent class with caution, and may want to code data type mapping methods of their own that are more suited to the unique needs of their custom components. The existing methods do not consider numeric precision or scale, or any other properties closely related to the data type itself. Microsoft may modify or remove these methods, or modify the mappings that they perform, in a future version of Integration Services.

Original Data Type Converted Data Type DT_DECIMAL DT_NUMERIC DT_DATE DT_DBTIMESTAMP DT_BOOL DT_I4 DT_TEXT DT_WSTR DT_STR DT_WSTR DT_IMAGE DT_BYTES

See Also

Reference

BufferTypeToDataRecordType
DataRecordTypeToBufferType
ConvertBufferDataTypeToFitManaged

|||This is a good start, thanks. I'll be mindful of the risks in using it.

Ken|||Let me clarify in particular that the sentence, "Other Integration Services data types not listed here cannot be converted to managed types." (already rewritten since that build of BOL) means "...by using these methods." That is, the API methods mentioned in that paragraph.|||

If you interested in mapping of tinyints have a look at my post

http://www.sqljunkies.com/WebLog/simons/archive/2006/02/24/tinyint_in_SSIS.aspx

MAPI error 273

I've setup SQL Mail and I'm trying to setup SQL Server Agent mail as
well, but this does not work.
Both MSSQLServer and SQLServerAgent services run under the standard
local Administrator account. A mailprofile and a postbox is created for
this account, client is Outlook 2000, mailserver is Exchange 5.5. The
mailaccount works fine, I can sent and receive mail.
When activating SQLMail, no problem. I can use xp_sendmail without a
problem.
However, when trying to activate SQL Server Agent mail, after choosing
the Exchange profile(the same 1 as for SQL Mail, there is only 1
profile) I receive a MAPI Logon failed:
Error 22022:SQL Server Agent error: MapiLogon Ex Failed due to MAPI
error 273: MAPI Logon failed.
I can understand what this means:the Administrator account is not
recognized as a legal login. Since both services run under this login,
and this poses noprob for SQL Mail, it makes no sence to me. Obviously I
am missing something, but I don't know what.
I have googled the internet and came up with guiet a view similar
questions. Read several Q&A's, among others also the ones from
Microsoft, but either the suggested causes were not, or the possible
reasons did not aply. I found 1 solution which I tried: enter the
profile(BTW, there is only 1 profile), accept it tho testing it
generates the failure and stop&restart the SQLServerAgent. It did not
work.
Now a new feature has arissen: the mailsession droplist, with which to
set the mailprofile is greyed out, and the TEST button as well! This
means I cannot change the SQLServerAgent Mail settings anymore! Probably
I can rectify this by changing the service under which the
SQLServerAgent runs, but still I wouild like to know what is causing
this behaviour, and how to solve it.
Currently I'm not using SQLServerAgentMail, I use SQL Mail, but this
problem is nagging me and it irritates me that I cannot find out what is
wrong.
Systeminfo: SQL2K, W2K, Exchange 5.5, all applicable servicepacks and
patches, Outlook 2000 v9.0.0.2711.
Any hints apreciated,
Hans Brouwer
Tnx,
Hans Brouwer
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
It's not clear how you have the service accounts set up. If
by "standard local Administrator account" you mean Local
System, then this would be the problem. If you are using
Exchange as your mail server, the service account needs to
be setup as a domain account.
-Sue
On Wed, 14 Apr 2004 03:40:34 -0700, hansje
<hansjes@.anonymous.com> wrote:

>I've setup SQL Mail and I'm trying to setup SQL Server Agent mail as
>well, but this does not work.
>Both MSSQLServer and SQLServerAgent services run under the standard
>local Administrator account. A mailprofile and a postbox is created for
>this account, client is Outlook 2000, mailserver is Exchange 5.5. The
>mailaccount works fine, I can sent and receive mail.
>When activating SQLMail, no problem. I can use xp_sendmail without a
>problem.
>However, when trying to activate SQL Server Agent mail, after choosing
>the Exchange profile(the same 1 as for SQL Mail, there is only 1
>profile) I receive a MAPI Logon failed:
>Error 22022:SQL Server Agent error: MapiLogon Ex Failed due to MAPI
>error 273: MAPI Logon failed.
>I can understand what this means:the Administrator account is not
>recognized as a legal login. Since both services run under this login,
>and this poses noprob for SQL Mail, it makes no sence to me. Obviously I
>am missing something, but I don't know what.
>I have googled the internet and came up with guiet a view similar
>questions. Read several Q&A's, among others also the ones from
>Microsoft, but either the suggested causes were not, or the possible
>reasons did not aply. I found 1 solution which I tried: enter the
>profile(BTW, there is only 1 profile), accept it tho testing it
>generates the failure and stop&restart the SQLServerAgent. It did not
>work.
>Now a new feature has arissen: the mailsession droplist, with which to
>set the mailprofile is greyed out, and the TEST button as well! This
>means I cannot change the SQLServerAgent Mail settings anymore! Probably
>I can rectify this by changing the service under which the
>SQLServerAgent runs, but still I wouild like to know what is causing
>this behaviour, and how to solve it.
>Currently I'm not using SQLServerAgentMail, I use SQL Mail, but this
>problem is nagging me and it irritates me that I cannot find out what is
>wrong.
>Systeminfo: SQL2K, W2K, Exchange 5.5, all applicable servicepacks and
>patches, Outlook 2000 v9.0.0.2711.
>Any hints apreciated,
>Hans Brouwer
>
>
>Tnx,
>Hans Brouwer
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
|||Hi Sue,
I use the Administrator account of the server, where SQL Server is
installed, not the local system account.
Tnx,
Hans Brouwer
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||If you are using the Administrator's account local to the
server then this isn't a domain account and won't work. You
need a domain account if you are using Exchange as the mail
server.
-Sue
On Thu, 15 Apr 2004 00:21:04 -0700, hansje
<hansjes@.anonymous.com> wrote:

>Hi Sue,
>I use the Administrator account of the server, where SQL Server is
>installed, not the local system account.
>Tnx,
>Hans Brouwer
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!
|||Tnx for the info Sue; it does kleave me flabbergasted. The server in
question is a stand-alone server, not part of a domain, obviously part
of the companynetwork. Why does SQLMail not need to be part of a
domainaccount? MSSQLServer is also running under the local
Adminstratoraccount.
I can think of 1 reason, why the SQLServerAgent should run under a
domainaccount, and that is for running jobs executing distributed
queries on remote servers. However, jobs can run which do not execute
remote queries. I can't imagine wy the mailfunctionality IS dependant on
a domaionaccount..
I know, if these are the facts I'll have to live with it, but I would
like to know the reason for choosing such a configuration.
Tnx anyway,
Hans Brouwer
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||You can find the requirement of a domain account for SQL
Mail when using an Exchange mail server in the following
Microsoft knowledge base article:
INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
If you'd rather just use smtp to run mail related tasks,
take a look at a free extended stored procedure which uses
just smtp for mail. You can find it at:
http://www.sqldev.net/xp/xpsmtp.htm
-Sue
On Tue, 20 Apr 2004 04:12:11 -0700, hansje
<hansjes@.anonymous.com> wrote:

>Tnx for the info Sue; it does kleave me flabbergasted. The server in
>question is a stand-alone server, not part of a domain, obviously part
>of the companynetwork. Why does SQLMail not need to be part of a
>domainaccount? MSSQLServer is also running under the local
>Adminstratoraccount.
>I can think of 1 reason, why the SQLServerAgent should run under a
>domainaccount, and that is for running jobs executing distributed
>queries on remote servers. However, jobs can run which do not execute
>remote queries. I can't imagine wy the mailfunctionality IS dependant on
>a domaionaccount..
>I know, if these are the facts I'll have to live with it, but I would
>like to know the reason for choosing such a configuration.
>Tnx anyway,
>Hans Brouwer
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!

many to many relationship in OLAP

Hi, this is a question about many to many relationship in Analysis services cube.

We have an Analysis services OLAP cube for reporting the amount of sold goods. One of the dimensions is customers another dimension is sales person. One sale person takes care of more than one customer and one costumer can be hold by more than one sales person. (many to many relationship, for connecting table customers and sales person I used intermediate table)

The problem is when one customer has, for example, two sales persons (A and B). If I chose just sale person A from sales person dimension everything is O.K. (Row area - customers, Column area - time dimension, Excel XP) but if I want to see how much was sold by both A and B, the data (amount of sold goods) is multiplied twice. (e.g. on 01.01.03 was sold to customer XX just 100 items and not 200 even though two sales person sold them) I am looking for something like distinct sum.

Can you suggest any solution to this problem?

Thanks in advance,

Daviddont summarize based on this data

you can:

a). not summarize based on this data

b). create a percentage of sales-- so that if 9 sales people are assigned to an account, then they each get 11% of the sales.|||So you think there is no other way how to solve my problem. We have more of these cases.|||uh there are a hundred ways to solve this

i would try to solve it on the database side, and not the OLAP side-- it is going to be a lot eaiser.

i deal with this all the time, and i have a cube for employee sales and then a cube for total sales.

let me look into this a little bit better..

im an olap developer and just generally avoid many to many.. but maybe there is a logical way to do this

(to be truthful, when i have a many to many, i shape the data using DTS in order to flatten it into a snowflake)--

isnt this just a snowflake schema?

maybe you could create a table that would assign a bunch of salespeople and then you assign the group to the record..

and allow drilldown to see what people are in a group--

but this seems oversimplified..

cant you just make a list of all of the sales people for each customer, and list it in text?

like you would push into a database field all of the sales reps for a particular order-- IE, 'John Smith, April Johnson, Mark Kay Latorneau' etc

this really wouldnt be that difficult to accomplish...|||thanks for advice

manually removing encryption keys

Hi,
I am trying to recover a reporting services installation to a different
server with a different name. At the moment I am battling a "this
edition of reporting services does not support web farms". I think now
I have remove all the encryption keys and then install just one key,
with the associated installation ID in the config file.
Anyway, I can't remove the encryption keys, I keep getting unexpected
database error (-2147159548) ox80040e21 when I run rskeymgmt -r
<installation id>
Can remove the keys manually?
ChandraWas wondering if you had found a fix for this problem? I've had the same
problem, and have found no solution.
Thanks
Marvin
"chandra@.cbmi.org.au" wrote:
> Hi,
> I am trying to recover a reporting services installation to a different
> server with a different name. At the moment I am battling a "this
> edition of reporting services does not support web farms". I think now
> I have remove all the encryption keys and then install just one key,
> with the associated installation ID in the config file.
> Anyway, I can't remove the encryption keys, I keep getting unexpected
> database error (-2147159548) ox80040e21 when I run rskeymgmt -r
> <installation id>
>
> Can remove the keys manually?
>
> Chandra
>|||Sorry, still don't have an answer :-)
very frustrating
Chandra
Marvin wrote:
> Was wondering if you had found a fix for this problem? I've had the same
> problem, and have found no solution.
> Thanks
> Marvin
> "chandra@.cbmi.org.au" wrote:
> > Hi,
> >
> > I am trying to recover a reporting services installation to a different
> > server with a different name. At the moment I am battling a "this
> > edition of reporting services does not support web farms". I think now
> > I have remove all the encryption keys and then install just one key,
> > with the associated installation ID in the config file.
> >
> > Anyway, I can't remove the encryption keys, I keep getting unexpected
> > database error (-2147159548) ox80040e21 when I run rskeymgmt -r
> > <installation id>
> >
> >
> > Can remove the keys manually?
> >
> >
> > Chandra
> >
> >

Manually change report

Hello,

I use Reporting Services in my solution. The data i have as source is very uncertain. Sometimes i manually have to delete a record from the report (not the database itself). I woluld like to have a checkbox or something simular to delete from report and totals, grafs would be updated. At the same time I would like the report to make a comment that a record was taken away, alternative let the user make a comment. Is this possible? DO i have to make a own application for that?

Thank you for your help!

Best regards,

Luskan

If you want my personal opinion, I don't think RS would be the best application for this.

Personally I would return the data from SQL to something like Excel using ADO/VBA or even MSQuery

and base your report / charts on the data then in Excel.

Deleting/changing the data will then have no effect on the data in your SQL database and if

constructed correcly, your charts/reports in excel will be updated automatically...

|||

Well, thank you for your opinion!

But i think I got a Solution. Besides exporting to Excel you can use cascading report parameters in order to to achive the same thing. The only problem i have know is that "Value field" and "label field" does not work as it is inteded to do.

Best regards,

Luskan

Manual Dowload/CD Burn/Installation

Where can I go to manually download SQL Server Express 2005 w/ Advanced Services so that I can burn a CD and install offline?

WHat about here: http://msdn.microsoft.com/vstudio/express/sql/download/

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Make sure you "unzip" the file into a temp directory. This will provide a "burnable" folder with an autostart file to install from a cd.

Manual Configuration - Installation

I have installed Reporting Services on a computer running Windows Server 2000
with SP4. At the end of the installation it told me it could not
automatically configure/start reporting services and that it needed to be
done. The only thing I can find in the readme file is in regards to
permissions dor IWAM on a domain controller, however this server is not a
domain controller. When I go to the page for ReportManager I get an access
denied and the ReportingServices page has a page cannot be displayed error.
Any help is greatly appreciated.
Thanks,
IanI have made it past that error but now I am getting: The Report Server Web
service has not generated a public key. The service may have not have
started successfully. Check the log files for more information.
I have done everything here:
http://www.sqlreportingservices.net/Ask/354.aspx with no luck thus far.
Please help.
Ian
"Ian T. Jones" wrote:
> I have installed Reporting Services on a computer running Windows Server 2000
> with SP4. At the end of the installation it told me it could not
> automatically configure/start reporting services and that it needed to be
> done. The only thing I can find in the readme file is in regards to
> permissions dor IWAM on a domain controller, however this server is not a
> domain controller. When I go to the page for ReportManager I get an access
> denied and the ReportingServices page has a page cannot be displayed error.
> Any help is greatly appreciated.
> Thanks,
> Ian

Manipulating a reports visual appearance

Hi,
we are changing at the moment our reporting system from crystal report to MS
Reporting services. Our reports are highly customizable during display. In
Crystal Reports we were using scripts into the reports to set the text align
and the column back- and foreground color by user decisions. It was also
possible to choose whether the report should be displayed in landscape or
portrait format. Now I need to give the reports with MS Reporting Services
the same abilities but I didn't find any possiblity to that yet. Because our
main application in which the reports are shown is written in good old MFC
we are using URL access to render and display the reports.
Does anyone here knows how to solve one or all of my issues described above?
Thanks in Advance
Markus
P.S. I'm a really newbie in MS Reporting Services.Style properties (like text alignment and color) can be expressions. These
expressions can depend on parameters to the report.
For example:
<TextAlign>=Parameters!TextAlign.Value</TextAlign>
Or:
<Color>=iif(Parameters!ColorScheme.Value="Rainbow","HotPink","LightBrown")</
Color>
For page orientation, you can set the page height and width via parameters
in the URL (something like rc:PageHeight=8.5... Check the documentation for
details)
My employer's lawyers require me to say:
"This posting is provided 'AS IS' with no warranties, and confers no
rights."
"Markus Heid" <markus.heid@.logasys.com> wrote in message
news:efgHn3$YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> Hi,
> we are changing at the moment our reporting system from crystal report to
MS
> Reporting services. Our reports are highly customizable during display. In
> Crystal Reports we were using scripts into the reports to set the text
align
> and the column back- and foreground color by user decisions. It was also
> possible to choose whether the report should be displayed in landscape or
> portrait format. Now I need to give the reports with MS Reporting Services
> the same abilities but I didn't find any possiblity to that yet. Because
our
> main application in which the reports are shown is written in good old MFC
> we are using URL access to render and display the reports.
> Does anyone here knows how to solve one or all of my issues described
above?
> Thanks in Advance
> Markus
> P.S. I'm a really newbie in MS Reporting Services.
>|||TextAlign, BackgroundColor, and ForegroundColor can be expressions, so you
can make them user-driven using user-entered parameters. Check
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_3983.asp
for details.
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Markus Heid" <markus.heid@.logasys.com> wrote in message
news:efgHn3$YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> Hi,
> we are changing at the moment our reporting system from crystal report to
MS
> Reporting services. Our reports are highly customizable during display. In
> Crystal Reports we were using scripts into the reports to set the text
align
> and the column back- and foreground color by user decisions. It was also
> possible to choose whether the report should be displayed in landscape or
> portrait format. Now I need to give the reports with MS Reporting Services
> the same abilities but I didn't find any possiblity to that yet. Because
our
> main application in which the reports are shown is written in good old MFC
> we are using URL access to render and display the reports.
> Does anyone here knows how to solve one or all of my issues described
above?
> Thanks in Advance
> Markus
> P.S. I'm a really newbie in MS Reporting Services.
>|||Thank you very much. It is much easier than I though!
"Chris Hays [MSFT]" <chays@.online.microsoft.com> wrote in message
news:uetiBzFZEHA.2972@.tk2msftngp13.phx.gbl...
> Style properties (like text alignment and color) can be expressions.
These
> expressions can depend on parameters to the report.
> For example:
> <TextAlign>=Parameters!TextAlign.Value</TextAlign>
> Or:
>
<Color>=iif(Parameters!ColorScheme.Value="Rainbow","HotPink","LightBrown")</
> Color>
> For page orientation, you can set the page height and width via parameters
> in the URL (something like rc:PageHeight=8.5... Check the documentation
for
> details)
>
> --
> My employer's lawyers require me to say:
> "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> "Markus Heid" <markus.heid@.logasys.com> wrote in message
> news:efgHn3$YEHA.1448@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> > we are changing at the moment our reporting system from crystal report
to
> MS
> > Reporting services. Our reports are highly customizable during display.
In
> > Crystal Reports we were using scripts into the reports to set the text
> align
> > and the column back- and foreground color by user decisions. It was also
> > possible to choose whether the report should be displayed in landscape
or
> > portrait format. Now I need to give the reports with MS Reporting
Services
> > the same abilities but I didn't find any possiblity to that yet. Because
> our
> > main application in which the reports are shown is written in good old
MFC
> > we are using URL access to render and display the reports.
> > Does anyone here knows how to solve one or all of my issues described
> above?
> >
> > Thanks in Advance
> >
> > Markus
> >
> > P.S. I'm a really newbie in MS Reporting Services.
> >
> >
>

Managing Reporting Services Users

Good afternoon,

I've just started using SQL Server 2005 Reporting Services and after managing to deploy it, on the dev server, I've found myself unable to manage the users that can access it. After searching on the web I've noticed that only windows users can access Reporting Services (unless you develop your custom authentication system using the extentions). That's ok for now, the problem is that I can't seem to find where to assign permissions to the windows users.

The bottom line is that I would like configure reporting services in a way that some users can add reports while others can only view them, as well as making their own using ad-hoc reports. Could someone help me out with this? :)

I started to answer a similar question here:http://forums.asp.net/thread/1573629.aspx

Within the security area you will see the different type of access that are available:

Browser Role : Run reports and navigate through the folder structure.

Content Manager Role : Define a folder structure for storing reports and other items, set security at the item level, and view and manage the items stored by the server.

Report Builder Role: Build and edit reports in Report Builder.

Publisher Role: Publish content to a report server.

My Reports Role : Build reports for personal use or store reports in a user-owned folder.

System Administrator Role: Enable features and set defaults, set site-wide security, create role definitions, and manage jobs.

System User Role : View basic information about the report server such as the schedule information in a shared schedule.

'Roles Copied straight outta the help'

I've found it's easier to set up Global groups within the Active Dir and add them to RS2005, it's a lot easier to manage the users.

Managing reporting services models

Hi everybody,

I have the following scenario: I have web application which is creting new SQL Server database each time when new customer is created in the application.

I would like to give users the possibility of creating ad hoc reports and thus I need to create new connection and report model (and deploy them to my reoport server) each time when the new database is created (report models can not use multiple databases). Does anybody knows how can I do that?

Maybe there is another approach to this kind of problem?

Thank you in advance,

Marek

You can create datasources and autogenerate models from these datasources using the SSRS SOAP API. Check out this article in BOL for information on how to get started using the SOAP API:

http://msdn2.microsoft.com/en-us/library/ms159162.aspx

Managing an axis in a SQL 2005 Reporting Services Chart

Hi,

I'm getting my feet wet with SQL 2005's reporting services charting features and I can't seem to find a way to have my Y-Axis stay constant. I've created a Line Chart and have set the Minimum and Maximum scale to 10 for my Y-Axis. The report seems to ignore this and sets each chart in the report relevant to the data for the series. This means that my charts, which are performance indicators for individuals, are not consistent in their layout. Is this a bug, or am I doing something wrong?

thanks

-James

james@.4divine.com

The minimum value of the y-axis describes the minimum axis value that should be shown in the chart. It sounds like you have set the minimum and the maximum value of the y-axis to have identical values. In that case your setting is ignored and the axis will autoscale based on the actual data point y-values.

I think you should set the minimum to be 0 and the maximum to be 10.

-- Robert

|||

hi,

i also have a problem with the numbers in charts, esp bar chart. currently, the y-axis is on auto-mode.

however, some of the numbers are hidden in the bar chart.

is there a way to set the maximum value of the y-axis to be max(globalvariable) + 10.

i believe this will force the chart to be bigger, thus the nos will not be hidden in the bar chart.

any idea how this can be done?

-

HY

|||

RS 2005 allows the usage of expressions for the axis settings, such as min, max, and so on.

E.g. =Max(Fields!A.Value, "Dataset1")

-- Robert

Managing an axis in a SQL 2005 Reporting Services Chart

Hi,

I'm getting my feet wet with SQL 2005's reporting services charting features and I can't seem to find a way to have my Y-Axis stay constant. I've created a Line Chart and have set the Minimum and Maximum scale to 10 for my Y-Axis. The report seems to ignore this and sets each chart in the report relevant to the data for the series. This means that my charts, which are performance indicators for individuals, are not consistent in their layout. Is this a bug, or am I doing something wrong?

thanks

-James

james@.4divine.com

The minimum value of the y-axis describes the minimum axis value that should be shown in the chart. It sounds like you have set the minimum and the maximum value of the y-axis to have identical values. In that case your setting is ignored and the axis will autoscale based on the actual data point y-values.

I think you should set the minimum to be 0 and the maximum to be 10.

-- Robert

|||

hi,

i also have a problem with the numbers in charts, esp bar chart. currently, the y-axis is on auto-mode.

however, some of the numbers are hidden in the bar chart.

is there a way to set the maximum value of the y-axis to be max(globalvariable) + 10.

i believe this will force the chart to be bigger, thus the nos will not be hidden in the bar chart.

any idea how this can be done?

-

HY

|||

RS 2005 allows the usage of expressions for the axis settings, such as min, max, and so on.

E.g. =Max(Fields!A.Value, "Dataset1")

-- Robert