This content has been marked as final. Show 7 replies
Would it be possible for you to create a combined request (union of reports on the Answers side) to get the data as required?1 person found this helpful
Another option would be to have multiple data models(each a data template) and use concatenated SQL data source and generate the data;
you would need to have a key/id that links these data sets if you need to display an element from one data set in the other..
I was able to get some of the data out by leveraging two sql querris and then creating two groups in the data structure. I kept the data elements for each in the same name but the group name is different. I am currently experitmenting with word template to see how I can spit it out correctly.
I will post up results. But over all UNION statements in the SQL Query (<sqlStatement>) just don't pull the data out correctly?
I had to go back to the orginal SQL with the union. Wha tI don't understand is that I get the correct amount of rows but the fields do not seem to be mapped correctly. They are all blank. I get the 20 rows I need to get but no data is in any of the elements.
I can check the SQL in the BI answers direct sql function and it functions as desired.
<![CDATA[SELECT saw_0, saw_1 FROM ((SELECT "- Reporting Date"."Reporting Month" saw_0, FILTER(FILTER("- CMS Call Metrics"."# CMS Calls Answered" USING (("- CMS Details"."CMS Interval" = 0.00) AND ("- VDN Details"."VDN Area" = 'SVC') AND ("- VDN Details"."VDN Business Unit" = 'HLT') AND ("- Reporting Date"."Reporting Date" Between "- VDN Details"."VDN Start Date" and "- VDN Details"."VDN End Date"))) USING ("- VDN Details"."VDN Partner" = 'UHG')) saw_1 FROM Finance WHERE ("- Reporting Date"."Reporting Year" = VALUEOF("CURRENT_YEAR")) AND ("- Reporting Date"."Reporting Month" <> VALUEOF("CURRENT_MONTH"))) UNION (SELECT "- Reporting Date"."Reporting Month" saw_0, 0 saw_1 FROM Finance WHERE "- Reporting Date"."Reporting Year" = VALUEOF("CURRENT_YEAR"))) t1 ORDER BY saw_0
<group name="CallData" source="CallData">
<element name="REPORTINGMONTH" value="saw_0"/>
<element name="CALLSANSWERED" value="saw_1"/>
I found a person who was using a very long SQL statement that included a UNION and looked like it is possible. I am unsure what is actually wrong. Is there something wrong with the data template? Wrong name or anything?
data template datatrigger error
I was able to replicate your issue and found a workaround that worked for me. Sent you an email. Take a look and try it out and see if that works.
Hope that helps!
(Short answer: give the outer sql an alias)
BIP and I worked this out off message board. BIP figured out that recreating the answers report to so that the individual outter select statement columns were filtered by another report seemed to solve the problem.
I reviewed the SQL of the two for differences and found that the outer select statement by default does not have it's columns aliased. Publisher can only understand the columns if it is aliased.
If the following union statement is used it will not be able to map the data structure correctly. You will recieve the correct amount of rows but emplty data.
Select Col1, Col2 from (Select Fname Col1, LName Col2 from customer ........)UNION(select FirstName col1, Lastname col2 from oldCustomer ....) t1 order by Col2
Instead apply an alias to the outer Select statement like so
Select Col1 Col1, Col2 Col2 from (Select Fname Col1, LName Col2 from customer ........)UNION(select FirstName col1, Lastname col2 from oldCustomer ....) t1 order by Col2
I am going to do some more checking but perhaps the engine that maps the returned dataset to the datastructure can not handle unaliased columns.
I was also running into errors trying to use a UNION in an xml report. This thread helped me. Thanks!