Report Designer deletes MDX when opening data tab

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.

Report Builder queries

I have not been able to take a look at the new report builder, but I needed
some information urgently!
Can someone please help me out?
Does the usage of report builder need a client to be installed in every user
who needs to use ad hoc reporting? Is this client installed automatically on
clicking on the report?
Does the client also need Visual Studio?
thanks!
--
Message posted via http://www.sqlmonster.comThere is no pre-installed software... When you click on report builder, it is
automatically downloaded onto the client and runs. You do not need visual
studio. This is for report builder developers.
For users of reports, there is no difference between report builder reports
and other reports, it is strictly an IIS web page...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"tarana h via SQLMonster.com" wrote:
> I have not been able to take a look at the new report builder, but I needed
> some information urgently!
> Can someone please help me out?
> Does the usage of report builder need a client to be installed in every user
> who needs to use ad hoc reporting? Is this client installed automatically on
> clicking on the report?
> Does the client also need Visual Studio?
> thanks!
>
> --
> Message posted via http://www.sqlmonster.com
>|||Sorry, I am still not clear!
A user who wants to use the new ad hoc reporting feature - will the report
builder client get installed on his machine? The reason I ask this is the
clients are external and will be accessing the reports over the internet and
may have strict security policies that may not allow any sort of software
downloads or installations
Thank you!
Wayne Snyder wrote:
>There is no pre-installed software... When you click on report builder, it is
>automatically downloaded onto the client and runs. You do not need visual
>studio. This is for report builder developers.
>For users of reports, there is no difference between report builder reports
>and other reports, it is strictly an IIS web page...
>> I have not been able to take a look at the new report builder, but I needed
>> some information urgently!
>[quoted text clipped - 7 lines]
>> thanks!
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200510/1

Reporint the last entry...

Hi,
OK, Im somewhat new to writing SQL queries and came across a problem that seems like it should be easy, but I cant figure out how to do this.
I have 2 tables with a 1 to many relationships.
The many table has a text field and a date field.
I want to report the last (most recent) row added to the 2nd table, by linking the two tables and somehow showing the last text field entered.
How can I do this?

ThanksYou can use a timestamp field in your 'many' table and use a MAX(ts_field) in your query, you will find more information on that topic in BOL.|||OK,
I must be missing something...
Here is the code for the 2nd table:

SELECT MAX(date), memo
FROM TABLE2
GROUP by memo

This does not work... how can I insure I select only the MAX date?

Thanks|||timestamp is an internal type that uses a code for each row, the higher ts mark is, the earlier insert/update was done.
So, add another field to your many table (ts_field timestamp) and perform some inserts/updates and use ts_field as any other field, lets say SELECT * FROM MANY ORDER BY TS_FIELD must show your records in updated/inserted order, ok?|||That gives me the records in order, however, I want on my 'report' only the most recent entry for each linked item between the two tables.
So if I have a quote number in table1 and 5 rows that have the same quote number in table2, I want in my join to create only once record-set that will include table1 values and the last record from table2.

Thanks|||try:

select value_field, max(date_field)
from many
where timestamp_field = (select max(timestamp_field)
from many m
where many.value_field = m.value_field)
group by value_field

note that max(date_field) has nothing to do with timestamp, is just like min(date_field) because the group by clause.

the trick is in the timestamp field.

later you can add the ONE table.|||Bad design=bad result. Why are you using 1:N when you need 1:1 ?
Use TR on {active table} to track changes to {audit table}.

/*
"OneTable" - "id" PK
"ManyTable" - "newid" PK,"id", "text", "date"
*/

select m."id",xx."date",xx."newid",m."text",o.*
from "OneTable" o
join "ManyTable" m on o."id"=m."id"
join
(
select m."id",x."date","newid"=max("newid")
from "ManyTable" m
join
(
select "id","date"=max("date")
from "ManyTable"
group by "id"
) x on m."id"=xx."id"
) xx on m."newid"=xx."newid"

This query is designed for more non-sorting columns than 1 ("text")
Not tested. Post creating query.|||OK,
Im thick today
I could not follow the advice.
Here is a part of the code Im trying to use. I have included the main table (QuoteMaster) with joins to two other tables (In reality there are several other joins all from the main table).
The QUOTE is a unique key in the main table and repeats in the QuoteNotes table.
The date field is a date/time stamp.
Im trying to get a single record-set for each quote that will have the latest MEMO field from the QuoteNotes table. There arent entries in the notes table for each quote, but they may repeat

---
Here is the code:

---
SELECT QuoteMaster.Quote, QuoteMaster.CustID,
QuoteNotes.Memo, QuoteNotes.Date,
[Product Group].PDescr
FROM QuoteMaster LEFT OUTER JOIN
[Product Group] ON
QuoteMaster.ProdGrp = [Product Group].ProdGrp LEFT OUTER
JOIN
QuoteNotes ON
QuoteMaster.Quote = QuoteNotes.Quote|||1. I wrote "Not tested. Post creating query." and you have posted nothing. I mean DDL script (CREATE TABLES, PK, FK).
I still do not know, what PK has your QuoteNotes. (Quote,"date"),(Quote,"date",Memo) are near candidate keys,
but can duplicities be there? Should I reverse engineer your design?

2. So I assume QuoteNotes PK(Quote,"date")

CREATE TABLE [Product Group] (ProdGrp int primary key,PDescr varchar(8000))
CREATE TABLE QuoteMaster(Quote int primary key,CustID int null,ProdGrp int null
,foreign key (ProdGrp) references [Product Group](ProdGrp))
CREATE TABLE QuoteNotes (Quote int not null,"date" datetime not null,Memo varchar(8000) not null
,primary key (Quote,"date"),foreign key (Quote) references QuoteMaster(Quote))

SELECT qm.Quote, qm.CustID, qn2.Memo, qn2."Date", pg.PDescr
from QuoteMaster qm
left join QuoteNotes qn2 on qm.Quote=qn2.Quote
left join
(
select qn1.Quote,"date"=max(qn1."date")
from QuoteNotes qn1
group by qn1.Quote
) X on qn2.Quote=X.Quote and qn2."date"=X."date"
left join [Product Group] pg on qm.ProdGrp = pg.ProdGrp