In this blog, I will show you how to add normal and cascading parameters to an SSRS report. I assume that you have already read my previous blog. I will continue from the end of that blog.
We have already done with SSRS report running without any parameters from our previous blog.
We are going to add two parameters. One is for customer country and the other one is for customer city. So, we will have two drop down items as parameters. When we select a value for country drop down, it will be a source for the other parameter and city drop down is going to load available cities related with selected country.
Click on design button and go to design view. On the left hand side panel, right click on Dataset folder and add a new dataset. Fill the opening window same as the picture below. We are using our existing Data Source which we added before. (Data Source has Customers and Orders tables.) Write the query and click OK.
Data Set is ready. After that, right click on Parameters folder and add a parameter. Fill out Name, and Prompt fields as “CountryParameter”. Click on Available Fields and select Get values from a query radio button. Select values same as the picture below.
For the second parameter, we need another Data Set. It is same as the previous one. The only difference is that when we write the query we will add the first parameter to the new Data Set. We need to get all cities which belong to a specific country. So, in the where clause, we should use “@CountryParameter”. You need to put @ before the parameter name to use it in Data Set.
What we did for CountryParamter, we need to do same thing for CityParameter. Right click on Parameters folder and a new parameter.
Last thing we need to do is that, implementing the city parameter into the main query. Right click on DataSet1 and select DataSet properties. Add where clause to query. (Where Customers.City=@CityParameter)
You are done! Click OK and run the project.
Tags: SQL Server, SSRS, Cascade, Parameters, Report Builder, Reports
If you want to create useful reports without spending too much time, you should definitely use SSRS reports. In this blog I will show you how to create reports using Report Builder. I will use Report Builder 3.0 version. You can use SQL Server Business Intelligence Development Studio as well.
After you run the Report Builder, select Table or Matrix Wizard under New Report tab. I will create a dataset, but you can use an existing dataset. Click on next and select the database which you want to connect.
Click OK and go to Design a query window. I am using Nortwind database. Under database view expand tables and select tables which you want to show in your report. I selected Customers and Orders table. You can use your own query instead of using the tool.
Then click on next button and go to Arrange fields window. At this point you are starting to select the fields for your report. Drag and drop the fields that you want to show in your report.
Click on next and finish the wizard. You can click on Run button and see what your report looks like.
You can play with the report settings and use expressions to make your report more flexible. I will show how to add dependable and individual parameters to your reports in my next blog.
Tags: SSRS, Reports, SQL Server, Report Builder
If you are going to export MS Word Documents using c# .Net, Open XML is a really helpful tool. I will show you a quick tutorial.
First we need to install “Open XML SDK 2.0 Productivity Tool for Microsoft Office”. After the installation, you need to create a sample word document and style it how you would like to.
Let’s say we styled a word document like below. Just a table and has Title, Detail1, Detail2, Detail3… etc. In this tutorial I will only update the first row. (Detail1x, Detail1y)
Save this template as “temp.docx” and close the file.
Open the file with Open Xml.
Expand “/word/document.xml” since you can see the Table tag in document body.
After selecting the “<>w:tbl (Table)” you need to click on Reflect Code button and Open Xml is going to generate a class of that table for you. That class has a “GeneratedTable()” function and return type is “DocumentFormat.OpenXml.Wordprocessing.Table “. To make this table dynamic, you can pass parameters into the “GenerateTable()” function.
Now you can implement this template to your project. Just you need to create a class in your project and copy generated code into that class.
Add some parameter to GenerateTable() function.
For example, I am adding two parameters which are Detail1xExplanation, and Detail1yExplanation. I will use these parameters to update the first row. You can add more parameters for the other rows.
Now, you need to find the static texts in this class and replace them with related parameters. For instance, find the “Detail1x” static text and replace it with “Detail1xExplanation” parameter.
So, you just need to create and instance and pass the values, than you will get a “DocumentFormat.OpenXml.Wordprocessing.Table” object which is already styled.
You should create another template “EmptyTemplate.docx” which has an empty table. Last thing is that you need to find that empty table and append the generated table inside of it same as below.
You are done!
Tags: OpenXml, Word Export
Normally Server side Automation of Office is not recommended. Here is an article that explains why,Considerations for server-side Automation of Office (http://support.microsoft.com/?id=257757)However, let’s say you have to export Visio Documents using server side automation. You should receive errors from below lines, while you are opening the template file. Server would not let you to open a Visio Application.visApp.Documents.Open(Template.vst);visApp.Documents.Add(Template.vst);Here is the solution. 1. You have to have Visio 2010 installed to your 2008 Server. 2. You have to enable Desktop Experience. 3. You have to Disable UAC.4. Start-Run-“Component Services”.5. Expand Component Services.6. Expand Computers-My Computer-DCOM Config. and find Microsoft Visio Drawing.7. Right click on Microsoft Visio Drawing and go to properties. 8. Under “Identity” tab, select “This User” option. (This User should be IIS Manager.)9. Click Ok and you are good to go.
Exsilio Homepage | Software Development
Powered by Exsilio Solutions