I'd like to give a special thanks to my wonderful husband Brian K McDonald (BLOG | TWITTER). He has been very supportive of me in my journey of the BI industry.
We are going to learn some of the basics to report writing. I will give you a sample scenario that you can follow using the AdventureWorksDW2008 database. Let's get started using BIDS.
1. Create a new report and name it ReportWalkThrough.rpt
2. Create a shared data source named “AdventureWorksDW2008”
Name the shared data source, select the server name and then the name of the database.
3. Now that you have created a shared data source, you need to add it to add the data source to this specific report.
4. Create a new dataset named “GetProductDetails” that uses the datasource for the report.
5. Set the dataset to use a TEXT sql statement. This query returns a list of all products that contain a list price and do not have “multi” as the color.
SELECT ProductKey, ProductAlternateKey, EnglishProductName, StandardCost, Color, ReorderPoint, SafetyStockLevel, ListPrice, Size, Weight, Style, ModelName, LargePhoto
WHERE ListPrice > 0 AND Color <> 'Multi'
Your new report data should look like the image below.
6. Now you need to add a table to the report . You can do that by right clicking on your design pane and insert a table.
Or you can click on your toolbox, click table and drag it out on the design pane.
7. Now that you have your table, click back on the Report Data tab and drag the ProductKey, EnglishProductName, Color , ListPrice.
8. Now add a new column to the right of List Price by dragging the ModelName column over from the data pane.
9. You will notice a blue I bar show up as you are dragging another column out. This indicates that you are adding a new column and the location of where it is being added. Your new set of columns should look like the image below:
10. When you click the preview you tab you will be able to view your report
11. Let's click back on the Design view tab and do a little formatting of our report before we wrap it up. Click on the columns to bring out the details bar on the left of the column headers. From here we will want to click on the "B" for bold and then we can also change the font and background color.
12. From here we will want to click on the "B" for bold and then we can also change the font and background color.
13. A few more details we can add to this report is centering up our column names, formatting our list price and changing the column name English Product Name to Product Name. If we go back into Design, Left Click on the column name to delete English. To format List Price, right click on the word list price (not the actual column name), select text box properties, select number, select currency, change the decimal places to 0, and check the box next to Use 1000 separator
14. Let's Left Format our values for the columns and the details
15. Click the Preview tab one more time to see how your report looks.
Great job. That was your very first report. We will continue building on this report in my next article and learn how to write some basic expressions to add more color to your report.