Hi!
I've got some problems with a few reports I've made. The MDX queries of the
datasets are quite complex, so I have turned off design mode when working
with them. Of course, design mode won't work anymore, but Report Designer
has understood this until now and disabled design mode when I've swapped to
the data tab. Recently though, it doesn't understand it anymore, and enters
design mode, effectively removing the MDX, if I go to the data tab.
(Removing is not quite true, it changes to an empty "select {} on 0 from
mycube" query)
I have to do more modifications to the MDX, so to keep working with my
reports I have to manually modify the XML source if I need a new field for
instance.
Tried installing VS '05 SP 1 and SQL Server '05 SP 2, but it still does the
same.
I've had the problem once before, then I solved it by comparing with an
older version - where the query definition xml elements had a slightly
different order - and setting the same order in the new version. Of course,
the elements are re-ordered when I save the reports, so I have to do this
each time.. I won't.. :P
Any ideas? Are there any hint I can include in the XML to tell the designer
to lay off my query?
Lars-ErikHello Erik,
I would like to know whether you could modify the Code of your report VS.
If so, you may try to modify the code.
Also, please verify that you have installed the SQL Server 2005 SP2
correctly.
I found a similar issue and it has been resolved in SQL 2005 SP1.
909535 FIX: A query disappears in the MDX query designer, and you cannot
modify a report in Layout view when you create the report in Business
Intelligence Development Studio in SQL Server 2005
http://support.microsoft.com/default.aspx?scid=kb;EN-US;909535
Could you please verify that?
If you have a SQL 2005 SP1 installed machine, did this issue appear on that
machine?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi again!
Not sure what code you mean by code. I can modify the RDL using the "open
code" from the context menu in solution expl., and I can modify the custom
code of the report. Modifying the RDL using code view is what I've done to
add fields now.
I've now got Microsoft SQL Server Reporting Services Designers Version
9.00.3042.00 installed.
I checked the info in the KB article, and now I'm a bit stumped. I was sure
I had <QueryDefinition .../> elements in the dataset definitions, but
somewhere along the road they've disappeared. I've had to update two query
definitions in one dataset before while the problem existed. One of my
reports still have the element (on one line) and I can modify the query in
the data designer as I should.
I can modify my reports in layout view though, so not sure if all the info
in the KB are relevant.
With this information I can probably do a compare with older versions, get
back the QueryDefinition elements, update any old info, verify one line, and
get it working again. What worries me is that the behavior probably comes
back, since it's done so before.. Any ideas on how I can ensure stability?
I have to mention a couple of other points that might influence this:
- I do modify the RDL manually because I have 14-15 drillthrough actions in
each report that are too complex to handle using the designer. (I generate
XML for the <action> elements from a matrix with complex formulas in Excel)
- There's an issue with line breaks VS keeps suggesting to fix, and does,
but it comes back for some reason. (might be a difference created when
saving on different machines)
Lars-Erik
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:QY98NdenHHA.5420@.TK2MSFTNGHUB02.phx.gbl...
> Hello Erik,
> I would like to know whether you could modify the Code of your report VS.
> If so, you may try to modify the code.
> Also, please verify that you have installed the SQL Server 2005 SP2
> correctly.
> I found a similar issue and it has been resolved in SQL 2005 SP1.
> 909535 FIX: A query disappears in the MDX query designer, and you cannot
> modify a report in Layout view when you create the report in Business
> Intelligence Development Studio in SQL Server 2005
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;909535
> Could you please verify that?
> If you have a SQL 2005 SP1 installed machine, did this issue appear on
> that
> machine?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello Lars,
I would like to konw whether you have this issue on other reports?
And did this issue happened on SQL 2005 Sp1?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi!
Thanks for the follow-up.
I made the reports with no SP installed. (About said RTM something)
Have had the same issue with other reports, but think they've been behaving
properly after some repairing.
Have not tried to repair my reports yet, I'll do the extra hours I need to
repair them some time soon. If the queries disappear again, I'll create a
new post.
L-E
"Wei Lu [MSFT]" <weilu@.online.microsoft.com> wrote in message
news:5uQJ0oboHHA.564@.TK2MSFTNGHUB02.phx.gbl...
> Hi ,
> How is everything going? Please feel free to let me know if you need any
> assistance.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>|||Hello,
Please let me know if you have any question and I will temporary close this
post.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Showing posts with label datasets. Show all posts
Showing posts with label datasets. Show all posts
Wednesday, March 28, 2012
Report Designer data Tables
I have a set of data tables with their own datasets from different database
tables. They go across the report page and all need to be linked together
via an ID number. How can this be achieved or is there a better way of doing
it other than in report tables?
The data tables across the report page are:
Details, Actuals and Budgets ie., ID number in Details need to be linked to
the same ID in Actuals and Budgets so that it appears as a single line of
continuous information across the page.
Thanks
IanStep 1. Create one report for each of the Details, Actuals, Budget data.
Give each report an ID parameter.
Step 2. Create a main report and add a list data region. Make the dataset of
the list be based on a query that returns unique id values. Inside the list
data region add subreport controls and map them each to the reports you
created at Step 1. You will need to map the subreport parameters to the ID
field in the main report.
Done.
HTH
Charles Kangai, MCT, MCDBA
"IanP" wrote:
> I have a set of data tables with their own datasets from different database
> tables. They go across the report page and all need to be linked together
> via an ID number. How can this be achieved or is there a better way of doing
> it other than in report tables?
>
> The data tables across the report page are:
>
> Details, Actuals and Budgets ie., ID number in Details need to be linked to
> the same ID in Actuals and Budgets so that it appears as a single line of
> continuous information across the page.
>
> Thanks
> Ian
>|||This sounds more like a query problem than a report problem. Is there a
reason that you are not joining the tables in the source query? Then each
record returned will contain all your information -- details, actuals, and
budgets. When you say single line of data, I presume that there is only one
row of data for each ID. Is that not the case?
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"IanP" <IanP@.discussions.microsoft.com> wrote in message
news:9F30A2FE-A28F-45E6-989C-913EB78FD61A@.microsoft.com...
>I have a set of data tables with their own datasets from different database
> tables. They go across the report page and all need to be linked together
> via an ID number. How can this be achieved or is there a better way of
> doing
> it other than in report tables?
>
> The data tables across the report page are:
>
> Details, Actuals and Budgets ie., ID number in Details need to be linked
> to
> the same ID in Actuals and Budgets so that it appears as a single line of
> continuous information across the page.
>
> Thanks
> Ian
>|||Thank you Charles and Jeff
Yes jeff it is a db design problem, I was not involved in its design, and
now it is being revisited and Jeff, your note sounds promising so I will go
back and revisit what I was experimenting with
Ian
"Jeff A. Stucker" wrote:
> This sounds more like a query problem than a report problem. Is there a
> reason that you are not joining the tables in the source query? Then each
> record returned will contain all your information -- details, actuals, and
> budgets. When you say single line of data, I presume that there is only one
> row of data for each ID. Is that not the case?
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "IanP" <IanP@.discussions.microsoft.com> wrote in message
> news:9F30A2FE-A28F-45E6-989C-913EB78FD61A@.microsoft.com...
> >I have a set of data tables with their own datasets from different database
> > tables. They go across the report page and all need to be linked together
> > via an ID number. How can this be achieved or is there a better way of
> > doing
> > it other than in report tables?
> >
> >
> > The data tables across the report page are:
> >
> >
> > Details, Actuals and Budgets ie., ID number in Details need to be linked
> > to
> > the same ID in Actuals and Budgets so that it appears as a single line of
> > continuous information across the page.
> >
> >
> > Thanks
> >
> > Ian
> >
>
>
tables. They go across the report page and all need to be linked together
via an ID number. How can this be achieved or is there a better way of doing
it other than in report tables?
The data tables across the report page are:
Details, Actuals and Budgets ie., ID number in Details need to be linked to
the same ID in Actuals and Budgets so that it appears as a single line of
continuous information across the page.
Thanks
IanStep 1. Create one report for each of the Details, Actuals, Budget data.
Give each report an ID parameter.
Step 2. Create a main report and add a list data region. Make the dataset of
the list be based on a query that returns unique id values. Inside the list
data region add subreport controls and map them each to the reports you
created at Step 1. You will need to map the subreport parameters to the ID
field in the main report.
Done.
HTH
Charles Kangai, MCT, MCDBA
"IanP" wrote:
> I have a set of data tables with their own datasets from different database
> tables. They go across the report page and all need to be linked together
> via an ID number. How can this be achieved or is there a better way of doing
> it other than in report tables?
>
> The data tables across the report page are:
>
> Details, Actuals and Budgets ie., ID number in Details need to be linked to
> the same ID in Actuals and Budgets so that it appears as a single line of
> continuous information across the page.
>
> Thanks
> Ian
>|||This sounds more like a query problem than a report problem. Is there a
reason that you are not joining the tables in the source query? Then each
record returned will contain all your information -- details, actuals, and
budgets. When you say single line of data, I presume that there is only one
row of data for each ID. Is that not the case?
--
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"IanP" <IanP@.discussions.microsoft.com> wrote in message
news:9F30A2FE-A28F-45E6-989C-913EB78FD61A@.microsoft.com...
>I have a set of data tables with their own datasets from different database
> tables. They go across the report page and all need to be linked together
> via an ID number. How can this be achieved or is there a better way of
> doing
> it other than in report tables?
>
> The data tables across the report page are:
>
> Details, Actuals and Budgets ie., ID number in Details need to be linked
> to
> the same ID in Actuals and Budgets so that it appears as a single line of
> continuous information across the page.
>
> Thanks
> Ian
>|||Thank you Charles and Jeff
Yes jeff it is a db design problem, I was not involved in its design, and
now it is being revisited and Jeff, your note sounds promising so I will go
back and revisit what I was experimenting with
Ian
"Jeff A. Stucker" wrote:
> This sounds more like a query problem than a report problem. Is there a
> reason that you are not joining the tables in the source query? Then each
> record returned will contain all your information -- details, actuals, and
> budgets. When you say single line of data, I presume that there is only one
> row of data for each ID. Is that not the case?
> --
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "IanP" <IanP@.discussions.microsoft.com> wrote in message
> news:9F30A2FE-A28F-45E6-989C-913EB78FD61A@.microsoft.com...
> >I have a set of data tables with their own datasets from different database
> > tables. They go across the report page and all need to be linked together
> > via an ID number. How can this be achieved or is there a better way of
> > doing
> > it other than in report tables?
> >
> >
> > The data tables across the report page are:
> >
> >
> > Details, Actuals and Budgets ie., ID number in Details need to be linked
> > to
> > the same ID in Actuals and Budgets so that it appears as a single line of
> > continuous information across the page.
> >
> >
> > Thanks
> >
> > Ian
> >
>
>
Report Designer - Importing Excel Files Into Datasets
Using the MS SQL Server 2005 CTP on os win2k prof.
I have set up two datasets in a report that are supposed to be referencing
to separate data sources where the following connection strings are aimed at
separate files in the same target folder: -
DataSource1: -
Dsn=Excel
Files;dbq=<drive>:\<folder>\<file1.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
DataSource2: -
Dsn=Excel
Files;dbq=<drive>:\<folder>\<file2.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
I can correctly import the columns of File1.xls to DataSet1 but when
attempting to import File2.xls to DataSet2, the columns of DataSet1 are only
visible.
I have definitely referenced the correct data sources to each dataset.
Any ideas why DataSource2 is not appearing?
Cheers
TrevI have found the solution - set up individual File DSNs for each file and
point to these; they can be generic too.
Thx to those who may have provided feedback if I had not answered my own
question!!!
"auldreidy" wrote:
> Using the MS SQL Server 2005 CTP on os win2k prof.
> I have set up two datasets in a report that are supposed to be referencing
> to separate data sources where the following connection strings are aimed at
> separate files in the same target folder: -
> DataSource1: -
> Dsn=Excel
> Files;dbq=<drive>:\<folder>\<file1.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
> DataSource2: -
> Dsn=Excel
> Files;dbq=<drive>:\<folder>\<file2.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
> I can correctly import the columns of File1.xls to DataSet1 but when
> attempting to import File2.xls to DataSet2, the columns of DataSet1 are only
> visible.
> I have definitely referenced the correct data sources to each dataset.
> Any ideas why DataSource2 is not appearing?
> Cheers
> Trev|||how did you get the excel file to import into the dataset?
i can't seem to figure this out in the Query String window after setting up
the dsn...
"auldreidy" <auldreidy@.discussions.microsoft.com> wrote in message
news:E7921576-2D35-45CC-A742-540C46D2E13B@.microsoft.com...
> Using the MS SQL Server 2005 CTP on os win2k prof.
> I have set up two datasets in a report that are supposed to be referencing
> to separate data sources where the following connection strings are aimed
> at
> separate files in the same target folder: -
> DataSource1: -
> Dsn=Excel
> Files;dbq=<drive>:\<folder>\<file1.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
> DataSource2: -
> Dsn=Excel
> Files;dbq=<drive>:\<folder>\<file2.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
> I can correctly import the columns of File1.xls to DataSet1 but when
> attempting to import File2.xls to DataSet2, the columns of DataSet1 are
> only
> visible.
> I have definitely referenced the correct data sources to each dataset.
> Any ideas why DataSource2 is not appearing?
> Cheers
> Trev|||I set up a shared data source and then referenced it in the report dataset.
When you're setting up the data source, RD allows you to browse for the dsn
so you don't need to figure it out!
Trev
"TroyS" wrote:
> how did you get the excel file to import into the dataset?
> i can't seem to figure this out in the Query String window after setting up
> the dsn...
>
> "auldreidy" <auldreidy@.discussions.microsoft.com> wrote in message
> news:E7921576-2D35-45CC-A742-540C46D2E13B@.microsoft.com...
> > Using the MS SQL Server 2005 CTP on os win2k prof.
> >
> > I have set up two datasets in a report that are supposed to be referencing
> > to separate data sources where the following connection strings are aimed
> > at
> > separate files in the same target folder: -
> >
> > DataSource1: -
> >
> > Dsn=Excel
> > Files;dbq=<drive>:\<folder>\<file1.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
> >
> > DataSource2: -
> >
> > Dsn=Excel
> > Files;dbq=<drive>:\<folder>\<file2.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
> >
> > I can correctly import the columns of File1.xls to DataSet1 but when
> > attempting to import File2.xls to DataSet2, the columns of DataSet1 are
> > only
> > visible.
> >
> > I have definitely referenced the correct data sources to each dataset.
> >
> > Any ideas why DataSource2 is not appearing?
> >
> > Cheers
> > Trev
>
>
I have set up two datasets in a report that are supposed to be referencing
to separate data sources where the following connection strings are aimed at
separate files in the same target folder: -
DataSource1: -
Dsn=Excel
Files;dbq=<drive>:\<folder>\<file1.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
DataSource2: -
Dsn=Excel
Files;dbq=<drive>:\<folder>\<file2.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
I can correctly import the columns of File1.xls to DataSet1 but when
attempting to import File2.xls to DataSet2, the columns of DataSet1 are only
visible.
I have definitely referenced the correct data sources to each dataset.
Any ideas why DataSource2 is not appearing?
Cheers
TrevI have found the solution - set up individual File DSNs for each file and
point to these; they can be generic too.
Thx to those who may have provided feedback if I had not answered my own
question!!!
"auldreidy" wrote:
> Using the MS SQL Server 2005 CTP on os win2k prof.
> I have set up two datasets in a report that are supposed to be referencing
> to separate data sources where the following connection strings are aimed at
> separate files in the same target folder: -
> DataSource1: -
> Dsn=Excel
> Files;dbq=<drive>:\<folder>\<file1.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
> DataSource2: -
> Dsn=Excel
> Files;dbq=<drive>:\<folder>\<file2.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
> I can correctly import the columns of File1.xls to DataSet1 but when
> attempting to import File2.xls to DataSet2, the columns of DataSet1 are only
> visible.
> I have definitely referenced the correct data sources to each dataset.
> Any ideas why DataSource2 is not appearing?
> Cheers
> Trev|||how did you get the excel file to import into the dataset?
i can't seem to figure this out in the Query String window after setting up
the dsn...
"auldreidy" <auldreidy@.discussions.microsoft.com> wrote in message
news:E7921576-2D35-45CC-A742-540C46D2E13B@.microsoft.com...
> Using the MS SQL Server 2005 CTP on os win2k prof.
> I have set up two datasets in a report that are supposed to be referencing
> to separate data sources where the following connection strings are aimed
> at
> separate files in the same target folder: -
> DataSource1: -
> Dsn=Excel
> Files;dbq=<drive>:\<folder>\<file1.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
> DataSource2: -
> Dsn=Excel
> Files;dbq=<drive>:\<folder>\<file2.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
> I can correctly import the columns of File1.xls to DataSet1 but when
> attempting to import File2.xls to DataSet2, the columns of DataSet1 are
> only
> visible.
> I have definitely referenced the correct data sources to each dataset.
> Any ideas why DataSource2 is not appearing?
> Cheers
> Trev|||I set up a shared data source and then referenced it in the report dataset.
When you're setting up the data source, RD allows you to browse for the dsn
so you don't need to figure it out!
Trev
"TroyS" wrote:
> how did you get the excel file to import into the dataset?
> i can't seem to figure this out in the Query String window after setting up
> the dsn...
>
> "auldreidy" <auldreidy@.discussions.microsoft.com> wrote in message
> news:E7921576-2D35-45CC-A742-540C46D2E13B@.microsoft.com...
> > Using the MS SQL Server 2005 CTP on os win2k prof.
> >
> > I have set up two datasets in a report that are supposed to be referencing
> > to separate data sources where the following connection strings are aimed
> > at
> > separate files in the same target folder: -
> >
> > DataSource1: -
> >
> > Dsn=Excel
> > Files;dbq=<drive>:\<folder>\<file1.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
> >
> > DataSource2: -
> >
> > Dsn=Excel
> > Files;dbq=<drive>:\<folder>\<file2.xls>;defaultdir=<drive>:\<folder>;driverid=790;maxbuffersize=2048;pagetimeout=5
> >
> > I can correctly import the columns of File1.xls to DataSet1 but when
> > attempting to import File2.xls to DataSet2, the columns of DataSet1 are
> > only
> > visible.
> >
> > I have definitely referenced the correct data sources to each dataset.
> >
> > Any ideas why DataSource2 is not appearing?
> >
> > Cheers
> > Trev
>
>
Monday, March 26, 2012
Report deployement
Hi,
I have a heavy report:
- 18 datasets (some with filters)
- 15 parameters
- about 10 tables (some with groups)
- lots of TextBox static or calculated (50 at least)
- and a little report footer
Anyway…
I’m using Visual Studio 2005 to deploy my report on a server. Recently, my deployments became very long and now, Visual Studio throw Timeouts when I deploy it. I walk around this problem with the report management site on the server But deploying my report through it takes about 8 or 10 minutes…quite long !
Here are my questions :
- What are report elements that causes such time span ?
- How lighten the deployment ?
- How configure the deployement timeout in visual studio ?
Thanks
Which exact error message are you experiencing ?Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||
The report services on the server has been installed with the french version
so the error message is in french : "Le dlai d'attente de l'opration a expir"
in english it looks like : "The wait delay of the operation has expire"
thanks
Subscribe to:
Posts (Atom)