"tutor" wrote:

> hi Scott
>
> Here's what my scenario is (just so you are following my examples):
>
> 1. I run reports where the source elements are mostly calculated (things
> that use to have massive MS Access SQL TRANSFORMs and reports - which DON'T
> have the direct equivalent in SQL Srvr 2K).
>
> 2. I ended up writing my own generic crosstab/pivot sproc (which dynamically
> creates temp tables in SQL Server from an ASP.NET application). Here's an
> example sproc call:
>
>
> EXEC sp_crosstab
> '6F9619FF-8B86-D011-B42D-00C04FC964FF',
> '##ct',
> 'SELECT TOP 100 PERCENT AEv2.CbgCode, AEv2.ModelYear, AEv2.CY,
> AEv2.ProgName,
> AEv2.BuildPhaseCode, AEv2.SorP,
> AEv2.ComplexityIndexCode,
> AEv2.PowertrainCI, AEv2.CountOfPhase
> FROM dbo.vw_AttributeEureka_5v2 AEv2 WITH (NOLOCK) INNER JOIN
> dbo.ct_BuildPhases WITH (NOLOCK) ON
> AEv2.BuildPhaseCode = dbo.ct_BuildPhases.BuildPhaseCode INNER JOIN
> dbo.ct_Programs WITH (NOLOCK) ON AEv2.ProgName =
> dbo.ct_Programs.ProgName INNER JOIN
> dbo.ct_CBGs WITH (NOLOCK) ON
> dbo.ct_Programs.fkCbgCode = dbo.ct_CBGs.CbgCode
> WHERE (AEv2.CY >= YEAR(GETDATE()))
> ORDER BY AEv2.CbgCode, dbo.ct_Programs.ProgramDescription, AEv2.ModelYear,
> AEv2.ProgName, dbo.ct_BuildPhases.ColumnSort',
> 'CbgCode,
> ModelYear,
> CY,
> ProgName,
> BuildPhaseCode,
> ComplexityIndexCode,
> PowertrainCI',
> ',,,, CIC, PCI',
> 'SorP','CountOfPhase',
> 'SELECT convert(varchar(10),(ReportGrouping*10000)+ColumnSort)
> +'',[''+rtrim(RptAttributeCode)+'']'' as RptAttributeCode FROM
> dbo.vw_Report_9_ColHeadings',1
>
> This throws SRSS into an absolute 'tissy' 95% of the time... It 'fills it's
> drawers' basically when it tries to gets it's nose into figuring out what's
> going on with temporary tables, arbitrary fields, dynamic field names, etc.
> in SQL Server. (I've written several posts in this forum explaining the
> problems in the Wizard, refreshing fields, etc.)
>
> WHAT I DO:
>
> 1. run the sproc and get your results
> 2. put these results into a (persisted) table in SQL Server
> 3. create your report from this persisted table
> 4. WHEN 'all is well' with your report ADD another Dataset(2) with the sproc
> (and don't fret if it falls on it's face trying to figure out the field
> during a fields refresh)
>
> 5. make a copy of the .RDL in XML (and make a backup copy of the project as
> well).
> 6. remove DataSet1 from the report (persisted table)
> 7. rename DataSet2 (your sproc) to DataSet1
> 8. if you get a gazillion errors about fields are unknown, field source,
> don't fret...
> take you .rdl XML COPY, find the fields section of it (this was the stuff
> from the original DataSet1 persisted fields) and PASTE these field
> definitions into the RDL (looking at it as XML from VS.NET).
>
> Underlying problem with this version of SRSS is that it thinks it needs to
> make TOO MANY ASSUMPTIONS about what SQL Server is doing under the covers. To
> be a 'well behaved' product, this functionality needs to be relaxed (ie. I'm
> not going to try to figure out what's going on with SQL Srvr, Oracle, MySQL,
> etc., I'm going to be a good citizen, wait for 60 seconds and whatever the
> source is, it's going to give me the field names).
>
> Good Luck
> Rob
>
> "Scott" wrote:
>
> > I am developing a Reporting services project using C#. I ran the query that
> > will determine the fields that would appear in the output. It is basically a
> > complex inner join query involving more than one table and the some of them
> > are calculated columns that are not part of the any of these tables. How do I
> > create the layout. If it is a single table and the columns are present in the
> > table, I know how to do it.
> >
> > Thank you for pointing me to any link or article or stepping me through.
> >