none
SQL server 2016 mobile reports - personalise user querys RRS feed

Answers

  • Hi julsey50,

    According to your description, there exists a parameter called @UserID in your mobile report dataset. And you want to pass the current user id who is visiting the report as a value for the parameter. Right?

    In current Reporting Service 2016 mobile reports. It is not supported to use built-in functions as a parameter value for mobile reports. As in your scenario, we can get current user id by using =User!UserID function in common SSRS reports. However as we use this function to give a parameter value for shared dataset for mobile reports. We will get just a string type value as User!UserID.

    So I’m afraid your requirement cannot be achieved. Also You could provide Microsoft a feature request at https://connect.microsoft.com/SQLServer so that we can try to modify and expand the product features based on your needs.

    If you still have any questions, please feel free to ask.
    Thanks,
    Xi Jin.

    Thursday, July 14, 2016 9:33 AM
    Moderator

All replies

  • Hi julsey50,

    According to your description, there exists a parameter called @UserID in your mobile report dataset. And you want to pass the current user id who is visiting the report as a value for the parameter. Right?

    In current Reporting Service 2016 mobile reports. It is not supported to use built-in functions as a parameter value for mobile reports. As in your scenario, we can get current user id by using =User!UserID function in common SSRS reports. However as we use this function to give a parameter value for shared dataset for mobile reports. We will get just a string type value as User!UserID.

    So I’m afraid your requirement cannot be achieved. Also You could provide Microsoft a feature request at https://connect.microsoft.com/SQLServer so that we can try to modify and expand the product features based on your needs.

    If you still have any questions, please feel free to ask.
    Thanks,
    Xi Jin.

    Thursday, July 14, 2016 9:33 AM
    Moderator
  • Xi Jin,

    I noticed that you are responding to a lot of posts about the mobile reports.  Are you associated with the Mobile Reports product?  We have run into many limitations and bugs and would like to know how to report them.  In regards to this particular thread, without the ability to pass in a parameter to a Mobile Report, you are seriously limiting the application for the report.  Passing in a parameter is a necessity for any reporting product, I don't understand why it wasn't thought of.  I know the product is based on DataZen, but you HAVE to be able to use parameters in reports.

    Wednesday, August 3, 2016 3:11 PM
  • Hi Phill,

    You can pass parameters to Mobile Reports - this is covered in the documentation here:

    https://msdn.microsoft.com/en-us/library/mt703703.aspx

    You can also use row-level security options available in SQL Server 2016 and in Analysis Services.  I cover this topic in my blogpost here - https://christopherfinlan.com/2016/03/19/row-level-security-options-for-mobile-reports-in-sql-server-2016/

    If you have feedback on the product, you can report that using the Connect site at - http://connect.microsoft.com

    You can also report issues there or contact your Microsoft support rep.  Hope this answers your questions.

    Thanks,

    Chris

    Wednesday, August 3, 2016 10:43 PM
  • Hi Chris. Thank you for responding but unfortunatlry we need to dynamically pass the parameter to the report before it is first loaded. Imagine that you are using a mobile report to present data to a customer. The data source for the report contains data for many all customers however, you only want to present data relavent to the customer logging in. In that scenario there is no predefined filter that will work because you don't want customers to be able to see each other's data. Also, as these are external customers, you don't want to create Active a Directory users in your domain just so you can use row level filtering. Without being able to pass in parameters to the report before it's loaded, you are exposing all data to all users and that really does limit the application for the reports to internal or non sensitive uses.
    Wednesday, August 3, 2016 11:43 PM
  • Hi Phill,

    Yes, of course - the data is dynamically filtered based on the user information for the logged in user - the records returned are only the records they have access to see.  The user parameter for row-level security scenarios doesn't work the same way other parameters do, as I describe in my blogpost I linked to in my response.

    Also, you don't have to use Active Directory for your scenario - for example, if you use Azure SQL database, you can create users in your database that match the user ID that is passed as part of the query, and it will work the same way.

    Hope this helps,

    Chris

    • Proposed as answer by chathux Wednesday, October 26, 2016 3:34 PM
    Thursday, August 4, 2016 3:46 AM
  • Hi Chris,

    We have over 200 users right now with new users being added all the time by non-I.T. staff.  Having to create database users in our SQL database and setting up Row level filters would be almost unmanageable unless we had dedicated resources to do it (which we don't).

    What we specifically need to do is allow a user to login to our website, then we read 2 parameters (the customer ID and an optional Cost Center ID) associated with the account and apply that to the report before the user sees any data.  I know it's possible to do that with the native Microsoft Reports (paginated Reports) but not the Data Zen (Mobile Reports).

    It just seems like there should be some way other than row level security to restrict data in the reports.

    Thursday, August 4, 2016 3:02 PM
  • Hi,

    If your Shared Dataset has a Parameter option (Load on Demand) you can pass in the value you would like to assign using the querystring like this:

    1 - Get the name of the Data Set as labelled in MRP (example TimeChartLoD)

    2 - Add the querystring parameter for your value (example the parameter is called category -- TimeChartLoD.category=Clothing)

    Hope this helps.

    Wednesday, August 31, 2016 3:51 PM
  • Due to bugs in the Query Builder of the Dataset designer in the Report Builder, it's very difficult to make an MDX query that connects to an SSAS Model with parameters.  I have a separate post on it.

    https://social.msdn.microsoft.com/Forums/en-US/9b61aa5e-7554-4612-86fb-361049b8667a/huge-mobile-report-publisher-bug-dont-think-about-connecting-to-ssas-model-with-any-parameters?forum=sqlreportingservices

    Friday, September 2, 2016 8:19 PM
  • Any way you can elaborate on this?  Like blank out your server name and show the entire URL string?  The URL in the image is pointing to the DataSources folder.  Are you manipulating the data source itself using the parms, if so how are you then executing the report?

    Any assistance would be greatly appreciated.
    Friday, October 28, 2016 6:51 PM
  • Hi

    I've struggled with passing dynamic params to shared Dataset (using expressions such as: =Globals!ExecutionTime [&ExecutionTime]).

    The parameter passed to stored procedure was a string literal @StartDate = 'Globals!ExecutionTime'.

    or 'User!UserID'

    Solution was to label these parameters in the dataset as "Internal (read only)". The values passed is now:

    @StartDate='2018-05-12 09:02:45.657', it also correctly passes my windows userid.

    I developing on the same machine as the SSRS and the database, so I am not sure if the user name will work over the internet/external logins.

    Hope this helps..

    Monday, May 14, 2018 9:15 AM