November 10, 2019
Let’s say you have spend a lot of time to build SSRS reports, and you want to re-use these data sets in Power BI.
Well this is possible even if you are running the older version of SSRS.
Even if this report is running in Native or Integrated SharePoint Mode, or is a Parametrized report
The SSRS report server has natively a web service interface, exposing a SOAP and URL Interface.
Open Power BI and GET DATA from the Web Connector
Fill in your report URL link and add the rs:Format=XML parameter to get an XML output from you report
If all goes well you will get the Report XML output and see the SSRS TABLIX and FIELDS
October 31, 2019
If you need a Multi Select Drop down box that fills up dynamically…
This is the way to do it.
1. Create a new data set
That holds the query to populate the drop down box.
Make sure you set the WHERE clause to point to new Multi Select Parameter
2. Create a new Parameter
Set the parameter settings to ALLOW MULTIPLE VALUES
And next set the available values to retrieve the data from the above Query
Next in Advance use these settings
4. Main ‘DataSet1’ Query selection settings
In the main dataset make sure that you set the WHERE clause :
To point to the parameter using this format IN (@yourparameter)
IMPORTANT : Notice the brackets around the Parameter name !
If you forget them you will get errors !
The result is that your drop down box will populate dynamically,
based on the previous parameter values
April 24, 2019
1. Create an New EXPORT Device using CSV without HEADERS
By default it exports the HEADER info as well …
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
<Extension Name="CSV (No Header)" Type="Microsoft.ReportingServices.Rendering.DataRenderer.CsvReport,Microsoft.ReportingServices.DataRendering">
<Name Language="en-us">CSV No Header</Name>
Next restart the Reporting Service again and test it …
Connect to the Web Server to see the result.
2. Ever wanted to create a report where the EXPORT FIELDS would be different from the Report VIEW.
Go to the field and set the Property DataElementOutput to NoOutput
This makes that these fields will NOT be EXPORTED
3. Print a label on the FOOTER page
Select a label field you want to have printed on the last page only.
January 27, 2019
SentryOne Plan Explorer :
It is Free see here
You can download here
Simplify Your SQL Server Query Tuning
Plan Explorer helps you quickly find and fix query problems …
You can do index Analysis as well
May 9, 2018
If you need to install only the SQL Management Studio these days, you will need to go to maximum SQL 2014 !
In the later versions this is not possible anymore to separately selecting the SSMS
Download the version INCLUDING the ADVANCED TOOLS
Use this option
Choose only “Management Tools – Basic and Management Tools – Complete” as shown below:
And you are ready to rock and roll …
Tip : When you need to do an SQL Azure Backup to an onside server YOU NEED SSMS a the tool to do so !
It will save your SQL Backup as *.bacpac file
See here for more info :
Export can also be triggered from Azure Portal and PowerShell scripts or using Command Line Tools :
Data-Tier Application Framework : https://www.microsoft.com/en-us/download/details.aspx?id=46898
SqlPackage.exe is part of the SSDT : https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017
More info : https://msdn.microsoft.com/en-us/library/hh531248(v=vs.103).aspx
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.
1. Go to DataSet –> Query
2. Apply a Filter
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
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…
See Example :
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.
1. You first need to add a PLACEHOLDER in the column
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