Change Chart line colour in SSRS 2008?

If you are a regular user of SSRS charts, you will know that charts use built-in predefined colour palettes. I will demonstrate this by using Line Charts which is already setup for our example. In the snapshot below, you can see that we have multiple lines each representing a series and has a unique colour. However, these colours were assigned by SSRS automatically. Some times the colours are too similar and would be hard to differentiate between them. So, it would be nice to change them with our own custom palette colours.

To do this, you have to be in Design mode in SSRS and follow the steps below:

1. Click once on the Chart to select its properties. Make sure that you have selected the Chart – a double click on the Chart will bring Chart Data properties rather than the Chart itself.

2. If you have selected by mistake Chart Data, you can switch to Chart properties by choosing Chart from the list of object names under Properties window box:

3. Under the Chart section in the Properties window, choose CustomPaletteColors and click on the botton to open the dialog window:

4. Add your custom colours that you like to create your own palettes. This site will help you to choose a colour scheme (http://www.colorschemer.com/schemes/index.php):

5. Finally, we can see that now our Chart is using the custom colours which we added rather than the predefined built-in colours:

http://www.soheib.com/technical-knowledge/how-to-change-chart-line-colour-in-ssrs-2008/

SSRS Prompt for username and password in IE

After the successful installation of SQL Sever Reporting Services 2012 or 2008 R2 , you will get constantly prompted with Windows Security dialog box to enter login details. 

It is easy to fix this issue by following these steps:

1. Open Internet Explorer (IE9) and from the tools menu, choose Internet Options:

2. From the Security Tab, choose Local intranet and click on Sites:

3. Click on Advanced button:

4. Type in localhost in Add this website text field and click on Add button (Un-tick the box Require server verification (https:) for all sites in this zone):

All done. If you open your IE again and type in Reporting Services URL, you will no longer be prompted to enter login details.

Source: http://www.soheib.com/technical-knowledge/ssrs-prompt-for-username-and-password-in-ie/

Multiple Result Sets in SSRS

Using SQL Server Reporting Services 2008R2 and AdventureWorks2008R2. I built a stored procedure that returns all products in a category, then a count of the number of products in the category, and the count of the number of products in that category in a specific color.

ALTER PROCEDURE [dbo].[ProductCountByCatColor]

    @Category varchar(25),

    @Color varchar(25)

AS

BEGIN

    SET NOCOUNT ON;

    SELECT PC.Name AS Category,

        PROD.ProductNumber,

        PROD.Name,

        ISNULL(PROD.Color, 'N/A') AS Color,

        ISNULL(PROD.Size, 'N/A') AS Size,

        ISNULL(PROD.[Weight], 0) AS Weight

    FROM Production.Product PROD

        INNER JOIN Production.ProductSubcategory PS ON PS.ProductSubcategoryID = PROD.ProductSubcategoryID

        INNER JOIN Production.ProductCategory PC ON PC.ProductCategoryID = PS.ProductCategoryID

    WHERE PC.Name IN (@Category)

    ORDER BY Category

    SELECT PC.Name AS Category,

        COUNT(PROD.ProductNumber) as ProdCount

    FROM Production.Product PROD

        INNER JOIN Production.ProductSubcategory PS ON PS.ProductSubcategoryID = PROD.ProductSubcategoryID

        INNER JOIN Production.ProductCategory PC ON PC.ProductCategoryID = PS.ProductCategoryID

    WHERE PC.Name IN (@Category)

    GROUP BY PC.Name

    ORDER BY PC.Name

    SELECT PC.Name AS Category,

        COUNT(PROD.ProductNumber) as ProdCount

    FROM Production.Product PROD

        INNER JOIN Production.ProductSubcategory PS ON PS.ProductSubcategoryID = PROD.ProductSubcategoryID

        INNER JOIN Production.ProductCategory PC ON PC.ProductCategoryID = PS.ProductCategoryID

    WHERE PC.Name IN (@Category)

        AND ISNULL(PROD.Color, 'N/A') IN (@Color)

    GROUP BY PC.Name

    ORDER BY PC.Name

END

When I execute the SP in SSMS, I get three result sets.


Now, I set up my report to use a stored procedure in the dataset.


I don’t even need to run the query to notice that the dataset only shows fields from the first result set.


When I run the report, I only see one set of results – the first listed in my stored procedure.

Why? The short answer is: it’s designed that way. According to BOL, “Multiple results sets from a single query are not supported.” (http://msdn.microsoft.com/en-us/library/dd239379.aspx)

How can you work around this?

If each result set is a separate query, you can put each query in a separate stored procedure, or dataset.

If the result sets need to tie together, you’ll need to work with the T-SQL to make it one result set. That may require temp tables, UNIONs, or another solution.

http://blogs.lessthandot.com/index.php/DataMgmt/ssrs/t-sql-tuesday-024-reporting 

Generating an SSRS Report From an XML Datasource

We can retrieve data from XML data sources directly with Reporting Services using the XML data provider. It converts XML structure into a data set and SSRS uses same to generate report.

We can use the XML content directly within the query and can generate report. We can build query using expressions to build queries and data dynamically within the report. We can use XML data provider in any of below manners.

XML Embedded Within the Query

We can use the XML content directly within the query and can generate report. We can build query using expressions to build queries and data dynamically within the report.

XML Using URL

We can use HTTP protocol to read XML content and can generate data based on that. In this post, we have sample with this option.

XML Using Web Services

The XML data provider can request Web services and in response of that, it can parse SOAP response into XML structure. We will see example of this option soon in some next post.

Let’s review one example of XML data provider using URL here. 

Step 1
As I have already mentioned that we can use any external xml file in HTTP protocol and can generate data, I have make one virtual directory in my localhost for demo purpose and create one xml file named “MyXMLFile2.xml” which gives data in XML structure (figure 1).

Figure 1

01.<Invoices>

02.<Invoice>

03.<InvoiceDetail>

04.<InvoiceNo>12201</InvoiceNo>

05.<Name>Sarah Greenberg</Name>

06.<InvoiceDate>03/15/2010</InvoiceDate>

07.<InvoiceAmount>145</InvoiceAmount>

08.</InvoiceDetail>

09.<InvoiceDetail>

10.<InvoiceNo>12203</InvoiceNo>

11.<Name>Joe Gonzalez</Name>

12.<InvoiceDate>04/12/2010</InvoiceDate>

13.<InvoiceAmount>255</InvoiceAmount>

14.</InvoiceDetail>

15.</Invoice>

16.</Invoices>

Step 2

Create new datasourace with “XML” as datasourace type. In connection string, you need to enter virtual path of your xml file like in this example, I have entered here 'http://localhost/MyXMLFile/MyXMLFile2.xml' in this new datasource “Datasource1” in Figure 2.

Figure 2

Step 3

Create new dataset like “DatasetXML” in Figure 3. Assign “Datasource1” as datasource , We can write query here to retrieve data from our source xml file mentioned in Figure 1. Keep Query type as “text” as its xml datasource. “Query” retrieves data here based on XPath in “Element Path”. You can keep query area blank and make query using “Query Designer” too. Click on button “Query Designer” for same.

 

Figure 3

Step 4

I like to make query from dialogue box as I can write my query here with different “element path” and preview data in same box by selecting the exclamation button (!) (Figure 4). The “element path” states a structure using XPath, and by the XML Data Provider retrieves the data from the XML datasource as per this element path. Query uses XPath but pay attention here in syntax. I have added empy {} here as I do not want values from these nodes.

You can copy code for “query” from here. 

1.<Query>

2.<ElementPath>

3.Invoices {}/es:Invoice{}/es:InvoiceDetail

4.</ElementPath>

5.</Query>

Figure 4

As you save your dataset changes , it would create datasource and you can find fields of query under new datasource in Data Window (Figure 5). Now you can design table and assign different fields in table in RDL file and your report is ready for preview (Figure 6)

Figure 5

Figure 6

So report is ready now. I hope, you like it !!!