Download reportings services report with parameters from Targit
For those using SSRS 2019 - you can skip this post and read this article: https://community.powerbi.com/t5/Report-Server/quot-RS-embed-true-quot-command-not-working-in-Sharepoint-2013/m-p/919640#M12867
Work around below also works for other site not working with iframe.
You can tweak the custom html page and display an iframe there and add correct header information using javascript to display the iframe.
With the addition of the new feature "web box" you could in theory integrate "SSRS - Sql Server Reporting Services" into Targit.
Targit however cannot display this page in the "web box".
To achieve this you could add a simple webpage to you Targit server. Either place it in the Targit website or create a new applicaiton for it on IIS in the default website.
I created a new application and called it "SSRSInterface".
In this applicaiton I added a webpage "index.html" with these contents.
<head>
<style>
.container {
position: relative;
display: inline-block;
background: #ffff0000;
width: 100%;
height: 100%;
cursor: pointer;
}
</style>
<script type="text/javascript">
function writeUrl() {
const url = window.location.search.substring(5);
window.open(url, "_blank");
}
</script>
</head>
<body>
<div class="container" onclick="writeUrl()"/>
</body>
This shows a transparent webpage. Anywhere you click will open the url in a new window.
This gives you the flexibilty to add the webbox on top of any targit objects such as labels, images, charts, tables. This way you do not have to worry about editing or styling the webpage and this can be done in Targit.
So if we use our SSRS report and use URL parameters we could either
- download the report in a given format in one go as Excel. The report will take parameters from Targit.
https://your-targit-server/SSRSInterface?url=https://your-reportserver-url/ReportServer/Pages/ReportViewer.aspx?%2fyour-report-path&rs:Format=EXCEL&CustomerCode={CustomerCode}
- browse to the report page with your value from Targit already parsed in the filter pane of ssrs
https://your-targit-server/SSRSInterface?url=https://your-reportserver-url/ReportServer/Pages/ReportViewer.aspx?%2fyour-report-path&rs:Command=Render&CustomerCode={CustomerCode}
By the way if your reportpath should contain forward slashes replace them by the html encoded value %2f.
You can see this for the root slash after this part "reportviewer.aspx?".
One way to use SSRS reports is for very detailed reports which would blow up your SSAS / Targit in memory models.
Now there is still one more issue we need to address. IIS limits the number of characters allow in the URL for query parameters.
To increase the number of characters we need to tweak the web.config file a bit. This can be found in the root folder of you website. If it does not exists you can create this file.
Because I added a new application in the default website (same one as where Targit is installed), I have to add a web.config file in the ISS folder INETPUB\wwwroot\web.config
<?xml version="1.0" encoding="UTF-8"?>
<configuration>
<system.web>
<httpRuntime maxQueryStringLength="89620" />
</system.web>
<system.webServer>
<security>
<requestFiltering>
<requestLimits maxQueryString="89620" />
</requestFiltering>
</security>
</system.webServer>
</configuration>
Tip for passing multi values to SSRS:
Set up your ssrs report parameter to be a string.
Create a second hidden parameter which is the actual parameter used in you queries. Set this one to multivalue and have it listed after the first one. Add a default value which is a formula: =split(@firstParam, "<your value seperator>")
Feature request: add required criteria to the web box object.
Comments
Please sign in to leave a comment.