Data Filter With Report Model
Sep 24, 2007
I am trying to applying an data filter that will filter out user based on userid
Three tables:
tblPerson (fact table): contains over 1 million records with about 20 fields. One of the fields is called BureauID (int). BureauID indicates what Bureau the person works in for an given record.
tblBureau (attribute table) which has a FK relationship to tblPerson) contains the following fields:
BureauID int - this is the linked field to tbl person
Bureau Code varchar
Bureau ShortName varchar
The reason we use tblBureau as in attribute table instead of place the bureau code and short name directly into table Person is so that we index tbl person quickly with "ints".
tblDataAccess, which has two fields loginID with bureau Code. The bureau code in this table tells me what bureau that userid has access to.
How do i get the report model to filter based on this userid????. I know i have to add some data filters but i am not sure where.
I put an filter on DataAccess so that login = getuserid().
But what do i do to table person or table bureau, so that a person can only see the people in there bureau.
Thanks for you help
Ryan Swann
View 5 Replies
ADVERTISEMENT
Oct 18, 2007
I have built a data model in visual studio that I am using with report builder in reporting services. The data model is using data from a view in SQL Server 2005. I am trying to apply a filter to the data model to restrict the data available in report builder. I know that I could change the code for the view and filter that data that way, but I would rather creates two models each one producing differnt sets of filtered data. I have tried adding a filter to the data model, but it does not seem to work. Do anyone have any suggestions?
View 3 Replies
View Related
Jan 24, 2007
In a Model designer when you select and Entity you have list of Attributes on the right side. There you have the option to add new "Folder", "Source Field", "Expression", "Role" and "Filter".
Can anyone explain in a bit detail what this filter can be used for. Can it be used to filter data from underlying entity? (I am not looking for SecurityFilter)
I am working on Microsoft SQL Server 2005 - 9.00.3027.00
Thanks,
Rahul
View 4 Replies
View Related
Jan 9, 2008
I have a MSSQL Report Services Report Model set up to allow users to create their own ad hoc reports. The data source for my model is a Named Query that queries a MS SQL view that actually pulls data from a series of tables & other views. When you create a report from this model and attempt to filter the data some of the fileds will provide you with a pick list to select which values you would like to filter on and other fields do not provide you with a pick list but require you to enter the data directly that you wish to filter on. Most of the data fields that I am trying to filter on are varchar fields and like I said some will create a pick list for you to selet from and others will not. This all seemed to start after I changed my data source to a Named Query rather than having my data source as the MS SQL View directly. I did this becuase it seemed that anytime I had to make a change to the views that the data source pulled from it would mess up any existing reports that had been created and this does not happen if I use a named query. I have gone crazy trying to figure this one out so any ideas would be greatly appreciated!
View 1 Replies
View Related
Jun 21, 2005
Good morning,
View 7 Replies
View Related
Dec 5, 2005
Running 2005 Beta 3 Refresh. When I first deploy, it works fine. Subsequent deployments yield the following error:
View 9 Replies
View Related
Jul 18, 2006
I perform data mining on all products and a specific product category.
Do I need to create 2 data source views, one for all products and the other one for the specific product category?
Afterward, I run the Data Mining Wizard 2 times to create 2 mining structures.
I also need to add the same mining model (e.g. Bayes, Cluster) to each of these mining structures.
Is there any simple way to do it?
Thanks.
Joe.
View 3 Replies
View Related
Jan 22, 2008
Hi All,
I have an SQL2005 db where I have some data I want to retrieve for a report. There are a couple of related tables holding some secondary info linked by foreign keys and one large table holding the bulk of the data. On my site I want to have a page where people can select filter options such as to display items that were entered before a date, after a date, between two dates, objects that are marked as complete in the table, etc.... Essentially various optional filters.
What I'm wondering is what's the best way to handle building the query to retrieve this data for the next page to display the report? I can think of a couple of ways but would essentially result in a lot of messy combining of strings to create the SQL query either in the C# code or in the stored procedure itself. I'm guessing, and hopping, there might be a cleaner method for this as it's a rather common thing I would think.
Thanks :)
View 1 Replies
View Related
Feb 22, 2007
I have been generating report models for users to use with Report Builder and there is no data when they select the model. I noticed that the tables I chose did not have a primary key and when I chose a different table, with a primary key, and generated a model from it, then there was data for the user to use in Report Builder.
Is there a documented work around or will I need to set a primary key on each table?
View 3 Replies
View Related
Feb 1, 2008
Hi,
I created a report model using reporting services, deployed the model locally and then built a report based on that model. The report works fine, but any data updates don't show up, seems like data is cached. I bounced reporting services, cleared cached tables in ReportServerTempDB but of no use. Has anybody faced this problem? How can I get updated data for reports built through a report model. Any help is appreciated.
Thanks,
Sri
View 4 Replies
View Related
Feb 8, 2007
Hi all
I have created security roles that restrict access to a certain Dimension
and a member therein.
The security works fine when the users finened in it runs a report:
The data is accordingley ristricted.
The problem is when those same users run Report Builder and create a report,
those members are no longer restricted and they have acces to absoluteley
all data from the cubes.
Is there some way that I can force the Data Model to follow the cube's security roles?
Any help is much appreciated...
Gerhard Davids
EDIT:
I have found that it uses the wrong user when opening report builder.
For some reason it uses my windows account instead of the one I used to log onto
report manager. This is way the security isnt working
Any thoughts what may be causing this?
View 3 Replies
View Related
Jan 29, 2007
Hi,
I've used the Dec CTP of SQL 2005 SP2 to create a Report Server Project.
In this project, I can create an XML datasource and successfully use that datasource in a report published on Sharepoint using the new Sharepoint AddIn.
I've also used Dec CTP of SQL 2005 SP2 to create a Report Model Project.
In this project I can create SQL Data Sources, DataSource Views andReport Models which I deploy to sharepoint and create a report builder based report from.
What I can't do is create an XML based datasource in a Report Model Project. Also, if I create one solution containing both types of project, the Report Model Project Data Source Wizard suggests I can create a data source based on an existing data source in my solution. What I find is that my XML data source from the Report Server Project is not listed. In fact even if I create a SQL data source in the Report Server Project that isn't available either.
Please let me know what should work now/RTM.
Thanks
Martin
View 2 Replies
View Related
Jan 17, 2008
Hello,
I've created a Report Model Project that can be used by Report Builder to generate ad-hoc reports. I'm trying to create a connection string in my Report Server Project that points to the Report Model Project data source view.
All I can do is create a regular datasource, which bypasses the metadata contained in the Data Source View.
Basically I want my Report Server Project and my Report Builder reports to leverage the same metadata. Is this possible? If so how do I get the connection string?
Thanks!!
-Matt
View 1 Replies
View Related
Oct 26, 2005
I am using RS 2005. I am setting up a Model for use within Report Builder so our clients can write their own reports.
View 12 Replies
View Related
Jul 6, 2007
We've built an oltp that uses nothing but synonyms that reach out to various DB's in the organization thru linked servers.
The issue that we are having is Data Source Views can only see tables and views. Is it possible to use synonyms? If not what would be a work around.
Please advise. Thanks
Steve
View 1 Replies
View Related
Oct 29, 2007
I am attempting to create a new report model. When I try to update statistics from the data source view I get the following error - Data is Null. This method or property cannot be called on Null values. I tried changing the generation rules so that attributes are only created for non-empty columns but that didn't help. Any assistance is appreciated. Thanks.
View 4 Replies
View Related
Dec 20, 2006
I've been doing research on the web trying to find if it's possible to create a Report Model that is based on a Data Source that is NOT SQL Server and NOT SSAS.
Specifically I'd like to be able to build a Report Model on a Web Server (or multiple web services).
Does anyone have any information relating to this? Based on my research it looks like there's a few interfaces that need to be implemented (such as ISemanticModelGenerator, etc.).
I did find a post relating to using ODBC data sources for a Report Model and the recommended solution was Linked Tables in SQL Server or UDM in SSAS. Both cases don't look feasible for a Web Service-based Data Source.
Thanks,
Jeremy Wiebe
View 5 Replies
View Related
Oct 18, 2007
I am trying to develop a data model that will filter data based on the user logged in. SUSER_SNAME()
I have a list of User Id's in a look up table, a where clause in a view that filters the data based on who is logged in. This works fine and the data even seems to be filtered when I explore the data in the Data Source View. How ever when I publish the data model to the reporting services site and use report builder the user logged in is no longer recognized. All I am really trying to do is set up report builder so that users can generate reports and only have access to thier data via a customer number. I just can't allow users to see other users data and I don't want to set up a different datasource view for each user. Does any one have any experience with this?
View 1 Replies
View Related
Feb 22, 2007
I have an existing project with the data source and report folder with working reports. I need to create a model for other department members to use Report Builder, not Business Intelligence Developer Studio, to develop their reports from. How do I add a data source view and report model folder to the project so I can then create a view and model?
View 1 Replies
View Related
Oct 5, 2015
I need to create a data model for powerview report but I don't have data right now. The data will be loaded later after I create the report. What technique should I use??
View 2 Replies
View Related
May 23, 2007
I have a datasource view DSV1. It points to a datasource DS1 that is considered the "primary".
I have created a Report Model that uses DSV1 (and thus uses DS1)
I created a new datasource, DS2 that I would like to use instead of DS1. (I can't just modify DS1 because if I modify it, it will overwrite it when we go to our Production environment and break that datasource)
So, I can go into DSV1 and change all the references from DS1 to DS2.
But that's where the problem lies.
When I try to build, I get the following error:
"The Table property of the Entity "E1" refers to the Table "dbo_View", which is not in the primary data source."
Somehow, the entity is tied to the "primary" datasource. When I change it back to DS1, everything works fine. Any thoughts? What can I do?
View 1 Replies
View Related
Jan 9, 2007
Hi everyone!,
Our team is new to SSRS and we are trying to figure out how to refresh the data source view, autogenerate the corressponding report model and Re-Deploy the Report Model (rewrite existing Report Model) on the server on a periodoc basis or trigger the whole action whenever there is a change in the database on some specific tables.
We prefer to do this action through a Agent job ( a diff approach is also welcome).
Are there any stored procs out there?
Appreciate your time.
-PN
View 6 Replies
View Related
Jul 17, 2007
Can someone please tell me how to retrieve/query the list of fields from an entity of a report data model that has been published on the reporting server programmatically ?
I am trying to upload a report data model to the reporting server and planning to use that model as the data source and consume it through our existing web application?
Thank you ,
Rashid A. Khan
View 1 Replies
View Related
Sep 6, 2007
I've created a linked server with a pretty basic Excel spreadsheet, and used this command to create a linked server to it:
sp_addlinkedserver ''XL_SPS_1', 'Excel', 'Microsoft.Jet.OLEDB.4.0', 'c:MyExcel.xls', null, 'Excel 8.0'
I want to use this as the data from which to build a report model. As linked servers don't show up in the Data Source View wizard, I created a view in SQL Server:
create view MyExcel
as
select * from XL_SPS_1...Sheet1$
Okay, great, now the view shows up in the DSV wizard and I can create the data source view. However, when I create a new report model based on this data source view, the Report Model Wizard tells me at "Create entities for all tables" that I've got an error when it processes dbo_MyExcel that "Table does not have a primary key."
I assume this is where the identifying attributes for the entities in the report model are taken from, so I really can't go further. Does anyone have an idea as to how to add a primary key to a linked server (Excel) in SQL 2005? Can this be done? Other than importing spreadsheet data to a SQL table, how can I get around this?
Thanks,
--Stan
View 3 Replies
View Related
Mar 6, 2008
Hi,
I have created and deployed a data source (which uses "Credentials supplied by the user running the report") and a model which uses this data source.
Also I have built a report using Report Builder and all works well.
Now I wish to use Visual Studio to build a report, so I'm trying to create a data source from my computer which the report should use and I want it to use the model on the remote server. In the Report Wizard I am using "Report Server Model" and the following connection string:
Server="http://<remoteserver>/reportserver";datasource="/Models/MyModel"
IHowever I can't make the credentials to work. I've tried them all with no luck. What username/password must I use? Because so far nothing of what I have tried works.
Any help is highly appretiated.
Kind Regards,
Robert
View 3 Replies
View Related
Feb 11, 2007
Hi,
I've created dsv that contain all fields from table database. in the smdl I've remove some fileds due to security. All fields in the smdl do not contain drill.
Issue: When I created calculated field in the report builder the field has a link. When I clicked the drill I saw all the record data including field that not in the smdl.
Questions:
1) Can I remove the link from the calculated fields?
2) Can I prevent from users drill to fields that not in the smdl?
Thanks,
Assaf
View 1 Replies
View Related
Jan 22, 2007
Is it possible to dynamically refresh the report model of the report builder?
could it even be using code with any of the interfaces?
When we add a table or add a column to the table in database , will the report model get refreshed automatically or do we need to do it externally. If so, can we use any of the objects and write a custom code in VB.
View 1 Replies
View Related
Apr 26, 2006
I get this error message:
Semantic query execution failed.
The 'PerspectiveID' custom property for the 'query' perspective is either not set or is not set to the string data type.
The report model does have a perspective.
Anyone else seen this?
View 9 Replies
View Related
Jun 15, 2007
In Report Designer I am able to create a Model data source and see my entities/fields.
But when I run the report I get a "Failed to execute Query" message.
I know in the past this was an issue because Designer doesn't allow you to specify a perspective.
Does anyone know if this has been resolved?
I'm using SP2
Thank you,
Joe
View 1 Replies
View Related
Jul 31, 2007
Hi,
I have posted this issue for a week, haven't got any reply yet, I posted it again and desperately need your help.
The article http://msdn2.microsoft.com/en-us/library/ms365343.aspx says:
Model Item Security can be set for differnt security filters, but when I use SQL Server Management Studio to set Model Item Security, it seems "Permissions" property surpass "Model Item Security" property. -- My report server is using Custom Authentication.
For example, in "Permissions" property of the model, if I checked "Use these roles for each group or user account" without setting any user or group, no matter what users I added to "Model Item Security" with "Secure individual model items independently for this model" checked, NO one user can see the model on report manager and report builder;
in above situation, if I added "user1" and gave role such as "Browser" role to "user1" in "Permissions" property, if I checked "Secure individual model items independently for this model" in "Model Item Security" property, even I did NOT grant "user1" to root model and any entities under the model, the "user1" is able to access the model and all entities in report builder.
My question is on the same report model, how to set "AdminFilter" (empty security filter) for administrator permissions and set "GeneralFilter" (filtered on UserID) for general user based on their UserID?
The article also says:
"Security filters are always applied, even for users who have Content Manager or Administrator permissions to the model. To allow administrators or other users to see all rows of an entity on which row-level security is defined, you can create an empty security filter (which always returns True) and then use the filter to grant those users access to all the rows."
So I defined 2 filters "GeneralFilter" and "AdminFilter" for "Staff" entity for my report model "SSRSModel", I expect after I deployed the report model, the administrator users use report builder to build reports with all rows available, and the non-admin users can only see rows based on their UserID.
I can only get one result at a time but not both:
either the rows are filtered or not filtered at all, no matter how I set the "SecurityFilter" for the entity: I tried setting both "AdminFilter" and "GeneralFilter" for SecurityFilter at the same time, combination of "DefaultSecurityFilter" and "SecurityFilter", or one at a time.
Your help is highly appreciated!
Desperate developer
View 1 Replies
View Related
Feb 13, 2008
I have a report model that I am trying to deploy using report manager web service. A folder is created by the administrator to place all my SSRS reports. I deployed .rdl files using the "Upload file" function in "Report Manager". All the SSRS reports work fine but when I tried to upload .smdl file (report model file) using the Report Manager's "Upload file" function it gave me the following error message.
The permissions granted to user 'xxxxxxman' are insufficient for performing this operation. (rsAccessDenied)
Does this mean I only have permission to upload .rdl file but NOT .smdl file to my designated folder?
View 6 Replies
View Related
Jul 14, 2006
I'm having some difficulty linking a custome drilldown or clickthroug report to another report that I have already created in report builder. I followed a MSDN article found at http://msdn2.microsoft.com/en-us/library/ms345226.aspx but I cannot find the " Select a page area, select Drill-through reports" option in report manager. Does anyone have a better explanation on how to do this? I'm assuming that when you create the clickthrough report there has to some sore of filter on it that relates to the item of the clickthrough. I guess I'm I little confused, any help would be great. Thanks.
View 5 Replies
View Related
Jun 27, 2007
Hi,
I need to filter a single .rdl page in accordance with an option chosen from an asp.net page.
I know how to filter a report within the reporting page. But how can I do the filtering from an asp.net page? For example I have a Report1.rdl that prints out all the locations. I need the same report1.rdl page to print out only for a certain location based on the option I chose from the asp.net page. My asp.net page has 3 buttons. One to for all locations, another for location1, and the other one for location2. Please help. Thanks
View 5 Replies
View Related
