Report Server: Passing multiple values to query parameter in multiple reports using Report Builder
Summary: We have power bi premium and love the new analytical reports, but needed to keep the functionality of our Business Objects report server that emailed out excel versions of the reports with multiple tabs of data. So I brought up the Power BI Report Server and am using Report Builder to create these reports. The fact SSRS didn't have tabs, per se, in reports forced me to use a specific solution: Insert "Rectangles" where I could add page breaks after each one and then insert a sub report. Works surprisingly well, but requires many files (subreports) that all have their own similar datasets that used a similar query. The delima is that i need to execute a rather complex sql statement that includes a where clause with an "in ('xxx','xxxx',xx')". The embarrassing part... this list of values for this statement is a manual list generated monthly . It can not be had from the EDW. If it were 1 report. I'd update once, grin and bear it. But this other solution for multiple tabs on excel sheets created a cluster of reports that all need the same filter applied. To all of you experts out there... how can I pull this off? -Can't use report level filters, need query level filter -Can't figure out exact syntax in query parameter expression window to include hardcoded values for the "in clause" -Unclear if I can publish a shared dataset using the PBI Report Server/Report Builder only? Seems to require steps in Report Designer in SQL Server Data Tools (SSDT)? Is there are workaround for this? so I can consume a shared dataset into each of these reports I need to apply the filter. -what about available global parameter/variable