Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

URL Filters for on-premise reports

Hi All, We have on-premise power bi reports, We wanted to filter with URL parameters. I have tried like below. But reports are not filtering based on values. Is the feature work with on-premise server?

 

http://Server/Report Folder/Report Name?rs:Embed=true&filter=Table/Field eq 'Value'

http://Server/Report Folder/Report Name?rs:Embed=true&filter=COVID Test/GroupCode eq 'CB'

 

If we have space for table or field

 

http://Server/Report Folder/Report Name?rs:Embed=true&filter=COVID%2FTest/GroupCode eq 'CB'

http://Server/Report Folder/Report Name?rs:Embed=true&filter=COVID%20Test/GroupCode eq 'CB' http://Server/Report Folder/Report Name?rs:Embed=true&filter=COVID0x20Test/GroupCode eq 'CB'

 

Please help me out on this.

On-Premise
url parameter

5 Replies

  • d_gosbell's avatar
    d_gosbell
    Icon for Super User rankSuper User
    6 years ago

    This work on my May 2020 instance of PBIRS.

     

    Anonymous wrote:

    If we have space for table or field

     

    It should be as per the instructions for special characters here https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-url-filters#special-characters-in-table-and-column-names where you use _x0020_ for a space. So something like the following should work.

     

    http://Server/Report Folder/Report Name?rs:Embed=true&filter=COVID_x0020_Test/GroupCode eq 'CB'

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi d_gosbell,

       

      Your suggestion worked like a charm.

       

      I heard query string filtering only works with text data type. Is there any restriction for filtering with query string.

       

      Thank you

       kranthi

      • d_gosbell's avatar
        d_gosbell
        Icon for Super User rankSuper User
        6 years ago
        Anonymous wrote:

         

        I heard query string filtering only works with text data type. Is there any restriction for filtering with query string.

         

        No this is not true, I just filtered on a numeric column and it worked fine. I don't really use URL filtering much myself. I'd expect any limitations to be in the documentation like I posted previously.

  • cirosr's avatar
    cirosr
    New Member
    3 years ago

    I recomend using below pattern to allow users to "bookmark" filters they currently have selected:

    (assuming you are using On-Prem PBIRS. PBI.com cahces your last filter options, does not need the below)

     

    In my reports, created as a button. Covers most used unichars transformations, add if necessary.

    Generate Filter Link = 

VAR VBU = 
IF(
     ISFILTERED('Business'[BU]) = TRUE(),
     VAR ___f = FILTERS ( 'Business'[BU])
     VAR ___r = COUNTROWS ( ___f )
     VAR ___d = CONCATENATEX ( ___f, 'Business'[BU], "%27,%20%27" )
     VAR ___x = "Business/BU%20" & 
        SUBSTITUTE ( 
            SUBSTITUTE ( 
                SUBSTITUTE ( 
                    SUBSTITUTE (
                        SUBSTITUTE ( 
                            SUBSTITUTE ( IF(___r = 1, "eq%20%27" & ___f & "%27", "in%20(%27" & ___d & "%27)")
                            , "&" , "%26" )
                        , "-" , "%2D" )
                    , ")" , "%29" )
                , "(" , "%28" )
            , "," , "%2C" )
        , "/" , "%2F" )
     VAR ___y = IF( ___d = BLANK() , BLANK(), ___x)
     RETURN ___y, BLANK())

VAR VCategory = 
IF(
     ISFILTERED('CategorySegment-Filter'[CategoryCode]) = TRUE(),
     VAR ___f = FILTERS ( 'CategorySegment-Filter'[CategoryCode])
     VAR ___r = COUNTROWS ( ___f )
     VAR ___d = CONCATENATEX ( ___f, 'CategorySegment-Filter'[CategoryCode], "%27,%20%27" )
     VAR ___x = "CategorySegment_x002D_Filter/CategoryCode%20" & 
        SUBSTITUTE ( 
            SUBSTITUTE ( 
                SUBSTITUTE ( 
                    SUBSTITUTE (
                        SUBSTITUTE ( 
                            SUBSTITUTE ( IF(___r = 1, "eq%20%27" & ___f & "%27", "in%20(%27" & ___d & "%27)")
                            , "&" , "%26" )
                        , "-" , "%2D" )
                    , ")" , "%29" )
                , "(" , "%28" )
            , "," , "%2C" )
        , "/" , "%2F" )
     VAR ___y = IF( ___d = BLANK() , BLANK(), ___x)
     RETURN ___y, BLANK())

VAR VSegment = 
IF(
     ISFILTERED('CategorySegment-Filter'[SegmentCode]) = TRUE(),
     VAR ___f = FILTERS ( 'CategorySegment-Filter'[SegmentCode])
     VAR ___r = COUNTROWS ( ___f )
     VAR ___d = CONCATENATEX ( ___f, 'CategorySegment-Filter'[SegmentCode], "%27,%20%27" )
     VAR ___x = "CategorySegment_x002D_Filter/SegmentCode%20" & 
        SUBSTITUTE ( 
            SUBSTITUTE ( 
                SUBSTITUTE ( 
                    SUBSTITUTE (
                        SUBSTITUTE ( 
                            SUBSTITUTE ( IF(___r = 1, "eq%20%27" & ___f & "%27", "in%20(%27" & ___d & "%27)")
                            , "&" , "%26" )
                        , "-" , "%2D" )
                    , ")" , "%29" )
                , "(" , "%28" )
            , "," , "%2C" )
        , "/" , "%2F" )
     VAR ___y = IF( ___d = BLANK() , BLANK(), ___x)
     RETURN ___y, BLANK())
     
VAR VBuyer = 
IF(
     ISFILTERED('Buyer-Link'[Buyer]) = TRUE(),
     VAR ___f = FILTERS ( 'Buyer-Link'[Buyer])
     VAR ___r = COUNTROWS ( ___f )
     VAR ___d = CONCATENATEX ( ___f, 'Buyer-Link'[Buyer], "%27,%20%27" )
     VAR ___x = "Buyer_x002D_Link/Buyer%20" &
        SUBSTITUTE ( 
            SUBSTITUTE ( 
                SUBSTITUTE ( 
                    SUBSTITUTE (
                        SUBSTITUTE ( 
                            SUBSTITUTE ( IF(___r = 1, "eq%20%27" & ___f & "%27", "in%20(%27" & ___d & "%27)")
                            , "&" , "%26" )
                        , "-" , "%2D" )
                    , ")" , "%29" )
                , "(" , "%28" )
            , "," , "%2C" )
        , "/" , "%2F" )
     VAR ___y = IF( ___d = BLANK() , BLANK(), ___x)
     RETURN ___y, BLANK())

var linkuc = COMBINEVALUES(" and ", VBU, VCategory, VSegment, VBuyer)

var linkc0 = IF(VBuyer = BLANK(), SUBSTITUTE(linkuc, " and ", "", 4), linkuc)
var linkc1 = IF(VSegment = BLANK(), SUBSTITUTE(linkc0, " and ", "", 3), linkc0)
var linkc2 = IF(VCategory = BLANK(), SUBSTITUTE(linkc1, " and ", "", 2), linkc1)
var linkc3 = IF(VBU = BLANK(), SUBSTITUTE(linkc2, " and ", "", 1), linkc2)


Var LinkClean = linkc3

VAR FullLink = IF(linkclean = BLANK(), BLANK(), "https://PBIRS/reportfolder/YOUR_REPORT_NAME?rs:embed=true&filter=" & linkclean)

RETURN
FullLink

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    I haven't been able to get the on prem powerbi reports to accept any filters. Is there any particular setting or circumstances that are needed? I thought I read somewhere that only paginated reports could be filtered with URL.