Building and Deploying Reports
Table of Contents
Use
Report Builder 2.0 to build a new report from a model
Deploying
a Model or Report from BIDS
Using
a model in a Bids Report
Using
Report Builder 2.0 to change a BIDS report and then going back to BIDS
Tools overview:
Note: we now have 2008 version of everything
- SQL server – actual database
- Hosts the actual database
- SQL server management Studio (IT tool)
- Can create SQL views
§ To create and develop report projects of 3 types
§ Model projects
§ Report server projects (and these can use deployed models)
§ Analysis projects – we are not using these now
- Report server – server for reports and models
- Hosts everything the report manager shows
- Report manager - web user delivery server for reports
- Shows reports that come from
- Report Builder 2.0
- It lists the models.
- Allows parameter entry if parameters were defined in report.
- Allows easy export to Excel
- Report builder 2.0 – (IT, Super Users)
- launched from report manager
- Create or change reports using models to allow easy new report building
- Create or change reports using or views or direct SQL
- Create
or change
- 2.0 needs to be installed on desktop and we will go live with 2.0 (free add-on to 2008)
Use Report Builder 2.0 to build a new report from a model
Prerequisite: The model has been deployed to the Report Server.
1. Get into Report Builder
- Open Internet Explorer
Type http://usa4.afsglobal.org/Reports/Pages/Folder.aspx?ItemPath=%2fMy+Reports&ViewMode=List
to access the Report manager.
- Because the Report Builder 2.0 launch from this screen has imperfect security, please report builder manually using Start / Program / MS SQL Server 2008 Report Builder 2.0.
- (If you need to download that first, get it from http://www.microsoft.com/downloads/details.aspx?FamilyID=9f783224-9871-4eea-b1d5-f3140a253db6&displaylang=en . When you install, the default report server should be http://usa4.afsglobal.org/Reportserver)
- You should then see:
2. Set
up Report Builder 2.0 to point to the
- Click on the MS icon in the upper left
- Click on the options button
- Type in the report server name http://usa4.afsglobal.org/reportserver
- Choose OK
- (Note: If you wanted to open an existing report now, you would click on the MS icon and then click open)
3. Create a new data source
- Click new
- Choose datasource
- Give the new datasource a name
- Choose embedded or shared connection. (I describe both below – use the one that works for you.)
- (Note on issue #4: When working with Avi, we could not use a shared datasource which would let us browse for the model because we ran into security issues. However, when I worked on integration manager, I was unable to choose model as a connection type, so I had to use the shared data source and it worked with no problem. MS support site: http://msdn.microsoft.com/en-us/library/dd239360.aspx says that Report Builder does not provide model as a type, so perhaps Avi has a slightly older version of Report Builder 2.0.)
- For an embedded connection:
- Click Embedded connection
- Choose type Report Server Model
- Type the connection string of: server=http://USA4.afsglobal.org/reportserver;datasource=/models/org but replace org with whatever model you want.
- (Note on issue #3: We will want to figure out an organization scheme for saving models.)
- You can find the models first by choosing to use a shared connection and then clicking on browse and models and then see the name of the model you want. If you don't embed it though, you will have security issues.
- Hit
OK
§ Click credentials
§ Enter your user and password
- For a shared connection instead:
§ Click Shared
§ Click the browse button
§ It should be looking in http://usa4.afsglobal.org/reportserver
§ Click models
§ Click the model you want
§ press open
§ double click on server to add user and password
4. Add the dataset which is the list of fields you intend to report upon
- right click on the data source
- choose add dataset
- give
the dataset a name and then click query designer
- Choose your fields by double clicking on them.
- Hit OK
- You can define parameters and filters here, but I am not sure how.
5. Actually create your report layout
- Click on table or matrix
- Say OK to choosing the existing dataset.
- Drag fields into the main data pane (and possibly rows and columns
- Choose Next and see:
- Choose Next
- Choose Finish
6. Run the report
- Click run
- Click design when you are done
7. Save the report
- Click the MS icon
- Click save
8. See the report in Report Manager:
- Open Internet Explorer
- Type
http://usa4.afsglobal.org/Reports/Pages/Folder.aspx to access the Report manager. (note on
issue #1: that right now, this can only be done at
- Click on the folder in which you placed your report
- You will see your report.
Deploying a Model or Report from BIDS
- Open the project (model or report)
- Set the report model project property
§ choose menu option project
§ set TargetServerURL as http://USA4.afsglobal.org /ReportServer
§ set overwrite datasources to True so it will redeploy all the models in your project.
§ Issue #5 – We need to set the targetModelFolder properly according to the folder scheme we decide. Each project should have models that belong in one folder.
- Build the project
§ Choose menu option build
§ Choose build
- Deploy the project
§ Choose menu option build
§ Choose deploy
§ You may need to enter your user and password
Using a model in a Bids Report
- When you create the datasource, set the type to Report Server Model and set the connection string to server=http://USA4.afsglobal.org/reportserver;datasource=/models/org (replacing org with your model name).
Using Report Builder 2.0 to change a BIDS report and then going back to BIDS
- Open the deployed report in Report Builder 2.0
- Make some changes and save to your local drive
- Open
the report in
- You will have to reset the datasource to not look at the datasource folder:
- Otherwise, I think it is fine. It may have glitches as we go further.