Coldfusion Report Builder - How can you set different datasources externally between prod/staging/de
- by Smooth Operator
Coldfusion Report Builder is great.
One small issue. We use ANT+CFANT to deploy.
When we create the report, say in a datasource called MyApp_dev on a dev box.
Everything works great when the report is created.
We deploy the report to our staging server, which has a datasource of MyApp_Staging. That server also, may or may not, have the live app working under MyApp_Live. Ant pushes the update to Staging just great.
Run the report, crashes and burns. Why?
It seems the report is looking for the MyApp_Dev data_source, even though the application is using the MyApp_Staging datasource.
In digging around I found a few approaches, I would like to do this one, final, ideal way from the beginning instead of having to go back to do dozens of reports differently when I have a new Aha! moment.
1) Obvious: Pass in the datasource in to the cfreport tag. Doesn't work for ColdFusion Builder Reports as of v8, or v9 as tested on Linux.
2) Most realistic option (but painful) so far: Pass in the query as an object into the ColdFusion Builder report. Let's think about this:
Create the Report with the report builder to my heart's content using the RDS, etc on my local box.
When I'm done, copy the query into a snippet of code, or into a database column to be dynamically be injected at runtime with correct datasource.
Modify my "run report" event to find the query from the database column, insert it into another dynamic cfquery and potentially... evaluate (!?!) it? Fun side is I can set the cfquery datasource to what I would need for each environment.
When I modify the report's columns in CF Report Builder, I always have to update the query in the database. Is there a snippet of code that can extract this for me? Hmm.
3) Less than ideal. Suck it up and let all the reports in staging run off the live server. Maybe copy the live data into staging (sans structural changes) to let it seem similar.
Are there any eloquent ways to accomplish the above?
Thanks in Advance!