1

I got a report with 2 data sources and both are Shared Data source. I am trying to change both of them to Custom Data Source but when I re-upload the report in my Report Manager, it won't work.

Edit: I don't have an error because the report successfully uploads, but the data source doesn't change. If I have a field in the report, the report shows the new field, but the data source stays the same.

Vickel
  • 7,879
  • 6
  • 35
  • 56
Marie-Catherine Richard
  • 13
  • 5

3 Answers3

0

Is there an error message? It won't work could be a bunch of things. I'd upload a picture of the error or whatever screen you can't get past. Let me know when you can do that, then it will be easier to troubleshoot. If you can't upload an image than I would:

  1. Check your datasource connections are correct.
  2. Check your datasets are using the correct connection.
Alex
  • 211
  • 1
  • 8
  • thanks for the response ! Sadly, i don't have a error message. When i reupload the report, the datasource are not changed but if i do something else like adding a field in my report. The new field will be in the report but the data source wont change – Marie-Catherine Richard Feb 16 '18 at 22:22
  • ok, when you changed the shared data source to the custom data source did you change the Tablix properties? I've had to reset each field in my report after making certain changes and the tablix is one I always seem to forget about checking. Each connection is based off is previous connection. So start from the sources, move to the sets, check a few fields to make sure there still set, and then check the tablix. That may let you catch a mistake you made. – Alex Feb 16 '18 at 22:28
  • i suppose i might have done a mistake because i only change the datasources. i didnt touch the Tablix properties. didnt know i had to – Marie-Catherine Richard Feb 16 '18 at 22:41
  • If you made sure your datasets are using the correct source you shouldn't have to. When you use a custom source you have to make sure its using the right source. If not it won't know where to try to pull data for the report. – Alex Feb 16 '18 at 22:44
  • the datasource look like ="Data Source=" + Parameters!pServerName.Value + ";Initial Catalog=" +";" – Marie-Catherine Richard Feb 16 '18 at 22:50
  • And thats set as datasource1and datasource2( since you said you had two), right click your dataSET and go to properties and verify those are correct, I'd show pics but I'm not in at my computer anymore – Alex Feb 16 '18 at 23:33
0

This might be because of caching that SSRS does with the datasource for the report on the server. Do the new datasources have the same name as the old ones?

You could create the custom datasources in the DataSources tab in the Manage menu for the report in the report manager.

To do this open up the Report Menu by click on the "..." then selecting Manage

Manage

Then click on the Data Sources Tab, then change the data source to Custom Datasource.

Data sources tab

Then set up the datasource like you did in the report, you will need to select SQL Server for the Connection, add the connection string and choose the authentication type which in the image below is windows authentication but I am not sure how it is set up on your server. Then click Test Connection. Then Click Save (make sure you click Save before navigating away).

Custom Datasource

Or you could delete the old report in the report manager and then upload it again. Note that deleting the report will delete any schedules and other customization you have done ie. Name and Description.

cabbagetreecustard
  • 647
  • 2
  • 13
  • 22
  • They have the same name yes. If i delete the old report and reupload it, the datasource will change but i can't do that because it won't work with how we manage our application :S – Marie-Catherine Richard Feb 19 '18 at 14:10
  • So can you change the name of the data sources in your report file and then reload? – cabbagetreecustard Feb 21 '18 at 07:14
  • I suggest it but it might not work because the c# would have to change a lot... so i might be stuck again with my problem. – Marie-Catherine Richard Feb 21 '18 at 16:40
  • Then you need to set up the custom datasource within the report in the report manager (or get someone else to do this), I will add some pictures to show where you would do this. Once you have done this once the next time you upload the report it will remember the custom datasource unless you delete the report. – cabbagetreecustard Feb 21 '18 at 18:33
0

This is a setting thats controlled from the deployment properties of the visual studio SSRS project. Right click the project name and select Properties. Then from the Property section of the general page notice "Overwrite Datasets" & "Overwrite Datasources". Change the one that's necessary and re-deploy to see it updated in the report portal.

BilliD
  • 577
  • 2
  • 7
  • 17