Report Designer best practice

Hi
When creating a best practice enterprise reporting solution, would you
normally create report models first, use these as datasources in Report
Designer, andd create datasets on top of these? Or would you create datasets
using SQL queries directly from Report Designer?
What is the preferred/best practice way of creating the datasets ?
Kind Regards,
Torkild HagenOn Mar 22, 2:15 am, thagen <tha...@.discussions.microsoft.com> wrote:
> Hi
> When creating a best practice enterprise reporting solution, would you
> normally create report models first, use these as datasources in Report
> Designer, andd create datasets on top of these? Or would you create datasets
> using SQL queries directly from Report Designer?
> What is the preferred/best practice way of creating the datasets ?
> Kind Regards,
> Torkild Hagen
My preference is to create stored procedures first and then create
datasets based on those stored procedures in the Report Designer, etc;
since, stored procedures generally have better performance. Just my
thoughts. Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||You can use report model as Datasource but this is meant for RB, for normal
reports, it is advisable to create stored proc and then create dataset. For
queries returning very small no of records, direct query will be good. As a
best Practise use shared datasource as far as possible, so that later if you
want to change any thing related to data source can be changed at one place
instead of many places.
Amarnath, MCTS
"thagen" wrote:
> Hi
> When creating a best practice enterprise reporting solution, would you
> normally create report models first, use these as datasources in Report
> Designer, andd create datasets on top of these? Or would you create datasets
> using SQL queries directly from Report Designer?
> What is the preferred/best practice way of creating the datasets ?
> Kind Regards,
> Torkild Hagen

report designer : BIDS seems to hang after querry

hi,

I'm creating my first report.
I do "new project" > "report server project".
Thereafter I create my shared datasource.
Thereafter I try to add a new report.
It ask to enter my querry.

I've built up my querry previously with the querry analyser in SSMS, and there it runs quite fast.

But when i copy-paste the querry in the reporting designer, and i choose to preview it, it takes more then 20 minutes.

however, my querry it quite big, but i've written querries ten times larger in my life.
(it's about 50lines: 10 querries, union-ed together and in each one i have 4 innerjoins.... all tables are even almost empty, so it's not that indexes are missing or whatever)...

is it known that in the report designer querries are much more slower?
one of the ten querry's that are union-ed together, takes 20seconds, (while in SSMS it takes nearly a second).

if i add a union to it, to add another set of data, it seems to hang already....

is it maybe bug?

can anyone try it also to create a report with a querry with 'union' in it?

the querry looks like this:

select c1,c2,c3,c4,c5
from t1
inner join t2 on t1.cX = t2.cX
inner join t3 on t2.cX = t3.cX
inner join t4 on t3.cX = t4.cX
inner join t5 on t4.cX = t5.cX
where t1.cY = 'conditionA'

union

select c1,c2,c3,c4,c5
from t1
inner join t2 on t1.cX = t2.cX
inner join t3 on t2.cX = t3.cX
inner join t4 on t3.cX = t4.cX
inner join t5 on t4.cX = t5.cX
where t1.cY = 'conditionB'|||it is solved, but i don't understand it....
in fact the querry above was extended with some other stuff:

select c1,c2,c3,c4,c5, 1 ext1, '' ext2, '' ext3
from t1
inner join t2 on t1.cX = t2.cX
inner join t3 on t2.cX = t3.cX
inner join t4 on t3.cX = t4.cX
inner join t5 on t4.cX = t5.cX
where t1.cY = 'conditionA'

union

select c1,c2,c3,c4,c5, '' ext1, 1 ext2, '' ext3
from t1
inner join t2 on t1.cX = t2.cX
inner join t3 on t2.cX = t3.cX
inner join t4 on t3.cX = t4.cX
inner join t5 on t4.cX = t5.cX
where t1.cY = 'conditionB'

now, when i replace the empty strings ('') in the select-parts with NULL then it goes much faster !!!!

can someone explain this performance issue?
(it is also very strange that i have this problem in BIDS and not in SSMS)
|||can someone try this out also?

and eventually give an explanation, if any?

Report Designer - deploying to a specific directory

Hi - When creating a report without using the report wizard, how does one
specify the directory on the RS server that each SPECIFIC report is to be
deployed to? I know that the report project has a "Deployment Folder"
property but that is project-wide. We want to do it for each specific
report. Do I need separate projects for multiple folders?
thanksUnfortunately, you can only designate one folder per solution currently. A
possible alternative is to use an rs script to publish them to desired
folders. See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_sampleapps_v1_1apd.asp
for an example.
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"ISGADMIN" <isgadmin@.newsgroup.nospam> wrote in message
news:BB9F0755-7F44-4507-B86F-B8691615BE59@.microsoft.com...
> Hi - When creating a report without using the report wizard, how does one
> specify the directory on the RS server that each SPECIFIC report is to be
> deployed to? I know that the report project has a "Deployment Folder"
> property but that is project-wide. We want to do it for each specific
> report. Do I need separate projects for multiple folders?
> thanks|||Just a slight correction, you specify per project, not per solution.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"John H. Miller" <jhmiller@.online.microsoft.com> wrote in message
news:41813be2$1@.news.microsoft.com...
> Unfortunately, you can only designate one folder per solution currently.
A
> possible alternative is to use an rs script to publish them to desired
> folders. See
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_sampleapps_v1_1apd.asp
> for an example.
> --
> -- "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> jhmiller@.online.microsoft.com
> "ISGADMIN" <isgadmin@.newsgroup.nospam> wrote in message
> news:BB9F0755-7F44-4507-B86F-B8691615BE59@.microsoft.com...
> > Hi - When creating a report without using the report wizard, how does
one
> > specify the directory on the RS server that each SPECIFIC report is to
be
> > deployed to? I know that the report project has a "Deployment Folder"
> > property but that is project-wide. We want to do it for each specific
> > report. Do I need separate projects for multiple folders?
> >
> > thanks
>|||Yes - thanks Bruce! It is per project.
--
-- "This posting is provided 'AS IS' with no warranties, and confers no
rights."
jhmiller@.online.microsoft.com
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:usX7J6RvEHA.1984@.TK2MSFTNGP14.phx.gbl...
> Just a slight correction, you specify per project, not per solution.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "John H. Miller" <jhmiller@.online.microsoft.com> wrote in message
> news:41813be2$1@.news.microsoft.com...
>> Unfortunately, you can only designate one folder per solution currently.
> A
>> possible alternative is to use an rs script to publish them to desired
>> folders. See
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_sampleapps_v1_1apd.asp
>> for an example.
>> --
>> -- "This posting is provided 'AS IS' with no warranties, and confers
>> no
>> rights."
>> jhmiller@.online.microsoft.com
>> "ISGADMIN" <isgadmin@.newsgroup.nospam> wrote in message
>> news:BB9F0755-7F44-4507-B86F-B8691615BE59@.microsoft.com...
>> > Hi - When creating a report without using the report wizard, how does
> one
>> > specify the directory on the RS server that each SPECIFIC report is to
> be
>> > deployed to? I know that the report project has a "Deployment Folder"
>> > property but that is project-wide. We want to do it for each specific
>> > report. Do I need separate projects for multiple folders?
>> >
>> > thanks
>>
>|||Hi John, thanks much for the info.
Matt
"John H. Miller" wrote:
> Unfortunately, you can only designate one folder per solution currently. A
> possible alternative is to use an rs script to publish them to desired
> folders. See
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_sampleapps_v1_1apd.asp
> for an example.
> --
> -- "This posting is provided 'AS IS' with no warranties, and confers no
> rights."
> jhmiller@.online.microsoft.com
> "ISGADMIN" <isgadmin@.newsgroup.nospam> wrote in message
> news:BB9F0755-7F44-4507-B86F-B8691615BE59@.microsoft.com...
> > Hi - When creating a report without using the report wizard, how does one
> > specify the directory on the RS server that each SPECIFIC report is to be
> > deployed to? I know that the report project has a "Deployment Folder"
> > property but that is project-wide. We want to do it for each specific
> > report. Do I need separate projects for multiple folders?
> >
> > thanks
>
>

Report Builder question

Hello there. We are having some problems in Reporting Services.

First off, we are creating a 'Dashboard' project (using Visual Studio 2005, Dundas Charts and SQL Server) that will display charts showing how their company is doing. On the Dashboard, we are also using the Report Builder so the users can create their own reports and see exactly what they want to see.

Before we had a problem with every company being able to access all databases (we wanted to limit it so that their company could ONLY see their data). We fixed that by adding a parameter to the end of the report builder path in the asp .net code.

What we want to do now is limit what directory they can save their reports too. Right now, they are able to save the report in any companies directory.

Remember, this is in Reporting Services, so it's a little different than Windows folder permissions.

If this isn't clear enough, let me know and i'll try to explain it a little clearer.

Thanks

Folders can be security-controlled using Windows or custom security. If the end user doesn't have rights to a folder, they won't eve see that folder.|||Are you saying that you can use the Windows permissions inside of the SQL Server Reporting Services? I've looked around and have not found anything on how to limit users to specific folders inside of RS.|||Open Report Manager and navigate to any folder. Go to the folder properties and then Security. You can define security policies for Windows users and/or groups.

Report Builder Query Timeout?

Is their a way when creating a Report Builder model to set a query timeout? Similar to what can be done when creating a Reporting DataSet in VS.NET during report development.

My ultimate goal is to be able to limit ad-hoc report users running Report Builder from waisting resources on my SQL instance. This has nothing to do with a report being published to a report server and setting a reports execution time limit.

Unfortunately, no, you cannot set the timeout of a Report Builder query. You can specify a query timeout in Report Designer so you could build the report in Report Builder and then import it into Report Designer. But this doesn't get what you want, which is a limit on end user queries.

This feature is under consideration for the next release.

|||

I'm trying to build a Report Model for a Data Source View that has a table in it with a little over 1 billion records. When I try to create the new Report Model, the process fails while trying to get a row count on the 1billion+ table:

An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Command:
SELECT COUNT(*) FROM [dbo].[Sor1_PageHist]

This timeout is at 5 minutes. Where is the query timeout setting for building the model, or is this like the problem above that there isn't a location to set the query timeout?

Any information would be great!

|||

I am getting the same error message.

"An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

How can I resolve this timeout issue?

|||"You can specify a query timeout in Report Designer"

How?
|||

I am setting the Querytimeout parameter in rsReportServer.config file to 10 seconds and timeout parameter to 10 sec in Dataset for query tab in Report Designer for particular ".rdl" file and expecting to get the query timeout error in 10 seconds from Report server but it takes more time to timeout around 40-50 or more seconds. Can u please tell me where I am going wrong ?

Report Builder Query Timeout?

Is their a way when creating a Report Builder model to set a query timeout? Similar to what can be done when creating a Reporting DataSet in VS.NET during report development.

My ultimate goal is to be able to limit ad-hoc report users running Report Builder from waisting resources on my SQL instance. This has nothing to do with a report being published to a report server and setting a reports execution time limit.

Unfortunately, no, you cannot set the timeout of a Report Builder query. You can specify a query timeout in Report Designer so you could build the report in Report Builder and then import it into Report Designer. But this doesn't get what you want, which is a limit on end user queries.

This feature is under consideration for the next release.

|||

I'm trying to build a Report Model for a Data Source View that has a table in it with a little over 1 billion records. When I try to create the new Report Model, the process fails while trying to get a row count on the 1billion+ table:

An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Command:
SELECT COUNT(*) FROM [dbo].[Sor1_PageHist]

This timeout is at 5 minutes. Where is the query timeout setting for building the model, or is this like the problem above that there isn't a location to set the query timeout?

Any information would be great!

|||

I am getting the same error message.

"An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

How can I resolve this timeout issue?

|||"You can specify a query timeout in Report Designer"

How?
|||I am setting the Querytimeout parameter in rsReportServer.config file to 10 seconds and timeout parameter to 10 sec in Dataset for query tab in Report Designer for particular ".rdl" file and expecting to get the query timeout error in 10 seconds from Report server but it takes more time to timeout around 40-50 or more seconds. Can u please tell me where I am going wrong ?

Report Builder Query Timeout?

Is their a way when creating a Report Builder model to set a query timeout? Similar to what can be done when creating a Reporting DataSet in VS.NET during report development.

My ultimate goal is to be able to limit ad-hoc report users running Report Builder from waisting resources on my SQL instance. This has nothing to do with a report being published to a report server and setting a reports execution time limit.

Unfortunately, no, you cannot set the timeout of a Report Builder query. You can specify a query timeout in Report Designer so you could build the report in Report Builder and then import it into Report Designer. But this doesn't get what you want, which is a limit on end user queries.

This feature is under consideration for the next release.

|||

I'm trying to build a Report Model for a Data Source View that has a table in it with a little over 1 billion records. When I try to create the new Report Model, the process fails while trying to get a row count on the 1billion+ table:

An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Command:
SELECT COUNT(*) FROM [dbo].[Sor1_PageHist]

This timeout is at 5 minutes. Where is the query timeout setting for building the model, or is this like the problem above that there isn't a location to set the query timeout?

Any information would be great!

|||

I am getting the same error message.

"An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

How can I resolve this timeout issue?

|||"You can specify a query timeout in Report Designer"

How?
|||

I am setting the Querytimeout parameter in rsReportServer.config file to 10 seconds and timeout parameter to 10 sec in Dataset for query tab in Report Designer for particular ".rdl" file and expecting to get the query timeout error in 10 seconds from Report server but it takes more time to timeout around 40-50 or more seconds. Can u please tell me where I am going wrong ?

Report Builder Query Timeout?

Is their a way when creating a Report Builder model to set a query timeout? Similar to what can be done when creating a Reporting DataSet in VS.NET during report development.

My ultimate goal is to be able to limit ad-hoc report users running Report Builder from waisting resources on my SQL instance. This has nothing to do with a report being published to a report server and setting a reports execution time limit.

Unfortunately, no, you cannot set the timeout of a Report Builder query. You can specify a query timeout in Report Designer so you could build the report in Report Builder and then import it into Report Designer. But this doesn't get what you want, which is a limit on end user queries.

This feature is under consideration for the next release.

|||

I'm trying to build a Report Model for a Data Source View that has a table in it with a little over 1 billion records. When I try to create the new Report Model, the process fails while trying to get a row count on the 1billion+ table:

An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Command:
SELECT COUNT(*) FROM [dbo].[Sor1_PageHist]

This timeout is at 5 minutes. Where is the query timeout setting for building the model, or is this like the problem above that there isn't a location to set the query timeout?

Any information would be great!

|||

I am getting the same error message.

"An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

How can I resolve this timeout issue?

|||"You can specify a query timeout in Report Designer"

How?
|||

I am setting the Querytimeout parameter in rsReportServer.config file to 10 seconds and timeout parameter to 10 sec in Dataset for query tab in Report Designer for particular ".rdl" file and expecting to get the query timeout error in 10 seconds from Report server but it takes more time to timeout around 40-50 or more seconds. Can u please tell me where I am going wrong ?

Report Builder Query Timeout?

Is their a way when creating a Report Builder model to set a query timeout? Similar to what can be done when creating a Reporting DataSet in VS.NET during report development.

My ultimate goal is to be able to limit ad-hoc report users running Report Builder from waisting resources on my SQL instance. This has nothing to do with a report being published to a report server and setting a reports execution time limit.

Unfortunately, no, you cannot set the timeout of a Report Builder query. You can specify a query timeout in Report Designer so you could build the report in Report Builder and then import it into Report Designer. But this doesn't get what you want, which is a limit on end user queries.

This feature is under consideration for the next release.

|||

I'm trying to build a Report Model for a Data Source View that has a table in it with a little over 1 billion records. When I try to create the new Report Model, the process fails while trying to get a row count on the 1billion+ table:

An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Command:
SELECT COUNT(*) FROM [dbo].[Sor1_PageHist]

This timeout is at 5 minutes. Where is the query timeout setting for building the model, or is this like the problem above that there isn't a location to set the query timeout?

Any information would be great!

|||

I am getting the same error message.

"An error occurred while executing a command.
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

How can I resolve this timeout issue?

|||"You can specify a query timeout in Report Designer"

How?
|||

I am setting the Querytimeout parameter in rsReportServer.config file to 10 seconds and timeout parameter to 10 sec in Dataset for query tab in Report Designer for particular ".rdl" file and expecting to get the query timeout error in 10 seconds from Report server but it takes more time to timeout around 40-50 or more seconds. Can u please tell me where I am going wrong ?sql

report builder cant join views?

Hi friends
i have quick question with report builder utility .am using VS2005 and Sql 2005.
When creating report model solution ,when adding field names from a table it loads fields names as it is ,right ? so to make end users life easier i created
Views with lengthy field names.

the issue is ,i'll start with an example ,i've 2 views (ParentView and ChildView) and the tables used by these views have foreign key relation ship .when i use these 2 entities in report builder as soon as i add a column from one view (say ParentView) the other view disappears from list !! I assume Report builder looks FKey to link 2 entities and since am using views which will not have any fkeys on them it removes other entities from list.
is there any way to link these 2 views so that users can add few columns from ParentView and few more from ChildView.
Yes, If i use tables directly everything works fine .
Thanks for your help.
i finally figured out .if any one interested
when we double click on data source view (in report model project you created) it gives graphical representation of objects where we can drag & drop to create relationships just like diagrams in Enterprise Managers

report builder cant join views?

Hi friends
i have quick question with report builder utility .am using VS2005 and Sql 2005.
When creating report model solution ,when adding field names from a table it loads fields names as it is ,right ? so to make end users life easier i created
Views with lengthy field names.

the issue is ,i'll start with an example ,i've 2 views (ParentView and ChildView) and the tables used by these views have foreign key relation ship .when i use these 2 entities in report builder as soon as i add a column from one view (say ParentView) the other view disappears from list !! I assume Report builder looks FKey to link 2 entities and since am using views which will not have any fkeys on them it removes other entities from list.
is there any way to link these 2 views so that users can add few columns from ParentView and few more from ChildView.
Yes, If i use tables directly everything works fine .
Thanks for your help.
i finally figured out .if any one interested
when we double click on data source view (in report model project you created) it gives graphical representation of objects where we can drag & drop to create relationships just like diagrams in Enterprise Managers

Report Builder - Unable to get catalog items from server

Hi,

I am running into an error that I cannot seem to resolve. Basically, I am creating a report from a report model that I created within reporting services. So far I have been able to create a data source and then create a model based on it. So far, so good. I then go to report builder to create a report based on the model... again, so far, so good. However, I go to save it and get an error.

Server Error Unable to get catalog items from serverThe permissions granted to user ' ' are insufficient for performing this operation. The permissions granted to user ' ' are insufficient for performing this operation.
---------
Exception of type 'Microsoft.ReportingServices.RsProxy.AccessDeniedException' was thrown.I know what you are thinking... the error seems self explanatory. However, I checked the permissions all the way down the path and I can't figure out where the permissions need to be adjusted. I am able to save a report if I use the administrator account but that is not acceptable in this situation. I must figure out how to adjust the permissions so that it works with the least amount of permissions.Actually for kicks, I gave the user "content manager" permissions at the root (Home) but that didn't help either.We are using the "My Reports" feature and I have made sure that the minimum permissions are in place to use Report Builder (http://msdn2.microsoft.com/en-us/library/ms159667.aspx). We are running the latest service pack too. The path it is trying to save to is: Home > Users Folders > servername user > My Reports > ModelsWebInterfaceHome permissions: server\userClient (server group) has "View Folders Role"Users Folders permissions: server\userClient (server group) has "View Folders Role"server user permissions: server\userClient (server group) has "View Folders Role"My reports permissions: BUILTIN\Administrators Content Manager / server\user My Reports, Report Builder / server\userclient (server group) View Folders Role

ModelsWebInterface permissions: BUILTIN\Administrators Content Manager / server\user My Reports, Report Builder / server\userclient (server group) View Folders Role

Any help is greatly appreciated!

~Des

I was able to figure this out with the help of a PSS tech… Here are the Default SSRS permissions when using the "My Reports" feature: The path – "Home > Users Folders > ServerName ServerUserAccount >My Reports" shows the following permissions: Folder name \ permissions----------Home \ view foldersUser folders \ No permissionsServerName ServerUserAccount \ No permissionsMy Reports \ My Reports,

The fix was to add "View Folder" permissions to the "Users Folder" but it allows the "My Reports" feature to render useless as every user folder is now viewable by the specific user and/or group added to the "Users Folders" folder. I am still working with the PSS technician for a work around for that bug but I was able to get it working.

~Des

|||

I had the same problem. Here's what worked for me. I'm still not sure it's the best way to solve it, but does seem to offer a little more security than the solution you indicated above. (That is, the "User's Folders" folder is not generically visible.)

The problem seemed to be that the Report Builder, by default, wanted to save in the "Models" directory. (I'm not sure why.) We just had to give *some* permission in that folder to the users; the least priviledged built-in role was "Report Builder" (which they already had anyway.) You may even be able to create a dummy role with no permissions and assign users to that role in the Models folder.

Anyhow, we created an "AdHoc Reporting" active directory group, and assigned that group the "Report Builder" role on the "Models" folder. Any user with the rights to run at least one report must be added to that group. They can now save reports in their "My Reports" folder. The only unfortunate thing is that now the "Models" folder is visible, but they can't see the items in their, or upload anything, or change permissions, so it seems secure.

Hope that helps.

Report Builder - TimeOut while creating model

How can I avoid the connection TimeOuts while creating a model for Report
Builder? The databases we're using are pretty big (100s of tables) and in
the millions of rows. Is Report Builder just not up for this kind of
processes?Are you trying to create a model that has 100s of tables? The whole point of
Report Builder is to make reporting easier for end users. So why not create
different models that have the data that different users/groups need access
to? (each model having only a few tables, not 100s.) Also, with that much
data, have you considered analysis services?
Mike G.
"SYoung" <SYoung@.discussions.microsoft.com> wrote in message
news:73F8298C-DE21-4F2D-B11B-CF11F284F9A6@.microsoft.com...
> How can I avoid the connection TimeOuts while creating a model for Report
> Builder? The databases we're using are pretty big (100s of tables) and in
> the millions of rows. Is Report Builder just not up for this kind of
> processes?|||I kept timing out while trying to deploy a model and found out the
deployment was being blocked by another process which was 'getting
model information'...the process was hung up for two days before I
found it.