Custom Groups

I'm wondering if there is any way in RS to create what Crystal reports calls Custom or Named Groups. This functionality allows you to define groups based on the values found in one of the fields on your report. For example, if I were grouping on a person's last name and I want 3 groups based on the first letter: A-G, H-P, Q-Z. I would want to keep it contained in one table.RS also has a concept of groups, and the construction of groups is very flexible. Groups are usedion DataRegion, such as Table, Matrix and Lists. Groups can be be grouped, filtered, sorted using multiple expressions. These expression can be just a field value, or they can be more complex.

In your case, you could create a group that uses an expression to return 1 if the value starts with A-G, return 2 if the value starts with H-P, and so on. The expression would look something like this, it assumes only upper case ASCII characters.

=Switch("ABCDEFGH".Contains(Fields!Name.Value.Substring(0,1)), 1, "IJKLMNOP".Contains(Fields!Name.Value.Substring(0,1)), 2, "QRSTUVWXYZ".Contains(Fields!Name.Value.Substring(0,1)), 3)

or

(use 64 since Choose uses the 1-based index into the array)

=Choose(Convert.ToInt32(Fields!Name.Value.Chars(0)) - 64, {1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3,3})

If you want something a little cleaner, then I would use a custom function that takes the first character as an argument and determines the appropriate value using regular control statements.|||

Thanks Ian!

Instead of editing the report, I decided to just add a field to my SQL statement to group on. It looks something like this:

select case
when left(lname,1) between 'a' and 'h' then 'A-H'
when left(lname,1) between 'i' and 'p' then 'I-P'
when left(lname,1) between 'q' and 'z' then 'Q-Z'
end as NameType from employee

I then created a group on the NameType field.

This worked great and my group headers were named properly as well.

|||I am trying something similar, but for some reason it does not like the syntax near AS. I see that it creates field called "NameType"...but does 'Employee' refer to the table the lname is coming from?|||Yes, the 'AS NameType' clause specifies the name for the result of the case statement, and 'lname' is a field in the 'Employee' table.

What is your query? Can you post it? I'm happy to take a look at it and see if there is something I can spot that would result in the syntax error.

Ian

Custom Groups

I'm wondering if there is any way in RS to create what Crystal reports calls Custom or Named Groups. This functionality allows you to define groups based on the values found in one of the fields on your report. For example, if I were grouping on a person's last name and I want 3 groups based on the first letter: A-G, H-P, Q-Z. I would want to keep it contained in one table.RS also has a concept of groups, and the construction of groups is very flexible. Groups are usedion DataRegion, such as Table, Matrix and Lists. Groups can be be grouped, filtered, sorted using multiple expressions. These expression can be just a field value, or they can be more complex.

In your case, you could create a group that uses an expression to return 1 if the value starts with A-G, return 2 if the value starts with H-P, and so on. The expression would look something like this, it assumes only upper case ASCII characters.

=Switch("ABCDEFGH".Contains(Fields!Name.Value.Substring(0,1)), 1, "IJKLMNOP".Contains(Fields!Name.Value.Substring(0,1)), 2, "QRSTUVWXYZ".Contains(Fields!Name.Value.Substring(0,1)), 3)

or

(use 64 since Choose uses the 1-based index into the array)

=Choose(Convert.ToInt32(Fields!Name.Value.Chars(0)) - 64, {1,1,1,1,1,1,1,1,2,2,2,2,2,2,2,2,3,3,3,3,3,3,3,3,3,3})

If you want something a little cleaner, then I would use a custom function that takes the first character as an argument and determines the appropriate value using regular control statements.|||

Thanks Ian!

Instead of editing the report, I decided to just add a field to my SQL statement to group on. It looks something like this:

select case
when left(lname,1) between 'a' and 'h' then 'A-H'
when left(lname,1) between 'i' and 'p' then 'I-P'
when left(lname,1) between 'q' and 'z' then 'Q-Z'
end as NameType from employee

I then created a group on the NameType field.

This worked great and my group headers were named properly as well.

|||I am trying something similar, but for some reason it does not like the syntax near AS. I see that it creates field called "NameType"...but does 'Employee' refer to the table the lname is coming from?|||Yes, the 'AS NameType' clause specifies the name for the result of the case statement, and 'lname' is a field in the 'Employee' table.

What is your query? Can you post it? I'm happy to take a look at it and see if there is something I can spot that would result in the syntax error.

Ian

Custom Database Roles -- Insert

I am trying to set up a Custom Database Role that allows the user to
select, update, insert and delete data. I created the role by
combining the built-in roles of db_datareader and db_datawriter.
However when I logged with the custom role I was not able to insert a
new record.
When I then went in and assigned Insert rights to the table I was
trying to insert to, I was able to do it. But if I go that route I
end up having to set permissions on every table in the database. I
just want to grant these rights to ALL the tables.
Is there a straighforward way to do this and why didn't adding
db_datawriter to the role definition accomplish this?
Thanks.
StevenHello,
See if there is any Deny permissions set for the user for the specific table
you tried to insert.
Thanks
Hari
"ExcelMan" <sfarkas@.sjfcg.com> wrote in message
news:1180227296.829693.158290@.i13g2000prf.googlegroups.com...
>I am trying to set up a Custom Database Role that allows the user to
> select, update, insert and delete data. I created the role by
> combining the built-in roles of db_datareader and db_datawriter.
> However when I logged with the custom role I was not able to insert a
> new record.
> When I then went in and assigned Insert rights to the table I was
> trying to insert to, I was able to do it. But if I go that route I
> end up having to set permissions on every table in the database. I
> just want to grant these rights to ALL the tables.
> Is there a straighforward way to do this and why didn't adding
> db_datawriter to the role definition accomplish this?
> Thanks.
> Steven
>|||ExcelMan (sfarkas@.sjfcg.com) writes:
> I am trying to set up a Custom Database Role that allows the user to
> select, update, insert and delete data. I created the role by
> combining the built-in roles of db_datareader and db_datawriter.
> However when I logged with the custom role I was not able to insert a
> new record.
> When I then went in and assigned Insert rights to the table I was
> trying to insert to, I was able to do it. But if I go that route I
> end up having to set permissions on every table in the database. I
> just want to grant these rights to ALL the tables.
> Is there a straighforward way to do this and why didn't adding
> db_datawriter to the role definition accomplish this?
As Hari suggested, a DENY permission may be the problem. Here is a
script for SQL 2000 that demonstrates that what you want to do really
works. By the way, if you are using SQL 2005, you are better off granting
access on schema or database level.
USE tempdb
go
CREATE DATABASE rolle
EXEC sp_addlogin rollerull, '12'
go
USE rolle
go
CREATE TABLE mulle (a int NOT NULL, x sysname DEFAULT USER)
go
EXEC sp_addrole rolle
EXEC sp_addrolemember 'db_datawriter', 'rolle'
EXEC sp_addrolemember 'db_datareader', 'rolle'
EXEC sp_grantdbaccess rollerull
EXEC sp_addrolemember rolle, rollerull
go
SETUSER 'rollerull'
go
INSERT mulle (a) VALUES (12)
go
SETUSER
go
SELECT * FROM mulle
go
use tempdb
go
DROP DATABASE rolle
EXEC sp_droplogin rollerull
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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 "Export to excel" button help needed

Hi - I'm trying to create a report (viewed through the reportviewer
web control) that has the toolbar hidden, but still allows people to
export to excel and pdf.
Ideally I need to create two asp.net linkbuttons on the web page
itself, that will call methods to save the report being viewed through
reportviewer as excel and pdf.
Is this possible?
Thanks for any help
JamesUse the Reporting Services Web Service method Render to do this. The web
service can be added as a reference from
http://ReportServerComputer/ReportServer/ReportService.asmx
After adding it and creating a proxy RS, you can use (in VB.NET)
RS.render("c:\myfile.xls", "EXCEL")
Charles Kangai, MCT, MCDBA
"jamesb" wrote:
> Hi - I'm trying to create a report (viewed through the reportviewer
> web control) that has the toolbar hidden, but still allows people to
> export to excel and pdf.
> Ideally I need to create two asp.net linkbuttons on the web page
> itself, that will call methods to save the report being viewed through
> reportviewer as excel and pdf.
> Is this possible?
> Thanks for any help
> James
>|||Just tweak the reportviewer web user control. Add a boolean property that
if true adds the following to the end of the report URL:
&rs:Format=EXCEL
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message
news:410C78F3-14C3-4D4A-8862-09ADABDDFDE4@.microsoft.com...
> Use the Reporting Services Web Service method Render to do this. The web
> service can be added as a reference from
> http://ReportServerComputer/ReportServer/ReportService.asmx
> After adding it and creating a proxy RS, you can use (in VB.NET)
> RS.render("c:\myfile.xls", "EXCEL")
> Charles Kangai, MCT, MCDBA
> "jamesb" wrote:
>> Hi - I'm trying to create a report (viewed through the reportviewer
>> web control) that has the toolbar hidden, but still allows people to
>> export to excel and pdf.
>> Ideally I need to create two asp.net linkbuttons on the web page
>> itself, that will call methods to save the report being viewed through
>> reportviewer as excel and pdf.
>> Is this possible?
>> Thanks for any help
>> James|||I'm sorry, I posted too soon. The ReportViewer already has a Format
property. Just set that to EXCEL or PDF at the appropriate point in your
code. Works like a charm.
Cheers,
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message
news:410C78F3-14C3-4D4A-8862-09ADABDDFDE4@.microsoft.com...
> Use the Reporting Services Web Service method Render to do this. The web
> service can be added as a reference from
> http://ReportServerComputer/ReportServer/ReportService.asmx
> After adding it and creating a proxy RS, you can use (in VB.NET)
> RS.render("c:\myfile.xls", "EXCEL")
> Charles Kangai, MCT, MCDBA
> "jamesb" wrote:
>> Hi - I'm trying to create a report (viewed through the reportviewer
>> web control) that has the toolbar hidden, but still allows people to
>> export to excel and pdf.
>> Ideally I need to create two asp.net linkbuttons on the web page
>> itself, that will call methods to save the report being viewed through
>> reportviewer as excel and pdf.
>> Is this possible?
>> Thanks for any help
>> James