Microsoft Dynamics CRM and SSRS -- migrating reports to new server

[Karla]

The SSRS report is in the CRM db in the Reports table. Changing anything directly on the CRM db is a strict no-no. We use Kingswaysoft software to make an ETL package.

I hate fucking CRM

The XML that makes up the report is clearly in the table. The connection string is obvious. You would think being able to do a search an replace would be simple.

There has to be an automated way to update the connection string.

Oh, and we can't use shared datasource in the report because fucking CRM (which is also why we can't use sprocs directly in our datasets which also cannot be shared because fucking CRM).

Did I mention I hate CRM?

I can't seem to find any information on SSRS with CRM or SSRS with Kingswaysoft.

Are we the only idiots that got sold on this ?

[Vixen]

@Karla said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

The SSRS report is in the CRM db in the Reports table. Changing anything directly on the CRM db is a strict no-no. We use Kingswaysoft software to make an ETL package.

I hate fucking CRM

The XML that makes up the report is clearly in the table. The connection string is obvious. You would think being able to do a search an replace would be simple.

There has to be an automated way to update the connection string.

Oh, and we can't use shared datasource in the report because fucking CRM (which is also why we can't use sprocs directly in our datasets which also cannot be shared because fucking CRM).

Did I mention I hate CRM?

I can't seem to find any information on SSRS with CRM or SSRS with Kingswaysoft.

Are we the only idiots that got sold on this ?

well.... the connection string is in plain text.... and the format of that makes it exceedingly unlikely that it would match by chance non connection string data.

and changing the DB is forbidden.... does that extend to a lowly UPDATE statement?

UPDATE CRM.table
SET
    COLUMN = REPLACE(COLUMN, "old connection string", "new Connection string")
;

if you could do that...... worth a shot, yeah?

you have a test environment, yes? so you can try it in not production?

[Captain]

@Karla This architecture makes me

[Karla]

@Vixen said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

and changing the DB is forbidden.... does that extend to a lowly UPDATE statement?

Unfortunately, yes. ( I know how to update the text itself, it was within the constraints given).

Likely because there are possibly unknown and unknowable side effects. I mean if someone knew from experience that there weren't any maybe I could convince someone. I think I recall something about invalidating our license or support contract.

[Karla]

@Captain said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

@Karla This architecture makes me

Me too.

So much fucking duplicate data.

[TheCPUWizard]

I must be missing something... can you not do this at the SSRS level?

[Captain]

@TheCPUWizard Presumably there's some script pulling the RDL's XML out and putting it on the report server dynamically. Or something.

[Karla]

@TheCPUWizard said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

I must be missing something... can you not do this at the SSRS level?

We can go into each report in Visual Studio and update the datasource and upload the new file to the new CRM server.

But for 50 fucking reports?

I'm a programmer. I'm lazy and don't want to do that shit.

[anonymous234]

@Karla
http://i.imgur.com/p3pQARM.jpg
I have zero idea about CRM or SSRS, but if it's just editing a textbox 50 times, it's probably faster to just do it than to figure out what kind of API there is, if there is any.

[TimeBandit]

@Karla said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

We can go into each report in Visual Studio and update the datasource and upload the new file to the new CRM server.
But for 50 fucking reports?
I'm a programmer. I'm lazy and don't want to do that shit.

That's when you need an intern

[Karla]

@anonymous234 said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

@Karla
http://i.imgur.com/p3pQARM.jpg
I have zero idea about CRM or SSRS, but if it's just editing a textbox 50 times, it's probably faster to just do it than to figure out what kind of API there is, if there is any.

At this point probably (and I only have limited time to research it), but we also have to do it every time we move a report from environment to environment (and we have screw up and deployed to production with the UAT datasource).

Microsoft wants us to automate all of other deployments WhyTF should this be any different?

[Karla]

@TimeBandit said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

@Karla said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

We can go into each report in Visual Studio and update the datasource and upload the new file to the new CRM server.
But for 50 fucking reports?
I'm a programmer. I'm lazy and don't want to do that shit.

That's when you need an intern

A sexy hot intern...wakes up.

[TheCPUWizard]

@Karla said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

@TheCPUWizard said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

I must be missing something... can you not do this at the SSRS level?

We can go into each report in Visual Studio and update the datasource and upload the new file to the new CRM server.

But for 50 fucking reports?

I'm a programmer. I'm lazy and don't want to do that shit.

You can do the same thing with the SSRS Web-Service

Only a few lines of C# (or you can even do it in PowerShell)

[Vixen]

@Karla said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

@TimeBandit said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

@Karla said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

We can go into each report in Visual Studio and update the datasource and upload the new file to the new CRM server.
But for 50 fucking reports?
I'm a programmer. I'm lazy and don't want to do that shit.

That's when you need an intern

A sexy hot intern...wakes up.

this idea has my attention.....

[Unperverted Vixen]

@TheCPUWizard said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

You can do the same thing with the SSRS Web-Service

Is that still possible when it's not using a shared datasource?

[Karla]

@TheCPUWizard said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

@Karla said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

@TheCPUWizard said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

I must be missing something... can you not do this at the SSRS level?

We can go into each report in Visual Studio and update the datasource and upload the new file to the new CRM server.

But for 50 fucking reports?

I'm a programmer. I'm lazy and don't want to do that shit.

You can do the same thing with the SSRS Web-Service

Only a few lines of C# (or you can even do it in PowerShell)

Shit, really? I think I probably found pieces about the rs.exe but didn't see where I could update an actual report and it seemed to only refer to shared datasources.

OK now, I found:

dotnet-bot

ReportingService2010.SetItemDataSources(String, DataSource[]) Method (ReportService2010)

Sets the data sources for an item in a report server database or SharePoint library. This method applies to the Report and Model item types.

Is that allowed with CRM? I can't find PS right now...and I believe all the C# code uses the CRM Web API to make any data changes.

I can't find anything specific. I don't even know if the implementation of standard ReportServer is the same when integrated into CRM.

Also, if it that simple why to we have to upload the compiled version of the report? Unless that's wrong.

[Unperverted Vixen]

@Karla said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

I think I probably found pieces about the rs.exe but didn't see where I could update an actual report and it seemed to only refer to shared datasources.

rs.exe is just a command-line wrapper for the ReportingService2010 (or older) API.

Also, if it that simple why to we have to upload the compiled version of the report? Unless that's wrong.

In non-CRM land, you upload the RDL file (I believe via the SetItemDefinition endpoint), and then use SetItemDataSources to update the data source reference(s) to point at the correct shared data source(s) on the target server.

[Karla]

@Unperverted-Vixen said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

@Karla said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

I think I probably found pieces about the rs.exe but didn't see where I could update an actual report and it seemed to only refer to shared datasources.

rs.exe is just a command-line wrapper for the ReportingService2010 (or older) API.

That explains why it was less than helpful.

Also, if it that simple why to we have to upload the compiled version of the report? Unless that's wrong.

In non-CRM land, you upload the RDL file (I believe via the SetItemDefinition endpoint), and then use SetItemDataSources to update the data source reference(s) to point at the correct shared data source(s) on the target server.

Yeah, I figured.

[TheCPUWizard]

@Karla said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

@TheCPUWizard said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

@Karla said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

@TheCPUWizard said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

I must be missing something... can you not do this at the SSRS level?

We can go into each report in Visual Studio and update the datasource and upload the new file to the new CRM server.

But for 50 fucking reports?

I'm a programmer. I'm lazy and don't want to do that shit.

You can do the same thing with the SSRS Web-Service

Only a few lines of C# (or you can even do it in PowerShell)

Shit, really? I think I probably found pieces about the rs.exe but didn't see where I could update an actual report and it seemed to only refer to shared datasources.

OK now, I found:

dotnet-bot

ReportingService2010.SetItemDataSources(String, DataSource[]) Method (ReportService2010)

Sets the data sources for an item in a report server database or SharePoint library. This method applies to the Report and Model item types.

Is that allowed with CRM? I can't find PS right now...and I believe all the C# code uses the CRM Web API to make any data changes.

I can't find anything specific. I don't even know if the implementation of standard ReportServer is the same when integrated into CRM.

Also, if it that simple why to we have to upload the compiled version of the report? Unless that's wrong.

Can you provide the exact steps from your previous statement:

"We can go into each report in Visual Studio and update the datasource and upload the new file to the new CRM server."

[Karla]

@TheCPUWizard said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

"We can go into each report in Visual Studio and update the datasource and upload the new file to the new CRM server."

Once we have the version that is currently in production (either download from CRM or from TFS):
Open in Reporting project in Visual Studio.
Edit the embedded datasource.
Preview report in Visual Studio so it compiles with the new DS.
Browse to new CRM.
Go to reports.
Find specific report.
Select report.
Press edit.
Popup appears.
Select compiled version of file from debug/bin (or bin/debug IDR) folder to upload.
Save and close.

I don't understand how CRM uses it under the hood in comparison to a standard SSRS environment but it has limited our ability to use many features of SSRS.

We also don't have the most recent version of SSRS (2015, I think) or of CRM (though that may be different on the new server-we are changing tenants on Azure to one that has an improved connection).

CRM was never meant to handle the workflows we have and I think the HPC were paid by the fields/entities that they added. They also never understood how complex our business rules are. Our internal IT ended up redoing so much of it because it was un-usable.

Our legacy app while old as fuck, managed to do this and I could reuse things.

We have to copy everything from report to report because of CRM.

[TheCPUWizard]

@Karla - Let's continue this in private...

[Tsaukpaetra]

@TheCPUWizard said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

@Karla - Let's continue this in private...

...

[Applied Mediocrity]

@Tsaukpaetra said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

@TheCPUWizard said in Microsoft Dynamics CRM and SSRS -- migrating reports to new server:

@Karla - Let's continue this in private...

...

CC @TimeBandit