Showing posts with label group. Show all posts
Showing posts with label group. Show all posts

Thursday, March 22, 2012

Early User Questions for developing large number of reports

Hi All,
Firstly, let me say who we are.....a group of us are developing a BI
product where we have decided to use Report Services as the reporting
tool. We are long term BI experts and IT consultants......We are
experienced in all manner of reporting and query tools we are just new
to RS. We settled on RS as our 'lower end' reporting tool for our
product and we will give our clients the 'option' of selecting other
reporting tools but the 'out of the box' reports will be in RS.
We have been dilligent and done our reading (WROX RS book) and testing
and figured out most of what we want to do. We have our first report up
and running and we are learning what the limits of RS seem to be to us.
So hopefully I will not ask too many 'dumb' questions... :-)
We also hope by asking questions here that other users of RS might also
benefit from the public discussion.
Q1. We are also working on our 'templates' for reports and looking at
how we develop a large number (200-300) of reports according to a
'template'. In the WROX Book they talk about codesmith and a way of
handling templates. But we didn't 'get' it...Also, when we try using
the VS.NET report wizard it seems that it insists on a query rather
than a stored procedure for it's data for the wizard to work.
So we are thinking we need to write each report by copying the rdl xml,
editting it by hand and then putting it into vs.net to further edit.
We have no real problem with that but we were thinking someone must
have come up with a better idea than editing the XML by now!!!
Has anyone published papers/ideas/tools (fee or free) to manage the
development and deployment of large numbers of reports?
Q2. We see MSFT has been out talking up RS2005/SQL Server 2005 and
talking about the pricing of SQL Server 2005. But we have not seen any
comment on the possible cost of RS2005 if it is NOT purchased with SQL
Server 2005. We would like to have the option of deploying our product
onto SQL Server with RS running either on that machine or elsewhere. Is
there any public comment from MSFT as to the possible pricing of RS2005
when not purchased with SQL Server?
Q3. Is there any published information on 'best practice' or 'best
examples of usage' of report services? For example MicroStrategy,
Business Objects etc post demos of their products to showcase the 'best
possible use' of the product. Dundas have also put out some really
great demos. But the RS demos from MSFTs web site look a little
'plain' especially the graphics. Are there any really great examples
of RS 'out there' or are the demos on the MSFT web site the best there
is? We have decided to go with DNN for the portal and we are currently
testing RS/DNN integration.
Q4. Layout of prompts on a report.
We have reports with quite a few prompts and they are 'grouped'. For
example we have a set of 4 prompts for the user to select the levels in
the company reporting structure. ANd then we have prompts for the
client to select year and month. Then we might have campaign structure
selection which is 3 levels....so we would like to lay out
Line 1: 4 prompts
Line 2: 2 Prompts
Line 3: 3 prompts
We are told this is not supported...but it also seems like it only
ever places two prompts on one line? If we cannot group prompts can we
at least get it to put more than 2 prompts per line?
Thanks in Advance
Peter Nolan
www.peternolan.comRS is like most of the other MS products: a "back-end" tool with high
custimization available. You can extend it from many way.
Q2:
The licensing is the same as 2000. RS is part of SQL 2005 and if you want
report capabilities only, you have to buy a license of SQL 2005. But the RS
repository required SQL Server.
So for your answer, yes RS can be purchased "separatly" but at the full
price of SQL 2005.
Q3:
On the MS web site, there is some samples reports for different usage:
CRM, Exchange, Axapta, SQL Logs, RS logs.
Best pratices & usages depend on your needs.
Designing printing reports is different from web based usage only reports.
Today there is nothing except simple samples. But these small samples don't
limit me to use RS.
Generally I start from an overall report, then I link to detailed reports.
Q4:
Create you own prompt page if you want to create a specific layout.
Remember that RS is designed for developpers to include reporting capability
into their applications.
But there is a lack of sample and helps to create front end tools around RS.
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1120988013.636226.99630@.o13g2000cwo.googlegroups.com...
> Hi All,
> Firstly, let me say who we are.....a group of us are developing a BI
> product where we have decided to use Report Services as the reporting
> tool. We are long term BI experts and IT consultants......We are
> experienced in all manner of reporting and query tools we are just new
> to RS. We settled on RS as our 'lower end' reporting tool for our
> product and we will give our clients the 'option' of selecting other
> reporting tools but the 'out of the box' reports will be in RS.
> We have been dilligent and done our reading (WROX RS book) and testing
> and figured out most of what we want to do. We have our first report up
> and running and we are learning what the limits of RS seem to be to us.
> So hopefully I will not ask too many 'dumb' questions... :-)
> We also hope by asking questions here that other users of RS might also
> benefit from the public discussion.
>
> Q1. We are also working on our 'templates' for reports and looking at
> how we develop a large number (200-300) of reports according to a
> 'template'. In the WROX Book they talk about codesmith and a way of
> handling templates. But we didn't 'get' it...Also, when we try using
> the VS.NET report wizard it seems that it insists on a query rather
> than a stored procedure for it's data for the wizard to work.
> So we are thinking we need to write each report by copying the rdl xml,
> editting it by hand and then putting it into vs.net to further edit.
> We have no real problem with that but we were thinking someone must
> have come up with a better idea than editing the XML by now!!!
> Has anyone published papers/ideas/tools (fee or free) to manage the
> development and deployment of large numbers of reports?
>
> Q2. We see MSFT has been out talking up RS2005/SQL Server 2005 and
> talking about the pricing of SQL Server 2005. But we have not seen any
> comment on the possible cost of RS2005 if it is NOT purchased with SQL
> Server 2005. We would like to have the option of deploying our product
> onto SQL Server with RS running either on that machine or elsewhere. Is
> there any public comment from MSFT as to the possible pricing of RS2005
> when not purchased with SQL Server?
>
> Q3. Is there any published information on 'best practice' or 'best
> examples of usage' of report services? For example MicroStrategy,
> Business Objects etc post demos of their products to showcase the 'best
> possible use' of the product. Dundas have also put out some really
> great demos. But the RS demos from MSFTs web site look a little
> 'plain' especially the graphics. Are there any really great examples
> of RS 'out there' or are the demos on the MSFT web site the best there
> is? We have decided to go with DNN for the portal and we are currently
> testing RS/DNN integration.
>
> Q4. Layout of prompts on a report.
> We have reports with quite a few prompts and they are 'grouped'. For
> example we have a set of 4 prompts for the user to select the levels in
> the company reporting structure. ANd then we have prompts for the
> client to select year and month. Then we might have campaign structure
> selection which is 3 levels....so we would like to lay out
> Line 1: 4 prompts
> Line 2: 2 Prompts
> Line 3: 3 prompts
> We are told this is not supported...but it also seems like it only
> ever places two prompts on one line? If we cannot group prompts can we
> at least get it to put more than 2 prompts per line?
>
> Thanks in Advance
>
> Peter Nolan
> www.peternolan.com
>|||Hi Jerome,
Q2. In licensing MSFT talks about buying RS when it needs to be
deployed on a server other than the SQL Server...and the WROX book
talks about this as being a 'medium' sized deployment.....if that
means that we need to buy a full SQL Server license (I guess workgroup)
to run RS on a separate server that's ok...we just wanted to
know....but that seems a bit pricey....
Q4. I found examples on odetocode web site but most of the examples I
am finding are 'fragments' intended for developers to include into
already designed/built apps.....that's ok..if there are no other
examples out there we can start from where we are at...just wanted to
check there was not something more publicly available before we spent
our time and effort...it's kind of suprising MSFT has not published
some more things to give developers a better base to work from to go up
against the likes of Crystal/Business Objects/Cognos...maybe more is
coming'
Best Regards
Peter|||Q1, Q3, and Q4--answered in depth in Peter Blackburn and William Vaughn's
Hitchhiker's Guide!
Amazon: http://shrinkster.com/6km
"Peter Nolan" <peter@.peternolan.com> wrote in message
news:1120988013.636226.99630@.o13g2000cwo.googlegroups.com...
> Hi All,
> Firstly, let me say who we are.....a group of us are developing a BI
> product where we have decided to use Report Services as the reporting
> tool. We are long term BI experts and IT consultants......We are
> experienced in all manner of reporting and query tools we are just new
> to RS. We settled on RS as our 'lower end' reporting tool for our
> product and we will give our clients the 'option' of selecting other
> reporting tools but the 'out of the box' reports will be in RS.
> We have been dilligent and done our reading (WROX RS book) and testing
> and figured out most of what we want to do. We have our first report up
> and running and we are learning what the limits of RS seem to be to us.
> So hopefully I will not ask too many 'dumb' questions... :-)
> We also hope by asking questions here that other users of RS might also
> benefit from the public discussion.
>
> Q1. We are also working on our 'templates' for reports and looking at
> how we develop a large number (200-300) of reports according to a
> 'template'. In the WROX Book they talk about codesmith and a way of
> handling templates. But we didn't 'get' it...Also, when we try using
> the VS.NET report wizard it seems that it insists on a query rather
> than a stored procedure for it's data for the wizard to work.
> So we are thinking we need to write each report by copying the rdl xml,
> editting it by hand and then putting it into vs.net to further edit.
> We have no real problem with that but we were thinking someone must
> have come up with a better idea than editing the XML by now!!!
> Has anyone published papers/ideas/tools (fee or free) to manage the
> development and deployment of large numbers of reports?
>
> Q2. We see MSFT has been out talking up RS2005/SQL Server 2005 and
> talking about the pricing of SQL Server 2005. But we have not seen any
> comment on the possible cost of RS2005 if it is NOT purchased with SQL
> Server 2005. We would like to have the option of deploying our product
> onto SQL Server with RS running either on that machine or elsewhere. Is
> there any public comment from MSFT as to the possible pricing of RS2005
> when not purchased with SQL Server?
>
> Q3. Is there any published information on 'best practice' or 'best
> examples of usage' of report services? For example MicroStrategy,
> Business Objects etc post demos of their products to showcase the 'best
> possible use' of the product. Dundas have also put out some really
> great demos. But the RS demos from MSFTs web site look a little
> 'plain' especially the graphics. Are there any really great examples
> of RS 'out there' or are the demos on the MSFT web site the best there
> is? We have decided to go with DNN for the portal and we are currently
> testing RS/DNN integration.
>
> Q4. Layout of prompts on a report.
> We have reports with quite a few prompts and they are 'grouped'. For
> example we have a set of 4 prompts for the user to select the levels in
> the company reporting structure. ANd then we have prompts for the
> client to select year and month. Then we might have campaign structure
> selection which is 3 levels....so we would like to lay out
> Line 1: 4 prompts
> Line 2: 2 Prompts
> Line 3: 3 prompts
> We are told this is not supported...but it also seems like it only
> ever places two prompts on one line? If we cannot group prompts can we
> at least get it to put more than 2 prompts per line?
>
> Thanks in Advance
>
> Peter Nolan
> www.peternolan.com
>|||thanks for the pointer goodman 93...I'll see if I can get a copy of
that too.....
Peter

Wednesday, March 21, 2012

'Earlier years' and 'Later years' in a timedimension

Hi,

I need to group together year 2003 and earlier in the bucket 'Earlier years' and 2009 and later in a bucket called 'Later years'. Is there any simple way to handle that within a timedimension?I have a table MatchDate with the column MatchDate, MatchYear, MatchQtr, MatchMonth.

I solved the issue by using MatchYear as 'Earlier years' for years before 2004 and 'Later years' for years after 2008. The same for MatchQtr and MatchMonth.

In the properties for Year I changed Member Key Column to MatchYear. The same thing for Quatrer and Month.That solved my issue so the Time dimension looks like:

Earlier years
2004
2005
2006
2007
2008
Later years

Of cause then it's possible to drill down into the different years.sql

Monday, March 19, 2012

Dynamically send query to report

hi all,
can we use our own SQL queries to display records in the report?
if report contains the Group field.
Can we do this in Crystal Report 8.5
and SQL server 2000
Thanks in Advance
Regards
Henry JonesYes u can
First of all tell me the Client side application techn... u r using

whether classic VB6 / VS2003

and u r requirement is bit unclear can u illustrate more on it

FaFa|||Make use of SQLQuery feauture

Sunday, March 11, 2012

Dynamically delete entire columns values

Hello all,
I was wondering if anyone knew of a way to dynamically delete all of the values for a group of columns. What I mean by this is that lets say a table (TableA) has five fields (Field1, Field2, Field3, Field4, and Field5) with 100 rows of data. I want to delete all of the data in Field1, Field2, and Field4. I do not want to delete any of the data in Field3 and Field5. I would then end up with a table with 5 fields and 100 row, but only 2 fields (Field3 and Field5) have data.

The catch is that I can't hardcode the field names of the fields I want to clear out (Field1, Field2, and Field4) into the SQL. This is because if any new fields are eventually added to the table I want them to be cleared out as well without modifying the SQL.

I can hardcode the field names of the fields that I want to keep values for (Field3 and Field5) in the SQL.

If anyone has any idea how to do this, I would greatly appreciate it.

Thanks in advance!This smells of dynamic SQL and queries against system tables to me.

-PatP|||If you implement your solution through a stored procedure, - it'll actually not require a dynamic SQL. But let's get something straight and agree to it, - it's an UPDATE we're talking about here, not a DELETE. Probably something like this would work:
create procedure dbo.sp_clear_fields (
@.string varchar(8000) = null,
@.delimiter char(1) = ',' )
as
if @.string is null return (0)
declare @.spos int, @.pos int
declare @.tbl table (
RecordID int identity(1,1) not null primary key clustered,
[Field] varchar(128) not null )
set @.string = replace(
replace(@.string, @.delimiter + ' ', @.delimiter), ' ' + @.delimiter, @.delimiter)
set @.spos = 1
set @.pos = 100
while (@.pos) > 0 begin
set @.pos = charindex(@.delimiter, @.string, @.spos)
insert @.tbl ([Field])
select
ltrim (
rtrim (
substring (
@.string, @.spos,
case
when (@.pos - @.spos) <= 0 then datalength(@.string) + 1
else @.pos
end - @.spos
)
)
)
set @.spos = @.pos + 1
end
update a
set Field1 = case when [Field] = 'Field1' then null else Field1 end,
Field2 = case when [Field] = 'Field2' then null else Field2,
Field3 = case when [Field] = 'Field3' then null else Field3,
Field4 = case when [Field] = 'Field4' then null else Field4,
Field5 = case when [Field] = 'Field5' then null else Field5
from TableA a, @.tbl
if @.@.error != 0 raiserror ('Failed to clear the fields!', 15, 1)
return (0)
go|||I'm with you thinking the question means that they want an update to NULL, but I think because of the dynamic nature of the query (the exrpessed need to pick up new columns that are added after the fact) that dynamic SQL is required. Even if it wasn't required because of that, I would find it preferable just because of the much lower load on the server (one pass through the table as opposed to one pass for each column being NULLed). I'd use something like:CREATE TABLE tMtrebs (
MtrebsID INT NOT NULL
CONSTRAINT XPKtMtrebs
PRIMARY KEY (MtrebsID)
, col1 VARCHAR(20) NULL
, col2 INT NULL
, col3 DATETIME NULL
, col4 VARCHAR(20) NULL
, col5 DATETIME NOT NULL
, col6 INT NOT NULL
)
GO

DECLARE @.cCmd VARCHAR(500)

SELECT @.cCmd = Coalesce(@.cCmd + ', [' + sc.name + '] = NULL'
, 'UPDATE tMtrebs SET [' + sc.name + '] = NULL')
FROM dbo.syscolumns AS sc
WHERE Object_id('dbo.tMtrebs') = sc.id
AND sc.name NOT IN ('col4', 'col5') -- protected columns
AND 1 = ColumnProperty(Object_Id('dbo.tMtrebs'), sc.name, 'AllowsNull')

EXECUTE (@.cCmd)-PatP|||I agree that this makes no sense|||I agree that this makes no senseOh, and since when have minor details like that bothered any of us in the least?!?!

-PatP

Friday, March 9, 2012

Dynamically change report grouping

Currently our users can filter data but they cannot manually group data
differently without us having to create them a new report with their
required group options.
Is there a way perhaps to hard code maybe 1 or 2 group levels in a reports
and then turn them off or turn them on dynamically depending on how the user
wanted to view the results.
Any help is appreciated.We use a 'Group By' parameter that the stored proc uses to do the
grouping. The results come back in the same format each time of
course, they're just grouped differently. This can sometimes lead to
some icky VB.NET conditionals inlined into the RDL, but in general it
has worked very well.|||Alternatively, group on an expression that is based on the value of a
parameter.
In our case, the client's requirements needed 6 reports but this
technique allowed us to give them one.
The stored proc must return a result set at a grouping level just above
the two alternative groupings.
BrianK
www.bolign.com

Friday, February 24, 2012

Dynamic Text in Group Headers

I'm woirking in SSRS 2005. I'm looking for a way to add "(Continued...)" to
a group header when the group spans multiple pages. When I used to use
Crystal Reports 8.5 this was very simple to do uising the
"InRepeatedGroupHeader" built in function. I have not been able to find
anything like this in SSRS. I've seen several people asking for the same
thing on MSDN but no solutions. Any ideas on how this can be done in SSRS?tachtenberg wrote:
> I'm woirking in SSRS 2005. I'm looking for a way to add "(Continued...)" to
> a group header when the group spans multiple pages. When I used to use
> Crystal Reports 8.5 this was very simple to do uising the
> "InRepeatedGroupHeader" built in function. I have not been able to find
> anything like this in SSRS. I've seen several people asking for the same
> thing on MSDN but no solutions. Any ideas on how this can be done in SSRS?
Try Something like this:
=IIF(Globals!PageNumber > 1,Globals!ReportName &" (Continued)"
,Globals!ReportName)
-MM|||That will not work because some groups span more than one page and others do
not. Page numbering does not reset with each group.
"Michael" wrote:
> tachtenberg wrote:
> > I'm woirking in SSRS 2005. I'm looking for a way to add "(Continued...)" to
> > a group header when the group spans multiple pages. When I used to use
> > Crystal Reports 8.5 this was very simple to do uising the
> > "InRepeatedGroupHeader" built in function. I have not been able to find
> > anything like this in SSRS. I've seen several people asking for the same
> > thing on MSDN but no solutions. Any ideas on how this can be done in SSRS?
> Try Something like this:
> =IIF(Globals!PageNumber > 1,Globals!ReportName &" (Continued)"
> ,Globals!ReportName)
>
> -MM
>|||My bad. I didn't read the question slow enough. I thought it seemed a
little too easy.
Anyway, I did find this that might be what you need.
http://blogs.msdn.com/chrishays/archive/2006/09/27/ContinuedHeader.aspx
tachtenberg wrote:
> That will not work because some groups span more than one page and others do
> not. Page numbering does not reset with each group.
> "Michael" wrote:
>> tachtenberg wrote:
>> I'm woirking in SSRS 2005. I'm looking for a way to add "(Continued...)" to
>> a group header when the group spans multiple pages. When I used to use
>> Crystal Reports 8.5 this was very simple to do uising the
>> "InRepeatedGroupHeader" built in function. I have not been able to find
>> anything like this in SSRS. I've seen several people asking for the same
>> thing on MSDN but no solutions. Any ideas on how this can be done in SSRS?
>> Try Something like this:
>> =IIF(Globals!PageNumber > 1,Globals!ReportName &" (Continued)"
>> ,Globals!ReportName)
>>
>> -MM|||On Jan 18, 2:25=A0pm, Michael <Mich...@.discussions.microsoft.com> wrote:
> My bad. I didn't read the question slow enough. I thought it seemed a
> little too easy.
> Anyway, I did find this that might be what you need.http://blogs.msdn.com/=
chrishays/archive/2006/09/27/ContinuedHeader.aspx
>
> tachtenberg wrote:
> > That will not work because some groups span more than one page and other=s do
> > not. =A0Page numbering does not reset with each group.
> > "Michael" wrote:
> >> tachtenberg wrote:
> >> I'm woirking in SSRS 2005. =A0I'm looking for a way to add "(Continued=...)" to
> >> a group header when the group spans multiple pages. =A0When I used to =use
> >> Crystal Reports 8.5 this was very simple to do uising the
> >> "InRepeatedGroupHeader" built in function. =A0I have not been able to =find
> >> anything like this in SSRS. =A0I've seen several people asking for the= same
> >> thing on MSDN but no solutions. =A0Any ideas on how this can be done i=n SSRS?
> >> Try Something like this:
> >> =3DIIF(Globals!PageNumber > 1,Globals!ReportName &" (Continued)"
> >> ,Globals!ReportName)
> >> -MM- Hide quoted text -
> - Show quoted text -
Also, here's white paper that shows (among other things) how to reset
page numbers when groups change.
http://msdn2.microsoft.com/en-us/library/bb395166.aspx|||I've looked at Chris's work around. Unfortunately, there are other items
above the table I need the repeated group headers in so it will not work.
"Michael" wrote:
> My bad. I didn't read the question slow enough. I thought it seemed a
> little too easy.
> Anyway, I did find this that might be what you need.
> http://blogs.msdn.com/chrishays/archive/2006/09/27/ContinuedHeader.aspx
>

Dynamic Summing

I have a 4th level grouping that has just 3 different values to group on (No
Cat, New and Current). I want to sum a field on that level, but only if the
grouping is not = "current". The sum can be in the 3rd or 4th level grouping,
whichever works. So something like this, but this doesn't work:
=SUM(IIF(LTRIM(RTRIM(Fields!Type.Value))<>"CURRENT",Sum(Fields!CurrMthSales.Value),0))I think it should be something like this. (You don't need the extra sum in
your formula)
=SUM(IIF(LTRIM(RTRIM(Fields!Type.Value))<>"CURRENT",Fields!CurrMthSales.Value,0))
"Chris Patten" wrote:
> I have a 4th level grouping that has just 3 different values to group on (No
> Cat, New and Current). I want to sum a field on that level, but only if the
> grouping is not = "current". The sum can be in the 3rd or 4th level grouping,
> whichever works. So something like this, but this doesn't work:
> =SUM(IIF(LTRIM(RTRIM(Fields!Type.Value))<>"CURRENT",Sum(Fields!CurrMthSales.Value),0))
>

Sunday, February 19, 2012

Dynamic subreport based on user group

How would you set visibility in a report based on which user group the user
is in?You can easily see the User, but not the groups the user is in ,unless you do
some sort of call outside RS.
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Doris" wrote:
> How would you set visibility in a report based on which user group the user
> is in?