Customising Legends for chart

Hi to all,

A query about legends in a chart, is it possible to customise legends in chart? I have used formula fields as the value of a pie chart and apparently the legend shows "@." infront of the fieldname... I'd like to have my own legend name for reading friendliness.

Thanks.

-HenryAnd for your information I am using Crystal Report included with VS.net 2003.

Cheers.

-Henry|||i'm having the same problem..

anyone der to help?|||Hi Cynthia, it has been many years since I have post my question... But I think I don't have a solution. I believe a full version of Crystal Report allow renaming field name.|||ok. where can i get the full version of Crystal report? is it consider an additional component to integrate into visual studio?

custom(and dynamic) record fields query

Hello,
I need to create a query that depends on custom fields (virtual).
First I'll try to outline the background.
I got Nodes table, that has NodeID field, and other (not really important)
fields like name, some boolean fields and so on.
Lets define node as an item for sale (just for the example purposes).
User of the application can create custom attribute values for each each
item (node).
To make the attribute names consistant through application I got another
table Attributes (AttID, AttributeName)
And the last table is association table between Node and Attributes
containing the value of the attribute as well.
I'd like to get following resultset:
NodeID, NodeName, CustomField1, CustomField2, CustomField3, ... and so on
(names of the fields would depend on Attribute name)
with values:
1,FirstNode, 'one of the values', 'another value', NULL, ...
2,SecondNode, NULL, NULL, NULL , ...
3,ThirdNode, NULL, NULL, 'some value', ...
SQL for creation such tables would look like (I ommited constrains and
indexes for siplicity):
CREATE TABLE Nodes (
NodeID INT IDENTITY (1, 1) NOT NULL,
NodeName NVARCHAR(255) NULL
)
CREATE TABLE Attributes (
AttID INT IDENTITY (1, 1) NOT NULL,
AttributeName NVARCHAR(50) NOT NULL
)
CREATE TABLE NodeAttr (
NodeID INT NOT NULL,
AttID INT NOT NULL,
Value NVARCHAR(255)
)
some data insertion:
INSERT INTO Nodes(NodeName) VALUES ('FirstNode')
INSERT INTO Nodes(NodeName) VALUES ('SecondNode')
INSERT INTO Nodes(NodeName) VALUES ('ThirdNode')
INSERT INTO Nodes(NodeName) VALUES ('FourthNode')
INSERT INTO Nodes(NodeName) VALUES ('FifthNode')
INSERT INTO Attributes (AttributeName) VALUES ('CustomField1')
INSERT INTO Attributes (AttributeName) VALUES ('CustomField2')
INSERT INTO Attributes (AttributeName) VALUES ('CustomField3')
INSERT INTO NodeAttr (NodeID,AttID,Value) VALUES (1,1,'My Value for Field1')
INSERT INTO NodeAttr (NodeID,AttID,Value) VALUES (1,2,'My Value for Field2')
INSERT INTO NodeAttr (NodeID,AttID,Value) VALUES (1,3,'My Value for Field3')
INSERT INTO NodeAttr (NodeID,AttID,Value) VALUES (3,3,'My Value for Field3
in third node')
Second I describe my approach to the problem.
I created function that retrieves data based on nodeID and Attribute name:
CREATE FUNCTION myfun(@.NodeID INT, @.AttributeName NVARCHAR(50))
RETURNS NVARCHAR(255)
AS
BEGIN
DECLARE @.Value NVARCHAR(255)
SET @.Value=NULL
SELECT @.Value=Value FROM NodeAttr NA
INNER JOIN Attributes A ON (NA.AttID=A.AttID)
WHERE NodeID=@.NodeID AND AttributeName=@.AttributeName
RETURN @.Value
END
Next, I create dynamic SQL for data retrieval:
declare @.query NVARCHAR(4000)
SELECT
@.query=isnull(@.query+',','SELECT NodeID,
')+'dbo.myfun(NodeID,'''+AttributeName+''') AS ['+AttributeName+']'
FROM Attributes
SET @.query = @.query + ' FROM Nodes'
and execute it:
EXECUTE sp_executesql @.query
Dynamicly generated SQL looks like this:
SELECT NodeID,
dbo.myfun(NodeID,'CustomField1') AS [CustomField1],
dbo.myfun(NodeID,'CustomField2') AS [CustomField2],
dbo.myfun(NodeID,'CustomField3') AS [CustomField3]
FROM Nodes
It is great when you deal with small number of nodes. When node number is
about 2000 the perfomarnce is really bad (executes over a minute). And I'd
like to have even more.
Anyone can point me to better solution? :)
regards,
Pawel Rogozinski
Software Engineer at Eracent Inc.
pawel.rogozinski@.eracent.com>> I need to create a query that depends on custom fields (virtual). <<
This flawed approach is called an EAV design and newbies re-invent it
about once a month here. The basic fallacy that leads to meltdown in
about a year is that you are mixing data and metadata. Also, columns
are not fields and rows are not records -- totally different concepts.
Then give the users a copy of SQL and get out of the way. If any
random user is a better database programmers and designer than you are,
you should not be programming.
It is not just exponently slow; since it has no data integrity, it is
error-prone, too!!|||>> I'd like to get following resultset:
It is better to use a client programming language or a report writer to
generate such cross tabulations. In t-SQL, most general approaches are
clumsy. For some methods refer to: http://tinyurl.com/ayjwa
Anith|||This sounds like an EAV (Entity-Attribute-Value) design. One of it's biggest
failings is that reporting is significantly more difficult and slow. Another
divanatage is that it requires dynamic SQL and all of its issues. Thus, y
ou
should be aware that the difficulties you are having in finding a solution a
re
directly related to the design.
That said and assuming you wanted to continue with this design, one solution
that takes the dynamic SQL out of the equation would be to make the equivale
nt
of a materialized view. In essence, create a permanent temp table where each
"attribute" in your design is a column in this temp table. You would then
populate and maintain this table with every entity's attribute values. To do
that you can use a massive case statement like so:
Select NA.NodeId
, Min(Case When NA.AttId = 1 Then NA.Value End) As Attribute1Name
, Min(Case When NA.AttId = 2 Then NA.Value End) As Attribute2Name
...
From NodeAttr As NA
Group By NA.NodeId
In order to maitain the contents of the table, you'll need to use triggers t
o
update the entity's appropriate attribute values in the temp table and so on
.
The solutions to the problems created by this type of design get uglier and
uglier and the performance gets worse and worse.
Thomas|||Celko,
thanks for your "constructive" comments.

> This flawed approach is called an EAV design
> and newbies re-invent it about once a month here.
Nice. I did not reinvent this, I'm just working on such flawed design,
as you called it.
Do you know better design, that could substitute EAV then?
[...cut...]

> It is not just exponently slow; since it has no data
> integrity, it is error-prone, too!!
Didn't I mention that I did not put integrity constraitns for simplicity
of the example? I believie I did.
I would be very gratefull if you could lead me to a better solution and
not criticizing my approach, as I am aware of the flaws that come with
my approach (and the design as well).
*** Sent via Developersdex http://www.examnotes.net ***|||I like the temporary table solution.
As the attributes are not added/deleted frequently it could be one of the
best approaches.
regards,
Pawel Rogozinski
Software Engineer at Eracent
pawel.rogozinski@.eracent.com|||>> Do you know better design, that could substitute EAV then? <<
The point everyone is trying to get across to you is that you need a
totally different approach; it is not a matter of finding a substutute.
Why not use an actual data model, based on the specifications from the
user. There is no "magical, one size fits all" answer that you s
with kludges like EAV. Each database is a thing in itself, but you can
look for patterns for reuse.
simplicity of the example? I believe I did. <<
Did I meniton that doing that is virtually impossible without a few
hundred lines of procedural code in triggers and procs if you have a
database of any size?
Let's say I have 50 attributes of type INTEGER in my EAV model. They
are crammed into one table. Each one has 1 or 2 CHECK() constraints
that are totally different. Now try to write the CASE expression with
50 WHEN clauses to validlate each one.
Let's say that I have a primary key among those 50 attributes. Can you
show me the uniquenss constraint to enforce that business rule?
Let's say that I have a a foreign key among those 50 attributes. Can
you show me the uniquness constraint to enforce that business rule?
And do the DRI Actions required to maintain this relationship?
Thomas Coleman had a recent posting where he worked out the equivalent
of a simple GROUP BY statement for an EAV. Google it.sql

Custom xml format with FOR XML EXPLICIT

I'm working on Sql Server 2005 xml capabilities, but I'm not able to obtain what I want.

Let's say I have a table with 10 fields: Field1, Field2, ..., Field10.

I would, with a "FOR XML" clause, obtain an xml document like this one:

<MyTable>
<Field1>F1value</Field1>
<Field2 Field3="F3value">F2value</Field2>
</MyTable>
<MyTable>
...
</MyTable>

I think correct way is with the "EXPLICIT" mode, but I'm not able to find the syntax to make Field3 become an attribute of the element generated from Field2.

Does someone knows if it's possible and, if it is, how?

Any help will be truly appreciated.

Easier to do this

select Field1 as "Field1",
Field3 as "Field2/@.Field3",
Field2 as "Field2"
from MyTable
for xml path('MyTable')

|||Thank you so much!

This is exactly what I was looking for.sql

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.

Custom names for summary fields

Using Crystal Reports 8.5 I have created a report which displays a pie chart of the column totals in a report. Unfortunately the names of the summary fields are used as labels for the pie chart slices and they are ugly - things like "sum qryOrders.OrderValue". Is there a way to replace these labels with my own choice of text?From the deafening silence, the answer appears to be 'No'. In case anyone else has the same issue, here is a workaround. Suppress the display of pie slice labels. Suppress the display of the chart legend. Create your own legend box using the basic drawing tools - filled rectangles - and add your own text labels.|||For the sake of future Googlers; I too was having this problem, and I had given up when I realized that if you sort "in specified order", you can then set up your "Named Groups" such that the "Group Name" is the name that you wish to have displayed.

For example, the formula for one of my named groups is "@.Allocation" "is equal to" "International Equity", and the name of the group is "Int'l Equity".

Works like a charm, sorry I'm too late to help you.|||Well, within a couple of minutes of posting, I realized that this only works if you have only one field to show a value for. So, it's not going to work where I really need it to, but still, it's useful elsewhere.

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.

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