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 Builder Security Filter Examples

Does anyone have any examples on how to use the security filters in
report builder models. Do you setup a standard filter. How do you pass
the user back to the server in the query?
ThanksI have the same type of question...I need to be able to pass parameters
(userid) back to my model due to security setup in our system...we have
several 'user-access' tables for varying levels of security. For
example, any given user has access to specific accounts, but only
certain members within that account and only specific coverages within
those members. I don't see how I can set this up using roles but
perhaps I'm just missing it.|||the getuserid() function can be used... but... reportbuilder keep in cache
the first user which access a filter object where the getuserid() function
is used!!!
so if an "administrator" try the model, then every other users use the same
access!!!
but using the getuserid() into reportbuilder himself (the user create a
report and explicitly setup a filter) then the ID used is the right one, but
its not a good solution.
I'll do some other tests and keep you informed.
"tim" <tbush@.ccmsi.com> wrote in message
news:1141664357.097038.87240@.i40g2000cwc.googlegroups.com...
>I have the same type of question...I need to be able to pass parameters
> (userid) back to my model due to security setup in our system...we have
> several 'user-access' tables for varying levels of security. For
> example, any given user has access to specific accounts, but only
> certain members within that account and only specific coverages within
> those members. I don't see how I can set this up using roles but
> perhaps I'm just missing it.
>|||I will be posting an entry on my blog (http://blogs.msdn.com/bobmeyers)
shortly explaining how to use security filters.
FYI, the incorrect behavior of the GETUSERID() function is a known issue
that is fixed in SP1, which will be available shortly.
"Jéjé" wrote:
> the getuserid() function can be used... but... reportbuilder keep in cache
> the first user which access a filter object where the getuserid() function
> is used!!!
> so if an "administrator" try the model, then every other users use the same
> access!!!
> but using the getuserid() into reportbuilder himself (the user create a
> report and explicitly setup a filter) then the ID used is the right one, but
> its not a good solution.
> I'll do some other tests and keep you informed.
>
> "tim" <tbush@.ccmsi.com> wrote in message
> news:1141664357.097038.87240@.i40g2000cwc.googlegroups.com...
> >I have the same type of question...I need to be able to pass parameters
> > (userid) back to my model due to security setup in our system...we have
> > several 'user-access' tables for varying levels of security. For
> > example, any given user has access to specific accounts, but only
> > certain members within that account and only specific coverages within
> > those members. I don't see how I can set this up using roles but
> > perhaps I'm just missing it.
> >
>
>|||thanks!
your blog appear to be great and usefull.
favorites favorites... ;-)
"Bob Meyers - MSFT" <BobMeyersMSFT@.discussions.microsoft.com> wrote in
message news:D083E8EC-F89C-4E6A-AE99-0BEFDE4F877B@.microsoft.com...
>I will be posting an entry on my blog (http://blogs.msdn.com/bobmeyers)
> shortly explaining how to use security filters.
> FYI, the incorrect behavior of the GETUSERID() function is a known issue
> that is fixed in SP1, which will be available shortly.
> "Jéjé" wrote:
>> the getuserid() function can be used... but... reportbuilder keep in
>> cache
>> the first user which access a filter object where the getuserid()
>> function
>> is used!!!
>> so if an "administrator" try the model, then every other users use the
>> same
>> access!!!
>> but using the getuserid() into reportbuilder himself (the user create a
>> report and explicitly setup a filter) then the ID used is the right one,
>> but
>> its not a good solution.
>> I'll do some other tests and keep you informed.
>>
>> "tim" <tbush@.ccmsi.com> wrote in message
>> news:1141664357.097038.87240@.i40g2000cwc.googlegroups.com...
>> >I have the same type of question...I need to be able to pass parameters
>> > (userid) back to my model due to security setup in our system...we have
>> > several 'user-access' tables for varying levels of security. For
>> > example, any given user has access to specific accounts, but only
>> > certain members within that account and only specific coverages within
>> > those members. I don't see how I can set this up using roles but
>> > perhaps I'm just missing it.
>> >
>>|||Thanks Bob...I look forward to it...I am stumped.
J=E9j=E9 wrote:
> thanks!
> your blog appear to be great and usefull.
> favorites favorites... ;-)
> "Bob Meyers - MSFT" <BobMeyersMSFT@.discussions.microsoft.com> wrote in
> message news:D083E8EC-F89C-4E6A-AE99-0BEFDE4F877B@.microsoft.com...
> >I will be posting an entry on my blog (http://blogs.msdn.com/bobmeyers)
> > shortly explaining how to use security filters.
> >
> > FYI, the incorrect behavior of the GETUSERID() function is a known issue
> > that is fixed in SP1, which will be available shortly.
> >
> > "J=E9j=E9" wrote:
> >
> >> the getuserid() function can be used... but... reportbuilder keep in
> >> cache
> >> the first user which access a filter object where the getuserid()
> >> function
> >> is used!!!
> >>
> >> so if an "administrator" try the model, then every other users use the
> >> same
> >> access!!!
> >> but using the getuserid() into reportbuilder himself (the user create a
> >> report and explicitly setup a filter) then the ID used is the right on=e,
> >> but
> >> its not a good solution.
> >>
> >> I'll do some other tests and keep you informed.
> >>
> >>
> >> "tim" <tbush@.ccmsi.com> wrote in message
> >> news:1141664357.097038.87240@.i40g2000cwc.googlegroups.com...
> >> >I have the same type of question...I need to be able to pass paramete=rs
> >> > (userid) back to my model due to security setup in our system...we h=ave
> >> > several 'user-access' tables for varying levels of security. For
> >> > example, any given user has access to specific accounts, but only
> >> > certain members within that account and only specific coverages with=in
> >> > those members. I don't see how I can set this up using roles but
> >> > perhaps I'm just missing it.
> >> >
> >>
> >>
> >>|||how can I get SQL userid into the model rather than the Windows ID?|||to use the SQL Server user id , use SQL servers syntaxes (suser_sname or
user_name and other user_id functions)
"tim" <bush.timothy@.gmail.com> wrote in message
news:1144296426.518067.71590@.i40g2000cwc.googlegroups.com...
> how can I get SQL userid into the model rather than the Windows ID?
>|||thanks...was finally able to use SUSER_SNAME(SUSER_SID()) to get what I
needed. Now I'm trying to get the filters to work with it.|||Note that with the SP1 version of BOL (which will be available for download
when SP1 is released), there is a tutorial that walks you through how to set
up security filters using the GetUserID().
"tim" wrote:
> Thanks Bob...I look forward to it...I am stumped.
> Jéjé wrote:
> > thanks!
> >
> > your blog appear to be great and usefull.
> > favorites favorites... ;-)
> >
> > "Bob Meyers - MSFT" <BobMeyersMSFT@.discussions.microsoft.com> wrote in
> > message news:D083E8EC-F89C-4E6A-AE99-0BEFDE4F877B@.microsoft.com...
> > >I will be posting an entry on my blog (http://blogs.msdn.com/bobmeyers)
> > > shortly explaining how to use security filters.
> > >
> > > FYI, the incorrect behavior of the GETUSERID() function is a known issue
> > > that is fixed in SP1, which will be available shortly.
> > >
> > > "Jéjé" wrote:
> > >
> > >> the getuserid() function can be used... but... reportbuilder keep in
> > >> cache
> > >> the first user which access a filter object where the getuserid()
> > >> function
> > >> is used!!!
> > >>
> > >> so if an "administrator" try the model, then every other users use the
> > >> same
> > >> access!!!
> > >> but using the getuserid() into reportbuilder himself (the user create a
> > >> report and explicitly setup a filter) then the ID used is the right one,
> > >> but
> > >> its not a good solution.
> > >>
> > >> I'll do some other tests and keep you informed.
> > >>
> > >>
> > >> "tim" <tbush@.ccmsi.com> wrote in message
> > >> news:1141664357.097038.87240@.i40g2000cwc.googlegroups.com...
> > >> >I have the same type of question...I need to be able to pass parameters
> > >> > (userid) back to my model due to security setup in our system...we have
> > >> > several 'user-access' tables for varying levels of security. For
> > >> > example, any given user has access to specific accounts, but only
> > >> > certain members within that account and only specific coverages within
> > >> > those members. I don't see how I can set this up using roles but
> > >> > perhaps I'm just missing it.
> > >> >
> > >>
> > >>
> > >>
>

Report Builder doesn't show models

Hello,

When I open Report Builder I do not see any of the models that I've created. The "Select Site or Server" is blank and I cannot get to any of the models. I can't change the dropdown from "Recent Sites and Servers."

I am not sure what I did, because it used to work. I did install SP2 but that did not cause a problem originally.

I can create new models (on both the BIDS and the ReportServer site) and I see that they were created just not in Report Builder. At this point Report Builder is non-functional for me.

The other thing that may be related is that I've never been able to run Report Builder from the website. When I click the button nothing happens. I made a shortcut to ReportBuilder.exe to get around this.

Thank you for the help.

-Gumbatman

When I click the button nothing happens.

This is probably the root of your problem. Try purging the Report Builder download files as explained in more details here. Check also the RS log files and Windows Event log for errors.

|||

Teo,

Thank you for the information. However, I could not find any folder structure like you mentioned.

What should I look for in the RS Log FIles and Windows Event logs?

Another problem with this is that from the first install (of which I've done many) RS has never really worked quite right. I think it has a lot to do with security settings on my machine - not that I know what they are. That is because it is a machine a work and they put all sorts of stuff on it. When I installed it at home, I have none of these problems. Both machines are using WIndows XP Pro - SP2.

Thank you.

-Gumbatman

Report builder and report models

Hi,

the database my app is using contains information from multiple user organisations, a sort of multi organisation database. I intend using the reprot builder to provide access to a user ine ach organisation. However the issue of who sees what info has popped up and i'm trying to think of a way to restict the user of report builder to only be able to see their own organisations information. This is a social services type application which is used by mutlitple service providers so organisations get twitchy about other organisations being able to see detailed client info. on the application the security stops this but at am a t a loss as to how to best restirct access to an organisation won client data.

any thoughts? is this somehting that can be configured through the reprt model and the report builder user account?

few ideas or advise would eb v helpful,

trhanks

Are you using SQL Server as the back end?

R

|||test. sorry to intrude.|||

Repot Models can have security rights assigned to different piece within the model. You can set up Perspectives and assigned users or roles to those perspectives. Or you can assign rights to individual folders. This can be done not through the report model (or the report server), but by opening the Report Model's Properties in SQL Server Management Studio.

Hope this helps.

|||

hi,

prev q answer - yes this is sql server 2005.

havn't come across perspectives but sounds v interesting. is that in SSRS or is it in SSAS?

i will check it out

many thanks

Report Builder / Report Models

We would like to support Report Builder / Report Models in our application,
and we know that Report models are generated from SQL Server relational or
Analysis Services multidimensional databases. Based on our superficial
understandings of report models, it seems that we should be able to create an
entity based on more than one source table/view with or without security
filters and even give it a friendly name. Is this correct? If so, is it
possible to create a report model programmatically?
If not programmatically, we would want more help with how to create a report
model in details. The current tutorial in the SQL Server Books online is
actually not detailed enough (most of the time it guides us to accept the
defaults).Hi,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you would like to whether it is
possible to create a report model programmatically. If I have misunderstood
your concern, please feel free to point it out.
I think BOL contains two useful articles for your reference
How to: Create a Model Using Report Manager
http://msdn2.microsoft.com/en-us/library/ms159118.aspx
How to: Create a Report Model Using the Model Designer Wizard
http://msdn2.microsoft.com/en-us/library/ms156284.aspx
Also, this article explains the major components of Microsoft Reporting
Services' ad-hoc architecture and semantic model, and walks you through an
end-to-end example that demonstrates how to author, manage, and deliver
ad-hoc reports.
Generate Ad-hoc Reports with Microsoft Reporting Services 2005
http://www.devx.com/dbzone/Article/28047
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Report Builder - Filter Dropdown Order Post 2005 SP2 Incorrect

Hi All,

Hope someone can help here. Since installing Service Pack 2 onto our SQL 2005 instances our report models are not working correctly. When selecting fields within the models as filters, and getting them to prompt when the report is run, the ordering of the data within the filters does not match the selected ordering in the model definition. The ordering is random each time the report is run.

This issue does not happen when setting the filter up, the data appears in the correct order, but when running the report the filter data is incorrect.

I have spent the last 4 hours building a new system and testing this with each stage of service packing. The RTM version of SQL 2005 is ok, SP1 is also OK, but when SP2 is applied the ordering fails.

Any ideas?

Many thanks in advance.

Regards

Nick

An update to this issue just in case it may help others.

I logged a call with Microsoft relating to this issue and the product support specialist managed to reproduce the issue at their end. It was passed up to the product teams and the reponse has come back that it is now designed this way even though it worked prior to SP2.

The specific issue is:- Dropdown parameter boxes on reports created using report builder do not adhere to the ordering specified in the report model at runtime, although they do at design time.

As we were adding parameter boxes allowing users to select from almost 1000 branch codes the fact that it isn't ordered and appears in a random order is obviously an issue that has meant we can no longer use report builder.

The workaround suggested by Microsoft is to index the underlying tables so that the table scans of the branch codes scan indexes instead and return ordered lists, as the index is already ordered. Unfortunately I cannot do this as my tables are indexed for performance, and I can't change them to fix an issue that shouldn't (in my personal opinion) be there in the first place.

Hope this helps anyone else in the same situation.

Nick

Report Builder

I am experimenting with Report Builder for the first time. I did an upgrade
from SQL Server 2000 to 2005. When I first strated trying to post models I
created I was getting an error that I didn't have the required permissions.
I then found that I needed to create a role with model permissions and was
able to post the model. Now, when I design a report with report builder and
try to run it I get the same error which says:
"The permissions granted to user xxxxx are insufficient for performing this
operation. (rsAccessDenied)"
I have given myself every permission available on that model. Does anyone
know what I need to change?
One other small problem is that the Report Builder icon is not showing up in
the portal. The only way that I can get to it is to go directly to the URL:
http://<servername>/reportserver/reportbuilder/reportbuilder.application
That works fine except, of course, when I get the error that I get when I
try to actually run a report. Any help on these two issues would be greatly
appreciated.
Thanks,
TjI just saved the report in report builder to the portal and was able to run
it from the portal so the only time I get the error is when trying to run
the report from within Report Builder.
Also Report Builder icon still not showing up in portal.
"TJ Davis" <TJDavis@.sagu.edu> wrote in message
news:60A250E1-EC1B-417F-971E-E1E4F9F1AB79@.microsoft.com...
>I am experimenting with Report Builder for the first time. I did an
>upgrade from SQL Server 2000 to 2005. When I first strated trying to post
>models I created I was getting an error that I didn't have the required
>permissions. I then found that I needed to create a role with model
>permissions and was able to post the model. Now, when I design a report
>with report builder and try to run it I get the same error which says:
> "The permissions granted to user xxxxx are insufficient for performing
> this operation. (rsAccessDenied)"
> I have given myself every permission available on that model. Does anyone
> know what I need to change?
> One other small problem is that the Report Builder icon is not showing up
> in the portal. The only way that I can get to it is to go directly to the
> URL:
> http://<servername>/reportserver/reportbuilder/reportbuilder.application
> That works fine except, of course, when I get the error that I get when I
> try to actually run a report. Any help on these two issues would be
> greatly appreciated.
> Thanks,
> Tj|||Hi Tj,
That has something to do with user security settings :-S
I noticed this once before. When I set the website to allow anonymous access
the builder button was gone, when I am administrator and not allow anonymous
access the button was there.
That's all I know, it may help you a little...
Regards,
Edgar
"TJ Davis" wrote:
> I just saved the report in report builder to the portal and was able to run
> it from the portal so the only time I get the error is when trying to run
> the report from within Report Builder.
> Also Report Builder icon still not showing up in portal.
> "TJ Davis" <TJDavis@.sagu.edu> wrote in message
> news:60A250E1-EC1B-417F-971E-E1E4F9F1AB79@.microsoft.com...
> >I am experimenting with Report Builder for the first time. I did an
> >upgrade from SQL Server 2000 to 2005. When I first strated trying to post
> >models I created I was getting an error that I didn't have the required
> >permissions. I then found that I needed to create a role with model
> >permissions and was able to post the model. Now, when I design a report
> >with report builder and try to run it I get the same error which says:
> >
> > "The permissions granted to user xxxxx are insufficient for performing
> > this operation. (rsAccessDenied)"
> >
> > I have given myself every permission available on that model. Does anyone
> > know what I need to change?
> >
> > One other small problem is that the Report Builder icon is not showing up
> > in the portal. The only way that I can get to it is to go directly to the
> > URL:
> >
> > http://<servername>/reportserver/reportbuilder/reportbuilder.application
> >
> > That works fine except, of course, when I get the error that I get when I
> > try to actually run a report. Any help on these two issues would be
> > greatly appreciated.
> >
> > Thanks,
> >
> > Tj
>

Report Authoring Using Report Builder

We have deployed multiple report builder models and the number 1 user complaint is that every report builder report created can only have 1 table or 1 matrix or 1 chart in per .rdl (or report created). Anyone found any way around this? Is there a fix planned?This was an intentional design constraint for this release. There are no workarounds. We are definitely looking to include this functionality in a future release.