I have a tabular report with several groupings.
I want to customize one of the groups.
For example I have a field called country. I want to group it by
region, western and eastern. How would I do this?where does the regional information come from? you need to provide us with
more info about your fields and their data.
"OogleGoogle" <Yvonhong@.gmail.com> wrote in message
news:1183576344.729726.5950@.i38g2000prf.googlegroups.com...
>I have a tabular report with several groupings.
> I want to customize one of the groups.
> For example I have a field called country. I want to group it by
> region, western and eastern. How would I do this?
>
Showing posts with label field. Show all posts
Showing posts with label field. Show all posts
Tuesday, March 27, 2012
Thursday, March 22, 2012
custom view according to date
I want to add custom view so that it show the records that their date
is less than a specific field like "2007/12/25". Dates are saved in DB like "2006/08/29 12:00:00 A.M" nad
smalldatetime format in MSSQL.
what should I do?
is less than a specific field like "2007/12/25". Dates are saved in DB like "2006/08/29 12:00:00 A.M" nad
smalldatetime format in MSSQL.
what should I do?
The easiest way to achieve that using SQL statement.
Did you try it?
|||I did it but It has no results.could you pu the sql query code here
thanks,
|||Whatever you are binding the field to, probably has a DataFormatString property, and a HtmlEncode property. Set the DataFormatString property to {0:d} and set the HtmlEncode property to false and see if that is what you are looking for. If that isn't it (Which uses whatever format you have set in your control panel's short date format), you can force it to a specific format by setting the DataFormatString to {0:yyyy/MM/dd}
PS. Please read up on data types. The database can not both store dates like "2006/01/01 12:00:00 PM" and also store them in a smalldatetime. It's one or the other. If it's storing them into a smalldatetime, then it's stored as a smalldatetime which is not a string format. If you are storing them as a varchar/char/nchar in YYYY/MM/DD HH:MM:SS format, then they aren't stored as a smalldatetime.
|||If you write VB.NET you have two DateDiff functions to use. Try the links below for details. Hope this helps.
http://blogs.msdn.com/vbfaq/
http://www.stanford.edu/~bsuter/sql-datecomputations.html
Custom user field in database
Is there a SQL 2005 table containing meta data about my database where I can set a column whose
purpose is to hold custom user data?
I have an application for which I need to change the table structure from version to version. Each
time I distribute an new build of the application, the setup program lookupthe DB revision level
then issue the proper DDL calls to perform the updates.
I could always create a small user table with only one column into which I would store the DB
revision level. However, I would prefer store this INT value somewhere else if SQL 2005 offers a
capability to do so.
Gaetan.
Look in Books Online about using Database Extended Properties. Relatively
easy to code to, and retrieve from; custom name value pairs.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Gaetan" <me@.somewhere.com> wrote in message
news:q4d9m2t2o8suqoo678js6qndkk59721ji5@.4ax.com...
> Is there a SQL 2005 table containing meta data about my database where I
> can set a column whose
> purpose is to hold custom user data?
> I have an application for which I need to change the table structure from
> version to version. Each
> time I distribute an new build of the application, the setup program
> lookupthe DB revision level
> then issue the proper DDL calls to perform the updates.
> I could always create a small user table with only one column into which I
> would store the DB
> revision level. However, I would prefer store this INT value somewhere
> else if SQL 2005 offers a
> capability to do so.
> Gaetan.
|||sys.sp_addextendedproperty and sys.sp_updateextendedproperty are exactly what I needed.
Thank you.
purpose is to hold custom user data?
I have an application for which I need to change the table structure from version to version. Each
time I distribute an new build of the application, the setup program lookupthe DB revision level
then issue the proper DDL calls to perform the updates.
I could always create a small user table with only one column into which I would store the DB
revision level. However, I would prefer store this INT value somewhere else if SQL 2005 offers a
capability to do so.
Gaetan.
Look in Books Online about using Database Extended Properties. Relatively
easy to code to, and retrieve from; custom name value pairs.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Gaetan" <me@.somewhere.com> wrote in message
news:q4d9m2t2o8suqoo678js6qndkk59721ji5@.4ax.com...
> Is there a SQL 2005 table containing meta data about my database where I
> can set a column whose
> purpose is to hold custom user data?
> I have an application for which I need to change the table structure from
> version to version. Each
> time I distribute an new build of the application, the setup program
> lookupthe DB revision level
> then issue the proper DDL calls to perform the updates.
> I could always create a small user table with only one column into which I
> would store the DB
> revision level. However, I would prefer store this INT value somewhere
> else if SQL 2005 offers a
> capability to do so.
> Gaetan.
|||sys.sp_addextendedproperty and sys.sp_updateextendedproperty are exactly what I needed.
Thank you.
Custom user field in database
Is there a SQL 2005 table containing meta data about my database where I can set a column whose
purpose is to hold custom user data?
I have an application for which I need to change the table structure from version to version. Each
time I distribute an new build of the application, the setup program lookupthe DB revision level
then issue the proper DDL calls to perform the updates.
I could always create a small user table with only one column into which I would store the DB
revision level. However, I would prefer store this INT value somewhere else if SQL 2005 offers a
capability to do so.
Gaetan.Look in Books Online about using Database Extended Properties. Relatively
easy to code to, and retrieve from; custom name value pairs.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Gaetan" <me@.somewhere.com> wrote in message
news:q4d9m2t2o8suqoo678js6qndkk59721ji5@.4ax.com...
> Is there a SQL 2005 table containing meta data about my database where I
> can set a column whose
> purpose is to hold custom user data?
> I have an application for which I need to change the table structure from
> version to version. Each
> time I distribute an new build of the application, the setup program
> lookupthe DB revision level
> then issue the proper DDL calls to perform the updates.
> I could always create a small user table with only one column into which I
> would store the DB
> revision level. However, I would prefer store this INT value somewhere
> else if SQL 2005 offers a
> capability to do so.
> Gaetan.|||sys.sp_addextendedproperty and sys.sp_updateextendedproperty are exactly what I needed.
Thank you.
purpose is to hold custom user data?
I have an application for which I need to change the table structure from version to version. Each
time I distribute an new build of the application, the setup program lookupthe DB revision level
then issue the proper DDL calls to perform the updates.
I could always create a small user table with only one column into which I would store the DB
revision level. However, I would prefer store this INT value somewhere else if SQL 2005 offers a
capability to do so.
Gaetan.Look in Books Online about using Database Extended Properties. Relatively
easy to code to, and retrieve from; custom name value pairs.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Gaetan" <me@.somewhere.com> wrote in message
news:q4d9m2t2o8suqoo678js6qndkk59721ji5@.4ax.com...
> Is there a SQL 2005 table containing meta data about my database where I
> can set a column whose
> purpose is to hold custom user data?
> I have an application for which I need to change the table structure from
> version to version. Each
> time I distribute an new build of the application, the setup program
> lookupthe DB revision level
> then issue the proper DDL calls to perform the updates.
> I could always create a small user table with only one column into which I
> would store the DB
> revision level. However, I would prefer store this INT value somewhere
> else if SQL 2005 offers a
> capability to do so.
> Gaetan.|||sys.sp_addextendedproperty and sys.sp_updateextendedproperty are exactly what I needed.
Thank you.
Custom user field in database
Is there a SQL 2005 table containing meta data about my database where I can
set a column whose
purpose is to hold custom user data?
I have an application for which I need to change the table structure from ve
rsion to version. Each
time I distribute an new build of the application, the setup program lookupt
he DB revision level
then issue the proper DDL calls to perform the updates.
I could always create a small user table with only one column into which I w
ould store the DB
revision level. However, I would prefer store this INT value somewhere else
if SQL 2005 offers a
capability to do so.
Gaetan.Look in Books Online about using Database Extended Properties. Relatively
easy to code to, and retrieve from; custom name value pairs.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Gaetan" <me@.somewhere.com> wrote in message
news:q4d9m2t2o8suqoo678js6qndkk59721ji5@.
4ax.com...
> Is there a SQL 2005 table containing meta data about my database where I
> can set a column whose
> purpose is to hold custom user data?
> I have an application for which I need to change the table structure from
> version to version. Each
> time I distribute an new build of the application, the setup program
> lookupthe DB revision level
> then issue the proper DDL calls to perform the updates.
> I could always create a small user table with only one column into which I
> would store the DB
> revision level. However, I would prefer store this INT value somewhere
> else if SQL 2005 offers a
> capability to do so.
> Gaetan.|||sys.sp_addextendedproperty and sys.sp_updateextendedproperty are exactly w
hat I needed.
Thank you.sql
set a column whose
purpose is to hold custom user data?
I have an application for which I need to change the table structure from ve
rsion to version. Each
time I distribute an new build of the application, the setup program lookupt
he DB revision level
then issue the proper DDL calls to perform the updates.
I could always create a small user table with only one column into which I w
ould store the DB
revision level. However, I would prefer store this INT value somewhere else
if SQL 2005 offers a
capability to do so.
Gaetan.Look in Books Online about using Database Extended Properties. Relatively
easy to code to, and retrieve from; custom name value pairs.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Gaetan" <me@.somewhere.com> wrote in message
news:q4d9m2t2o8suqoo678js6qndkk59721ji5@.
4ax.com...
> Is there a SQL 2005 table containing meta data about my database where I
> can set a column whose
> purpose is to hold custom user data?
> I have an application for which I need to change the table structure from
> version to version. Each
> time I distribute an new build of the application, the setup program
> lookupthe DB revision level
> then issue the proper DDL calls to perform the updates.
> I could always create a small user table with only one column into which I
> would store the DB
> revision level. However, I would prefer store this INT value somewhere
> else if SQL 2005 offers a
> capability to do so.
> Gaetan.|||sys.sp_addextendedproperty and sys.sp_updateextendedproperty are exactly w
hat I needed.
Thank you.sql
Monday, March 19, 2012
Custom Report Item: Textbox
Hi all,
If it is possible, how I can extend the Textbox item with a DataSet
property. In order to display a field from multiple rows, with a separator ?
Example:
a dataset with 10 rows (and 1 field "Name")
and I want to display in the Textbox
"Name1, Name2, Name3, Name4, Name5, Name6, Name7, Name8, Name9, Name10"
ThanksI'm sure this is possible, just to give you a point in one possible
direction...
From a Database side of things, you could pivot the data in your SQL
statement. SQL Server 2005 has new SQL commands called PIVOT and UNPIVOT...
Once your rows values are pivotted to columns in a single row, then you
could specify them easily enough in the text box.
Hope that helps.
Dan.
"gbouzebra" <gbouzebra@.discussions.microsoft.com> wrote in message
news:D8959BBA-3778-4129-9E3F-AB4443A58C5F@.microsoft.com...
> Hi all,
> If it is possible, how I can extend the Textbox item with a DataSet
> property. In order to display a field from multiple rows, with a separator
> ?
> Example:
> a dataset with 10 rows (and 1 field "Name")
> and I want to display in the Textbox
> "Name1, Name2, Name3, Name4, Name5, Name6, Name7, Name8, Name9, Name10"
> Thanks
If it is possible, how I can extend the Textbox item with a DataSet
property. In order to display a field from multiple rows, with a separator ?
Example:
a dataset with 10 rows (and 1 field "Name")
and I want to display in the Textbox
"Name1, Name2, Name3, Name4, Name5, Name6, Name7, Name8, Name9, Name10"
ThanksI'm sure this is possible, just to give you a point in one possible
direction...
From a Database side of things, you could pivot the data in your SQL
statement. SQL Server 2005 has new SQL commands called PIVOT and UNPIVOT...
Once your rows values are pivotted to columns in a single row, then you
could specify them easily enough in the text box.
Hope that helps.
Dan.
"gbouzebra" <gbouzebra@.discussions.microsoft.com> wrote in message
news:D8959BBA-3778-4129-9E3F-AB4443A58C5F@.microsoft.com...
> Hi all,
> If it is possible, how I can extend the Textbox item with a DataSet
> property. In order to display a field from multiple rows, with a separator
> ?
> Example:
> a dataset with 10 rows (and 1 field "Name")
> and I want to display in the Textbox
> "Name1, Name2, Name3, Name4, Name5, Name6, Name7, Name8, Name9, Name10"
> Thanks
Custom Rendering Extension for HTML Fields
I am using RS 2005, a "comments" field in a database contains HTML content. When rendering the field on the report it displays raw HTML.
Does anybody knows about a custom rendering extension that will render HTML content?
Thanks
Fernando
Has anyone resolved this? I am facing a similar issue. My website has form that takes comments via FreeTextBox. It is probably similar to what is used on this FORUM! The FreeTextBox control returns text with HTML tags throughout. I need to display the same data via Reporting Services. Currently using SQL Server 2000, Migrating to 2005 soon.
This has to be a common scenario. How about a third party control?
Thanks.
|||This is currently not supported. See http://blogs.msdn.com/bimusings/archive/2005/12/14/503648.aspx. We are working on this for an upcoming release.
Custom Rendering Extension for HTML Fields
I am using RS 2005, a "comments" field in a database contains HTML content. When rendering the field on the report it displays raw HTML.
Does anybody knows about a custom rendering extension that will render HTML content?
Thanks
Fernando
Has anyone resolved this? I am facing a similar issue. My website has form that takes comments via FreeTextBox. It is probably similar to what is used on this FORUM! The FreeTextBox control returns text with HTML tags throughout. I need to display the same data via Reporting Services. Currently using SQL Server 2000, Migrating to 2005 soon.
This has to be a common scenario. How about a third party control?
Thanks.
|||This is currently not supported. See http://blogs.msdn.com/bimusings/archive/2005/12/14/503648.aspx. We are working on this for an upcoming release.
Custom Rendering Extension for HTML Fields
I am using RS 2005, a "comments" field in a database contains HTML content. When rendering the field on the report it displays raw HTML.
Does anybody knows about a custom rendering extension that will render HTML content?
Thanks
Fernando
Has anyone resolved this? I am facing a similar issue. My website has form that takes comments via FreeTextBox. It is probably similar to what is used on this FORUM! The FreeTextBox control returns text with HTML tags throughout. I need to display the same data via Reporting Services. Currently using SQL Server 2000, Migrating to 2005 soon.
This has to be a common scenario. How about a third party control?
Thanks.
|||This is currently not supported. See http://blogs.msdn.com/bimusings/archive/2005/12/14/503648.aspx. We are working on this for an upcoming release.
Sunday, March 11, 2012
Custom or 3rd party report filtering tool
Hi,
I am looking for a tool/application that can be used by users to select and
filter reports. The report list and field list would be pulled from a
database along with field type, and query to use to populate drop down list
filters.
User would select the report they want from a drop down list. The form
would populate with the fields required for the filter and the user would
enter into the filter as required. This information would then be sent to
the report server as either report paramaters, or have the filter items
combined into a where clause and passed as a single parameter to the report
where it would be used to filter the SQL coming from the data source.
Has anyone seen anything like this? I can't find anything on google. We'd
like an easy to use reporting interface for our users and are not willing
allow our users to use the standard RS interface for report filtering.Maybe use the Windows Sharepoint Services report explorer to let users
select their report and enable the filtering in the report.
"Dan" <kgh@.kg.com> wrote in message
news:uueqfouzHHA.3788@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am looking for a tool/application that can be used by users to select
> and filter reports. The report list and field list would be pulled from a
> database along with field type, and query to use to populate drop down
> list filters.
> User would select the report they want from a drop down list. The form
> would populate with the fields required for the filter and the user would
> enter into the filter as required. This information would then be sent to
> the report server as either report paramaters, or have the filter items
> combined into a where clause and passed as a single parameter to the
> report where it would be used to filter the SQL coming from the data
> source.
> Has anyone seen anything like this? I can't find anything on google.
> We'd like an easy to use reporting interface for our users and are not
> willing allow our users to use the standard RS interface for report
> filtering.
>
>|||That's the standard sharepoint site for reports that comes with RS?
I don't mind this interface too much, but it's not as intuitive as I like.
One majour problem is in the difference in which we do filtering with our
reports. All of our reports have only 1 parameter and we call it
WhereAndSort. We just append this parameter value to the SQL statement in
the dataset. We do not create a parameter for every field we want the user
to potentially filter on because 1)we like to be able to add new filtering
options without re-compiling the report. 2) We recieve better performance
filtering on the SQL query rather than pulling all records and do report
filtering...
Thanks...
....still looking for another option.
Dan
"Joel Lindstrom" <joel.lindstrom@.customereffective.com> wrote in message
news:2425A0D9-A741-4A05-B694-1C9F6E7FB086@.microsoft.com...
> Maybe use the Windows Sharepoint Services report explorer to let users
> select their report and enable the filtering in the report.
> "Dan" <kgh@.kg.com> wrote in message
> news:uueqfouzHHA.3788@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I am looking for a tool/application that can be used by users to select
>> and filter reports. The report list and field list would be pulled from
>> a database along with field type, and query to use to populate drop down
>> list filters.
>> User would select the report they want from a drop down list. The form
>> would populate with the fields required for the filter and the user would
>> enter into the filter as required. This information would then be sent
>> to the report server as either report paramaters, or have the filter
>> items combined into a where clause and passed as a single parameter to
>> the report where it would be used to filter the SQL coming from the data
>> source.
>> Has anyone seen anything like this? I can't find anything on google.
>> We'd like an easy to use reporting interface for our users and are not
>> willing allow our users to use the standard RS interface for report
>> filtering.
>>
>
I am looking for a tool/application that can be used by users to select and
filter reports. The report list and field list would be pulled from a
database along with field type, and query to use to populate drop down list
filters.
User would select the report they want from a drop down list. The form
would populate with the fields required for the filter and the user would
enter into the filter as required. This information would then be sent to
the report server as either report paramaters, or have the filter items
combined into a where clause and passed as a single parameter to the report
where it would be used to filter the SQL coming from the data source.
Has anyone seen anything like this? I can't find anything on google. We'd
like an easy to use reporting interface for our users and are not willing
allow our users to use the standard RS interface for report filtering.Maybe use the Windows Sharepoint Services report explorer to let users
select their report and enable the filtering in the report.
"Dan" <kgh@.kg.com> wrote in message
news:uueqfouzHHA.3788@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I am looking for a tool/application that can be used by users to select
> and filter reports. The report list and field list would be pulled from a
> database along with field type, and query to use to populate drop down
> list filters.
> User would select the report they want from a drop down list. The form
> would populate with the fields required for the filter and the user would
> enter into the filter as required. This information would then be sent to
> the report server as either report paramaters, or have the filter items
> combined into a where clause and passed as a single parameter to the
> report where it would be used to filter the SQL coming from the data
> source.
> Has anyone seen anything like this? I can't find anything on google.
> We'd like an easy to use reporting interface for our users and are not
> willing allow our users to use the standard RS interface for report
> filtering.
>
>|||That's the standard sharepoint site for reports that comes with RS?
I don't mind this interface too much, but it's not as intuitive as I like.
One majour problem is in the difference in which we do filtering with our
reports. All of our reports have only 1 parameter and we call it
WhereAndSort. We just append this parameter value to the SQL statement in
the dataset. We do not create a parameter for every field we want the user
to potentially filter on because 1)we like to be able to add new filtering
options without re-compiling the report. 2) We recieve better performance
filtering on the SQL query rather than pulling all records and do report
filtering...
Thanks...
....still looking for another option.
Dan
"Joel Lindstrom" <joel.lindstrom@.customereffective.com> wrote in message
news:2425A0D9-A741-4A05-B694-1C9F6E7FB086@.microsoft.com...
> Maybe use the Windows Sharepoint Services report explorer to let users
> select their report and enable the filtering in the report.
> "Dan" <kgh@.kg.com> wrote in message
> news:uueqfouzHHA.3788@.TK2MSFTNGP02.phx.gbl...
>> Hi,
>> I am looking for a tool/application that can be used by users to select
>> and filter reports. The report list and field list would be pulled from
>> a database along with field type, and query to use to populate drop down
>> list filters.
>> User would select the report they want from a drop down list. The form
>> would populate with the fields required for the filter and the user would
>> enter into the filter as required. This information would then be sent
>> to the report server as either report paramaters, or have the filter
>> items combined into a where clause and passed as a single parameter to
>> the report where it would be used to filter the SQL coming from the data
>> source.
>> Has anyone seen anything like this? I can't find anything on google.
>> We'd like an easy to use reporting interface for our users and are not
>> willing allow our users to use the standard RS interface for report
>> filtering.
>>
>
Custom number format
Hello everybody,
I have a float type field in my sql server table and I want to select the
data in the format $XX,XXX.XX.
Any ideas '
Thanks.usamaalam wrote:
> Hello everybody,
> I have a float type field in my sql server table and I want to select the
> data in the format $XX,XXX.XX.
> Any ideas '
> Thanks.
Your client application controls how data is formatted for display, not
SQL Server. If you want to do that in SQL you'll have to return a
string rather than a number. I don't recommend it, but you can take a
look at the CONVERT function in Books Online.
Why do you use FLOATs for monetary amounts? FLOAT is an inexact numeric
so is an unusual and probably unwise choice for accounting. More
typically, I would suggest you should use NUMERIC/DECIMAL for this.
Hope this helps.
David Portas
SQL Server MVP
--|||I need select float in the specified format and return the formatted string.
Thanks.
"David Portas" wrote:
> usamaalam wrote:
>
> Your client application controls how data is formatted for display, not
> SQL Server. If you want to do that in SQL you'll have to return a
> string rather than a number. I don't recommend it, but you can take a
> look at the CONVERT function in Books Online.
> Why do you use FLOATs for monetary amounts? FLOAT is an inexact numeric
> so is an unusual and probably unwise choice for accounting. More
> typically, I would suggest you should use NUMERIC/DECIMAL for this.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||As David says, you should do it in the application tier and not in the
database. If you are using .NET in your application tier, there are powerful
formatting capabilities.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"usamaalam" <usamaalam@.discussions.microsoft.com> wrote in message
news:8207FDF2-C83E-491A-AE06-FF91FD965E14@.microsoft.com...
>I need select float in the specified format and return the formatted
>string.
> Thanks.
> "David Portas" wrote:
>|||Basically the application mechanism has been developed by someone else. I
need to export data from some tables in CSV format. The mechanism executes
an stored procedure, get data in a data table and a method gets the data
table as a parameter and generates CSV whatever present in the data table.
This will be more helpful for me to do it in sql, if possible.
Thanks a lot.
"SriSamp" wrote:
> As David says, you should do it in the application tier and not in the
> database. If you are using .NET in your application tier, there are powerf
ul
> formatting capabilities.
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "usamaalam" <usamaalam@.discussions.microsoft.com> wrote in message
> news:8207FDF2-C83E-491A-AE06-FF91FD965E14@.microsoft.com...
>
>|||One method:
SELECT
'$' + CONVERT(varchar(20), CAST(MyFloatColumn AS money), 1)
FROM MyTable
I agree with the others that this is a kludge. This is a deficiency in the
application that ought to be addressed in the app code rather than
Transact-SQL.
Happy Holidays
Dan Guzman
SQL Server MVP
"usamaalam" <usamaalam@.discussions.microsoft.com> wrote in message
news:874B2D06-CA54-4852-AB97-4EB07762DA84@.microsoft.com...
> Basically the application mechanism has been developed by someone else. I
> need to export data from some tables in CSV format. The mechanism
> executes
> an stored procedure, get data in a data table and a method gets the data
> table as a parameter and generates CSV whatever present in the data table.
> This will be more helpful for me to do it in sql, if possible.
> Thanks a lot.
> "SriSamp" wrote:
>|||i've needed to do this myself a number of times, format the output on the
SELECT because i'm using BCP to plonk the data into an output file ready for
FTP'ing off to another server somewhere for another department/company to
use.
I'm not going to go to the effort of building a .NET application just to do
this; far simpler and quicker to format on the SELECT and output using BCP -
less components, less complexity.
There are times in reporting too where we need to do this because it would
be more difficult to do it in the reporting tool, for instance reporting
services; consider a trading report where trade amounts might be in
different currency, two ways of doing it - either concatenate in the front
end which might be more difficult or just simply do it easily on the SELECT.
You need to consider what you are doing before definitively saying do it in
the front end or in the db.
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1135337208.989446.250540@.g43g2000cwa.googlegroups.com...
> usamaalam wrote:
>
> Your client application controls how data is formatted for display, not
> SQL Server. If you want to do that in SQL you'll have to return a
> string rather than a number. I don't recommend it, but you can take a
> look at the CONVERT function in Books Online.
> Why do you use FLOATs for monetary amounts? FLOAT is an inexact numeric
> so is an unusual and probably unwise choice for accounting. More
> typically, I would suggest you should use NUMERIC/DECIMAL for this.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||Maybe you should consider using XML as a transport medium, instead of CSV.
After all, this is the 21st century. :)
If the data is being used in an application at the other end then I really
see no point in formatting it, unless the actual data type is ignored at the
other end, which I doubt to be true.
ML
http://milambda.blogspot.com/|||i just want to reiterate what David said: if at all possible, change the
float to another type - you *will* get bad data at some point, and this
being money, that will be a problem.
if not possible for you to do it, hound the person who can do it :)
usamaalam wrote:
> Hello everybody,
> I have a float type field in my sql server table and I want to select the
> data in the format $XX,XXX.XX.
> Any ideas '
> Thanks.|||On Fri, 23 Dec 2005 08:17:02 -0800, "ML" <ML@.discussions.microsoft.com> wrot
e:
in <61588BC5-7F5E-4BD4-A95E-7B6B24984F3A@.microsoft.com>
>Maybe you should consider using XML as a transport medium, instead of CSV.
>After all, this is the 21st century. :)
I don't understand that sort of reasoning. What makes a proprietary schema
and
bloated data better than the CSV format?
Stefan Berglund
I have a float type field in my sql server table and I want to select the
data in the format $XX,XXX.XX.
Any ideas '
Thanks.usamaalam wrote:
> Hello everybody,
> I have a float type field in my sql server table and I want to select the
> data in the format $XX,XXX.XX.
> Any ideas '
> Thanks.
Your client application controls how data is formatted for display, not
SQL Server. If you want to do that in SQL you'll have to return a
string rather than a number. I don't recommend it, but you can take a
look at the CONVERT function in Books Online.
Why do you use FLOATs for monetary amounts? FLOAT is an inexact numeric
so is an unusual and probably unwise choice for accounting. More
typically, I would suggest you should use NUMERIC/DECIMAL for this.
Hope this helps.
David Portas
SQL Server MVP
--|||I need select float in the specified format and return the formatted string.
Thanks.
"David Portas" wrote:
> usamaalam wrote:
>
> Your client application controls how data is formatted for display, not
> SQL Server. If you want to do that in SQL you'll have to return a
> string rather than a number. I don't recommend it, but you can take a
> look at the CONVERT function in Books Online.
> Why do you use FLOATs for monetary amounts? FLOAT is an inexact numeric
> so is an unusual and probably unwise choice for accounting. More
> typically, I would suggest you should use NUMERIC/DECIMAL for this.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||As David says, you should do it in the application tier and not in the
database. If you are using .NET in your application tier, there are powerful
formatting capabilities.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"usamaalam" <usamaalam@.discussions.microsoft.com> wrote in message
news:8207FDF2-C83E-491A-AE06-FF91FD965E14@.microsoft.com...
>I need select float in the specified format and return the formatted
>string.
> Thanks.
> "David Portas" wrote:
>|||Basically the application mechanism has been developed by someone else. I
need to export data from some tables in CSV format. The mechanism executes
an stored procedure, get data in a data table and a method gets the data
table as a parameter and generates CSV whatever present in the data table.
This will be more helpful for me to do it in sql, if possible.
Thanks a lot.
"SriSamp" wrote:
> As David says, you should do it in the application tier and not in the
> database. If you are using .NET in your application tier, there are powerf
ul
> formatting capabilities.
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "usamaalam" <usamaalam@.discussions.microsoft.com> wrote in message
> news:8207FDF2-C83E-491A-AE06-FF91FD965E14@.microsoft.com...
>
>|||One method:
SELECT
'$' + CONVERT(varchar(20), CAST(MyFloatColumn AS money), 1)
FROM MyTable
I agree with the others that this is a kludge. This is a deficiency in the
application that ought to be addressed in the app code rather than
Transact-SQL.
Happy Holidays
Dan Guzman
SQL Server MVP
"usamaalam" <usamaalam@.discussions.microsoft.com> wrote in message
news:874B2D06-CA54-4852-AB97-4EB07762DA84@.microsoft.com...
> Basically the application mechanism has been developed by someone else. I
> need to export data from some tables in CSV format. The mechanism
> executes
> an stored procedure, get data in a data table and a method gets the data
> table as a parameter and generates CSV whatever present in the data table.
> This will be more helpful for me to do it in sql, if possible.
> Thanks a lot.
> "SriSamp" wrote:
>|||i've needed to do this myself a number of times, format the output on the
SELECT because i'm using BCP to plonk the data into an output file ready for
FTP'ing off to another server somewhere for another department/company to
use.
I'm not going to go to the effort of building a .NET application just to do
this; far simpler and quicker to format on the SELECT and output using BCP -
less components, less complexity.
There are times in reporting too where we need to do this because it would
be more difficult to do it in the reporting tool, for instance reporting
services; consider a trading report where trade amounts might be in
different currency, two ways of doing it - either concatenate in the front
end which might be more difficult or just simply do it easily on the SELECT.
You need to consider what you are doing before definitively saying do it in
the front end or in the db.
Tony
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1135337208.989446.250540@.g43g2000cwa.googlegroups.com...
> usamaalam wrote:
>
> Your client application controls how data is formatted for display, not
> SQL Server. If you want to do that in SQL you'll have to return a
> string rather than a number. I don't recommend it, but you can take a
> look at the CONVERT function in Books Online.
> Why do you use FLOATs for monetary amounts? FLOAT is an inexact numeric
> so is an unusual and probably unwise choice for accounting. More
> typically, I would suggest you should use NUMERIC/DECIMAL for this.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||Maybe you should consider using XML as a transport medium, instead of CSV.
After all, this is the 21st century. :)
If the data is being used in an application at the other end then I really
see no point in formatting it, unless the actual data type is ignored at the
other end, which I doubt to be true.
ML
http://milambda.blogspot.com/|||i just want to reiterate what David said: if at all possible, change the
float to another type - you *will* get bad data at some point, and this
being money, that will be a problem.
if not possible for you to do it, hound the person who can do it :)
usamaalam wrote:
> Hello everybody,
> I have a float type field in my sql server table and I want to select the
> data in the format $XX,XXX.XX.
> Any ideas '
> Thanks.|||On Fri, 23 Dec 2005 08:17:02 -0800, "ML" <ML@.discussions.microsoft.com> wrot
e:
in <61588BC5-7F5E-4BD4-A95E-7B6B24984F3A@.microsoft.com>
>Maybe you should consider using XML as a transport medium, instead of CSV.
>After all, this is the 21st century. :)
I don't understand that sort of reasoning. What makes a proprietary schema
and
bloated data better than the CSV format?
Stefan Berglund
Thursday, March 8, 2012
custom identity field
I have what I thought would be a simple task but I keep hitting dead ends.
My database has many tables, each with an identity column as the primary
key. This worked fine until we had a requirement to import rows from other
sites/database installs (same tables, different servers). We won't have
very many databases, but the table keys must be unique to the database and
the sites. Since our users refer to the rows by ID, GUIDs are far too
awkward and will not work. The key needs to work much like the identity
field, easy to query the last key added and the key is generated
automagically.
The easy solution would be to just add the site code (3 letter alpha) to the
keys. We had hoped to just create a simple function that would return the
key and we could set the default value of the key to point to the function.
For example, the table User would have a primary key of UserID with a
default value of getKey('user') which would return 'AAA1' for the first user
entered in site 'AAA'. If the first user from site 'BBB' was imported, it
would be simple to synchronize and determine at a glance as we would have a
user 'BBB1'.
After playing around with functions, procs, default values, creating system
functions, formulas, triggers, we have not found a way to do what we want.
Anybody have any advice on this? Is there a better way?
Don't use IDENTITY keys to maintain integrity between databases - it's a
waste of time. Use alternate keys for that. The only sensible use of an
IDENTITY key is as a SURROGATE so just assign new IDENTITY keys (parent and
foreign keys) when you import the data.
David Portas
SQL Server MVP
|||>> "Don't use IDENTITY keys...Use alternate keys..."
That is exactly my question. I stated that I cannot use IDENTITY keys and
want to create my own alternate key. How can I generate an alternate key
that can, as seemlessly as possible, replace the IDENTITY key? I've tried
to create system functions, triggers, and procs but cannot seem to find a
mechanism whereby I can autogenerate a default value for the row based on a
procedure call.
|||I don't see a problem. Your tables on the source systems should already
have alternate keys because IDENTITY should never be the only key of a
table. So if you need to preserve potential duplicates between the two
systems just add another column to make up a compound key. The
additional column identifies the source as "A", "B", "C" or whatever.
Why go to the trouble of putting it into one single column? You can
always concatenate the key as one in a view if you need to.
David Portas
SQL Server MVP
|||An example is worth a thousand words so here's how I would do it.
The two sites, A and B:
CREATE TABLE A_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE A_bar (x INTEGER NOT NULL REFERENCES A_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
CREATE TABLE B_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE B_bar (x INTEGER NOT NULL REFERENCES B_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
Generate some sample data:
INSERT INTO A_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO A_bar (x,k)
SELECT 1,'XXX' UNION ALL
SELECT 1,'YYY' UNION ALL
SELECT 2,'XXX' UNION ALL
SELECT 2,'ZZZ'
INSERT INTO B_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO B_bar (x,k)
SELECT 1,'111' UNION ALL
SELECT 1,'222' UNION ALL
SELECT 2,'111' UNION ALL
SELECT 2,'333'
These are the two tables for the merged data:
CREATE TABLE foo (x INTEGER IDENTITY PRIMARY KEY, source CHAR(1) NOT
NULL, z CHAR(10) NOT NULL, UNIQUE (source,z))
CREATE TABLE bar (x INTEGER NOT NULL REFERENCES foo (x), k CHAR(10) NOT
NULL, PRIMARY KEY (x,k))
Now do the merge:
INSERT INTO foo (source, z)
SELECT 'A', z
FROM A_foo
UNION ALL
SELECT 'B', z
FROM B_foo
INSERT INTO bar (x,k)
SELECT foo.x, A_bar.k
FROM A_bar
JOIN A_foo
ON A_foo.x = A_bar.x
JOIN foo
ON A_foo.z = foo.z
AND foo.source = 'A'
UNION ALL
SELECT foo.x, B_bar.k
FROM B_bar
JOIN B_foo
ON B_foo.x = B_bar.x
JOIN foo
ON B_foo.z = foo.z
AND foo.source = 'B'
You'll probably want to add a WHERE NOT EXISTS condition to the INSERTs
to ensure that only new data gets loaded.
David Portas
SQL Server MVP
My database has many tables, each with an identity column as the primary
key. This worked fine until we had a requirement to import rows from other
sites/database installs (same tables, different servers). We won't have
very many databases, but the table keys must be unique to the database and
the sites. Since our users refer to the rows by ID, GUIDs are far too
awkward and will not work. The key needs to work much like the identity
field, easy to query the last key added and the key is generated
automagically.
The easy solution would be to just add the site code (3 letter alpha) to the
keys. We had hoped to just create a simple function that would return the
key and we could set the default value of the key to point to the function.
For example, the table User would have a primary key of UserID with a
default value of getKey('user') which would return 'AAA1' for the first user
entered in site 'AAA'. If the first user from site 'BBB' was imported, it
would be simple to synchronize and determine at a glance as we would have a
user 'BBB1'.
After playing around with functions, procs, default values, creating system
functions, formulas, triggers, we have not found a way to do what we want.
Anybody have any advice on this? Is there a better way?
Don't use IDENTITY keys to maintain integrity between databases - it's a
waste of time. Use alternate keys for that. The only sensible use of an
IDENTITY key is as a SURROGATE so just assign new IDENTITY keys (parent and
foreign keys) when you import the data.
David Portas
SQL Server MVP
|||>> "Don't use IDENTITY keys...Use alternate keys..."
That is exactly my question. I stated that I cannot use IDENTITY keys and
want to create my own alternate key. How can I generate an alternate key
that can, as seemlessly as possible, replace the IDENTITY key? I've tried
to create system functions, triggers, and procs but cannot seem to find a
mechanism whereby I can autogenerate a default value for the row based on a
procedure call.
|||I don't see a problem. Your tables on the source systems should already
have alternate keys because IDENTITY should never be the only key of a
table. So if you need to preserve potential duplicates between the two
systems just add another column to make up a compound key. The
additional column identifies the source as "A", "B", "C" or whatever.
Why go to the trouble of putting it into one single column? You can
always concatenate the key as one in a view if you need to.
David Portas
SQL Server MVP
|||An example is worth a thousand words so here's how I would do it.
The two sites, A and B:
CREATE TABLE A_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE A_bar (x INTEGER NOT NULL REFERENCES A_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
CREATE TABLE B_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE B_bar (x INTEGER NOT NULL REFERENCES B_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
Generate some sample data:
INSERT INTO A_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO A_bar (x,k)
SELECT 1,'XXX' UNION ALL
SELECT 1,'YYY' UNION ALL
SELECT 2,'XXX' UNION ALL
SELECT 2,'ZZZ'
INSERT INTO B_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO B_bar (x,k)
SELECT 1,'111' UNION ALL
SELECT 1,'222' UNION ALL
SELECT 2,'111' UNION ALL
SELECT 2,'333'
These are the two tables for the merged data:
CREATE TABLE foo (x INTEGER IDENTITY PRIMARY KEY, source CHAR(1) NOT
NULL, z CHAR(10) NOT NULL, UNIQUE (source,z))
CREATE TABLE bar (x INTEGER NOT NULL REFERENCES foo (x), k CHAR(10) NOT
NULL, PRIMARY KEY (x,k))
Now do the merge:
INSERT INTO foo (source, z)
SELECT 'A', z
FROM A_foo
UNION ALL
SELECT 'B', z
FROM B_foo
INSERT INTO bar (x,k)
SELECT foo.x, A_bar.k
FROM A_bar
JOIN A_foo
ON A_foo.x = A_bar.x
JOIN foo
ON A_foo.z = foo.z
AND foo.source = 'A'
UNION ALL
SELECT foo.x, B_bar.k
FROM B_bar
JOIN B_foo
ON B_foo.x = B_bar.x
JOIN foo
ON B_foo.z = foo.z
AND foo.source = 'B'
You'll probably want to add a WHERE NOT EXISTS condition to the INSERTs
to ensure that only new data gets loaded.
David Portas
SQL Server MVP
custom identity field
I have what I thought would be a simple task but I keep hitting dead ends.
My database has many tables, each with an identity column as the primary
key. This worked fine until we had a requirement to import rows from other
sites/database installs (same tables, different servers). We won't have
very many databases, but the table keys must be unique to the database and
the sites. Since our users refer to the rows by ID, GUIDs are far too
awkward and will not work. The key needs to work much like the identity
field, easy to query the last key added and the key is generated
automagically.
The easy solution would be to just add the site code (3 letter alpha) to the
keys. We had hoped to just create a simple function that would return the
key and we could set the default value of the key to point to the function.
For example, the table User would have a primary key of UserID with a
default value of getKey('user') which would return 'AAA1' for the first user
entered in site 'AAA'. If the first user from site 'BBB' was imported, it
would be simple to synchronize and determine at a glance as we would have a
user 'BBB1'.
After playing around with functions, procs, default values, creating system
functions, formulas, triggers, we have not found a way to do what we want.
Anybody have any advice on this? Is there a better way?Don't use IDENTITY keys to maintain integrity between databases - it's a
waste of time. Use alternate keys for that. The only sensible use of an
IDENTITY key is as a SURROGATE so just assign new IDENTITY keys (parent and
foreign keys) when you import the data.
David Portas
SQL Server MVP
--|||>> "Don't use IDENTITY keys...Use alternate keys..."
That is exactly my question. I stated that I cannot use IDENTITY keys and
want to create my own alternate key. How can I generate an alternate key
that can, as seemlessly as possible, replace the IDENTITY key? I've tried
to create system functions, triggers, and procs but cannot seem to find a
mechanism whereby I can autogenerate a default value for the row based on a
procedure call.|||I don't see a problem. Your tables on the source systems should already
have alternate keys because IDENTITY should never be the only key of a
table. So if you need to preserve potential duplicates between the two
systems just add another column to make up a compound key. The
additional column identifies the source as "A", "B", "C" or whatever.
Why go to the trouble of putting it into one single column? You can
always concatenate the key as one in a view if you need to.
David Portas
SQL Server MVP
--|||An example is worth a thousand words so here's how I would do it.
The two sites, A and B:
CREATE TABLE A_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE A_bar (x INTEGER NOT NULL REFERENCES A_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
CREATE TABLE B_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE B_bar (x INTEGER NOT NULL REFERENCES B_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
Generate some sample data:
INSERT INTO A_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO A_bar (x,k)
SELECT 1,'XXX' UNION ALL
SELECT 1,'YYY' UNION ALL
SELECT 2,'XXX' UNION ALL
SELECT 2,'ZZZ'
INSERT INTO B_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO B_bar (x,k)
SELECT 1,'111' UNION ALL
SELECT 1,'222' UNION ALL
SELECT 2,'111' UNION ALL
SELECT 2,'333'
These are the two tables for the merged data:
CREATE TABLE foo (x INTEGER IDENTITY PRIMARY KEY, source CHAR(1) NOT
NULL, z CHAR(10) NOT NULL, UNIQUE (source,z))
CREATE TABLE bar (x INTEGER NOT NULL REFERENCES foo (x), k CHAR(10) NOT
NULL, PRIMARY KEY (x,k))
Now do the merge:
INSERT INTO foo (source, z)
SELECT 'A', z
FROM A_foo
UNION ALL
SELECT 'B', z
FROM B_foo
INSERT INTO bar (x,k)
SELECT foo.x, A_bar.k
FROM A_bar
JOIN A_foo
ON A_foo.x = A_bar.x
JOIN foo
ON A_foo.z = foo.z
AND foo.source = 'A'
UNION ALL
SELECT foo.x, B_bar.k
FROM B_bar
JOIN B_foo
ON B_foo.x = B_bar.x
JOIN foo
ON B_foo.z = foo.z
AND foo.source = 'B'
You'll probably want to add a WHERE NOT EXISTS condition to the INSERTs
to ensure that only new data gets loaded.
David Portas
SQL Server MVP
--
My database has many tables, each with an identity column as the primary
key. This worked fine until we had a requirement to import rows from other
sites/database installs (same tables, different servers). We won't have
very many databases, but the table keys must be unique to the database and
the sites. Since our users refer to the rows by ID, GUIDs are far too
awkward and will not work. The key needs to work much like the identity
field, easy to query the last key added and the key is generated
automagically.
The easy solution would be to just add the site code (3 letter alpha) to the
keys. We had hoped to just create a simple function that would return the
key and we could set the default value of the key to point to the function.
For example, the table User would have a primary key of UserID with a
default value of getKey('user') which would return 'AAA1' for the first user
entered in site 'AAA'. If the first user from site 'BBB' was imported, it
would be simple to synchronize and determine at a glance as we would have a
user 'BBB1'.
After playing around with functions, procs, default values, creating system
functions, formulas, triggers, we have not found a way to do what we want.
Anybody have any advice on this? Is there a better way?Don't use IDENTITY keys to maintain integrity between databases - it's a
waste of time. Use alternate keys for that. The only sensible use of an
IDENTITY key is as a SURROGATE so just assign new IDENTITY keys (parent and
foreign keys) when you import the data.
David Portas
SQL Server MVP
--|||>> "Don't use IDENTITY keys...Use alternate keys..."
That is exactly my question. I stated that I cannot use IDENTITY keys and
want to create my own alternate key. How can I generate an alternate key
that can, as seemlessly as possible, replace the IDENTITY key? I've tried
to create system functions, triggers, and procs but cannot seem to find a
mechanism whereby I can autogenerate a default value for the row based on a
procedure call.|||I don't see a problem. Your tables on the source systems should already
have alternate keys because IDENTITY should never be the only key of a
table. So if you need to preserve potential duplicates between the two
systems just add another column to make up a compound key. The
additional column identifies the source as "A", "B", "C" or whatever.
Why go to the trouble of putting it into one single column? You can
always concatenate the key as one in a view if you need to.
David Portas
SQL Server MVP
--|||An example is worth a thousand words so here's how I would do it.
The two sites, A and B:
CREATE TABLE A_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE A_bar (x INTEGER NOT NULL REFERENCES A_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
CREATE TABLE B_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE B_bar (x INTEGER NOT NULL REFERENCES B_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
Generate some sample data:
INSERT INTO A_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO A_bar (x,k)
SELECT 1,'XXX' UNION ALL
SELECT 1,'YYY' UNION ALL
SELECT 2,'XXX' UNION ALL
SELECT 2,'ZZZ'
INSERT INTO B_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO B_bar (x,k)
SELECT 1,'111' UNION ALL
SELECT 1,'222' UNION ALL
SELECT 2,'111' UNION ALL
SELECT 2,'333'
These are the two tables for the merged data:
CREATE TABLE foo (x INTEGER IDENTITY PRIMARY KEY, source CHAR(1) NOT
NULL, z CHAR(10) NOT NULL, UNIQUE (source,z))
CREATE TABLE bar (x INTEGER NOT NULL REFERENCES foo (x), k CHAR(10) NOT
NULL, PRIMARY KEY (x,k))
Now do the merge:
INSERT INTO foo (source, z)
SELECT 'A', z
FROM A_foo
UNION ALL
SELECT 'B', z
FROM B_foo
INSERT INTO bar (x,k)
SELECT foo.x, A_bar.k
FROM A_bar
JOIN A_foo
ON A_foo.x = A_bar.x
JOIN foo
ON A_foo.z = foo.z
AND foo.source = 'A'
UNION ALL
SELECT foo.x, B_bar.k
FROM B_bar
JOIN B_foo
ON B_foo.x = B_bar.x
JOIN foo
ON B_foo.z = foo.z
AND foo.source = 'B'
You'll probably want to add a WHERE NOT EXISTS condition to the INSERTs
to ensure that only new data gets loaded.
David Portas
SQL Server MVP
--
custom identity field
I have what I thought would be a simple task but I keep hitting dead ends.
My database has many tables, each with an identity column as the primary
key. This worked fine until we had a requirement to import rows from other
sites/database installs (same tables, different servers). We won't have
very many databases, but the table keys must be unique to the database and
the sites. Since our users refer to the rows by ID, GUIDs are far too
awkward and will not work. The key needs to work much like the identity
field, easy to query the last key added and the key is generated
automagically.
The easy solution would be to just add the site code (3 letter alpha) to the
keys. We had hoped to just create a simple function that would return the
key and we could set the default value of the key to point to the function.
For example, the table User would have a primary key of UserID with a
default value of getKey('user') which would return 'AAA1' for the first user
entered in site 'AAA'. If the first user from site 'BBB' was imported, it
would be simple to synchronize and determine at a glance as we would have a
user 'BBB1'.
After playing around with functions, procs, default values, creating system
functions, formulas, triggers, we have not found a way to do what we want.
Anybody have any advice on this? Is there a better way?Don't use IDENTITY keys to maintain integrity between databases - it's a
waste of time. Use alternate keys for that. The only sensible use of an
IDENTITY key is as a SURROGATE so just assign new IDENTITY keys (parent and
foreign keys) when you import the data.
--
David Portas
SQL Server MVP
--|||>> "Don't use IDENTITY keys...Use alternate keys..."
That is exactly my question. I stated that I cannot use IDENTITY keys and
want to create my own alternate key. How can I generate an alternate key
that can, as seemlessly as possible, replace the IDENTITY key? I've tried
to create system functions, triggers, and procs but cannot seem to find a
mechanism whereby I can autogenerate a default value for the row based on a
procedure call.|||I don't see a problem. Your tables on the source systems should already
have alternate keys because IDENTITY should never be the only key of a
table. So if you need to preserve potential duplicates between the two
systems just add another column to make up a compound key. The
additional column identifies the source as "A", "B", "C" or whatever.
Why go to the trouble of putting it into one single column? You can
always concatenate the key as one in a view if you need to.
--
David Portas
SQL Server MVP
--|||An example is worth a thousand words so here's how I would do it.
The two sites, A and B:
CREATE TABLE A_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE A_bar (x INTEGER NOT NULL REFERENCES A_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
CREATE TABLE B_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE B_bar (x INTEGER NOT NULL REFERENCES B_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
Generate some sample data:
INSERT INTO A_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO A_bar (x,k)
SELECT 1,'XXX' UNION ALL
SELECT 1,'YYY' UNION ALL
SELECT 2,'XXX' UNION ALL
SELECT 2,'ZZZ'
INSERT INTO B_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO B_bar (x,k)
SELECT 1,'111' UNION ALL
SELECT 1,'222' UNION ALL
SELECT 2,'111' UNION ALL
SELECT 2,'333'
These are the two tables for the merged data:
CREATE TABLE foo (x INTEGER IDENTITY PRIMARY KEY, source CHAR(1) NOT
NULL, z CHAR(10) NOT NULL, UNIQUE (source,z))
CREATE TABLE bar (x INTEGER NOT NULL REFERENCES foo (x), k CHAR(10) NOT
NULL, PRIMARY KEY (x,k))
Now do the merge:
INSERT INTO foo (source, z)
SELECT 'A', z
FROM A_foo
UNION ALL
SELECT 'B', z
FROM B_foo
INSERT INTO bar (x,k)
SELECT foo.x, A_bar.k
FROM A_bar
JOIN A_foo
ON A_foo.x = A_bar.x
JOIN foo
ON A_foo.z = foo.z
AND foo.source = 'A'
UNION ALL
SELECT foo.x, B_bar.k
FROM B_bar
JOIN B_foo
ON B_foo.x = B_bar.x
JOIN foo
ON B_foo.z = foo.z
AND foo.source = 'B'
You'll probably want to add a WHERE NOT EXISTS condition to the INSERTs
to ensure that only new data gets loaded.
--
David Portas
SQL Server MVP
--
My database has many tables, each with an identity column as the primary
key. This worked fine until we had a requirement to import rows from other
sites/database installs (same tables, different servers). We won't have
very many databases, but the table keys must be unique to the database and
the sites. Since our users refer to the rows by ID, GUIDs are far too
awkward and will not work. The key needs to work much like the identity
field, easy to query the last key added and the key is generated
automagically.
The easy solution would be to just add the site code (3 letter alpha) to the
keys. We had hoped to just create a simple function that would return the
key and we could set the default value of the key to point to the function.
For example, the table User would have a primary key of UserID with a
default value of getKey('user') which would return 'AAA1' for the first user
entered in site 'AAA'. If the first user from site 'BBB' was imported, it
would be simple to synchronize and determine at a glance as we would have a
user 'BBB1'.
After playing around with functions, procs, default values, creating system
functions, formulas, triggers, we have not found a way to do what we want.
Anybody have any advice on this? Is there a better way?Don't use IDENTITY keys to maintain integrity between databases - it's a
waste of time. Use alternate keys for that. The only sensible use of an
IDENTITY key is as a SURROGATE so just assign new IDENTITY keys (parent and
foreign keys) when you import the data.
--
David Portas
SQL Server MVP
--|||>> "Don't use IDENTITY keys...Use alternate keys..."
That is exactly my question. I stated that I cannot use IDENTITY keys and
want to create my own alternate key. How can I generate an alternate key
that can, as seemlessly as possible, replace the IDENTITY key? I've tried
to create system functions, triggers, and procs but cannot seem to find a
mechanism whereby I can autogenerate a default value for the row based on a
procedure call.|||I don't see a problem. Your tables on the source systems should already
have alternate keys because IDENTITY should never be the only key of a
table. So if you need to preserve potential duplicates between the two
systems just add another column to make up a compound key. The
additional column identifies the source as "A", "B", "C" or whatever.
Why go to the trouble of putting it into one single column? You can
always concatenate the key as one in a view if you need to.
--
David Portas
SQL Server MVP
--|||An example is worth a thousand words so here's how I would do it.
The two sites, A and B:
CREATE TABLE A_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE A_bar (x INTEGER NOT NULL REFERENCES A_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
CREATE TABLE B_foo (x INTEGER IDENTITY PRIMARY KEY, z CHAR(10) NOT NULL
UNIQUE)
CREATE TABLE B_bar (x INTEGER NOT NULL REFERENCES B_foo (x), k CHAR(10)
NOT NULL, PRIMARY KEY (x,k))
Generate some sample data:
INSERT INTO A_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO A_bar (x,k)
SELECT 1,'XXX' UNION ALL
SELECT 1,'YYY' UNION ALL
SELECT 2,'XXX' UNION ALL
SELECT 2,'ZZZ'
INSERT INTO B_foo (z)
SELECT 'Alpha' UNION ALL
SELECT 'Beta'
INSERT INTO B_bar (x,k)
SELECT 1,'111' UNION ALL
SELECT 1,'222' UNION ALL
SELECT 2,'111' UNION ALL
SELECT 2,'333'
These are the two tables for the merged data:
CREATE TABLE foo (x INTEGER IDENTITY PRIMARY KEY, source CHAR(1) NOT
NULL, z CHAR(10) NOT NULL, UNIQUE (source,z))
CREATE TABLE bar (x INTEGER NOT NULL REFERENCES foo (x), k CHAR(10) NOT
NULL, PRIMARY KEY (x,k))
Now do the merge:
INSERT INTO foo (source, z)
SELECT 'A', z
FROM A_foo
UNION ALL
SELECT 'B', z
FROM B_foo
INSERT INTO bar (x,k)
SELECT foo.x, A_bar.k
FROM A_bar
JOIN A_foo
ON A_foo.x = A_bar.x
JOIN foo
ON A_foo.z = foo.z
AND foo.source = 'A'
UNION ALL
SELECT foo.x, B_bar.k
FROM B_bar
JOIN B_foo
ON B_foo.x = B_bar.x
JOIN foo
ON B_foo.z = foo.z
AND foo.source = 'B'
You'll probably want to add a WHERE NOT EXISTS condition to the INSERTs
to ensure that only new data gets loaded.
--
David Portas
SQL Server MVP
--
Custom Grouping Problem
I am having some trouble with grouping. I have a custom function that returns the field to group on depending on the parameters provided by the user. This is not working, however if I select the same value from the grouping drop down list that is returned by the custom function the grouping works. My custom funtion is as follows:
Function OrganisationGroup() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!EnterpriseOption.Value
Case "1"
Group = Group1Value()
Case "2"
Group = "DepartmentDesc"
Case "3"
Group = "OrganizationDesc"
Case "4"
Group = "OrganizationDesc"
End Select
If Group = "" Then
Return "0"
Else
Return "Fields!" & Group & ".Value"
End If
End Function
Function Group1Value() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!ReportType.Value
Case "1"
Group = "StockFamilyDesc"
Case "2"
Group = "GroupDesc"
Case "3"
Group = "Saleable"
Case "4"
Group = "Saleable"
Case "5"
Group = "SaleableGLCodeDesc"
Case "6"
Group = "Saleable"
End Select
Return Group
End Function
Does anyone have any ideas on why the grouping would not be working with the custom function?
Thanks in advance.
I have solved the problem myself. The following changes to the custom code fixed the problem.
Function Group1Value() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!ReportType.Value
Case "1" Group = "StockFamilyDesc"
Case "2" Group = "GroupDesc"
Case "3" Group = "Saleable"
Case "4" Group = "Saleable"
Case "5" Group = "SaleableGLCodeDesc"
Case "6" Group = "Saleable"
End Select
Return Group
End Function
Now is ...
Function Group1Value(fields As Fields) As String
Dim Group As String
Group = ""
Select Case Report.Parameters!ReportType.Value
Case "1" Group = fields!StockFamilyDesc.Value
Case "2" Group = fields!GroupDesc.Value
Case "3" Group = fields!Saleable.Value
Case "4" Group = fields!Saleable.Value
Case "5" Group = fields!SaleableGLCodeDesc.Value
Case "6" Group = fields!Saleable.Value
End Select
Return Group
End Function
Similar changes in Function OrganisationGroup()
Custom Grouping Problem
I am having some trouble with grouping. I have a custom function that returns the field to group on depending on the parameters provided by the user. This is not working, however if I select the same value from the grouping drop down list that is returned by the custom function the grouping works. My custom funtion is as follows:
Function OrganisationGroup() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!EnterpriseOption.Value
Case "1"
Group = Group1Value()
Case "2"
Group = "DepartmentDesc"
Case "3"
Group = "OrganizationDesc"
Case "4"
Group = "OrganizationDesc"
End Select
If Group = "" Then
Return "0"
Else
Return "Fields!" & Group & ".Value"
End If
End Function
Function Group1Value() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!ReportType.Value
Case "1"
Group = "StockFamilyDesc"
Case "2"
Group = "GroupDesc"
Case "3"
Group = "Saleable"
Case "4"
Group = "Saleable"
Case "5"
Group = "SaleableGLCodeDesc"
Case "6"
Group = "Saleable"
End Select
Return Group
End Function
Does anyone have any ideas on why the grouping would not be working with the custom function?
Thanks in advance.
I have solved the problem myself. The following changes to the custom code fixed the problem.
Function Group1Value() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!ReportType.Value
Case "1" Group = "StockFamilyDesc"
Case "2" Group = "GroupDesc"
Case "3" Group = "Saleable"
Case "4" Group = "Saleable"
Case "5" Group = "SaleableGLCodeDesc"
Case "6" Group = "Saleable"
End Select
Return Group
End Function
Now is ...
Function Group1Value(fields As Fields) As String
Dim Group As String
Group = ""
Select Case Report.Parameters!ReportType.Value
Case "1" Group = fields!StockFamilyDesc.Value
Case "2" Group = fields!GroupDesc.Value
Case "3" Group = fields!Saleable.Value
Case "4" Group = fields!Saleable.Value
Case "5" Group = fields!SaleableGLCodeDesc.Value
Case "6" Group = fields!Saleable.Value
End Select
Return Group
End Function
Similar changes in Function OrganisationGroup()
Custom fields in SSAS 2005 KPI's
Hi there,
What is the best way to get a custom field in a SSAS 2005 Cube KPI?
For example, what if the user wanted two target fields, or a budget field, or a contact person field for a KPI? (mostly static data, hard coded by the user).
Thanks.
You can use the Annotations property of KPIs (and other AS 2005 objects) to store custom information. The annotations is a property bag of name-value pairs and can contain most anything you like (including complex XML). If you set Visibility=SchemaRowset on an annotation, then the annotation will be available in the schema rowset and can be used by client applications. The down side to this approach is that off-the-shelf clients will generally ignore annotations and you'll probably need a custom client application to look for and use the annotations.
Friday, February 24, 2012
Custom columns names for custom reports?
Our application allows the customer to change the on-screen field names to his personal requirements (eg Target = 2008 Target). My customer wants his on-screen field names to be reflected the metadata view so he can write reports with his change field names. We have several customers with this application so the field names will change by customer.
How do you do this, can SQL/Server support 2 field names - the original and a preferred display option?
If we change the field names how do we ensure that existing (or future) reports from Reporting Services dynamically pick up the customers preferred field name and any existing (or inherited) reports will continue always run.Question moved to SQL Server Forum.
Custom code using case statement
My report contains a field which shows a '# of days on Hand' column. This
column is the result of a datediff calc. I am not familar with vb.net but I
need to add custom code logic so that if the value in '# of days on hand" is
null that it shows a hardcoded text like "inv" or "onhand". The rest of the
value are >= 0 and I would just want to show there values as-is. Can anyone
show me what the a sample of code would look like to do this and how I call
this in my report?
ANY HELP IS MUCH APPRECIATEDAn expression would work well in this situation
=iif(Fields!column1.Value < 0,"inv",Fields!column1.Value)
I haven't tested this but it should work. If the expression evaluates to
true, i.e. If the value of the field is less than zero, then the string
'inv' is returned, if false, the field's value is returned as-is.
Put this in your column and replace Fields!column1 with whatever your field
is called.
HTH
"stacey" wrote:
> My report contains a field which shows a '# of days on Hand' column. This
> column is the result of a datediff calc. I am not familar with vb.net but I
> need to add custom code logic so that if the value in '# of days on hand" is
> null that it shows a hardcoded text like "inv" or "onhand". The rest of the
> value are >= 0 and I would just want to show there values as-is. Can anyone
> show me what the a sample of code would look like to do this and how I call
> this in my report?
> ANY HELP IS MUCH APPRECIATED
column is the result of a datediff calc. I am not familar with vb.net but I
need to add custom code logic so that if the value in '# of days on hand" is
null that it shows a hardcoded text like "inv" or "onhand". The rest of the
value are >= 0 and I would just want to show there values as-is. Can anyone
show me what the a sample of code would look like to do this and how I call
this in my report?
ANY HELP IS MUCH APPRECIATEDAn expression would work well in this situation
=iif(Fields!column1.Value < 0,"inv",Fields!column1.Value)
I haven't tested this but it should work. If the expression evaluates to
true, i.e. If the value of the field is less than zero, then the string
'inv' is returned, if false, the field's value is returned as-is.
Put this in your column and replace Fields!column1 with whatever your field
is called.
HTH
"stacey" wrote:
> My report contains a field which shows a '# of days on Hand' column. This
> column is the result of a datediff calc. I am not familar with vb.net but I
> need to add custom code logic so that if the value in '# of days on hand" is
> null that it shows a hardcoded text like "inv" or "onhand". The rest of the
> value are >= 0 and I would just want to show there values as-is. Can anyone
> show me what the a sample of code would look like to do this and how I call
> this in my report?
> ANY HELP IS MUCH APPRECIATED
Friday, February 17, 2012
CUSTOM ASSEMBLY IN RDL - ISSUE - NEED YOUR HELP!
Hi All MSTR Mentor's
I have a RDL, where the stored procedure returns 10 fields among that 1
field is the creditcard# which is encrypted, I need to decryt the
creditcard # and display it in the report.
I use a .net custom aseembly which has a decrypt function and it is added
as a reference to the rdl and it is WORKING FINE when i view the report in
the PREVIEW TAB of the report designer.
When I deploy the report to my ReportServer of the localmachine and view
the report it is showing #Error on the field value.
What could be the problem, plese help me on this issue.
using System;
using System.Text;
using System.Collections.Specialized;
using System.Configuration;
using System.Security.Cryptography;
using FCLX509 = System.Security.Cryptography.X509Certificates;
using WSEX509 = Microsoft.Web.Services2.Security.X509;
using WSECRY = Microsoft.Web.Services2.Security.Cryptography;
namespace RDLCustomCode
{
public class DataDecryptionClass
{
public DataDecryptionClass()
{
}
public static string DecryptCardInfo(string cc,string subjectName,string
storeName)
{
try
{
string sCreditCard = "";
WSEX509.X509CertificateStore.StoreLocation location = WSEX509.X509CertificateStore.StoreLocation.CurrentUser;
WSEX509.X509CertificateStore.StoreProvider provider = WSEX509.X509CertificateStore.StoreProvider.System;
WSEX509.X509CertificateStore store = new WSEX509.X509CertificateStore
(provider, location, storeName);
bool fopen = store.OpenRead();
if(fopen)
{
WSEX509.X509CertificateCollection certs = store.FindCertificateBySubjectString(subjectName);
if (certs.Count > 0)
{
WSEX509.X509Certificate cer = certs[0];
WSECRY.RSACryptoServiceProvider rsaCsp = (WSECRY.RSACryptoServiceProvider)cer.Key;
byte[] cipherData = Convert.FromBase64String(cc);
byte[] plainData = rsaCsp.Decrypt(cipherData, false);
sCreditCard = Encoding.UTF8.GetString(plainData);
}
}
if (store != null)
store.Close();
return sCreditCard;
}
catch
{
return "";
}
}
--
Message posted via http://www.sqlmonster.comHave you copied custom assembly to the ReportServer bin directory (e.g. to
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin)?
"BALAJI KRISHNAN via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:05caacfe42a94a4c95e196c9e9cd672b@.SQLMonster.com...
> Hi All MSTR Mentor's
> I have a RDL, where the stored procedure returns 10 fields among that 1
> field is the creditcard# which is encrypted, I need to decryt the
> creditcard # and display it in the report.
> I use a .net custom aseembly which has a decrypt function and it is added
> as a reference to the rdl and it is WORKING FINE when i view the report in
> the PREVIEW TAB of the report designer.
> When I deploy the report to my ReportServer of the localmachine and view
> the report it is showing #Error on the field value.
> What could be the problem, plese help me on this issue.
>
>
>
>
>
>
> using System;
> using System.Text;
> using System.Collections.Specialized;
> using System.Configuration;
> using System.Security.Cryptography;
> using FCLX509 = System.Security.Cryptography.X509Certificates;
> using WSEX509 = Microsoft.Web.Services2.Security.X509;
> using WSECRY = Microsoft.Web.Services2.Security.Cryptography;
> namespace RDLCustomCode
> {
> public class DataDecryptionClass
> {
> public DataDecryptionClass()
> {
> }
> public static string DecryptCardInfo(string cc,string subjectName,string
> storeName)
> {
> try
> {
> string sCreditCard = "";
> WSEX509.X509CertificateStore.StoreLocation location => WSEX509.X509CertificateStore.StoreLocation.CurrentUser;
> WSEX509.X509CertificateStore.StoreProvider provider => WSEX509.X509CertificateStore.StoreProvider.System;
> WSEX509.X509CertificateStore store = new WSEX509.X509CertificateStore
> (provider, location, storeName);
> bool fopen = store.OpenRead();
> if(fopen)
> {
> WSEX509.X509CertificateCollection certs => store.FindCertificateBySubjectString(subjectName);
> if (certs.Count > 0)
> {
> WSEX509.X509Certificate cer = certs[0];
> WSECRY.RSACryptoServiceProvider rsaCsp => (WSECRY.RSACryptoServiceProvider)cer.Key;
> byte[] cipherData = Convert.FromBase64String(cc);
> byte[] plainData = rsaCsp.Decrypt(cipherData, false);
> sCreditCard = Encoding.UTF8.GetString(plainData);
> }
> }
> if (store != null)
> store.Close();
> return sCreditCard;
> }
> catch
> {
> return "";
> }
> }
> --
> Message posted via http://www.sqlmonster.com|||Dmitry Nechipor,
Yes, I have copied the dll in C:\Program Files\Microsoft SQL Server\MSSQL\
ReportingServices\ReportServer\bin.
But still the same result.
Do I need to set CAS to the code..if so how to give the permission.
Balaji
--
Message posted via http://www.sqlmonster.com|||Please give CAS permission in file:
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\rssrvpolicy.xml
See Online book with RS installation for XML entry for your DLL
"BALAJI KRISHNAN via SQLMonster.com" wrote:
> Hi All MSTR Mentor's
> I have a RDL, where the stored procedure returns 10 fields among that 1
> field is the creditcard# which is encrypted, I need to decryt the
> creditcard # and display it in the report.
> I use a .net custom aseembly which has a decrypt function and it is added
> as a reference to the rdl and it is WORKING FINE when i view the report in
> the PREVIEW TAB of the report designer.
> When I deploy the report to my ReportServer of the localmachine and view
> the report it is showing #Error on the field value.
> What could be the problem, plese help me on this issue.
>
>
>
>
>
>
> using System;
> using System.Text;
> using System.Collections.Specialized;
> using System.Configuration;
> using System.Security.Cryptography;
> using FCLX509 = System.Security.Cryptography.X509Certificates;
> using WSEX509 = Microsoft.Web.Services2.Security.X509;
> using WSECRY = Microsoft.Web.Services2.Security.Cryptography;
> namespace RDLCustomCode
> {
> public class DataDecryptionClass
> {
> public DataDecryptionClass()
> {
> }
> public static string DecryptCardInfo(string cc,string subjectName,string
> storeName)
> {
> try
> {
> string sCreditCard = "";
> WSEX509.X509CertificateStore.StoreLocation location => WSEX509.X509CertificateStore.StoreLocation.CurrentUser;
> WSEX509.X509CertificateStore.StoreProvider provider => WSEX509.X509CertificateStore.StoreProvider.System;
> WSEX509.X509CertificateStore store = new WSEX509.X509CertificateStore
> (provider, location, storeName);
> bool fopen = store.OpenRead();
> if(fopen)
> {
> WSEX509.X509CertificateCollection certs => store.FindCertificateBySubjectString(subjectName);
> if (certs.Count > 0)
> {
> WSEX509.X509Certificate cer = certs[0];
> WSECRY.RSACryptoServiceProvider rsaCsp => (WSECRY.RSACryptoServiceProvider)cer.Key;
> byte[] cipherData = Convert.FromBase64String(cc);
> byte[] plainData = rsaCsp.Decrypt(cipherData, false);
> sCreditCard = Encoding.UTF8.GetString(plainData);
> }
> }
> if (store != null)
> store.Close();
> return sCreditCard;
> }
> catch
> {
> return "";
> }
> }
> --
> Message posted via http://www.sqlmonster.com
>|||Hi Sunnet,
I have added the CAS PERMISSION by adding this code group to the
rssrvpolicy.config file on C:\Program Files\Microsoft SQL Server\MSSQL\
Reporting Services\ReportServer
Here is the code I have added to the config file
</CodeGroup>
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="XMLCodeGroup"
Description="Code group for my XML data processing extension">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\RDLCustomCode.dll" />
</CodeGroup>
But still I have the same problem.
Balaji
--
Message posted via http://www.sqlmonster.com|||Hi Sunnet,
C:\Program Files\Microsoft SQL Server\MSSQL\
Reporting Services\ReportServer
I have changed class="AllMembershipCondition" instead of
class="UrlMembershipCondition"
Now, I am not seeing the #Error, but still i could not see the value, it is
blank now...
What could be the problem
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="Report_Expressions_Default_Permissions"
Description="A special code group for my custom assembly.">
<IMembershipCondition
class="AllMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\RDLCustomCode.dll"
/>
--
Message posted via http://www.sqlmonster.com
I have a RDL, where the stored procedure returns 10 fields among that 1
field is the creditcard# which is encrypted, I need to decryt the
creditcard # and display it in the report.
I use a .net custom aseembly which has a decrypt function and it is added
as a reference to the rdl and it is WORKING FINE when i view the report in
the PREVIEW TAB of the report designer.
When I deploy the report to my ReportServer of the localmachine and view
the report it is showing #Error on the field value.
What could be the problem, plese help me on this issue.
using System;
using System.Text;
using System.Collections.Specialized;
using System.Configuration;
using System.Security.Cryptography;
using FCLX509 = System.Security.Cryptography.X509Certificates;
using WSEX509 = Microsoft.Web.Services2.Security.X509;
using WSECRY = Microsoft.Web.Services2.Security.Cryptography;
namespace RDLCustomCode
{
public class DataDecryptionClass
{
public DataDecryptionClass()
{
}
public static string DecryptCardInfo(string cc,string subjectName,string
storeName)
{
try
{
string sCreditCard = "";
WSEX509.X509CertificateStore.StoreLocation location = WSEX509.X509CertificateStore.StoreLocation.CurrentUser;
WSEX509.X509CertificateStore.StoreProvider provider = WSEX509.X509CertificateStore.StoreProvider.System;
WSEX509.X509CertificateStore store = new WSEX509.X509CertificateStore
(provider, location, storeName);
bool fopen = store.OpenRead();
if(fopen)
{
WSEX509.X509CertificateCollection certs = store.FindCertificateBySubjectString(subjectName);
if (certs.Count > 0)
{
WSEX509.X509Certificate cer = certs[0];
WSECRY.RSACryptoServiceProvider rsaCsp = (WSECRY.RSACryptoServiceProvider)cer.Key;
byte[] cipherData = Convert.FromBase64String(cc);
byte[] plainData = rsaCsp.Decrypt(cipherData, false);
sCreditCard = Encoding.UTF8.GetString(plainData);
}
}
if (store != null)
store.Close();
return sCreditCard;
}
catch
{
return "";
}
}
--
Message posted via http://www.sqlmonster.comHave you copied custom assembly to the ReportServer bin directory (e.g. to
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin)?
"BALAJI KRISHNAN via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:05caacfe42a94a4c95e196c9e9cd672b@.SQLMonster.com...
> Hi All MSTR Mentor's
> I have a RDL, where the stored procedure returns 10 fields among that 1
> field is the creditcard# which is encrypted, I need to decryt the
> creditcard # and display it in the report.
> I use a .net custom aseembly which has a decrypt function and it is added
> as a reference to the rdl and it is WORKING FINE when i view the report in
> the PREVIEW TAB of the report designer.
> When I deploy the report to my ReportServer of the localmachine and view
> the report it is showing #Error on the field value.
> What could be the problem, plese help me on this issue.
>
>
>
>
>
>
> using System;
> using System.Text;
> using System.Collections.Specialized;
> using System.Configuration;
> using System.Security.Cryptography;
> using FCLX509 = System.Security.Cryptography.X509Certificates;
> using WSEX509 = Microsoft.Web.Services2.Security.X509;
> using WSECRY = Microsoft.Web.Services2.Security.Cryptography;
> namespace RDLCustomCode
> {
> public class DataDecryptionClass
> {
> public DataDecryptionClass()
> {
> }
> public static string DecryptCardInfo(string cc,string subjectName,string
> storeName)
> {
> try
> {
> string sCreditCard = "";
> WSEX509.X509CertificateStore.StoreLocation location => WSEX509.X509CertificateStore.StoreLocation.CurrentUser;
> WSEX509.X509CertificateStore.StoreProvider provider => WSEX509.X509CertificateStore.StoreProvider.System;
> WSEX509.X509CertificateStore store = new WSEX509.X509CertificateStore
> (provider, location, storeName);
> bool fopen = store.OpenRead();
> if(fopen)
> {
> WSEX509.X509CertificateCollection certs => store.FindCertificateBySubjectString(subjectName);
> if (certs.Count > 0)
> {
> WSEX509.X509Certificate cer = certs[0];
> WSECRY.RSACryptoServiceProvider rsaCsp => (WSECRY.RSACryptoServiceProvider)cer.Key;
> byte[] cipherData = Convert.FromBase64String(cc);
> byte[] plainData = rsaCsp.Decrypt(cipherData, false);
> sCreditCard = Encoding.UTF8.GetString(plainData);
> }
> }
> if (store != null)
> store.Close();
> return sCreditCard;
> }
> catch
> {
> return "";
> }
> }
> --
> Message posted via http://www.sqlmonster.com|||Dmitry Nechipor,
Yes, I have copied the dll in C:\Program Files\Microsoft SQL Server\MSSQL\
ReportingServices\ReportServer\bin.
But still the same result.
Do I need to set CAS to the code..if so how to give the permission.
Balaji
--
Message posted via http://www.sqlmonster.com|||Please give CAS permission in file:
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\rssrvpolicy.xml
See Online book with RS installation for XML entry for your DLL
"BALAJI KRISHNAN via SQLMonster.com" wrote:
> Hi All MSTR Mentor's
> I have a RDL, where the stored procedure returns 10 fields among that 1
> field is the creditcard# which is encrypted, I need to decryt the
> creditcard # and display it in the report.
> I use a .net custom aseembly which has a decrypt function and it is added
> as a reference to the rdl and it is WORKING FINE when i view the report in
> the PREVIEW TAB of the report designer.
> When I deploy the report to my ReportServer of the localmachine and view
> the report it is showing #Error on the field value.
> What could be the problem, plese help me on this issue.
>
>
>
>
>
>
> using System;
> using System.Text;
> using System.Collections.Specialized;
> using System.Configuration;
> using System.Security.Cryptography;
> using FCLX509 = System.Security.Cryptography.X509Certificates;
> using WSEX509 = Microsoft.Web.Services2.Security.X509;
> using WSECRY = Microsoft.Web.Services2.Security.Cryptography;
> namespace RDLCustomCode
> {
> public class DataDecryptionClass
> {
> public DataDecryptionClass()
> {
> }
> public static string DecryptCardInfo(string cc,string subjectName,string
> storeName)
> {
> try
> {
> string sCreditCard = "";
> WSEX509.X509CertificateStore.StoreLocation location => WSEX509.X509CertificateStore.StoreLocation.CurrentUser;
> WSEX509.X509CertificateStore.StoreProvider provider => WSEX509.X509CertificateStore.StoreProvider.System;
> WSEX509.X509CertificateStore store = new WSEX509.X509CertificateStore
> (provider, location, storeName);
> bool fopen = store.OpenRead();
> if(fopen)
> {
> WSEX509.X509CertificateCollection certs => store.FindCertificateBySubjectString(subjectName);
> if (certs.Count > 0)
> {
> WSEX509.X509Certificate cer = certs[0];
> WSECRY.RSACryptoServiceProvider rsaCsp => (WSECRY.RSACryptoServiceProvider)cer.Key;
> byte[] cipherData = Convert.FromBase64String(cc);
> byte[] plainData = rsaCsp.Decrypt(cipherData, false);
> sCreditCard = Encoding.UTF8.GetString(plainData);
> }
> }
> if (store != null)
> store.Close();
> return sCreditCard;
> }
> catch
> {
> return "";
> }
> }
> --
> Message posted via http://www.sqlmonster.com
>|||Hi Sunnet,
I have added the CAS PERMISSION by adding this code group to the
rssrvpolicy.config file on C:\Program Files\Microsoft SQL Server\MSSQL\
Reporting Services\ReportServer
Here is the code I have added to the config file
</CodeGroup>
<CodeGroup class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="XMLCodeGroup"
Description="Code group for my XML data processing extension">
<IMembershipCondition class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\RDLCustomCode.dll" />
</CodeGroup>
But still I have the same problem.
Balaji
--
Message posted via http://www.sqlmonster.com|||Hi Sunnet,
C:\Program Files\Microsoft SQL Server\MSSQL\
Reporting Services\ReportServer
I have changed class="AllMembershipCondition" instead of
class="UrlMembershipCondition"
Now, I am not seeing the #Error, but still i could not see the value, it is
blank now...
What could be the problem
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="Report_Expressions_Default_Permissions"
Description="A special code group for my custom assembly.">
<IMembershipCondition
class="AllMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\RDLCustomCode.dll"
/>
--
Message posted via http://www.sqlmonster.com
Subscribe to:
Posts (Atom)