Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sue1127
Starting Member
23 Posts |
 Posted - 2008-10-24 : 08:38:29
|
| I am using SSRS 2005. I'm exporting data--ledger accounts, expenses to date, etc--from an application to Access 97, and then bringing in that information to SSRS from Access. But I want to restrict the information viewed, based on a security table in another database. The security table has controls the accounts viewed based on userid.I've heard that you can't link tables from 2 different datasets in SSRS. But I've also heard that using a subreport would allow me to use the security information from one dataset to control what is viewed in the other dataset.Can anyone tell me if this is possible?Thanks very much.Sue |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-26 : 07:46:12
|
| what exactly are you trying to do with field of another dataset inside your dataset? are you planning for something like join between datasets?if so, its not possible. Using a subreport will be make report a bit slow,especially if subreport has quite a lot of data to display.Probably, you could explain on what you're trying to do so that i can suggest an alternate solution if possible. |
 |
|
|
sue1127
Starting Member
23 Posts |
Posted - 2008-10-27 : 08:37:38
|
| I don't really need to display the data in the 2nd dataset. Rather, I need to use the 2nd dataset to restrict the information from the first dataset to certain accounts.One dataset contains all the accounts in the general ledger. The second dataset would contain a table that restricts, by user id, the account information that can be viewed. The second dataset would grab the userid that was entered, determine which accounts should be viewed, and pass that information to the first dataset.I would really appreciate any alternative solution you could suggest. If there is no alternative solution, can I do this with a subreport? I realize it would make the report slow, but I really need to find a solution to this problem.Thanks very much,Sue |
|
|
|
Scott.Thornburg
Starting Member
10 Posts |
Posted - 2008-10-29 : 01:25:38
|
| Sue,SSRS doesn't allow you to join datasets at the application server level, but you can sometimes accomplish the same goal at the dataset level. That is, if you have the security data in a SQL Server database, you may be able to use a a linked server between SQL and your Access db. Within a single dataset you can then filter the application data based on your security logic.As the previous responder stated, additional detail about the specific reqiurements might provide a better answer as well.Hope this helps,Scott |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-29 : 05:06:17
|
quote:
Originally posted by sue1127
I don't really need to display the data in the 2nd dataset. Rather, I need to use the 2nd dataset to restrict the information from the first dataset to certain accounts.One dataset contains all the accounts in the general ledger. The second dataset would contain a table that restricts, by user id, the account information that can be viewed. The second dataset would grab the userid that was entered, determine which accounts should be viewed, and pass that information to the first dataset.I would really appreciate any alternative solution you could suggest. If there is no alternative solution, can I do this with a subreport? I realize it would make the report slow, but I really need to find a solution to this problem.Thanks very much,Sue
are both the data existing in same db? then cant you include all of logic in a single dataset? |
|
|
|
sue1127
Starting Member
23 Posts |
Posted - 2008-10-29 : 09:04:00
|
| The data are in different databases.I'm not sure, Scott, what you mean by "if you have the security data in a SQL Server database, you may be able to use a a linked server between SQL and your Access db." I'm not clear on how I would do this.Here is some more specific information on what I am trying to do:The first dataset consists of account number, fiscal year end, and several fields such as Current Year Year-to-Date and Current Month Actual Amount. I am exporting this data from a financial application in to a Microsoft Access table, and from there I am bringing it into SSRS.2-955-0000-9990 FYE Jun 2009 $1,039.00 $735.002-956-0000-9991 FYE Jun 2009 $0.00 $0.002-957-0000-9992 FYE Jun 2009 $139.83 $24.27The second dataset consists of security information to ensure that people can view only their own accounts. This information is retrieved from a table in a different database from where the financial information is stored.This table contains user name and, by account segment(Fund-Dept-Pcode-AccountCode) the accounts that the user may view. UserName Fund Dept Pcode Accountcodedduck 2 955 NULL NULLmmouse 2 956 0000 NULLefudd 2 956 0000 8653If a field is Null, the user can see anything in that field. So, in the example above, dduck can see any accounts that begin with 2-955, mmouse can see any accounts that begin with 2-956-0000, and efudd can see only account 2-956-0000-8653.My question is, Can I set something up, in a subreport or another way, that will enable me to compare the userid of the person who is viewing the accounts with the userid in the security table, and display only those accounts that he or she is permitted to see.Thanks very much to all who are responding. I have experience in Crystal Reports, but I'm a newbie to SSRS, and am trying to figure out how it works.Sue |
|
|
|
Scott.Thornburg
Starting Member
10 Posts |
Posted - 2008-10-29 : 22:17:07
|
| Sue,The approach I am suggesting is that you do the joins between the account data and the user security data within a single dataset which you then bring up to SSRS. The fundamental assumption is that the join can be done at the query level. Off the top of my head, there are two approaches that can accomplish this:1) If your user security data resides in SQL Server, you could set up a linked server to the Access database and then use the Access data within a SQL Server query. I can give you more detail about how to accomplish this, but I'd like to verify the assumption about SQL Server first. And if so, what version (2000? 2005?). If not SQL Server, then what type of database?2) A second option is to bypass Access altogether and simply import the data from your original application into SQL Server (again, assuming that's where the security data lives). This is even more straightforward as no linked server is involved.How would you like to proceed?Scott |
|
|
|
sue1127
Starting Member
23 Posts |
Posted - 2008-10-30 : 09:04:34
|
| Hi Scott,I'm using Sql Server 2005. Both sets of data reside in sql server. The account data is in a database that we purchased, and we can't add anything to it. So we have the security data in a separate database.I think I haven't explained clearly the background, and what I'm trying to do. Originally, I had tried to go against the data by using a stored procedure, in which case I know that I could have joined the account data and the security data. But because of the complexity of the account database, I was having a great deal of trouble bringing back the necessary data and placing it in the format that the controller wanted.Then, my boss recommended exporting the account data to Access through the application's export functionality, and then bring that Access table into Reporting Services. This method would enable me to get the necessary information in the desired format. The hope is that through the application's queue functionality, the information would be refreshed every night and therefore available online.Thanks,Sue |
|
|
|
|
|
|
|
|
using data from one dataset in another dataset