Multiple Contexts in Universe on Single Crystal Report
This is exciting news to many of developers who wants to have Crystal Report behave just like the way WebI merges the dimensions when adding multiple queries to a WebI document. And No, I am not proposing to have a Sub-report. Make sure to click on ‘Read More’ button to view the “Video” and watch how its done.
The Video demonstrates how you could achieve using a single main report which actually generates a UNION query.This is very powerful way of reporting when you have multiple contexts in the Universe and want to pick and choose the dimensions/measures from both contexts on a single report without using a Sub-Report.
If the above player loads slow, you can click the link to view this video on YouTube Pulling Information from Multiple Contexts in Universe on Single Crystal Report
Hi Imran,
can you please share what is the formula you have written in the Dimension “Flag” to return the context numbers?
i badly need the formula as you know i have 80 odd contexts in my universe,now to adopt this feature what you have explained i need to write the dimension “Flag”, but really wondering for this 80 odd contexts, what to write?
regards,
prakashb
Hello Prakash:
Do you really want to merge the 80 contexts in a single Crystal Report ? Maybe you should go back to re-design the Universe or even the data structure if you have 80 different contexts in a single Universe.
If you really want to use this for 80 odd contexts, then you need to create this “flag” dimensions separately for all 80 contexts and join these to corresponding contexts and then you will be able to use this dimension based on value it returns for each context, though it does not make sense to have 80 contexts in universe and all of these needs to be used on single report. Email me more details and maybe i can take a look and do some suggestions.
Hi Imran,
First of all thank you very much for the quick reply.
My requirement is little typical. i have a webi report which uses couple of dimensions, 9 measures and like this there are 6 data providers. so to replicate this webi report in crystal, i have to design such a scenario.
Now when i tried to include all measures it gave me multiple sql error as crystal 2011 does not support multiple context or measure, hence i thought of going by your approach.
Now here the universe is such a huge one and its globally used by many projects, hence removing/resolving some context or reducing the context count is really not in my control as it might impact to other users/project.
Though i might not require all the contexts for this specific crystal report, but of course many of them. hence i thought of creating such a global FLAG dimension which will include all the context, by which i would not be in trouble in adopting your approach.
regards,
prakashb
Hi Imran,
Can I have the syntax of the Flag dimension. I believe you created Derived Objects for this Flag dimension.My requirement is I have 7 measure objects which from multiple context and creating more than SQL statement which is not supported by Crystal reports.
Here I was trying the way you described in the Video. but unable to write exact flag dimension for my requirement.
Each measure is included in 4 context.
so basically i want the condition like {if (ct1,ct2,ct3,ct4) then 1 elsif(ct5,ct6,ct7,ct8) then 2 and so on want to create for all 7measures to merge the sql statement.Please let me know this approch works or sugguest alternative method.I am not finding if then else condition in derived Table.
Thanks,
PrakashB
Hello Prakash:
Send me email or your phone number and we can go over it. The creation of this dimension is already there in video. I can elaborate and explain on how it could be done in your scenario. Email me at malam@rpeortgurus.com
Hi Imran,
i have sent more details to your below mentioned ID, but it bounced back
malam@rpeortgurus.com
anyhowthanks for all your advises so far.
here i have couple of updates & questions.
i have 80 contexts, out of which 28 contexts are getting used in the 9 measures i will be using in the crystal report.
now what i have done is have created several derived tables, e.g.
measure1 (MSU Shipment) consumes 4 contexts:
hence i have written the derived table as follows:
SELECT ‘SH DLY NC P710 PC063’ as msu_shipments FROM DUAL
UNION
SELECT ‘SH DLY NC P710 PC064’ as msu_shipments FROM DUAL
UNION
SELECT ‘SH DLY NC P5005 PC064’ as msu_shipments FROM DUAL
UNION
SELECT ‘SH DLY NC P5005 PC063’ as msu_shipments FROM DUAL
when i see the table values i could see the following:
MSU Shipments
—————————
SH DLY NC P710 PC063
SH DLY NC P710 PC064
SH DLY NC P5005 PC064
SH DLY NC P5005 PC063
and then created one dimension object named: MSU Shipments.
now in the crystal report i am calling the required dimensions, derived Table’s Dimension MSU Shipments and the required measure MSU Shipments; finally the query filters:
even then i am getting Failed to Retrieve SQL Error:
Query Generated Multiple SQL Statements, However multiple SQL is not supported in the Crystal Reports Query Panel.
if this would have been clicked i would have tried creating further all the derived tables and its dummy dimensions to pull all the measures into the Crystal reports Panel via UNION.
would appreciate your quick response, as struggling a hard to achieve the requirement.
Thanks and Regards,
Prakash Ranjan Behera
+91-974 107 7776
Hi Imran,
i have done two more steps to avoid the multiple sql
added the new derived table dimensions to the respective contexts
and provided joins to the respective fact tables.
now sql generation error is not there. but still getting context to choose from as well getting no data in the report.
regards,
prakashb
Hi Imran,
i still want clarification on the FLAG object what you have created for each derived tables i.e. for each context. what is the select statement you have written over there?
i could see the data your flag objects return is a number either 1 or 2 (a number), now in my case its returning a string MSU Shipments as per my above scenario. hence wanted some hint on the dummy object creation and further steps, just to ensure whether i am approaching correctly or not?
regards,
prakashb
+91-9741077776
Hi,
I have few questions on crystal reports? It will be very helpful if you reply or make a brief video showing this.
1. can we connect to multiple databases using crytsal reports
2. can we pull data from multiple universes in CR. (link)
3. how to connect to CR from QAAWS
1. can we connect to multiple databases using crytsal reports
Ans: Yes, Easiest way is to create two different ODBC for different DBs and use them in report, however be vary of the tables and joins if you going to use them when reporting against multiple DBs.
2. can we pull data from multiple universes in CR. (link)
Ans: It doesn’t work like Linked Universe, but you could add multiple Universes in different Sub-Reports and make joins betweek main reports to sub-reports or could also use shared variables to pass the data. Will depend on your requirement really to see if that fits your case or you could not.
3. how to connect to CR from QAAWS
Ans: Never had chance to do that, but will surely try it out and might make a vid of it.