Ad-hoc reports using report model ??
-
Hi! I have a requirement to create ad-hoc reports using the SSRS report Model. Now in report model we first create the Datasource (*.ds), then we create Data Source Views(*.dsv), then we create the report model (*.smdl) using the dsv, but I have few below questions on this approach:
1. After including all the tables from the database in the ds, which will be transfered to the dsv , but where can I implement the functionality of exposing only few of the columns of our tables to the business user in report builder and How?
2. I don't have access to create views in the database, so where can I write the query in the Report Model to create business fields for the end user and How?
Please guide me on the above two questions need it Urgently
Thanks!
-
Hi steven,
After taking all the tables required for your report in ur datasource view, u can :
1. Remove the fields that are not required for you in the report.
2. Specify friendly names to ur fields.(right click and set the "friendly name" property).
3. You also add any table by right clicking on the pane in Datasource view and specify an alias for a query. (This is like creating a view, which u can access afterwards in the report).
4. You can also specify calulations and expressions for columns in a table.(Just like adding an extra column called Name which is concatenation of first name and last name).
The above are the advantages of using a DSV.
Does this answer your question? Plz let me know.
Thanks
Kiran Mahamkali
-
Thanks for your response, sorry for the replying late as I was not in town.
On your 3rd point, I think this is what we call Named Query on Data Source View(DSV) but after creating a named query using SQL query it ask for logical primary key(dont know why). And apart from this I have two more ques:
1. I have created deployed my ds, dsv and smdl (report model) and now I have made changes in the DSV. But I don't know how to get it to show up in the Report Model. Using Autogenerate did not work.
The only way it has worked for me, is to just delete the smdl and re-create it, but there has to be an easier way.
2. On the Report Builder, after selecting( drag n drop) the fields on the report, when I try to run it. It gives me an error message specifying "Cannot run this report. A filter must be specified". Even though I dont want any filter property to be there on my report.
Thanks for your time!
-
I think I figured out the 2nd one. The entity in my model had a property called "InstanceSelection" with the value of "MandatoryFilter." I cannot completely explain what this property does, but I changed the value to "List" and fixed the problem.
I don't know why the filter are mandatory sometime.
Still the 1st is OPEN. adding one more 🙂
3. For each Entity in the report model (smdl) we have some Role and Attribute associated with it (visible on right side of entity). Though on deploying the Report Model with data source, in the Report Builder I am able to see the Entity with its respective Attributes but not the Roles which were their in the report model as roles represent the relation of that entity with other entities. Can anyone tell me why?
Please reply !!
-
steven,
Some times, you may want to add a mandatory filter to prevent a user from pulling too many records when creating a report.
After you Autogenerate a smdl, try editing the model on the report server (http://yourreportservername/Reports) and Regenerate it there (under properties>general).
I hope this helps.
-
Hello,
I need to deploy the Report Models in Report Server using script.
How I can do it?
Thanks
-
Hello,
I need to deploy the Report Models in Report Server using script.
How I can do it?
Thanks
-
1. After including all the tables from the database in the ds, which will be transfered to the dsv , but where can I implement the functionality of exposing only few of the columns of our tables to the business user in report builder and How?
On the SMDL, click on the Entity, and then the attribute(columns) that you want to hide. View properties and set Hidden to True. That attribute will not be visible to the end user when building his report off of your model.
2. I don't have access to create views in the database, so where can I write the query in the Report Model to create business fields for the end user and How?
Named query in DSV.
3. I have created deployed my ds, dsv and smdl (report model) and now I have made changes in the DSV. But I don't know how to get it to show up in the Report Model. Using Autogenerate did not work.
The only way it has worked for me, is to just delete the smdl and re-create it, but there has to be an easier way.
The golden rule is NEVER Auto-Generate.
The first time when you Generate GUIDs are created for each element in the DSV, be it entities,roles, attribs.
when you re generate, new GUIDs are created. This will cause exisitng reports(oof of Model) to break.
Whenever you change your DSV, right click on it and Refresh. This way the recent chnages will be reflected in the Model. On renaming columns, refer to Binding(a separate discussion in itself) properties.
4. On the Report Builder, after selecting( drag n drop) the fields on the report, when I try to run it. It gives me an error message specifying "Cannot run this report. A filter must be specified". Even though I dont want any filter property to be there on my report.
Set the selection to List
for en explanation, pls refer http://msdn.microsoft.com/en-us/library/ms160327(SQL.90).aspx
5. For each Entity in the report model (smdl) we have some Role and Attribute associated with it (visible on right side of entity). Though on deploying the Report Model with data source, in the Report Builder I am able to see the Entity with its respective Attributes but not the Roles which were their in the report model as roles represent the relation of that entity with other entities. Can anyone tell me why?
Not sure about this. Please check your DSV.
"Software changes. This is a rather obvious statement, but it is a fact that must be ever present in the minds of developers and architects. Although we tend to think of software development as chiefly an engineering exercise, the analogy breaks down very quickly. When was the last time someone asked the designers of the Empire State building to add ten new floors at the bottom, put a pool on the top, and have all of this done before Monday morning? " : Doug Purdy, Microsoft Corporation
-
1. After including all the tables from the database in the ds, which will be transfered to the dsv , but where can I implement the functionality of exposing only few of the columns of our tables to the business user in report builder and How?
On the SMDL, click on the Entity, and then the attribute(columns) that you want to hide. View properties and set Hidden to True. That attribute will not be visible to the end user when building his report off of your model.
2. I don't have access to create views in the database, so where can I write the query in the Report Model to create business fields for the end user and How?
Named query in DSV.
3. I have created deployed my ds, dsv and smdl (report model) and now I have made changes in the DSV. But I don't know how to get it to show up in the Report Model. Using Autogenerate did not work.
The only way it has worked for me, is to just delete the smdl and re-create it, but there has to be an easier way.
The golden rule is NEVER Auto-Generate.
The first time when you Generate GUIDs are created for each element in the DSV, be it entities,roles, attribs.
when you re generate, new GUIDs are created. This will cause exisitng reports(oof of Model) to break.
Whenever you change your DSV, right click on it and Refresh. This way the recent chnages will be reflected in the Model. On renaming columns, refer to Binding(a separate discussion in itself) properties.
4. On the Report Builder, after selecting( drag n drop) the fields on the report, when I try to run it. It gives me an error message specifying "Cannot run this report. A filter must be specified". Even though I dont want any filter property to be there on my report.
Set the selection to List
for en explanation, pls refer http://msdn.microsoft.com/en-us/library/ms160327(SQL.90).aspx
5. For each Entity in the report model (smdl) we have some Role and Attribute associated with it (visible on right side of entity). Though on deploying the Report Model with data source, in the Report Builder I am able to see the Entity with its respective Attributes but not the Roles which were their in the report model as roles represent the relation of that entity with other entities. Can anyone tell me why?
Not sure about this. Please check your DSV.
"Software changes. This is a rather obvious statement, but it is a fact that must be ever present in the minds of developers and architects. Although we tend to think of software development as chiefly an engineering exercise, the analogy breaks down very quickly. When was the last time someone asked the designers of the Empire State building to add ten new floors at the bottom, put a pool on the top, and have all of this done before Monday morning? " : Doug Purdy, Microsoft Corporation
-
Mods, pls chk. I got a error page and i made repeated attempts to post it. Hence many copies.
"Software changes. This is a rather obvious statement, but it is a fact that must be ever present in the minds of developers and architects. Although we tend to think of software development as chiefly an engineering exercise, the analogy breaks down very quickly. When was the last time someone asked the designers of the Empire State building to add ten new floors at the bottom, put a pool on the top, and have all of this done before Monday morning? " : Doug Purdy, Microsoft Corporation
-
I experience the same problem: On the Report Builder, after selecting( drag n drop) the fields on the report, when I try to run it. It gives me an error message specifying "Cannot run this report. A filter must be specified". Even though I dont want any filter property to be there on my report.
You said in entity property- InstanceSelect I cannot find this property. Please advise detail where is the entity property ? in report builder/report models?
thank you very much for your time.
-
look for the "InstanceSelection" property of Entity in your Report Model.
-
Thank you Steve! You are real!!!
I find entity property InstanceSelection in report models. it needs open report model property window first, and click on Entity, then entity property will show. otherwise just right click on entity , it does not have property. Thank you so much! I will implement this instanceselection to my report model, hope it solve my problem.
other question:- How can I use User NT login ID to filter data. I have a User table maintain user login info. and a order table. The user only allow to see their own order not others.
In report builder, how can I automatically filter data as soon as user login report servers? I saw there is a function GETUSER() how can I implement? Please advise. Thanks A LOT!!!
Caroline
-
Hello all - your forum has really helped me get our report model out to our organization thank you all for your input. I do have a question,
I have 12 entities that users can build their reports from the problem that we are having is that from the 12 entities they can only join 2 tables. How can I build a relationship between tables?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply