| |
|
|
|
|
|
|
|
|
|
 |  | |
| | |
| | |
| | |
 | | |
|
|
|
| Multiple-row query in Reports |
|
|
| With the deadline for my project growing closer, I've abandoned the idea of getting the UDT Data Source plugin working in my Reports module. Instead, I'm querying directly into the database to pull the data values I want to report on. There's just one huge problem: because of how UDT data is structured, I'm getting each row of data as 26 separate one-column rows.
My first question: is there any way to write an HTML Visualizer template that displays a single logical record (as viewed in the HTML page) from more than one row in a SQL recordset?
My next question: If one Visualizer record always equals one SQL recordset row, does anyone have an example of a query that 'flattens' a fixed-length set of rows returned from a base query into a single row to satisfy Visualizer? All of the solutions available out there involve adding the CASE and PIVOT clauses to my query in conjunction with a grouping, but none of these work on the alphanumeric data returned by my query. Both CASE and PIVOT require grouping with MAX or other aggegate functions, but none of these will cooperate with the ntext column data type generated by UDT. |
|
|
|
| |
|
|
| Re: Multiple-row query in Reports |
|
|
| Well, I got my crosstab query working, but now there's a new problem. By using CAST(), I was able to get the MAX() calls to acceptUDT fields, returning one row per UDT logical record:
select uf.ModuleId,
max(case when uf.UserDefinedFieldId = 6 then cast(ud.FieldValue as varchar) end) as BusinessName,
max(case when uf.UserDefinedFieldId = 7 then cast(ud.FieldValue as varchar) end) as ContactName,
max(case when uf.UserDefinedFieldId = 8 then cast(ud.FieldValue as varchar) end) as Email,
max(case when uf.UserDefinedFieldId = 9 then cast(ud.FieldValue as varchar) end) as WebURL,
max(case when uf.UserDefinedFieldId = 10 then cast(ud.FieldValue as varchar) end) as Phone1Type,
max(case when uf.UserDefinedFieldId = 11 then cast(ud.FieldValue as varchar) end) as Phone1,
max(case when uf.UserDefinedFieldId = 12 then cast(ud.FieldValue as varchar) end) as Phone2Type,
max(case when uf.UserDefinedFieldId = 13 then cast(ud.FieldValue as varchar) end) as Phone2,
max(case when uf.UserDefinedFieldId = 14 then cast(ud.FieldValue as varchar) end) as Address1,
max(case when uf.UserDefinedFieldId = 15 then cast(ud.FieldValue as varchar) end) as Address2,
max(case when uf.UserDefinedFieldId = 16 then cast(ud.FieldValue as varchar) end) as City,
max(case when uf.UserDefinedFieldId = 17 then cast(ud.FieldValue as varchar) end) as StateProv,
max(case when uf.UserDefinedFieldId = 18 then cast(ud.FieldValue as varchar) end) as PostCode,
max(case when uf.UserDefinedFieldId = 19 then cast(ud.FieldValue as varchar) end) as Country,
max(case when uf.UserDefinedFieldId = 20 then cast(ud.FieldValue as varchar) end) as ProductName,
max(case when uf.UserDefinedFieldId = 21 then cast(ud.FieldValue as varchar) end) as ProductDescription,
max(case when uf.UserDefinedFieldId = 24 then cast(ud.FieldValue as varchar) end) as FeaturedProduct,
max(case when uf.UserDefinedFieldId = 25 then cast(ud.FieldValue as varchar) end) as WebAdvertising,
max(case when uf.UserDefinedFieldId = 26 then cast(ud.FieldValue as varchar) end) as NewsletterCoverage,
max(case when uf.UserDefinedFieldId = 31 then cast(ud.FieldValue as varchar) end) as ProductSummary,
max(case when uf.UserDefinedFieldId = 32 then cast(ud.FieldValue as varchar) end) as MainGraphic,
max(case when uf.UserDefinedFieldId = 33 then cast(ud.FieldValue as varchar) end) as SecondaryGraphic
from UserDefinedData ud
join UserDefinedFields uf on ud.UserDefinedFieldId = uf.UserDefinedFieldId
where uf.ModuleId = 414
group by uf.ModuleId
When I present this to the Reports query check link, it works fine. But when I Update, I get gibberish because the query gets truncated about three-fourths through. I tried loading the query from a file instead of cut/paste, with the same result. Please don't tell me that there's a chintzy query size limit in Reports! This query is not large by SQL Server standards. If we're going to see DNN used for real grown-up applications with large and complex data sets, we're going to have to do better than this. |
|
|
|
| |
|
|
| Re: Multiple-row query in Reports |
|
|
Alan, Try SQLGridSelectedview from www.tressleworks.ca ... it will handle up to 8000 characters in the primary Select. I believe you will find this reporting / grid tool has other features you may like. There is a free version, or for a modest fee, you can get the most recent. The free version should be able to handle the query you listed.
Paul
|
|
|
|
| |
| | |
| | |
| | |
|  |
| |
|
|
|
These Discussion Forums are dedicated to the discussion of the DotNetNuke Web Application Framework.
For the benefit of the community and to protect the integrity of the project, please observe the following posting guidelines:
1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DotNetNuke.
2. Discussion or promotion of DotNetNuke product releases under a different brand name are strictly prohibited.
3. No Flaming or Trolling.
4. No Profanity, Racism, or Prejudice.
5. Site Moderators have the final word on approving/removing a thread or post or comment.
6. English language posting only, please.
|
| |
|
|
|
|
|
|
|
|