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
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
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 RelatedI 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!
Running 2005 Beta 3 Refresh. When I first deploy, it works fine. Subsequent deployments yield the following error:
View 9 Replies View RelatedI 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 RelatedGood morning,
View 7 Replies View RelatedI'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
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
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
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.
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?
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
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
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.
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?
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 RelatedHi 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 :)
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
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
Can anybody please let me know if cascading parameter feature of Report Designer can be implemented in Report Builder in any possible way (using filter or report parameters).
We have a scenario where the end user wants to create an adhoc report with two input parameters (or filters). Both the parameters will be selected from a list of available values at run time. The problem is that the second parameter's list of values should be populated based on what is selected in the first parameter.
Thanks in advance.
Hi All,
I have to filter my report with the date parameter. Custom Dates are comming from the database.
I have to filter with the date just previous Date which user selected as a parameter.
As an example if parameter contains 2/04/2008 , 10/04/2008, 14/04/2008, 16/04/2008 and user is selecting 14/04/2008 then I want to filter the data from just previous parameter i.e. 10/04/2008
Ritesh Kumar
Guys,
In my report builder when I drag the SKU in the filter area, its doesnt give me the dropdown and show me the distinct values, it just gives me a text box and expects me to type in the SKU.
I have other attributes whcih have the same property as SKU but they all work fine when I drag them into Filter area.
Is there a limitation in reporting svcs where if the Field has more than "n" number of distinct records it will not populate the dropdown, instead just present you with a text box?
My DataModel Table has around 110K records and roughly 7K distinct SKU's
I compared the SKU field to other fields in the Report Model Designer and also DataSource View and nothing seems to be out of normal to not show the dropdown with distinct values except for the # of distinct records.
Appreciate your help in advance
Is this a known issue? When I try to filter my report using Like operator on one of the Report parameters/dataset field it returns nothing. If I use = it works? Any ideas how to use the "Like" operator in reporting?
Thanks,
I have a requirement to add the ability to filter a report by partial last name, at the report level (i.e. not at the database server). In case someone else needs to work out how to do this, here is what I did.
Add a Parameter to the report. In my case it is named LastName, is non-queried, allows Null or Blank, and has a default of null.
Add a filter to the dataset that contains the field to be searched. Set its Expression to:
=IIF(Parameters!LastName.Value = System.DBNull.Value OR Parameters!LastName.Value = "",
Fields!LastName.Value, Nothing) ,
its operator to "="
and its value to =IIF(Parameters!LastName.Value = Nothing OR Parameters!LastName.Value = "", Nothing, Parameters!LastName.Value & "*")
voila!
Helen
When applying a filter in Report Builder the filter will timeout when loading data for the filter. It does not happen all of the time seems to happen more often during the business day. Has anyone else run into this probelm? Is there a way to increase the timeout?
View 6 Replies View RelatedHi
Im creating a filter in Report Builder with a start date and an end date. That is I want the user to able to choose a dateinterval. How do I change the label shown in the report for the interval. I want it to say Startdate and Enddate not what I have in my Sql server
Thanks
/Stefan
I'm creating report model using named queries. Is it possible to specify parameters?
View 3 Replies View RelatedIs there an API or a way to consume a Report Model? I want to try and roll my own simple web-based / thin-client Report Builder without using the ClickOnce control.
Thanks,
--jason
Here is one scenario. There is category X which has subcategories A,B,C,D. A has 5 products and B has some 7 products, C 5 products and D has some 12 products. My problem is I need create a Report model where is if we select X
all the list of subcategories and their products should be displayed. I.e if we select X then
A
1
2
3
4
5
B
1
2
3
4
5
C and so on..........every thing should be displayed that are under category, i.e subcategories under that category and the products under each subcategory of that category.
Can anyone please help me out to create a Report Model to generate ad hoc reports
I am using VS 2005/SQL 2005 and must use a stored procedure as provided. The stored procedure returns parent rows and child rows (same as parent with additional columns populated, like ChildID). I cannot change the stored procedure.
I have been unable to filter the data (either at the Dataset level or the Table Properties level) to only see the "parent" rows. On my report, I added one column to print the value for my test and a second to use it:
=Trim(CStr(Len(Fields!ChiIdID.Value)))
=IIf(Trim(CStr(Len(Fields!ChildId.Value)) = "0"),"PARENT","CHILD")
Both columns produce the values I expect. However, if I try a filter using the first expression, it fails! I.E. if I test = 0, it filters out ALL rows and if I test != 0, all rows are displayed?????
I am new to SSRS, so I would not be surprised if this is cockpit error. It seemed like a very simple requirement that I have used in othe report tools for years.
Any help would be appreciated.
I have a report I'm working on that provides a census of members. These members have certain properties;
Load date,Report Type,Clinic,Care Manager,PCP Name.
The census is from an outside group and what I do is massage it for our internal databases and reports to our care managers.
The report starts out by returning only those entries that were received in the last report from the outside vendor and I filter each of the parameters by using a WITH statement and an INNER JOIN, as follows;
WITH Date_of_Most_Recent_Census AS
(
SELECT
MAX(Load_Date) AS [CurDate]
FROM
Census_Rpt_Final
)
[Code] ...
The default is for all parameters to start out with all possible values for the current load date. But, I need to be able to filter the available - and default - values for each parameter based upon the selection of the other parameters.
So, the Load Date is built in by use of the CTE that is a part of all the parameters. But, I need the report to allow the end user to select from each of the others, but limiting what is avaliable for selection based upon the settings of the other parameters.
For example, the second parameter is the Report Type. It can be either "Hospital," or "SNF." But, not all Clinics, Care Managers or PCPs may have an entry in both types. So, if the user selects, say, "Hospital," all the parameters would alter their available and default values such that they only include options where the census shows they are a "Hospital" entry.But, not all Clinics in the most recent census may have both report types. So, if the end user selects a particular clinic, it would also recalculate the available and default values for Report Type, as well as those beneath it on the list. And, likewise for the remainder of the parameters.
My initial thought was to add WHERE statements to the datasets controlling each parameter, but SSRS keeps asking me to define the parameters when I click out of the dataset's properties. Now, I know that SSRS is a single pass process - and, I'll cross the bridge on adjusting parameters defined earlier in the list when I come to it - but I thought that parameters lower in the list would update, since they'd have their values defined.For instance, the Clinic parameter is after Report Type, so the code I used to set up Clinic was;
WITH Date_of_Most_Recent_Census AS
(
SELECT
MAX(Load_Date) AS [CurDate]
FROM
Census_Rpt_Final
)
SELECT DISTINCT
[code]....
But, this asks me to define @ReportType, even though it precedes Clinic on the parameter list.
when i use a filter in report builder, it's not able to retrieve the information to populate the dropdown menu, the dropdown menu is empty. I get a triangle with exclamation mark and the following message and the drop down box is not being populated:
the requested list could not be retrieved because the query is not valid or connection could not be made to datasource
I am able to generate reports but not able to use filter. what could cause this issue ?
Chi