Introduction

At some time in all of our working lives we need a little guidance from the community, and there is no better place to start than the forums at SQLServerCentral (SSC). Sometimes though, we need a little help with posing the question in the right way to best facilitate those wonderful volunteers who offer their time to assist us.

This article intends to address the problem of how to post a Reporting Services problem in such a way that it becomes simple for the community to not only understand your question, but provide you with a working solution – something that can be pretty tricky at times.

Why do we need this article?

If you take a look at the SSRS forums on SSC, and on other sites, you will often see questions such as these:

• “I have a multi-column report and can’t get the left indent to line up with my status code”
• “How can I display the time without days?”
• “Can I put breaks in for saving as CSV?”

All these questions are fairly hard to answer without seeing the report and being able to “feel the problem”.

This article will describe a method of providing a working sample report that anyone can safely pick up, modify and pass back as a working solution without the need for any database connections. There is nothing better than an actual working example to help you see how to solve a problem and if you follow the simple steps in this article, you will soon reap the benefits.

Embed your sample data in the report

Something you may not know is that you don’t have to connect an SSRS report to a database as its data source. You can actually store the data within the report, making the report self-contained and portable, which makes it an ideal way of providing a sample of your specific reporting problem to the community.

You can follow along with this section by firing up Visual Studio or SQL Data Tools and starting a new Reporting Services project. All you will need is a new empty report or you could load up the sample attached to this article and just inspect the elements as we go along.

Creating an embedded data source

In order to store data within the report definition, you will need to create an XML Data Source, which you can do by right-clicking on the “Data Sources” node of the “Report Data” tree and selecting “Add Data Source…”

Provide a “Name” for your data source and choose “XML” from the “Embedded Connection” drop down list.

You will also need a “Dataset” for the sample report, which can be created by right-clicking the “Datasets” node and selecting “Add Dataset…”

Provide a “Name” for your new dataset and select “Use a dataset embedded in my report”. Select the newly created data source from the “Data source” drop down list and click OK.

You now have a report Dataset which is embedded in the report and connects to a Data Source which is also embedded in the report, but there is not yet any data for the report to consume.

Where is my data coming from?

In order to populate the report with some test data, you will need to have created some, which is where we switch to SSMS or open a query connection to the database. For my sample, I am going to keep things simple as the point of this article is not to learn how to produce XML data, but rather how to use that data to provide a working sample report. Let’s say I have a small table of people with activities and dates.

(All names are randomly generated for the purpose of this article. Any resemblance to persons living or dead is purely coincidental.)

This data is going to form my sample data set, but first I need to turn it into XML in the correct format for an RDL data source. The embedded XML data source requires a root element called “Query” underneath which is another element called “XmlData” and it is within this element that we must place our data.

<Query>
 <XmlData>
  ….
 </XmlData>
</Query>

To achieve this from our sample data is simple, first we need a SELECT to get the data.

SELECT Name
      , Description
      , Activity
    FROM PeopleDidThings;

This query then needs to be embedded inside this template wrapper.

SELECT Root
 from
 (
  <place query here>
  for xml auto, type
 ) XmlData(Root)
FOR XML AUTO, ELEMENTS, ROOT('Query');

Once the SELECT query has been placed inside the template wrapper above, we have a final query:

SELECT Root     FROM ( SELECT Name               , Description               , Activity             FROM PeopleDidThings          FOR            XML AUTO              , TYPE          ) XmlData ( Root )     FOR XML AUTO           , ELEMENTS           , ROOT('Query')

This produces the desired XML output for an RDL embedded dataset.

Now we can copy the resulting XML to the clipboard, switch back to our Report definition, open the Dataset properties and paste the XML into the “Query” window.

In order to refresh the column metadata in the report definition, you now need to click the “Refresh Fields” button. This will update the dataset with the column names from your sample data.

Data types in XML datasets

Due to the character nature of an XML dataset, data types can be a problem, but thankfully there is a solution – ElementPath. The ElementPath node in an XML Data Query defines what part of the XML data you want to use in the Dataset and can also be used to cast nodes as specific data types.

ElementPath is added to the XML data as a child of the “Query” node. Here is the ElementPath for this sample report:

  <ElementPath>
            Root {}/PeopleDidThings{ @Name, @Description, @Activity(Date) }
  </ElementPath>

It tells the report that we want to use the Data found in the “PeopleDidThings” element, returning the @Name, @Description and @Activity fields as columns in our dataset, additionally casting the @Activity field as a “Date”. The image below shows the final XML dataset query.

Build your report layout

With the embedded data in place, you can now build a report design that demonstrates the problem you are experiencing, or simply demonstrates what you have attempted whilst trying to achieve the desired report behaviour. The resulting RDL file can be uploaded to the SSC forums along with your problem description.

Conclusion

In this article, I have demonstrated a method for posting sample reports to the forums. This will allow the community to see precisely what is happening with your report and can easily provide you with an updated report definition that will demonstrate a solution to your problem.

If you are encountering an error or cannot get an expression to work right, then posting a sample report along with a description of what you are trying to achieve and how it is failing should be enough to get help. For more complicated layout / rendering problems you should try to produce a visual guide to the problem, such as a screenshot or a mock-up of the desired layout.

You may find it helpful to read the articles in the section “How to post other types of problems” for more advice on how to get the best help on SSC.

Happy reporting!

How to post other types of problems

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden

How to Post Performance Problems - by Gail Shaw

Further reading

Walkthrough: Defining a Report Dataset from Embedded XML Data

Element Path Syntax for XML Report Data (SSRS)

Acknowledgements

I would like to thank Koen Verbeeck and Jeff Moden for giving up their valuable time to proof read this article.