4/27/2010 4:51:49 PM
In this blog post you will learn how to connect to a Data Source and create your first Data Set. You can then use this to extract data from any OLAP cubes and compile a report that you can then export in PDF, XLS or XLSX format.
Follow the instructions below in order to get some data from the Adventure Works 2008 Data Warehouse Cubes.
- Create a new Data Source by clicking on New Data Source in the Report Data section of SSRS. If this window is not shown you can view it by going to View Report Data.
- This should open a Data Source Properties window.
- Inside the Data Source Properties window give your Data Source a name
- Change its type to Microsoft SQL Server Analysis Services.
- Press Edit beside connection string and point to your source of the cubes.
- Once you have sucessfully tested your connection you can press OK.
- You can now use that Data Source to create a Data Set and bring back some data for your report.
- Right click on the new Data Source and select "Add Data Set"
- Give your new Data Set an appropriate name.
- Select your Data Source from the drop down box.
- Click on Query Designer to create a query that will be used to extract data.
- Here you can drag whatetver measures and dimensions you want. For me I will drag out the Internet Gross Profit Measure on Internet Sales and the Product Category and Prodcut Sub Category.
- If you press OK now you will see that your Data Set (in the Report Data window) now contains all of the dimensions you need in order to make a report.
- You can now drag a chart onto your page now and drag thosefields onto the report.
4/26/2010 9:00:09 PM
I have found that a common behaviour that people want to see in their reports is changing the font color depending on whether or not a value meets, or does not meet certain thresholds. This can be easily accomplished with conditional formatting in SSRS 2008.
We will work with the SWITCH statement to accomplish this. In a previous post where I explained how to get alternating background colors for rows to make the report easier to read, we used a single IIF statement.
Lets use an example from the Adventure Works 2008 Data Cubes again. Shown below.
We want to paint the font color:
- Green if the value is greater than 20,000
- Red if the value is less than 1,000
- Balck if the value is less than 20,000
Right click on the texbox that you want to change and click on properties. When the Text Box Properties window opens click on fx in the color section. In here we can enter our expression:
=SWITCH(Fields!Internet_Gross_Profit.Value < 10000, "Red",
Fields!Internet_Gross_Profit.Value < 20000, "Black",
Fields!Internet_Gross_Profit.Value >= 20000, "Green")
And our final should look something like:
4/26/2010 4:34:30 PM
One common behavior that people want to implement on their SSRS Reports is alternating background colors for a table. This makes the report rows easier to read and allows the user to find the data that they are looking for quickly.
Below is an example of tabular data taken from the Adventure Works DW 2008 Cubes:
If we want to make the report a little easier to read we need to change the background color of the rows.
- In design mode click on the entire row of the data table you can see in the Properties window a Background color property.
- Click on Expression to add an Expression to this property.
Enter:
=IIF(RowNumber(Nothing) Mod 2, "Silver" , "#FFFFFF")
You can see above that I used Silver and #FFFFFF. This is just to show you that you can use hex values for the colors as well. It is not constrained to the colors that are given.
This will produce :
Which is exaclty what we are looking to achieve. This has made the horizontal background colors alternate so that the report becomes easier to read.
4/18/2010 6:30:28 PM
One of the useful things you can do in SSRS 2008 is pagination. This is useful if you are creating a PDF report for your client and want to display the page number in the footer, or other parts of the page.
If you drag a textbox onto the page you can right click in the textbox and click on Expression you can tell SSRS what you want to display in the text box.
Inside the Expression box in the bottom left you will find an area called Category. If you click on "Built-in Fields" you will see some useful things for us. PageNumber and TotalPages are what we are after.
So lets make a string that will display "Page 1 of 2". Where 1 is our current PageNumber and 2 TotalPages.
Simply use the following and you should get the desired result:
="Page " & Globals!PageNumber & " of " & Globals!TotalPages