Reporting on Sharepoint Lists?

I was wondering if it would be possible to generate reports directly from
Windows Sharepoint Services (WSS) Lists or from the output of a Webservice?
tnx
-= Maximizing Creativity and Productivity =-Dear Maarten
Yes, you can generate a report from data of WSS - although it is a bit of a
guesswork. WSS saves all user data in one table only (called UserData). You
will have to filter the right data from this table. In order to know what to
filter, you will have to identify the tp_ListId (uniqueidentifier) of the
WSS-List on which you want to base your report. In order to find this
tp_ListId you have to search first in the Sites table for the tp_ID of the
site. With this ID you can look up the tp_ID of the subweb in the Webs table.
And finally, with the ID of the subweb you can look up the ID of the WSS list
in the Lists table.
The next step will be to identify the fields for the identified list. There
is only one way: make a print screen from the WSS-list and scroll the list
date in the UserData table in order to find the fields that store the WSS
list data.
Furthermore, I have found it easiest to define different views on the
UserData table and to work with this views in Reporting Services (not
directly with the UserData table). In the view, you can rename the fields of
the UserData table with aliases that are the names of the columns of the WSS
list.
A last hint: you can display reports in WSS. Just define a WSS page with a
webpart aerea and give this webpart the reportserver link of the report you
want to show. It works fine.
Good luck - Judith Schuetz (www.osn.ch)
"Maarten Visser" wrote:
> I was wondering if it would be possible to generate reports directly from
> Windows Sharepoint Services (WSS) Lists or from the output of a Webservice?
> tnx
> -= Maximizing Creativity and Productivity =-|||Judith.
Thanks for the information, I understand your solution.
But, like you are saying.. ' It is a bit of a guesswork'.
I have to build a solution for a customer, where end users have to build
reports and the manipulation of data has to be as easy as possible. Because
of this, i was thinking to use WSS as a front-end for data input and
Reporting Services for generating reports on this data.
I'm afraid the given solution would not work in this case, because the user
needs to be able to add columns (in WSS) and the report should than easily be
adapted by the user to show this column. (It would be best if the report
automatically showed the extra column).
Could the ADO.Net extensibility of Reporting Services do something here..
Would it be possible to build a webservice that gets the information from
Sharepoint and let Reporting Services build reports based on the data (rows)
that this webservice provides?
Tnx.
Maarten
I'm afraid the given solution would not work in this case, because the user
needs to be able to add columns and the report should than easely be adapted
by the user to show this column. (It would be best if the report
automatecally showed the extra column).
"j schuetz" wrote:
> Dear Maarten
> Yes, you can generate a report from data of WSS - although it is a bit of a
> guesswork. WSS saves all user data in one table only (called UserData). You
> will have to filter the right data from this table. In order to know what to
> filter, you will have to identify the tp_ListId (uniqueidentifier) of the
> WSS-List on which you want to base your report. In order to find this
> tp_ListId you have to search first in the Sites table for the tp_ID of the
> site. With this ID you can look up the tp_ID of the subweb in the Webs table.
> And finally, with the ID of the subweb you can look up the ID of the WSS list
> in the Lists table.
> The next step will be to identify the fields for the identified list. There
> is only one way: make a print screen from the WSS-list and scroll the list
> date in the UserData table in order to find the fields that store the WSS
> list data.
> Furthermore, I have found it easiest to define different views on the
> UserData table and to work with this views in Reporting Services (not
> directly with the UserData table). In the view, you can rename the fields of
> the UserData table with aliases that are the names of the columns of the WSS
> list.
> A last hint: you can display reports in WSS. Just define a WSS page with a
> webpart aerea and give this webpart the reportserver link of the report you
> want to show. It works fine.
> Good luck - Judith Schuetz (www.osn.ch)
>
> "Maarten Visser" wrote:
> >
> > I was wondering if it would be possible to generate reports directly from
> > Windows Sharepoint Services (WSS) Lists or from the output of a Webservice?
> >
> > tnx
> >
> > -= Maximizing Creativity and Productivity =-|||Sorry - I can't help you with this one.
"Maarten Visser" wrote:
> Judith.
> Thanks for the information, I understand your solution.
> But, like you are saying.. ' It is a bit of a guesswork'.
> I have to build a solution for a customer, where end users have to build
> reports and the manipulation of data has to be as easy as possible. Because
> of this, i was thinking to use WSS as a front-end for data input and
> Reporting Services for generating reports on this data.
> I'm afraid the given solution would not work in this case, because the user
> needs to be able to add columns (in WSS) and the report should than easily be
> adapted by the user to show this column. (It would be best if the report
> automatically showed the extra column).
> Could the ADO.Net extensibility of Reporting Services do something here..
> Would it be possible to build a webservice that gets the information from
> Sharepoint and let Reporting Services build reports based on the data (rows)
> that this webservice provides?
> Tnx.
> Maarten
>
> I'm afraid the given solution would not work in this case, because the user
> needs to be able to add columns and the report should than easely be adapted
> by the user to show this column. (It would be best if the report
> automatecally showed the extra column).
>
> "j schuetz" wrote:
> > Dear Maarten
> >
> > Yes, you can generate a report from data of WSS - although it is a bit of a
> > guesswork. WSS saves all user data in one table only (called UserData). You
> > will have to filter the right data from this table. In order to know what to
> > filter, you will have to identify the tp_ListId (uniqueidentifier) of the
> > WSS-List on which you want to base your report. In order to find this
> > tp_ListId you have to search first in the Sites table for the tp_ID of the
> > site. With this ID you can look up the tp_ID of the subweb in the Webs table.
> > And finally, with the ID of the subweb you can look up the ID of the WSS list
> > in the Lists table.
> >
> > The next step will be to identify the fields for the identified list. There
> > is only one way: make a print screen from the WSS-list and scroll the list
> > date in the UserData table in order to find the fields that store the WSS
> > list data.
> >
> > Furthermore, I have found it easiest to define different views on the
> > UserData table and to work with this views in Reporting Services (not
> > directly with the UserData table). In the view, you can rename the fields of
> > the UserData table with aliases that are the names of the columns of the WSS
> > list.
> >
> > A last hint: you can display reports in WSS. Just define a WSS page with a
> > webpart aerea and give this webpart the reportserver link of the report you
> > want to show. It works fine.
> >
> > Good luck - Judith Schuetz (www.osn.ch)
> >
> >
> > "Maarten Visser" wrote:
> >
> > >
> > > I was wondering if it would be possible to generate reports directly from
> > > Windows Sharepoint Services (WSS) Lists or from the output of a Webservice?
> > >
> > > tnx
> > >
> > > -= Maximizing Creativity and Productivity =-|||This is a fragment from a sp I wrote for a customer that ought to get you
started...
declare @.tbl table(DisplayName varchar(100), ColName varchar(100), List
varchar(20), ShowFld varchar(100))
select top 1 @.flds=cast(tp_Fields as varchar(8000))
from dbo.Lists
where <-- some selection clause -->
select @.flds = '<root>'+@.flds+'</root>' -- surround field list with top
level node, otherwise DOM throws error
exec sp_xml_preparedocument @.hnd output, @.flds
insert into @.tbl
select *
from openxml(@.hnd, N'/root/Field') with (DisplayName varchar(100), ColName
varchar(100), List varchar(20), ShowField varchar(100))
where DisplayName is not null
exec sp_xml_removedocument @.hnd
select @.cmd='select "ID"=ud.tp_ID, Created=ud.tp_Created,
Modified=ud.tp_Modified, Author=u1.tp_Title'
declare crs cursor for select DisplayName, ColName, List, ShowFld from @.tbl
open crs
fetch next from crs into @.name, @.colname, @.list, @.showfld
select @.joinum=2, @.join=''
while @.@.fetch_status = 0
begin
if @.list is not NULL -- if this is a userlist field, join in the list and
return the value (not the list id)
begin
select @.alias = 'u'+cast(@.joinum as varchar(3))
select @.join=@.join+' join dbo.'+@.list+' '+@.alias+' on
(ud.'+@.colname+'='+@.alias+'.tp_ID and ud.tp_Siteid='+@.alias+'.tp_SiteID)'
select @.cmd=@.cmd+', '''+@.name+'''='+@.alias+'.tp_'+@.showfld
select @.joinum=@.joinum+1
end
else
begin
if left(@.colname, 5) = 'ntext' select @.colname = 'cast('+@.colname+' as
varchar('+cast(@.txtlen as varchar(4))+'))'
select @.cmd=@.cmd+', '''+@.name+'''='+@.colname
end
fetch next from crs into @.name, @.colname, @.list, @.showfld
end
close crs
deallocate crs
select @.cmd=@.cmd+' from dbo.UserData ud join dbo.UserInfo u1 on
(ud.tp_Author=u1.tp_ID and ud.tp_Siteid=u1.tp_SiteID) '+@.join
select @.cmd=@.cmd+' order by ud.tp_ID'
Cheers
Mitch
"j schuetz" wrote:
> Sorry - I can't help you with this one.
> "Maarten Visser" wrote:
> > Judith.
> >
> > Thanks for the information, I understand your solution.
> > But, like you are saying.. ' It is a bit of a guesswork'.
> >
> > I have to build a solution for a customer, where end users have to build
> > reports and the manipulation of data has to be as easy as possible. Because
> > of this, i was thinking to use WSS as a front-end for data input and
> > Reporting Services for generating reports on this data.
> >
> > I'm afraid the given solution would not work in this case, because the user
> > needs to be able to add columns (in WSS) and the report should than easily be
> > adapted by the user to show this column. (It would be best if the report
> > automatically showed the extra column).
> >
> > Could the ADO.Net extensibility of Reporting Services do something here..
> > Would it be possible to build a webservice that gets the information from
> > Sharepoint and let Reporting Services build reports based on the data (rows)
> > that this webservice provides?
> >
> > Tnx.
> > Maarten
> >
> >
> > I'm afraid the given solution would not work in this case, because the user
> > needs to be able to add columns and the report should than easely be adapted
> > by the user to show this column. (It would be best if the report
> > automatecally showed the extra column).
> >
> >
> >
> > "j schuetz" wrote:
> >
> > > Dear Maarten
> > >
> > > Yes, you can generate a report from data of WSS - although it is a bit of a
> > > guesswork. WSS saves all user data in one table only (called UserData). You
> > > will have to filter the right data from this table. In order to know what to
> > > filter, you will have to identify the tp_ListId (uniqueidentifier) of the
> > > WSS-List on which you want to base your report. In order to find this
> > > tp_ListId you have to search first in the Sites table for the tp_ID of the
> > > site. With this ID you can look up the tp_ID of the subweb in the Webs table.
> > > And finally, with the ID of the subweb you can look up the ID of the WSS list
> > > in the Lists table.
> > >
> > > The next step will be to identify the fields for the identified list. There
> > > is only one way: make a print screen from the WSS-list and scroll the list
> > > date in the UserData table in order to find the fields that store the WSS
> > > list data.
> > >
> > > Furthermore, I have found it easiest to define different views on the
> > > UserData table and to work with this views in Reporting Services (not
> > > directly with the UserData table). In the view, you can rename the fields of
> > > the UserData table with aliases that are the names of the columns of the WSS
> > > list.
> > >
> > > A last hint: you can display reports in WSS. Just define a WSS page with a
> > > webpart aerea and give this webpart the reportserver link of the report you
> > > want to show. It works fine.
> > >
> > > Good luck - Judith Schuetz (www.osn.ch)
> > >
> > >
> > > "Maarten Visser" wrote:
> > >
> > > >
> > > > I was wondering if it would be possible to generate reports directly from
> > > > Windows Sharepoint Services (WSS) Lists or from the output of a Webservice?
> > > >
> > > > tnx
> > > >
> > > > -= Maximizing Creativity and Productivity =-|||I was negligent in noting that;
This posting is provided "AS IS" with no warranties, and confers no rights.
Now that the lawyers are happy - have fun... :-)
"Mitch vH [MSFT]" wrote:
> This is a fragment from a sp I wrote for a customer that ought to get you
> started...
> declare @.tbl table(DisplayName varchar(100), ColName varchar(100), List
> varchar(20), ShowFld varchar(100))
> select top 1 @.flds=cast(tp_Fields as varchar(8000))
> from dbo.Lists
> where <-- some selection clause -->
> select @.flds = '<root>'+@.flds+'</root>' -- surround field list with top
> level node, otherwise DOM throws error
> exec sp_xml_preparedocument @.hnd output, @.flds
> insert into @.tbl
> select *
> from openxml(@.hnd, N'/root/Field') with (DisplayName varchar(100), ColName
> varchar(100), List varchar(20), ShowField varchar(100))
> where DisplayName is not null
> exec sp_xml_removedocument @.hnd
> select @.cmd='select "ID"=ud.tp_ID, Created=ud.tp_Created,
> Modified=ud.tp_Modified, Author=u1.tp_Title'
> declare crs cursor for select DisplayName, ColName, List, ShowFld from @.tbl
> open crs
> fetch next from crs into @.name, @.colname, @.list, @.showfld
> select @.joinum=2, @.join=''
> while @.@.fetch_status = 0
> begin
> if @.list is not NULL -- if this is a userlist field, join in the list and
> return the value (not the list id)
> begin
> select @.alias = 'u'+cast(@.joinum as varchar(3))
> select @.join=@.join+' join dbo.'+@.list+' '+@.alias+' on
> (ud.'+@.colname+'='+@.alias+'.tp_ID and ud.tp_Siteid='+@.alias+'.tp_SiteID)'
> select @.cmd=@.cmd+', '''+@.name+'''='+@.alias+'.tp_'+@.showfld
> select @.joinum=@.joinum+1
> end
> else
> begin
> if left(@.colname, 5) = 'ntext' select @.colname = 'cast('+@.colname+' as
> varchar('+cast(@.txtlen as varchar(4))+'))'
> select @.cmd=@.cmd+', '''+@.name+'''='+@.colname
> end
> fetch next from crs into @.name, @.colname, @.list, @.showfld
> end
> close crs
> deallocate crs
> select @.cmd=@.cmd+' from dbo.UserData ud join dbo.UserInfo u1 on
> (ud.tp_Author=u1.tp_ID and ud.tp_Siteid=u1.tp_SiteID) '+@.join
> select @.cmd=@.cmd+' order by ud.tp_ID'
> Cheers
> Mitch
> "j schuetz" wrote:
> > Sorry - I can't help you with this one.
> >
> > "Maarten Visser" wrote:
> >
> > > Judith.
> > >
> > > Thanks for the information, I understand your solution.
> > > But, like you are saying.. ' It is a bit of a guesswork'.
> > >
> > > I have to build a solution for a customer, where end users have to build
> > > reports and the manipulation of data has to be as easy as possible. Because
> > > of this, i was thinking to use WSS as a front-end for data input and
> > > Reporting Services for generating reports on this data.
> > >
> > > I'm afraid the given solution would not work in this case, because the user
> > > needs to be able to add columns (in WSS) and the report should than easily be
> > > adapted by the user to show this column. (It would be best if the report
> > > automatically showed the extra column).
> > >
> > > Could the ADO.Net extensibility of Reporting Services do something here..
> > > Would it be possible to build a webservice that gets the information from
> > > Sharepoint and let Reporting Services build reports based on the data (rows)
> > > that this webservice provides?
> > >
> > > Tnx.
> > > Maarten
> > >
> > >
> > > I'm afraid the given solution would not work in this case, because the user
> > > needs to be able to add columns and the report should than easely be adapted
> > > by the user to show this column. (It would be best if the report
> > > automatecally showed the extra column).
> > >
> > >
> > >
> > > "j schuetz" wrote:
> > >
> > > > Dear Maarten
> > > >
> > > > Yes, you can generate a report from data of WSS - although it is a bit of a
> > > > guesswork. WSS saves all user data in one table only (called UserData). You
> > > > will have to filter the right data from this table. In order to know what to
> > > > filter, you will have to identify the tp_ListId (uniqueidentifier) of the
> > > > WSS-List on which you want to base your report. In order to find this
> > > > tp_ListId you have to search first in the Sites table for the tp_ID of the
> > > > site. With this ID you can look up the tp_ID of the subweb in the Webs table.
> > > > And finally, with the ID of the subweb you can look up the ID of the WSS list
> > > > in the Lists table.
> > > >
> > > > The next step will be to identify the fields for the identified list. There
> > > > is only one way: make a print screen from the WSS-list and scroll the list
> > > > date in the UserData table in order to find the fields that store the WSS
> > > > list data.
> > > >
> > > > Furthermore, I have found it easiest to define different views on the
> > > > UserData table and to work with this views in Reporting Services (not
> > > > directly with the UserData table). In the view, you can rename the fields of
> > > > the UserData table with aliases that are the names of the columns of the WSS
> > > > list.
> > > >
> > > > A last hint: you can display reports in WSS. Just define a WSS page with a
> > > > webpart aerea and give this webpart the reportserver link of the report you
> > > > want to show. It works fine.
> > > >
> > > > Good luck - Judith Schuetz (www.osn.ch)
> > > >
> > > >
> > > > "Maarten Visser" wrote:
> > > >
> > > > >
> > > > > I was wondering if it would be possible to generate reports directly from
> > > > > Windows Sharepoint Services (WSS) Lists or from the output of a Webservice?
> > > > >
> > > > > tnx
> > > > >
> > > > > -= Maximizing Creativity and Productivity =-|||I'm reviewing this... sounds promising:
http://www.teuntostring.net/blog/2006/03/update-reporting-over-sharepoint-lists.html
"Maarten Visser" wrote:
> Judith.
> Thanks for the information, I understand your solution.
> But, like you are saying.. ' It is a bit of a guesswork'.
> I have to build a solution for a customer, where end users have to build
> reports and the manipulation of data has to be as easy as possible. Because
> of this, i was thinking to use WSS as a front-end for data input and
> Reporting Services for generating reports on this data.
> I'm afraid the given solution would not work in this case, because the user
> needs to be able to add columns (in WSS) and the report should than easily be
> adapted by the user to show this column. (It would be best if the report
> automatically showed the extra column).
> Could the ADO.Net extensibility of Reporting Services do something here..
> Would it be possible to build a webservice that gets the information from
> Sharepoint and let Reporting Services build reports based on the data (rows)
> that this webservice provides?
> Tnx.
> Maarten
>
> I'm afraid the given solution would not work in this case, because the user
> needs to be able to add columns and the report should than easely be adapted
> by the user to show this column. (It would be best if the report
> automatecally showed the extra column).
>
> "j schuetz" wrote:
> > Dear Maarten
> >
> > Yes, you can generate a report from data of WSS - although it is a bit of a
> > guesswork. WSS saves all user data in one table only (called UserData). You
> > will have to filter the right data from this table. In order to know what to
> > filter, you will have to identify the tp_ListId (uniqueidentifier) of the
> > WSS-List on which you want to base your report. In order to find this
> > tp_ListId you have to search first in the Sites table for the tp_ID of the
> > site. With this ID you can look up the tp_ID of the subweb in the Webs table.
> > And finally, with the ID of the subweb you can look up the ID of the WSS list
> > in the Lists table.
> >
> > The next step will be to identify the fields for the identified list. There
> > is only one way: make a print screen from the WSS-list and scroll the list
> > date in the UserData table in order to find the fields that store the WSS
> > list data.
> >
> > Furthermore, I have found it easiest to define different views on the
> > UserData table and to work with this views in Reporting Services (not
> > directly with the UserData table). In the view, you can rename the fields of
> > the UserData table with aliases that are the names of the columns of the WSS
> > list.
> >
> > A last hint: you can display reports in WSS. Just define a WSS page with a
> > webpart aerea and give this webpart the reportserver link of the report you
> > want to show. It works fine.
> >
> > Good luck - Judith Schuetz (www.osn.ch)
> >
> >
> > "Maarten Visser" wrote:
> >
> > >
> > > I was wondering if it would be possible to generate reports directly from
> > > Windows Sharepoint Services (WSS) Lists or from the output of a Webservice?
> > >
> > > tnx
> > >
> > > -= Maximizing Creativity and Productivity =-|||Do you know if this will be valid for the new version of WSS(v3)?
"Mitch vH [MSFT]" wrote:
> This is a fragment from a sp I wrote for a customer that ought to get you
> started...
> declare @.tbl table(DisplayName varchar(100), ColName varchar(100), List
> varchar(20), ShowFld varchar(100))
> select top 1 @.flds=cast(tp_Fields as varchar(8000))
> from dbo.Lists
> where <-- some selection clause -->
> select @.flds = '<root>'+@.flds+'</root>' -- surround field list with top
> level node, otherwise DOM throws error
> exec sp_xml_preparedocument @.hnd output, @.flds
> insert into @.tbl
> select *
> from openxml(@.hnd, N'/root/Field') with (DisplayName varchar(100), ColName
> varchar(100), List varchar(20), ShowField varchar(100))
> where DisplayName is not null
> exec sp_xml_removedocument @.hnd
> select @.cmd='select "ID"=ud.tp_ID, Created=ud.tp_Created,
> Modified=ud.tp_Modified, Author=u1.tp_Title'
> declare crs cursor for select DisplayName, ColName, List, ShowFld from @.tbl
> open crs
> fetch next from crs into @.name, @.colname, @.list, @.showfld
> select @.joinum=2, @.join=''
> while @.@.fetch_status = 0
> begin
> if @.list is not NULL -- if this is a userlist field, join in the list and
> return the value (not the list id)
> begin
> select @.alias = 'u'+cast(@.joinum as varchar(3))
> select @.join=@.join+' join dbo.'+@.list+' '+@.alias+' on
> (ud.'+@.colname+'='+@.alias+'.tp_ID and ud.tp_Siteid='+@.alias+'.tp_SiteID)'
> select @.cmd=@.cmd+', '''+@.name+'''='+@.alias+'.tp_'+@.showfld
> select @.joinum=@.joinum+1
> end
> else
> begin
> if left(@.colname, 5) = 'ntext' select @.colname = 'cast('+@.colname+' as
> varchar('+cast(@.txtlen as varchar(4))+'))'
> select @.cmd=@.cmd+', '''+@.name+'''='+@.colname
> end
> fetch next from crs into @.name, @.colname, @.list, @.showfld
> end
> close crs
> deallocate crs
> select @.cmd=@.cmd+' from dbo.UserData ud join dbo.UserInfo u1 on
> (ud.tp_Author=u1.tp_ID and ud.tp_Siteid=u1.tp_SiteID) '+@.join
> select @.cmd=@.cmd+' order by ud.tp_ID'
> Cheers
> Mitch
> "j schuetz" wrote:
> > Sorry - I can't help you with this one.
> >
> > "Maarten Visser" wrote:
> >
> > > Judith.
> > >
> > > Thanks for the information, I understand your solution.
> > > But, like you are saying.. ' It is a bit of a guesswork'.
> > >
> > > I have to build a solution for a customer, where end users have to build
> > > reports and the manipulation of data has to be as easy as possible. Because
> > > of this, i was thinking to use WSS as a front-end for data input and
> > > Reporting Services for generating reports on this data.
> > >
> > > I'm afraid the given solution would not work in this case, because the user
> > > needs to be able to add columns (in WSS) and the report should than easily be
> > > adapted by the user to show this column. (It would be best if the report
> > > automatically showed the extra column).
> > >
> > > Could the ADO.Net extensibility of Reporting Services do something here..
> > > Would it be possible to build a webservice that gets the information from
> > > Sharepoint and let Reporting Services build reports based on the data (rows)
> > > that this webservice provides?
> > >
> > > Tnx.
> > > Maarten
> > >
> > >
> > > I'm afraid the given solution would not work in this case, because the user
> > > needs to be able to add columns and the report should than easely be adapted
> > > by the user to show this column. (It would be best if the report
> > > automatecally showed the extra column).
> > >
> > >
> > >
> > > "j schuetz" wrote:
> > >
> > > > Dear Maarten
> > > >
> > > > Yes, you can generate a report from data of WSS - although it is a bit of a
> > > > guesswork. WSS saves all user data in one table only (called UserData). You
> > > > will have to filter the right data from this table. In order to know what to
> > > > filter, you will have to identify the tp_ListId (uniqueidentifier) of the
> > > > WSS-List on which you want to base your report. In order to find this
> > > > tp_ListId you have to search first in the Sites table for the tp_ID of the
> > > > site. With this ID you can look up the tp_ID of the subweb in the Webs table.
> > > > And finally, with the ID of the subweb you can look up the ID of the WSS list
> > > > in the Lists table.
> > > >
> > > > The next step will be to identify the fields for the identified list. There
> > > > is only one way: make a print screen from the WSS-list and scroll the list
> > > > date in the UserData table in order to find the fields that store the WSS
> > > > list data.
> > > >
> > > > Furthermore, I have found it easiest to define different views on the
> > > > UserData table and to work with this views in Reporting Services (not
> > > > directly with the UserData table). In the view, you can rename the fields of
> > > > the UserData table with aliases that are the names of the columns of the WSS
> > > > list.
> > > >
> > > > A last hint: you can display reports in WSS. Just define a WSS page with a
> > > > webpart aerea and give this webpart the reportserver link of the report you
> > > > want to show. It works fine.
> > > >
> > > > Good luck - Judith Schuetz (www.osn.ch)
> > > >
> > > >
> > > > "Maarten Visser" wrote:
> > > >
> > > > >
> > > > > I was wondering if it would be possible to generate reports directly from
> > > > > Windows Sharepoint Services (WSS) Lists or from the output of a Webservice?
> > > > >
> > > > > tnx
> > > > >
> > > > > -= Maximizing Creativity and Productivity =-|||Hi,
In case you are interested, we have developped a Reporting Services Data
Extension for SharePoint. An evaluation version is available on our web site
at http://www.enesyssoftware.com/Default.aspx?tabid=56.
If you would like to develop it from scratch, the article from Teun Duynstee
is surely the way to go.
--
Frederic Latour
Enesys
http://www.enesyssoftware.com
"Bob C." wrote:
> I'm reviewing this... sounds promising:
> http://www.teuntostring.net/blog/2006/03/update-reporting-over-sharepoint-lists.html
> "Maarten Visser" wrote:
> > Judith.
> >
> > Thanks for the information, I understand your solution.
> > But, like you are saying.. ' It is a bit of a guesswork'.
> >
> > I have to build a solution for a customer, where end users have to build
> > reports and the manipulation of data has to be as easy as possible. Because
> > of this, i was thinking to use WSS as a front-end for data input and
> > Reporting Services for generating reports on this data.
> >
> > I'm afraid the given solution would not work in this case, because the user
> > needs to be able to add columns (in WSS) and the report should than easily be
> > adapted by the user to show this column. (It would be best if the report
> > automatically showed the extra column).
> >
> > Could the ADO.Net extensibility of Reporting Services do something here..
> > Would it be possible to build a webservice that gets the information from
> > Sharepoint and let Reporting Services build reports based on the data (rows)
> > that this webservice provides?
> >
> > Tnx.
> > Maarten
> >
> >
> > I'm afraid the given solution would not work in this case, because the user
> > needs to be able to add columns and the report should than easely be adapted
> > by the user to show this column. (It would be best if the report
> > automatecally showed the extra column).
> >
> >
> >
> > "j schuetz" wrote:
> >
> > > Dear Maarten
> > >
> > > Yes, you can generate a report from data of WSS - although it is a bit of a
> > > guesswork. WSS saves all user data in one table only (called UserData). You
> > > will have to filter the right data from this table. In order to know what to
> > > filter, you will have to identify the tp_ListId (uniqueidentifier) of the
> > > WSS-List on which you want to base your report. In order to find this
> > > tp_ListId you have to search first in the Sites table for the tp_ID of the
> > > site. With this ID you can look up the tp_ID of the subweb in the Webs table.
> > > And finally, with the ID of the subweb you can look up the ID of the WSS list
> > > in the Lists table.
> > >
> > > The next step will be to identify the fields for the identified list. There
> > > is only one way: make a print screen from the WSS-list and scroll the list
> > > date in the UserData table in order to find the fields that store the WSS
> > > list data.
> > >
> > > Furthermore, I have found it easiest to define different views on the
> > > UserData table and to work with this views in Reporting Services (not
> > > directly with the UserData table). In the view, you can rename the fields of
> > > the UserData table with aliases that are the names of the columns of the WSS
> > > list.
> > >
> > > A last hint: you can display reports in WSS. Just define a WSS page with a
> > > webpart aerea and give this webpart the reportserver link of the report you
> > > want to show. It works fine.
> > >
> > > Good luck - Judith Schuetz (www.osn.ch)
> > >
> > >
> > > "Maarten Visser" wrote:
> > >
> > > >
> > > > I was wondering if it would be possible to generate reports directly from
> > > > Windows Sharepoint Services (WSS) Lists or from the output of a Webservice?
> > > >
> > > > tnx
> > > >
> > > > -= Maximizing Creativity and Productivity =-

Reporting NIGHTMARE

Hello all,
I have a report that has a list of citys on the left: eg.
Vancouver
Melbourne
Auckland
New York
And the body of the report lists sales in Year to Date and Period to Date
which is set by a parameter.
e.g
City PTD YTD
Vancouver 10000 30000
Melbourne 5000 12000
Auckland 2000 3000
New York 23000 50000
Total 40000 95000
The Period to date data comes from one dataset that is configured to use the
parameter... the Year to date data comes from another dataset that doesn't
use the parameter.
However the cities come from another dataset... What ends up happening is I
get the total of PTD in every city...
eg.
City PTD YTD
Vancouver 40000 30000
Melbourne 40000 12000
Auckland 40000 3000
New York 40000 50000
Total 40000
Rather than the correct amounts. What can I do to fix this?
ThanksHave you considered basing the dataset on a view which joins the City, PTD
and YTD columns together?
Alan
"AshVsAOD" <.> wrote in message news:#1LMFS$VEHA.808@.tk2msftngp13.phx.gbl...
> Hello all,
> I have a report that has a list of citys on the left: eg.
> Vancouver
> Melbourne
> Auckland
> New York
> And the body of the report lists sales in Year to Date and Period to Date
> which is set by a parameter.
> e.g
> City PTD YTD
> Vancouver 10000 30000
> Melbourne 5000 12000
> Auckland 2000 3000
> New York 23000 50000
> Total 40000 95000
> The Period to date data comes from one dataset that is configured to use
the
> parameter... the Year to date data comes from another dataset that doesn't
> use the parameter.
> However the cities come from another dataset... What ends up happening is
I
> get the total of PTD in every city...
> eg.
> City PTD YTD
> Vancouver 40000 30000
> Melbourne 40000 12000
> Auckland 40000 3000
> New York 40000 50000
> Total 40000
> Rather than the correct amounts. What can I do to fix this?
> Thanks
>|||Yes I have considered that, However Period to Date relies on a Parameter
from RS so I can't see how this would work...
Anyone else got any ideas?
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:OLs24ZAWEHA.1164@.tk2msftngp13.phx.gbl...
> Have you considered basing the dataset on a view which joins the City, PTD
> and YTD columns together?
> Alan
> "AshVsAOD" <.> wrote in message
news:#1LMFS$VEHA.808@.tk2msftngp13.phx.gbl...
> > Hello all,
> >
> > I have a report that has a list of citys on the left: eg.
> >
> > Vancouver
> > Melbourne
> > Auckland
> > New York
> >
> > And the body of the report lists sales in Year to Date and Period to
Date
> > which is set by a parameter.
> > e.g
> >
> > City PTD YTD
> > Vancouver 10000 30000
> > Melbourne 5000 12000
> > Auckland 2000 3000
> > New York 23000 50000
> > Total 40000 95000
> >
> > The Period to date data comes from one dataset that is configured to use
> the
> > parameter... the Year to date data comes from another dataset that
doesn't
> > use the parameter.
> >
> > However the cities come from another dataset... What ends up happening
is
> I
> > get the total of PTD in every city...
> > eg.
> > City PTD YTD
> > Vancouver 40000 30000
> > Melbourne 40000 12000
> > Auckland 40000 3000
> > New York 40000 50000
> > Total 40000
> >
> > Rather than the correct amounts. What can I do to fix this?
> >
> > Thanks
> >
> >
>|||Reporting Services does not join datasets together. For the second data set,
you are most likely getting First(), the default aggregate expression. You
need to merge these together before they get to reporting services. You can
take a report parameter and bind it into a data set.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"AshVsAOD" <.> wrote in message
news:%23oIOXVBWEHA.808@.tk2msftngp13.phx.gbl...
> Yes I have considered that, However Period to Date relies on a Parameter
> from RS so I can't see how this would work...
> Anyone else got any ideas?
> "Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
> news:OLs24ZAWEHA.1164@.tk2msftngp13.phx.gbl...
> > Have you considered basing the dataset on a view which joins the City,
PTD
> > and YTD columns together?
> >
> > Alan
> >
> > "AshVsAOD" <.> wrote in message
> news:#1LMFS$VEHA.808@.tk2msftngp13.phx.gbl...
> > > Hello all,
> > >
> > > I have a report that has a list of citys on the left: eg.
> > >
> > > Vancouver
> > > Melbourne
> > > Auckland
> > > New York
> > >
> > > And the body of the report lists sales in Year to Date and Period to
> Date
> > > which is set by a parameter.
> > > e.g
> > >
> > > City PTD YTD
> > > Vancouver 10000 30000
> > > Melbourne 5000 12000
> > > Auckland 2000 3000
> > > New York 23000 50000
> > > Total 40000 95000
> > >
> > > The Period to date data comes from one dataset that is configured to
use
> > the
> > > parameter... the Year to date data comes from another dataset that
> doesn't
> > > use the parameter.
> > >
> > > However the cities come from another dataset... What ends up
happening
> is
> > I
> > > get the total of PTD in every city...
> > > eg.
> > > City PTD YTD
> > > Vancouver 40000 30000
> > > Melbourne 40000 12000
> > > Auckland 40000 3000
> > > New York 40000 50000
> > > Total 40000
> > >
> > > Rather than the correct amounts. What can I do to fix this?
> > >
> > > Thanks
> > >
> > >
> >
> >
>|||Brian,
Thanks for the advice, I was worried that would be the answer.
Ash
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:%23gddTHCWEHA.1380@.TK2MSFTNGP09.phx.gbl...
> Reporting Services does not join datasets together. For the second data
set,
> you are most likely getting First(), the default aggregate expression. You
> need to merge these together before they get to reporting services. You
can
> take a report parameter and bind it into a data set.
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "AshVsAOD" <.> wrote in message
> news:%23oIOXVBWEHA.808@.tk2msftngp13.phx.gbl...
> > Yes I have considered that, However Period to Date relies on a Parameter
> > from RS so I can't see how this would work...
> >
> > Anyone else got any ideas?
> > "Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
> > news:OLs24ZAWEHA.1164@.tk2msftngp13.phx.gbl...
> > > Have you considered basing the dataset on a view which joins the City,
> PTD
> > > and YTD columns together?
> > >
> > > Alan
> > >
> > > "AshVsAOD" <.> wrote in message
> > news:#1LMFS$VEHA.808@.tk2msftngp13.phx.gbl...
> > > > Hello all,
> > > >
> > > > I have a report that has a list of citys on the left: eg.
> > > >
> > > > Vancouver
> > > > Melbourne
> > > > Auckland
> > > > New York
> > > >
> > > > And the body of the report lists sales in Year to Date and Period to
> > Date
> > > > which is set by a parameter.
> > > > e.g
> > > >
> > > > City PTD YTD
> > > > Vancouver 10000 30000
> > > > Melbourne 5000 12000
> > > > Auckland 2000 3000
> > > > New York 23000 50000
> > > > Total 40000 95000
> > > >
> > > > The Period to date data comes from one dataset that is configured to
> use
> > > the
> > > > parameter... the Year to date data comes from another dataset that
> > doesn't
> > > > use the parameter.
> > > >
> > > > However the cities come from another dataset... What ends up
> happening
> > is
> > > I
> > > > get the total of PTD in every city...
> > > > eg.
> > > > City PTD YTD
> > > > Vancouver 40000 30000
> > > > Melbourne 40000 12000
> > > > Auckland 40000 3000
> > > > New York 40000 50000
> > > > Total 40000
> > > >
> > > > Rather than the correct amounts. What can I do to fix this?
> > > >
> > > > Thanks
> > > >
> > > >
> > >
> > >
> >
> >
>|||Maybe I'm missing something.
I developed a report several years ago that shows performance for a list of
stocks with columns for Date, Stock,Weekt TD, MonthTD, QuarterTD, Yeat TD,
all side by side coumns.
The columns are each calculated in separate tables and then joined together
in a view as one table on Date and Stock..
It sounds like that's the type of table you want to report on.
Alan
"AshVsAOD" <.> wrote in message news:#oIOXVBWEHA.808@.tk2msftngp13.phx.gbl...
> Yes I have considered that, However Period to Date relies on a Parameter
> from RS so I can't see how this would work...
> Anyone else got any ideas?
> "Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
> news:OLs24ZAWEHA.1164@.tk2msftngp13.phx.gbl...
> > Have you considered basing the dataset on a view which joins the City,
PTD
> > and YTD columns together?
> >
> > Alan
> >
> > "AshVsAOD" <.> wrote in message
> news:#1LMFS$VEHA.808@.tk2msftngp13.phx.gbl...
> > > Hello all,
> > >
> > > I have a report that has a list of citys on the left: eg.
> > >
> > > Vancouver
> > > Melbourne
> > > Auckland
> > > New York
> > >
> > > And the body of the report lists sales in Year to Date and Period to
> Date
> > > which is set by a parameter.
> > > e.g
> > >
> > > City PTD YTD
> > > Vancouver 10000 30000
> > > Melbourne 5000 12000
> > > Auckland 2000 3000
> > > New York 23000 50000
> > > Total 40000 95000
> > >
> > > The Period to date data comes from one dataset that is configured to
use
> > the
> > > parameter... the Year to date data comes from another dataset that
> doesn't
> > > use the parameter.
> > >
> > > However the cities come from another dataset... What ends up
happening
> is
> > I
> > > get the total of PTD in every city...
> > > eg.
> > > City PTD YTD
> > > Vancouver 40000 30000
> > > Melbourne 40000 12000
> > > Auckland 40000 3000
> > > New York 40000 50000
> > > Total 40000
> > >
> > > Rather than the correct amounts. What can I do to fix this?
> > >
> > > Thanks
> > >
> > >
> >
> >
>|||Alan,
That does sound like what I want... But...
I need the user's to be able to enter a Parameter that Specifies the Period
to Date and Year to Date.
Example:
To Date: 16/06/04 From Date 01/06/04
So Period to date would be the data between 01/06 and 16/06
Year to date has to be the entire year.
Does this make sense?
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:OFZD5vDWEHA.3016@.tk2msftngp13.phx.gbl...
> Maybe I'm missing something.
> I developed a report several years ago that shows performance for a list
of
> stocks with columns for Date, Stock,Weekt TD, MonthTD, QuarterTD, Yeat TD,
> all side by side coumns.
> The columns are each calculated in separate tables and then joined
together
> in a view as one table on Date and Stock..
> It sounds like that's the type of table you want to report on.
> Alan
>
>
>
> "AshVsAOD" <.> wrote in message
news:#oIOXVBWEHA.808@.tk2msftngp13.phx.gbl...
> > Yes I have considered that, However Period to Date relies on a Parameter
> > from RS so I can't see how this would work...
> >
> > Anyone else got any ideas?
> > "Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
> > news:OLs24ZAWEHA.1164@.tk2msftngp13.phx.gbl...
> > > Have you considered basing the dataset on a view which joins the City,
> PTD
> > > and YTD columns together?
> > >
> > > Alan
> > >
> > > "AshVsAOD" <.> wrote in message
> > news:#1LMFS$VEHA.808@.tk2msftngp13.phx.gbl...
> > > > Hello all,
> > > >
> > > > I have a report that has a list of citys on the left: eg.
> > > >
> > > > Vancouver
> > > > Melbourne
> > > > Auckland
> > > > New York
> > > >
> > > > And the body of the report lists sales in Year to Date and Period to
> > Date
> > > > which is set by a parameter.
> > > > e.g
> > > >
> > > > City PTD YTD
> > > > Vancouver 10000 30000
> > > > Melbourne 5000 12000
> > > > Auckland 2000 3000
> > > > New York 23000 50000
> > > > Total 40000 95000
> > > >
> > > > The Period to date data comes from one dataset that is configured to
> use
> > > the
> > > > parameter... the Year to date data comes from another dataset that
> > doesn't
> > > > use the parameter.
> > > >
> > > > However the cities come from another dataset... What ends up
> happening
> > is
> > > I
> > > > get the total of PTD in every city...
> > > > eg.
> > > > City PTD YTD
> > > > Vancouver 40000 30000
> > > > Melbourne 40000 12000
> > > > Auckland 40000 3000
> > > > New York 40000 50000
> > > > Total 40000
> > > >
> > > > Rather than the correct amounts. What can I do to fix this?
> > > >
> > > > Thanks
> > > >
> > > >
> > >
> > >
> >
> >
>|||"AshVsAOD" <.> wrote in message
news:udJBVqKWEHA.2340@.TK2MSFTNGP09.phx.gbl...
> Alan,
> That does sound like what I want... But...
> I need the user's to be able to enter a Parameter that Specifies the
Period
> to Date and Year to Date.
> Example:
> To Date: 16/06/04 From Date 01/06/04
> So Period to date would be the data between 01/06 and 16/06
> Year to date has to be the entire year.
>
Have a parameter each for beginning day/month, ending day/month, and year,
used to set a BETWEEN date range in a SQL statement. Is it as simple as
that or are you trying to do something more complex?
Or just two parameterts for beeginning and ending date.
Alan|||I am positive you are on to something... but I don't quite understand what
you mean...
How can I use parameters in T-SQL... I understand them in RS but in SQL I am
quite new.
Thanks!
"Alan Z. Scharf" <ascharf@.grapevines.com> wrote in message
news:e9bns1LWEHA.1048@.tk2msftngp13.phx.gbl...
> "AshVsAOD" <.> wrote in message
> news:udJBVqKWEHA.2340@.TK2MSFTNGP09.phx.gbl...
> > Alan,
> >
> > That does sound like what I want... But...
> >
> > I need the user's to be able to enter a Parameter that Specifies the
> Period
> > to Date and Year to Date.
> >
> > Example:
> >
> > To Date: 16/06/04 From Date 01/06/04
> >
> > So Period to date would be the data between 01/06 and 16/06
> > Year to date has to be the entire year.
> >
> Have a parameter each for beginning day/month, ending day/month, and year,
> used to set a BETWEEN date range in a SQL statement. Is it as simple as
> that or are you trying to do something more complex?
> Or just two parameterts for beeginning and ending date.
> Alan
>|||> How can I use parameters in T-SQL... I understand them in RS but in SQL I
am
> quite new.
Now, after all the messages, the real issue is discovered. You have put the
cart before the horse.
1. Reports are only a window on data.
2. Hence, you should put Rpt Svcs aside and concentrate on the data.
3. First you have to get a table with your required PTD and YTD columns
side-by-side with cols for Date and City.
4. As for parameters in T-SQL, it's up to you to learn this very elementary
subject. There are so many books as well as the excellent online
documentation that comes with SQLServer.
5. Parameters aren't even relevant until you get the table together.
Alan|||This will work.
Make an outer table
Here's your header row:
----
| City | PDT | YDT
|
----
In your detail row:
Column 1 (CITY) = User the table dataset like you are now to get the
city
Column 2 (PDT) = Make this a sub report. Pass in your date range
values and your city id as your sub report parameters.
Column 3 (YDT) = Make this a sub report. Pass in your date range
values and your city id as your sub report parameters.
To make your subreport just make another report and be sure to make it
the same page width as your table column.
The other city dataset will loop through each subreport and give you
the values you want.
"AshVsAOD" <.> wrote in message news:<#1LMFS$VEHA.808@.tk2msftngp13.phx.gbl>...
> Hello all,
> I have a report that has a list of citys on the left: eg.
> Vancouver
> Melbourne
> Auckland
> New York
> And the body of the report lists sales in Year to Date and Period to Date
> which is set by a parameter.
> e.g
> City PTD YTD
> Vancouver 10000 30000
> Melbourne 5000 12000
> Auckland 2000 3000
> New York 23000 50000
> Total 40000 95000
> The Period to date data comes from one dataset that is configured to use the
> parameter... the Year to date data comes from another dataset that doesn't
> use the parameter.
> However the cities come from another dataset... What ends up happening is I
> get the total of PTD in every city...
> eg.
> City PTD YTD
> Vancouver 40000 30000
> Melbourne 40000 12000
> Auckland 40000 3000
> New York 40000 50000
> Total 40000
> Rather than the correct amounts. What can I do to fix this?
> Thanks