January 28, 2009 at 3:13 pm
Here is the question. Day after the day, the request for changes in existing reports and creating reports are piling up and getting bigger.. I am trying to find a way to reduce the requests levels to defcon 3 for ad-hoc or canned reports to a minimum and keep the fires away from the IT department.
Is there a way to have the client / users to pick and choose fields and create their own reports without using CUBEs and Dimensions??
Kind of having stored procedures and views available for the users and let them create their own reports??!!
I know we can use microsoft access to create reports based on pass-throughs and other means, but, I was wondering if the ms sql server 2008 can provide all the right tools.. sort of a one stop shop deal.
thx
Cheers,
John Esraelo
January 28, 2009 at 3:22 pm
That is what you do with the Report Builder, I have not used the SQL Server 2008 version but in SQL Server 2005 you create models your users can use to create their own reports. I think models can use tables and views as datasource. Check below to download and install SQL Server 2008 Report Builder 2.0.
Kind regards,
Gift Peddie
January 29, 2009 at 4:35 am
As stated, that is what Report Builder was designed for. You can use the ClickOnce Report Builder 1.0 version that comes with SQL Server and can be accessed through the Toolbar (assuming that the users have the necessary permissions granted to them. Report Builder 1.0 works off of Report Models that you would have to build as a project within BIDS or they can be generated it you go into the properties of a data source within Report Manager (that is how you have to do it for Analysis Services).
Report Builder 1.0 is still part of SQL Server 2008 Report Manager and gets installed with the product. With Enterprise Edition this version does allow for a feature known as infinite clickthrough also.
With SQL Server 2008 a new version of the product was release and is part of the SQL Server 2008 Feature Pack. This is a standalone application like any other Office product and includes the Office 2007 look-and-feel with the ribbon interface. I have a posting detailing the features here Report Builder 2.0 RTM install and overview. This version can work directly against the relational data sources that you can reference in BIDS and also go against Report Models. You can use locally defined data sources or reference shared data sources on the Report Server.
Using the Report Model will provide the functionality that you are looking for with the drag-and-drop functionality and the ability for the users to acccess the data based on what is defined in the schema that is available and defined for them in the Report Model.
In Report Builder 1.0 you can only have one type of data region (report layout) for the report - tabular, matrix, or chart. That is another limitation along with some of the other differences since it is not a full blown designer like BIDS (or Report Builder 2.0).
If users have created reports in MS Access just remember that these can be imported into and converted through BIDS if needed.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
January 29, 2009 at 3:05 pm
I am testing this tool right now and it does sound like what we were looking for to pass on to the power users for their dynamic reporting..
thx
JohnE
Cheers,
John Esraelo
January 29, 2009 at 3:11 pm
What's a BIDS, I probably used it and did even know it..
😎
Cheers,
John Esraelo
January 29, 2009 at 3:21 pm
BID(Business intelligence development studio) you get it if you are running SQL Server for pay editions. However if you are running any of VS2005/8 Team Suites it will not run as a stand alone. When I click on mine it will open the long project type pane I have to look for BI project to run it. It was the same with the VS2005 Team Suites edition because Microsoft wants users to use one tool for all development.
Kind regards,
Gift Peddie
January 29, 2009 at 4:18 pm
oh, okay, like I said, used it and the name was.. well, I know what you are referring to, but, I had no idea you can convert MS Access rpts in there.. that's great..
thx for the information and by the way I have created couple of reports in the builder 2.0 and I think those certain users will like it too.
Thank you for the direction and the knowledge shared here.
JohnE
Cheers,
John Esraelo
February 10, 2009 at 7:48 am
Hello comunity
I want to know if i can install Report Builder 2.0 RTM on my machine OS Vista SP1, but u have SQL Server 2005 SP2 STD Edition.
This software work with Sql 2005 ?
Can use them as stand-alone report builder for 2005, also do i need install sql server 2005 reporting services, this program have their own engine ?
Many thanks
Luis Santos
February 10, 2009 at 7:55 am
You can install the Report Builder 2.0, but you will not be able to deploy to a SSRS 2005 server since the RDL format will not be supported. You can create reports in Report Builder 2.0 against a SQL 2005 instance. Report Builder 2.0 is for SSRS 2008 deployment. Also, SSRS installation is part of the SQL Server install. SSRS 2005 requires IIS to be installed, but SSRS 2008 no longer has that requirement.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
February 10, 2009 at 8:22 am
Hello Dan
Thanks for your reply, i will test with my sql server 2005 to see what i can build and how it work.
Many thanks
Luis Santos
February 10, 2009 at 8:53 am
I have actually tried and created a report that passes a parameter to a function / stored procedure in sql 2005.. nice..
and then I created one simple one with a stored procedure call. no problem.
Here is the question:
Now, I am getting into the cube/dimension/fact, etc.. can rdl hit these objects?
thx
Cheers,
John Esraelo
February 10, 2009 at 9:01 am
Yes. You can use the Microsoft SQL Server Analysis Services or the OLE DB provider. With the Analysis Services provider you will get to use the MDX Query Designer similar to when you browse the cube. You can also setup the filters to be parameters within the designer and it will generate the dataset needed to create this parameter. If you need to generate a Report Model off this type of a data source that has to be done within Report Manager within the actual data source. In the properties of the data source you will see the button on towards the bottom that says 'Generate Model'.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
February 10, 2009 at 9:33 am
good deal.. however, before I start playing around and hurt myself 😀 I would like to go and find out who came first the chick or the egg.. I read the OLAP tutorial on cubes, etc,.. and I am finding it a little confusing. I have this sequential way of thinking when it comes to training and get confused when reading that the sequence of the creation does not matter; cube and dimension/facts could be created in any order...
so, here I am trying to find a site that I can actually step back a little and get a better runway..
any ideas would be appreciated.
thx
Cheers,
John Esraelo
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply