7 Replies Latest reply: Feb 15, 2012 5:54 PM by OregonAlpha RSS

    Union SQL Statements in a DataTemplate

    danscan1712
      I am in the process of converting a bunch (150+) BI Answers reports into about 9 Publisher reports(we have 9 dashboards now).
      Due to business logic and other considerations I have a large amount of reports that leverage unions.

      How do I have to handle SQL Unions statements in a Data Template?
      Do I have to break them up?
      How do I map multiple data sources to one data element name. I have some instances where four or five different SQL statements make one BI Report
      ie
      <element name="PublisherName" value="BIAnswers1"/>
      <element name="PublisherName" value="BIAnswer2"/>
      <element name="PublisherName" value="BIAnswers3"/>
        • 1. Re: Union SQL Statements in a DataTemplate
          BIPuser
          Would it be possible for you to create a combined request (union of reports on the Answers side) to get the data as required?
          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..
          • 2. Re: Union SQL Statements in a DataTemplate
            danscan1712
            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?
            • 3. Re: Union SQL Statements in a DataTemplate
              danscan1712
              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.

              XML:
              <dataQuery>
                        <sqlStatement name="CallData">
                        <![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

              ]]></sqlStatement>
                   </dataQuery>
                   <dataStructure>
                        <group name="CallData" source="CallData">
                                  <element name="REPORTINGMONTH" value="saw_0"/>
                                  <element name="CALLSANSWERED" value="saw_1"/>
                        </group>
              • 4. Re: Union SQL Statements in a DataTemplate
                danscan1712
                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
                • 5. Re: Union SQL Statements in a DataTemplate
                  BIPuser
                  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!
                  • 6. Re: Union SQL Statements in a DataTemplate
                    danscan1712
                    (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.
                    • 7. Re: Union SQL Statements in a DataTemplate
                      OregonAlpha
                      I was also running into errors trying to use a UNION in an xml report. This thread helped me. Thanks!