I’m not considering myslef a SQL Server expert but to be honest I was afraid of this beginning word in the title. Yet I’ve been using SQL Server for a couple of years (apart from Java, C#, etc. – so not full time SQL developing). Also I attended a few SQL related courses when I was at the university. So I should have quite solid theoretical and pratctical background. However, as I was not working with SQL Server full time I decided to give that book a bash. You can always stop reading if you don’t like the book
I’m going to Microsoft Technology Summit (MTS) 2009 which takes place in Warsaw, Poland on September 29th-30th. This is the biggest Microsoft (.NET, SQL Server, Windows platform) related conference that takes place in Poland.
This will be the first time for me. I’ve never been to this conference but heard it’s decent – good speakers, lots of information is passed, highly developing. We’ll see how it will be but I expect to get back home overwhelmed with new knowledge, both theoretical and practical.
Is anyone of you, readers of this blog, attending this conference too?
I spend too long on finding the solution for this problem…
Basically I had a table which looked like this
What I wanted was to select all as one row as a set of items separated with a comma, where an item was ‘id: name’. The items had to be sorted by the id. So the result was supposed to look as below:
C2C is a history now. I attended .NET stream and one session on SQL. The conference as a whole was very interesting, well organised, and basically developing. It was a Polish conference so most sessions were conducted in Polish but there were a couple of speakers from abroad too, and they spoke in English.
Basically I’m happy I had a chance to attend the conference. Below are my notes and comments. Please note they are my comments; should I write something which is not true or accurate, please let me know, sometimes pace was fast so I might have skipped something Continue reading ‘Summary of Communities to Communities (C2C) 2009 Conference’
Imagine you have a report that displays some data and the number of columns cannot be defined at the design stage, i.e. the number of columns depends on user input. That seems to cause some problems when you want to render this report with Reporting Services…
Solution:
Note: The described solution applies to the case when RDLC files are used by ReportViewer control in local mode. However, it can be simply tweaked so it works in general.
Because the number of columns that should be displayed on the report can be named only at runtime it seems reasonable to update the RDLC file and only then deliver the report. RDLC is an XML file that contains the definition of the report – styling and all fields displayed. So all you need to do is to read the RLDC file into XmlDocument, get some nodes and update them. Continue reading ‘How to dynamically add new columns to report created with Reporting Services?’
Once you deploy reports on to Report Server they can be accessed from any Web application with an established URL. However, I see a scenario when one wants to prevent some users from accessing reports (e.g. a user should be able to view reports presenting only their personal information).
The way around this is to view reports on an aspx page by employing ReportViewer control, which makes it possible to view reports hosted locally or deployed on (in particular remote) ReportServer. I’ve chosen the former approach.
In order to use ReportViewer with reports hosted locally, you need to:
change the extension of the report files from RDL to RDLC
configure ReportViewer control so it is associated with an appropriate RDLC file and DataSource (you will need to configure it by providing the connection details, specifying the SQL query or stored procedure that will retrieve the data)
in code behind for that aspx page (in Page_Load method) you can add some logics that checks if the currently logged user can access the report; if not, you can just throw an Exception
if you want to pass parameters to the ReportViewer, you will have to add a few lines to the above mentioned method:
ReportParameter p = new ReportParameter(PARAM_NAME, PARAM_VALUE);
reportViewer.LocalReport.SetParameters(new ReportParameter[] { p });
In this post I’ll describe how to deploy RDL files using Report Manager (more often http://localhost/Reports or http://localhost/Reports$SQLExpress). Note: This description applies to the situation where Microsoft SQL Server is used.
Go to Report Manager
Too keep all clean, you can create a folder in which you will keep all your reports
Create the data source, if not yet existing, by selecting the New Data Source link.
Specify Microsoft SQL Server as the Connection Type.
Specify the database Connection String as follows: data source=[Name of database server SQL Instance];initial catalog=[Name of database]
Connect Using the Credentials stored securely in the report server and specify the user name and password used to connect to SQL Server instance installed in the database server.
Datasource configuration
Upload the report by selecting the Upload File link and navigate to the RDL file stored in the system.
Using Show Details view, edit the uploaded reports by selecting the Edit link.
Under the Data source link, assign the previously created shared datasource as the source of data for the report.
Note: The “Apply” button needs to be selected to apply the changes made for the report.
In one of the projects I had to create reports (using Reporting Services) that then should be accessible from the application I’ve been developing.
As the first thing I created the reports themselves (RDL files), deployed them using the Report Manager (more often http://localhost/Reports or http://localhost/Reports$SQLExpress), and configured so I could access them. However, each time I accessed a report I had to provide the input parameters which were used by the stored procedure in order to deliver appropriate amount of information. I wanted to avoid that by passing the parameters somwhowin the URL.
It was only my friend who spotted that I tried to pass the parameters to the Report Manager using URL similar to http://localhost/Reports/Pages/Report.aspx?ItemPath=XXX&rs:Command=Render&ParamName=ParamValue. Instead I should use the Report Server, so link looking this way: http://localhost/ReportServer/?XXX&rc:parameters=false&rs:Command=Render&ParamName=ParamValue.
Note: Please remember there are two services: Report Manager and Report Server and understand the difference between them. This way you won’t loose presous time, as I did
Author
Jarosław Dobrzański
Software engineer at Intel Corporation.
Particularly interested in Microsoft .NET platform, Java/JEE and web technologies.
![Reblog this post [with Zemanta]](http://img.zemanta.com/reblog_e.png?x-id=74a0895b-fe33-4f4a-8d85-db61eb86e165 "Microsoft Technology Summit 2009 Photo")
![Reblog this post [with Zemanta]](http://img.zemanta.com/reblog_e.png?x-id=91778ec2-c1bd-46f8-815d-70dde86631ea "Communities to Communities (C2C) Conference Photo")
![Reblog this post [with Zemanta]](http://img.zemanta.com/reblog_e.png?x-id=58af6b28-da41-4947-9b69-73ab1e8203e1 "Security in accessing reports on Report Server Photo")
![Reblog this post [with Zemanta]](http://img.zemanta.com/reblog_e.png?x-id=1684654f-b396-4455-841d-4f557933327d "Reporting Services deploying RDL files Photo")
![Reblog this post [with Zemanta]](http://img.zemanta.com/reblog_e.png?x-id=9ade69db-f527-4c78-8dd8-fccfc1974408 "Reporting Services problem with passing parameters directly in the URL Photo")
