0

I have Report pointing to 20-25 servers, Which Has Dataset Queries something like these

#Dataset-1
Select A,B,C,D,E,F From Table1 where a>10 and a<15

#Dataset-2
Select A,B,C,D,E,F From Table1 where a>15 and a<13

#Dataset-3
Select A,B,C,D,E,F From Table1 where a>05 and a<07

and so on.Presently the report is running completely fine.But whenever we want to change the condition value from one servers dataset,We go to particular dataset and make the changes.

#Dataset-1
Select A,B,C,D,E,F From Table1 where a>10 and a<15
We change it to may be 
Select A,B,C,D,E,F From Table1 where a>10 and a<20

And redeploy everything, which is a overhead that is being caused.

Rather we want to have a configuration in a SSRS server and then fetch the '<' value and '>' value and get the data and report it as it is already happening.In realtime ,we make these changes very frequently,Hence we want report to morph itself and handle the changes without redeploying it again and again.

I hope I have given the comprehensive information of the problem.

Chetan Kulkarni
  • 404
  • 4
  • 15

1 Answers1

0

If you're able to work with the database the data is coming from, I'd add these values in a separate table. The datasets can then get their values from the config values table directly as part of their queries.

Rich
  • 2,207
  • 1
  • 23
  • 27
  • I was thinking of the same,Problem is we cant connect to the databases,We have only Readonly access. – Chetan Kulkarni Jun 30 '17 at 10:23
  • And even if I managed to get one db and one table permission to tweak with,How do I incorporate these in all the datasets. – Chetan Kulkarni Jun 30 '17 at 10:24
  • The alternative is to store your values in a text file, and use a connection to that to pull the values, then pass these values in as parameters to your datasets. – Rich Jun 30 '17 at 10:28
  • You could have a table called DataSetConfig for instance DatasetName, UpperLimit, LowerLimit. Then you could do for example Select A,B,C,D,E,F From Table1 INNER JOIN DataSetConfig c ON c.DataSetname = 'Dataset-1' where a>c.LowerLimit and a – Rich Jun 30 '17 at 10:30
  • Negative there as well!!! :(What,we can do is change the report itself and redeploy and they are expecting us to do that everytime).Redeploying the report with new configuaration is the only solution. – Chetan Kulkarni Jun 30 '17 at 10:31
  • You could also use a common assembly that gets the values from wherever. If you can't ever store a config file, why ask how to store a config file? Ssrs config files are for the server, not for specific reports. – Rich Jun 30 '17 at 10:33
  • Cant I have some temp config in SSRS itself?Where I change the config and redeploy the report.(I know this is sounding Vague,The problem is I cant save anything outside the instance of SSRS) – Chetan Kulkarni Jun 30 '17 at 10:35
  • The config values have to be stored somewhere- where were you thinking? Ssrs config is all about how the server runs, not the data in reports. – Rich Jun 30 '17 at 10:38
  • Yes Sir I Agree,My question something like temp to serve my problem? – Chetan Kulkarni Jun 30 '17 at 10:39
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/148043/discussion-between-chetan-kulkarni-and-rich). – Chetan Kulkarni Jun 30 '17 at 10:40
  • Not as far as I know- if you needed to change these things you would have to sort them out in the dataset or as parameters to the dataset – Rich Jun 30 '17 at 10:41
  • ,I tried Too much and found a place where I can actually get to store Config file on ONe of the DB's.But when I am accessing the Dataset In otherDataset its saying invalid Object – Chetan Kulkarni Jul 05 '17 at 05:35
  • From FI inner join Environment E on E.Servername= FI.Servername ,Here Fi my main table from Server1,And Environment is my Dataset Name – Chetan Kulkarni Jul 05 '17 at 05:37
  • The queries need to be self contained in a dataset. You could get 'parameters' by using a dataset and then pass the parameters to the other dataset. Or if its the same db, just join the tables. However since this is a separate problem you might want to make a separate question, you would get more space to write about the error and what you have tried to resolve it. – Rich Jul 05 '17 at 07:11
  • https://stackoverflow.com/questions/44917858/accessing-one-dataset-in-another-dataset-ssrs – Chetan Kulkarni Jul 05 '17 at 07:15
  • I have Added the different Question on this particular problem Aswell. – Chetan Kulkarni Jul 05 '17 at 07:16
  • We wont be able to have the config table in the same DB, – Chetan Kulkarni Jul 05 '17 at 07:16
  • I've tried to answer this over there. You can make parameters in your report that get their values from one dataset. Then your main dataset can be written to accept parameters (the values for your config). – Rich Jul 05 '17 at 07:25