Yes, we do this all of the time. You should have two separate URLs (see below), one pointing to your production server(s) and one pointing to your development server(s). You simply create a new personal connection to the applications you want to compare. I'm not sure if you can accomplish this via AdHoc or not, as we use the HsGetValue functions in Smartview to do this.
Ok but those are the Private connections right? I'm talking about having one report with data from both servers being retrieved at the same time.
Account July (Prod) July (Dev)
JDN gave you good advice, but perhaps a bit incomplete. You can do what you want, but not using a grid retrieval. You would have to use HSGETValue formulas
July(prod) would point to the production private connection and July(Dev) would point to the development connection.
In HSGetValue fomulas you specify the intersections to be returned
That's correct, you can retrieve data from both at the same time. You would have one column where you are pulling in your Production data and another column where you are pulling in your Development data within the same Excel sheet. Again, this is using HsGetValue, not Ad Hoc reporting. Maybe it's different for you, but for me I would go under Private Connections - Create New Connection - Select my Dev server from the drop down or key it in - this brings up a list of all of the applications on the dev server, select the application you want to connect to, give it a name and then use that name in your formula (i.e. HsGetValue("Dev",etc....). In your other coulumn you would connect to production in a similar manner, but your forumula would be HsGetValue("Prod",etc....).
Could i use this method on a report thats already existing?
Assuming you mean a Smartview report, yes. If you look at the formula in one of the cells it will be =HsGetValue("ConnectionName",etc...). You would simply do a find/replace for the connection name to change it to which ever connection you want to pull data from. As an example, if you have a report today that has the below setup pulling data from your production server:
Account DataSet1 DataSet2
You could save this report as a different name and then change the formulas in the DataSet2 column to reference your development application by using find/replace. When you refresh the data, DataSet1 will pull from production and DataSet2 will pull from development and you can add in another column to check the variance.