SSRS – How to export a csv without header and more…

April 24, 2019

1. Create an New EXPORT Device using CSV without HEADERS

By default it exports the HEADER info as well …

image

 

Solution :

Go to the Reporting server config file.

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\rsreportserver.config

Make a copy for backup reason and add this to the RENDER section

image

 

<Extension Name="CSV (No Header)" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
<OverrideNames>
   <Name Language="en-us">CSV No Header</Name>
</OverrideNames>
<Configuration>
   <DeviceInfo>
      <NoHeader>true</NoHeader>
      <FieldDelimiter>|</FieldDelimiter>
      <ExcelMode>False</ExcelMode>
   </DeviceInfo>
</Configuration>
</Extension>

 

Next restart the Reporting Service again and test it …

Connect to the Web Server to see the result.

image

 

2. Ever wanted to create a report where the EXPORT FIELDS would be different from the Report VIEW.

 

Solution :

Go to the field and set the Property DataElementOutput to NoOutput

 

image

This makes that these fields will NOT be EXPORTED  Smile

 

3. Print a label on the FOOTER page

 

Solution :

Select a label field you want to have printed on the last page only.

image

Enjoy !!

Advertisements

SSRS – SharePoint Lists Default VIEW

March 22, 2018

Once you connect to a SharePoint List you will always get connected to the list using the DEFAULT VIEW.

Which might not be what you want because it can be a FILTERED View. And therefore not showing you all the records you want.

SOLUTION :

1. Go to DataSet –> Query

image

2. Apply a Filter

image

Strangely enough if you DO NOT Apply a filter in the Query Designer. It will fetch the data using the DEFAULT VIEW set in SharePoint ???

So basically in order to get ALL the DATA, you need to SET a FILTER that covers the whole range in your data set.

Very contradictory approach Confused smile

Enjoy !


SSRS – SharePoint Lists HTML Tags

March 22, 2018

If you are working a lot using SSRS against SharePoint Lists.

You will see that some lists that contain Text are not displayed properly… Sad smile

See Example :

image

The first column shows the raw output with all the HTML Tags.

While the second column shows you the proper output you need for the report.

SOLUTION :

1. You first need to add a PLACEHOLDER in the column

image

Two times a LEFT click in the field and next a RIGHT click.

2. Secondly put in the field that has the HTML text, and choose HTML as MARKUP TYPE

image

 

Enjoy !


MS SQL – Query & Reporting Tools

November 11, 2017

Getting data out of a Database hasn’t been easier these days. Giving all the tools you have at your disposal…

Giving the fact that all the fuss about BI and Cloud Storage, Big Data etc. We seem to loose feeling with all the o

image

Here are a few examples we can use for simple and complex query and reporting purposes.

1. Query using the MS SQL SSMS

It’s obvious that SQL SSMS offers all the functionality you need to get the data out of the database.

This example shows a combination of Common Table Expression (CTE) Query combined with the PIVOT function, to generate you dataset.

image

2. Using PowerShell – Query

Re-using this quite complex Query using Scripting language is quite Powerful.

image

image

3. Excel – Query

Using Excel combined with MS Query we can re-use the same Query

image

image

image

image

4. Access – Pass-Through Query

Re-using the complex query using MS Access in a Pass-Through Query Statement.

image

image

image

5. MS SQL Reporting Services

Re-using the complex query using MS SQL Reporting Services & Report Builder

image

image

6. MS PowerPivot – Excel Addin

Re-using the complex query using MS Power Pivot – Addin

image

image

image

image

image

7. MS Power BI

Re-using the complex query using MS Power BI

image

image

This is not a limit list of tools you have a hand. There many more which you might overlook …

QlikView Client

MS Power Query

– …

For getting data out of a database you need to the proper tools, but this is not a constraint at the moment.

What is, is being able to manage all these different applications and technologies.

Bottom-line is that one you spend efforts in getting your queries right you can re-use them most any tool that comes around Smile


MS SQL – SSRS HTTP Error 500

April 15, 2016

Recently we got an MS SQL SSRS – HTTP Error 500.

image

The Reporting services was working just fine the day before?

EventViewer ID’s. you see the message that there is a security issue on the RSTempFiles folder.

image

Message: The current identity (NT AUTHORITY\LOCAL SERVICE) does not have write access to ‘C:\Program Files\Microsoft SQL Server\MSRS10_50.SIGMA\Reporting Services\RSTempFiles\’.

REASON :

The main reason is that before the SSRS server was a local server in the Domain.

Afterwards we promote the server to a Domain Controller.

As you can see on the RSTempFiles folder Security, you see that the local account SYSTEM account has become obsolete.

clip_image002

SOLUTION :

clip_image004

Next add the new Domain AD Account to grant access to the Reporting Folders.

image


QlikView – Access Data from SSRS

March 3, 2015

Since QlikView can’t access certain data sources like MS Analysis Services (SSAS) or other exotic data sources (SAP NetWeaver BI, Hyperion, TERADATA) natively.

We can fall back on the perfect middleware for this being MS SQL Reporting services

image

The approach is a simple as can be. Setup an SSRS server (can even be the MS SQL Express (Free) Edition & SSRS add-on)

The SSRS report server has natively a web service interface, exposing a SOAP and URL Interface.

Next develop your SSRS reports (which can handle multi data sources in 1 report Smile)

image

Like for example a SharePoint List combined with an Oracle database, or anything else.

Simular to PowerPivot that can access an SSRS Data Source. We can do the same with QlikView.

Use a Web File connection as Data Source

image

Fill in your report URL link and add the rs:Format=XML parameter to get an XML output from you report

image

If all goes well you will get the Report XML output and see the SSRS TABLIX and FIELDS Smile

image

That’s it, now you are ready to build your QlikView GUI

Once you know this technique you can as well use this to access an SSRS in the MS Azure cloud.Winking smile

Enjoy!


SharePoint – SSRS Web Part Parameter Issue

January 31, 2015

We had an annoying problem with a published SSRS report in SharePoint that uses a Parameter.

Most likely this appears only in combination with a external data source like an ODBC connection that retrieves data from a data source not within the current domain ?

This strange ‘bug’ I would call it so far is not showing when your run the report from within the Report Designer, but only from within SharePoint.

So I suspect that this is a malfunction in the SSRS front end Web Part.

Problem :

When selecting the first time a parameter in the report, it returns all the filtered data. But when selecting a new parameter value, for the second time, it returns a blank page ? And the parameter box jumps down.

image

Solution :

After debugging with trial and errors, we found out that if you add a Default Value, in the parameter field, the problem is solved.

image

If you get the parameter values from a different Dataset, you can also specify the Default Value using the Get Values from a Query option. But this is not needed to make it work.

Just give any valid default value in the Specify Values field will do the job as well.

image

Again, this problem is not occurring in the Report Designer itself?

So the SharePoint users are happy to see that this annoyance is gone Smile