CTE question

Question

Post reply

CTE question

antonio.collins

SSCrazy Eights

Points: 9600
More actions
April 1, 2008 at 1:19 pm

#184669

I'm writing a report in Report Services that will allow users to pick columns from a very wide data set (over 250 columns) and compare data sets. We initially had this logic in a view and it worked fine but prevented us from specifying criteria on columns that weren't exposed by the view (obviously).
With a view, there's (usually) no penalty to exposing lots of columns since only the selected columns will impact the query. Is the same true with a CTE?
with WideData( fixedCol1, fixedCol2, ...,
flexCol1, flexCol2, ..., flexCol250 )
as ( select ... complex summarization for 250+ flexCols ... group by ... )
select isnull(D1.fixedCol1,D2.fixedCol1) as fixedCol1,
isnull(D1.fixedCol2,D2.fixedCol2) as fixedCol2,
...
D1.flexCol01 as leftCol01, D1.flexCol02 as leftCol02, ...
D2.flexCol02 as rightCol01, D2.flexCol02 as rightCol02, ...
from WideData D1 full outer join WideData D2 on ...
Our strategy is to basically make an .RDL on-the-fly by transforming a template .RDL with the user's selections. The template will have the maximum number of flex columns allowed and the transform will replace D1/D2.flexCol01, D1/D2.flexCol03, ... with the actual columns the user desires. Unused columns in the report will simply be hidden.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 1

Yes, you could do it that way. 😀

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 2

Antonio...

When you say ".RDL", are you talking about a "CLR"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 3

Jeff Moden (4/1/2008)
Antonio...
When you say ".RDL", are you talking about a "CLR"?

No, I'll bet he's dynamically creating the .RDL (Reporting Services Definition) files on the fly. 😀

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Jason Selburg (4/2/2008)
Jeff Moden (4/1/2008)
Antonio...
When you say ".RDL", are you talking about a "CLR"?
No, I'll bet he's dynamically creating the .RDL (Reporting Services Definition) files on the fly. 😀

(shiver)

Did someone mention doing that ON THE FLY???

(shiver)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 5

Thanks, Jason... I'm still a newbie when it comes to 2k5.

Hey, Matt... why is on the fly bad for this?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

It's just a tad bit messy. Performance issue prone from what I can see. It evokes building Excel files from macros, etc.... Hard to troubleshoot, etc...

I guess if we are only messing with the connection/query, and not the "meat" or display of the report, it's not so bad. I've however seen some rather unfortunate attempts at creating the entire report object dynamically (query, parameter, layout, grouping, etc...), and since it "regenerates every time", there was no telling what it might show this time versus the last...;)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

antonio.collins SSCrazy Eights Points: 9600 More actions · Answer 7

Matt Miller (4/2/2008)
It's just a tad bit messy. Performance issue prone from what I can see. It evokes building Excel files from macros, etc.... Hard to troubleshoot, etc...
I guess if we are only messing with the connection/query, and not the "meat" or display of the report, it's not so bad. I've however seen some rather unfortunate attempts at creating the entire report object dynamically (query, parameter, layout, grouping, etc...), and since it "regenerates every time", there was no telling what it might show this time versus the last...;)

actually, it's very straight-forward. the .rdl is an xml document so it's simple to transform it with XSLT. the query footprint remains the same as does the execution plan... just different metrics are selected.

the only issue is identifying the elements that should be dynamically changed. the ideal solution would be to add another namespace to the .rdl and tag elements with our own attributes. but if the 'master' template is edited, the report designer won't preserve our extra namespace data. so, it looks like we'll have to encode instructions within the DataElementName. Here's a dynamic cell. The changeable stuff is in bold and is very straightforward.

<Style>

<Color>#202020</Color>

<BackgroundColor>=IIf(Sum(Fields!metric_03.Value)>=4.0,"PaleGreen","Transparent")</BackgroundColor>

<Default>Gainsboro</Default>

<Right>DimGray</Right>

</BorderColor>

<Default>Solid</Default>

</BorderStyle>

<FontFamily>Lucida Sans</FontFamily>

<FontWeight>=IIf(Sum(Fields!metric_03.Value)>1.0,"Bold","Normal")</FontWeight>

<Format>0.0</Format>

</Style>

<DataElementName>metric_03:action=;style=;</DataElementName>

<Value>Sum(Fields!metric_03.Value)</Value>

</Textbox>

</ReportItems>

</MatrixCell>