How do Microsoft reports (RDL) query the data?

Question

I am not creating RDL from scratch so maybe this is a problem -- I work on already prepared files.

MSDN states that CommandText in RDL file can contain T-SQL query. Ok, this I understand, but what else it can contains?

I am asking because the phrasing clearly indicates you can put some other expression there So if I understand correctly, I can look at RDL code (in Visual Studio, RMB on RDL file, "view code") and the interesting parts would be...?

DataSourceName -- this is a "link" to database via definitions of data sources
CommandText -- I thought this is the place to put query, like SELECT... but from what I see there are no queries used

**Note:** I added [some info](http://stackoverflow.com/a/40524951/1016343) regarding reverse engineering a *.rdl file, but it would be great if someone could show how to find that out in Visual Studio's report editor. For this I have offered the bounty. — Matt, Nov 10 '16 at 13:45
Are you looking for RDL format specification? It's documented here: https://msdn.microsoft.com/en-us/library/ee240083.aspx — Simon Mourier, Nov 11 '16 at 06:34
No, I am rather looking for a way to use Visual Studio 2015's report editor (the UI) to reverse engineer a report without having to study the XML data. As you can see, I provided an answer how to get his information out of the XML data, but there must be a different way also by using Visual Studio. — Matt, Nov 14 '16 at 11:48

score 3 · Answer 1 · answered Oct 26 '10 at 13:31

Reporting service, loads the rdl file into it, and starts parsing and reading the command according to their sections like

data source, report params, etc.

gets the values of params (if any). start using the data source database connection. execute the query/ sp command. get the data, and store in seperate data fields which are also mentioned in rdl. binds their values with controls (text box, grid columns etc), if there is any expression written into it, execute them as well.

Generate the output (html/ pdf).

And there you Go.

I just tried to explain in short and simple words. you can check out msdn for complete detail.

Regards,

Mazhar Karimi

As I understand, the text I am seeing is the 4th type -- expression, right? So to get it work, when executing this report the expression is defined and that's how it works. Let's say the expression is "expr1" -- now I have to define some method expr1 in C# code? — greenoldman, Oct 27 '10 at 05:50

score 2 · Answer 2 · answered Oct 26 '10 at 12:35

2

You can create reports manually and fill them with any data that you would like to.

Sth like:

ReportDataSource reportDataSource = new ReportDataSource();
reportViewer.Reset();
reportDataSource.Name = "DataSetOdczyty_klienci_adresy";
reportDataSource.Value = klienciadresyBindingSource;
reportViewer.LocalReport.DataSources.Add(reportDataSource);
reportViewer.LocalReport.ReportEmbeddedResource = "Wodociagi.Reports.ReportListaKlientow.rdlc";

answered Oct 26 '10 at 12:35

kubal5003

7,186
8
52
90

1

Thank you. In my case I don't have direct C# code counterpart though and the CommandText in RDL is given. – greenoldman Oct 27 '10 at 05:47

score 1 · Answer 3 · edited May 23 '17 at 12:01

You can open the report file *.rdl with an XML editor like Notepad++. Then, search for <DataSets> and you will find the datasets used in the report.

The field names of each data set are in the <Fields> section
In the <Query> section of each data set you can find <CommandText> and <QueryParameters> as shown in the example below

Example:

  <Query>
    <DataSourceName>MyDataSource</DataSourceName>
    <CommandType>StoredProcedure</CommandType>
    <CommandText>usp_QueryCustomers</CommandText>
    <QueryParameters>
      <QueryParameter Name="@CustomerId">
        <Value>=Parameters!PersSysId.Value</Value>
      </QueryParameter>
      <QueryParameter Name="@RowsCnt">
        <Value>=Parameters!RowsCnt.Value</Value>
      </QueryParameter>
    </QueryParameters>
  </Query>

I didn't find a way to see that in Visual Studio's report editor easily. Maybe the bounty I have started helps here (does someone like to earn the 50 reputation points)?

score 1 · Answer 4 · edited May 23 '17 at 12:13

1

Initially, I was not sure why both the OP and @Matt are reading the XML directly instead of editing the query in Visual Studio (I only resort to that in extreme cases). But now I think you might have failed victims of the missing "Report Data" pane.

Open the report in Visual Studio BIDS like normally, then from View menu select "Report Data". If it's not there, click on the report canvas anywhere, then it should appear. In the "Report Data" pane that will appear, you're interested in Data Sources (where's the data coming from?) and Datasets (what are the queries, parameters, expressions?).

edited May 23 '17 at 12:13

Community

1
1

answered Nov 15 '16 at 14:00

Misza

635
4
15

Thank you! That's exactly what I was looking for. I am using VS Enterprise 2015 and open it there. In the "Report Data" pane, in the Datasets folder, when I right-click on a data set, I can select "Dataset Properties" and there I see the stored procedure associated with the report. You earned +50 and upvote, good answer! – Matt Nov 16 '16 at 08:53

How do Microsoft reports (RDL) query the data?

4 Answers4