Displaying xml with xslt in RS

Hi,

I've got an xml and an xslt - I want to get that into reporting services. Right now I have a link to the xml file in a 'report', which will open it correctly and format with the xslt. I'd like it to display without going to an external link.

I know how to use an xml datasource, but I need the xslt applied, since it has some nice formatting in it - so i don't think that will work.

I'm trying to report on the results of a scripted ms baseline security analyzer of several servers - the style sheet lets you drill down and has links to the base reports.

Thanks for your help.

There is a way to apply xslt "automagically" in a report definition (check the Data Output tab of the Report Properties dialog) , although unfortunately you would probably have to rework your xslt a bit to use it. The reason is that the xml it is going to be applied to is the data XML of the report itself. You either need to define a report that will supply exactly the xml that you currently have as its data result, or you need to change the XSLT to fit the data xml you *will* get from some report.

The key point heres are "from some report" and "data output of a report". You can't just take any xslt result and display it within a ReportViewer interface. Is that what you are trying to do?

Note that it *is* possible to upload arbitrary files to Report Manager and then link to them, I think. But that's not the same thing as displaying those files within a reportviewer control interface.

>L<

|||

"Note that it *is* possible to upload arbitrary files to Report Manager and then link to them, I think. But that's not the same thing as displaying those files within a reportviewer control interface."

Yes, that sounds like it would be what I'm after - I really just need it there because all of our other reports are there, not because of any RS functionality. Now if I only knew the technical term for this to find it in the manual, I'd be set.

"You either need to define a report that will supply exactly the xml that you currently have as its data result"

Seems like this could work too - I'll have to look into it.

Thanks for your help Lisa.

|||

>>Yes, that sounds like it would be what I'm after - I really just need it there because all of our other reports are there, not because of any RS functionality. Now if I only knew the technical term for this to find it in the manual, I'd be set.

<rofl> Well I don't know the techical term but I can tell you that you upload the file using a button in the Report Manager that says... wait for it... Upload File. You give it a title, it becomes a browseable item in the Report manager interface (unless of course you mark it "hide in list view"), etc.

You can do it programmatically as well, you might want to look at this http://www.sqldbatips.com/showarticle.asp?ID=62 or you can probably use the SOAP API for this as well.

>L<

displaying xml using reporting service web service

I'm using VS2005 web developer. I can't display XML results when using the
render method, although it renders fine.
I'm using the SQL Server 2000 reporting services web service render method
to get a byte array like this:
result = rs.Render(reportPath, format, historyID, devInfo,
parameters, _
credentials, showHideToggle, encoding, mimeType,
reportHistoryParameters, warnings, streamIDs)
This is from the sample at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_service_lz_6x0z.asp
It works great if I assign HTML4.0 and EXCEL to the "format" variable.
Then I put
response.binarywrite(result)
and it is just fine.
When I try this with the XML format, it creates a file perfectly if I put
Dim stream As FileStream = File.Create("report.xml", result.Length)
but I don't know how to display the xml in a browser.
Response.binarywrite(result) and Response.writefile("report.xml") gives me
an error
The XML page cannot be displayed.
...
Cannot have a DOCTYPE declaration outside of a prolog
I guess because the XML is written inside the html.
I would appreciate any suggestions.
Thanks
BillCorrection - response.writefile(filename) works, but I don't want to have to
create files on the server and clean them up all the time.
Thanks
Bill
"bill" <belgie@.datamti.com> wrote in message
news:ubuEOCnMGHA.2124@.TK2MSFTNGP14.phx.gbl...
> I'm using VS2005 web developer. I can't display XML results when using
> the render method, although it renders fine.
> I'm using the SQL Server 2000 reporting services web service render method
> to get a byte array like this:
> result = rs.Render(reportPath, format, historyID, devInfo,
> parameters, _
> credentials, showHideToggle, encoding, mimeType,
> reportHistoryParameters, warnings, streamIDs)
> This is from the sample at
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_service_lz_6x0z.asp
> It works great if I assign HTML4.0 and EXCEL to the "format" variable.
> Then I put
> response.binarywrite(result)
> and it is just fine.
> When I try this with the XML format, it creates a file perfectly if I put
> Dim stream As FileStream = File.Create("report.xml", result.Length)
> but I don't know how to display the xml in a browser.
> Response.binarywrite(result) and Response.writefile("report.xml") gives
> me an error
> The XML page cannot be displayed.
> ...
> Cannot have a DOCTYPE declaration outside of a prolog
> I guess because the XML is written inside the html.
> I would appreciate any suggestions.
> Thanks
> Bill
>

Displaying XML Data

There is a requirement that I need to display the XML data stored in
database in one of the report. How can I display this data in Reporting
Services with the color schema similar to IE (elements and attributes in
different colors etc..)?
Thanks,
Live_Love_LaughThe displaying the XML data itself is not an issue. You can pass it to a
custom function (preferebaly located in an external assembly) and apply a XSL
transformation when the report is processed. What makes your task difficult
is the color-coding. Unfortunately, version 1.0 doesn't support HTML markers,
e.g. <b> for bold, <font> etc. To make the task even more difficult textboxes
are rendered as table cells and don't have ids which makes it difficult to
reference them by DHTML.
One thing you can try is exporting the report as XML and associating an XSL
stylesheet in the DataOutput properties which will render the report the way
you want it.
"Live_Love_Laugh" wrote:
> There is a requirement that I need to display the XML data stored in
> database in one of the report. How can I display this data in Reporting
> Services with the color schema similar to IE (elements and attributes in
> different colors etc..)?
> Thanks,
> Live_Love_Laugh
>
>|||I have the same requirement
Did either of you succeed?
If so can you tell me what you did or post an example?
Thanks in advance
"Teo Lachev" wrote:
> The displaying the XML data itself is not an issue. etc
> "Live_Love_Laugh" wrote:
> > There is a requirement that I need to display the XML data stored in
> > database in one of the report. How can I display this data in Reporting
> > Services with the color schema similar to IE (elements and attributes in
> > different colors etc..)?
> >
> > Thanks,
> > Live_Love_Laughsql

Displaying Windows Icons

Sir/Mam

I am creating a task manager functionality in c#.net 2003. I am checking only for open microsoft applications . If any microsoft application is opened ,it will display the application name for ex "Microsoft Word" in the listview control and its status "Running" . Now, I want to display the icon for Microsoft word or any other microsoft application, how should I display it in the ListView control.?

I have tried by taking it in the imagelist , because I have a class which returns the Windows Icons for these applications which are opened. But, I am unable to display it.

Please help me?

Thanks,
Shivanee.
You will want to post this in a .NET forum not a SQL Server forum.

Displaying Windows Icons

Sir/Mam

I am creating a task manager functionality in c#.net 2003. I am checking only for open microsoft applications . If any microsoft application is opened ,it will display the application name for ex "Microsoft Word" in the listview control and its status "Running" . Now, I want to display the icon for Microsoft word or any other microsoft application, how should I display it in the ListView control.?

I have tried by taking it in the imagelist , because I have a class which returns the Windows Icons for these applications which are opened. But, I am unable to display it.

Please help me?

Thanks,
Shivanee.
You will want to post this in a .NET forum not a SQL Server forum.

displaying vertical text in details section using sql reporting service

Nico,
U can change the "WritingMode" property of text box to set display
vertically.
I have another problem with the same, i want to display a text in the
details section spanning multiple rows.
below is example
S_NO NAME VERTICAL_TEXT
_______________________________
1 ABC S
2 CDE A
3 FGH M
4 IJK P
L
E
Appreciate any help!!..
VenkatCan anyone help on this?..
venkat.oar@.gmail.com wrote:
> Nico,
> U can change the "WritingMode" property of text box to set display
> vertically.
> I have another problem with the same, i want to display a text in the
> details section spanning multiple rows.
> below is example
> S_NO NAME VERTICAL_TEXT
> _______________________________
> 1 ABC S
> 2 CDE A
> 3 FGH M
> 4 IJK P
> L
> E
> Appreciate any help!!..
> Venkat

Displaying various Time Intelligence calculations at the same time

I created a new calculation for a measure to display Year over Year growth % using the business intelligence wizard which created new calculated members in my time dimension. How can I display the new calculation and the original value at the same time without these two options being columns or rows? For example:

2005

Sales Sales Year over Year Growth %

Product A $23 1%

Product B $45 15%

I have tried to do this using perspectives but these are not included in the 'Standard Edition' SKU. Thanks in advance.

Here is an example that should help you (from Foodmart 2000)

with
member [Measures].[Prev Sales] as '([Measures].[Unit Sales],[Time].currentmember.lag(1))'
member [Measures].[% Increase] as 'iif([Measures].[Prev Sales]<>0,100*([Measures].[Unit Sales]-[Measures].[Prev Sales]) / [Measures].[Prev Sales],0)'

select crossjoin({[Time].[1997].[Q3],[Time].[1997].[Q4]},{[Measures].[Unit Sales],[Measures].[% Increase]}) on columns,
{[Product].[Product Name].members} on rows
from sales

Hope this helps,

Santi

Displaying various instances of the same report in one report

Here's my problem. I have a report that displays information for one
office. The users are now asking for a master report where they can
display all of the offices in their group. I'm thinking I can do this
with a sub report but I don't know enough about subreports to set it
the passing of multiple report parameters and the sql server book's
how to is a piece of crap.
Where else can I find some examples of how to set this up?
Is there a better way to do this?
Thanks in advance for the help and if you need something cleared up let
me know.
MathiasHi, Mathias
from what I understand, it seems like you are gonna have the following
scenario:
create a report that will query for all offices, and then group by office
group categoriy.
If so, you can create you query (sproc or adhoc) to take in the office group
identifier/name as a parameter, and filter with a Where clause in the query.
The report rdl will have a parameter for the office group id/name which you
pass into that query, and will retrieve a list of all offices for that
particular group.
On the item that displays, say, the name of the office, you can go to the
textbox properties window and setup a hyperlink to another report (your
existing one), passing the appropriate office id/name, which your current
office report will use
to retrieve detailed information on.
Another way to go about this: in you "master report", drop a sub report
element, and in the properties, set the report rdl of the original report,
and for parameters, pass in the field from the master report containing the
office id into the sub-report (your current report).
so you will have the following layout:
user enters report parameter Office Group: <value>;
query executes filtered to that office group, and returns a list of office
name/ids;
rdl renders the list of office names returned by the query in a table layout;
within table, the detail row will contain a cell with a subreport pointing
to your original "office info" rdl, and passing the office id field to the
office id parameter of the subreport:
<begin table>
<begindetailrow>
pass Fields!officeID.value =>subreport (current report)
parameter Parameters!officeid.value
<enddetailrow>
<endtable>
Note, you could modify your original query so that your grouping and logic
is done on the query side in one stored procedure, and avoid having to deal
w/ subreports, by joining the appropriate tables and building your result set
in the query w/ all of the office details there. This reduces some of the
overhead on the report server having to render subreports.
hope this helps you out.
--
Regards,
Thiago Silva
"Mathias" wrote:
> Here's my problem. I have a report that displays information for one
> office. The users are now asking for a master report where they can
> display all of the offices in their group. I'm thinking I can do this
> with a sub report but I don't know enough about subreports to set it
> the passing of multiple report parameters and the sql server book's
> how to is a piece of crap.
> Where else can I find some examples of how to set this up?
> Is there a better way to do this?
> Thanks in advance for the help and if you need something cleared up let
> me know.
> Mathias
>|||Thiago Silva
Thank you very much for taking the time to respond. I think i'm going
to have to go with the second option and use the subreport. my users
want to be able to see the various reports all at one time. I set up
the query to bring back the office id's in their respective groups
however when I pass that id to the subreport only the first offices'
report is generated.
for example there are 10 offices comming back it will only display the
first office. How do I tell the subreport to move on to the next
offices?
I tried using the value straight from the query by doing this
=Fields!ReportingOfficeID.Value which did not work.
I also tried placing the value returened from the query into a report
parameter this also did not work. I tried with the multi- value box
selected and with the box not selected.
where am I going wrong?
on a side note that I should have mentioned to start with I am using
Reporting server 2005.
thanks once again for all of the help. And If you need me to clear
something up let me know.
Mathias|||Mathias,
could you provide a sample of the data that you're using for the report, and
how you want the report layout to be? I am trying to understand exactly what
you want versus what you're getting right now.
--
Regards,
Thiago Silva
"Mathias" wrote:
> Thiago Silva
> Thank you very much for taking the time to respond. I think i'm going
> to have to go with the second option and use the subreport. my users
> want to be able to see the various reports all at one time. I set up
> the query to bring back the office id's in their respective groups
> however when I pass that id to the subreport only the first offices'
> report is generated.
> for example there are 10 offices comming back it will only display the
> first office. How do I tell the subreport to move on to the next
> offices?
> I tried using the value straight from the query by doing this
> =Fields!ReportingOfficeID.Value which did not work.
> I also tried placing the value returened from the query into a report
> parameter this also did not work. I tried with the multi- value box
> selected and with the box not selected.
> where am I going wrong?
> on a side note that I should have mentioned to start with I am using
> Reporting server 2005.
> thanks once again for all of the help. And If you need me to clear
> something up let me know.
> Mathias
>|||Thiago Silva
I figured out how to solve my problem. I ened up following the steps
laid out in this forum post.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=299810&SiteID=1
what I did was place the subreport in a list and then pass the list of
10-15 office id's to the list / subreport. this allowed the master
report to display all 10-15 different subreports.
Thank you very much for all of you help with this.
Mathiassql

Displaying variable values after execution

Hello,

I have just developed my first full package and it has been, ahem, an adventure- but I can see the power of SSIS. I am splitting 1M rows in to up to 11 parts (therefore up to 11M rows) for several files and it takes a matter of seconds!

I have used some variables in the package and would like to see the results of these at the end of execution. They are purely for interest at the moment.

I think I can output them to a flat file, but is it possible to output them to the Immediate window at the end of execution?

I can not figure out how to have a watch on them either- is this possible? Ideally I would like a counter on screen next to my loop containers.

All of the web pages I have seen regarding debugging seem to assume that VB is being used to create the package.

Thanks,

Alan.

I'm not sure how the output the variables to the Immediate window. You could log them, or output them to a flat file, as you suggested.

You can put a watch on them. To do this, set a breakpoint on a task (right-click on it). Pick on towards the end of your packageif you want to see the values at the end. When you run the package and it hits the breakpoint, you can type the variable name into the watch window.

|||You could also use a script component to issue a MsgBox, but that would only be handy when debugging.

Displaying variable values after execution

Hello,

I have just developed my first full package and it has been, ahem, an adventure- but I can see the power of SSIS. I am splitting 1M rows in to up to 11 parts (therefore up to 11M rows) for several files and it takes a matter of seconds!

I have used some variables in the package and would like to see the results of these at the end of execution. They are purely for interest at the moment.

I think I can output them to a flat file, but is it possible to output them to the Immediate window at the end of execution?

I can not figure out how to have a watch on them either- is this possible? Ideally I would like a counter on screen next to my loop containers.

All of the web pages I have seen regarding debugging seem to assume that VB is being used to create the package.

Thanks,

Alan.

I'm not sure how the output the variables to the Immediate window. You could log them, or output them to a flat file, as you suggested.

You can put a watch on them. To do this, set a breakpoint on a task (right-click on it). Pick on towards the end of your packageif you want to see the values at the end. When you run the package and it hits the breakpoint, you can type the variable name into the watch window.

|||You could also use a script component to issue a MsgBox, but that would only be handy when debugging.

Displaying values of a textbox

Use
textbox2.value: =ReportItems!textbox1.value
"Sanjeev" <sanjeev@.microsoft.com> wrote in message
news:<uafhla3qEHA.3700@.TK2MSFTNGP15.phx.gbl>...
> Hi,
>
> I want to display the value of one textbox in another textbox.
> Does anyone know the syntax for this expression.
> Both Textboxes are in a matrix which hopefully does not complicate
matters.
>
> T.I.A.
> Sanjeev
>
>
>
>
>Thanks. That works perfectly for textboxes that are rows or columns of the
matrix
Now i have another problem.
What if the textbox i wish to refer to is the actual subtotal textbox of the
matrix. The syntax below does not work.
Is there any syntax for this type of situation.
This is the error i get : "Report item expressions can only refer to other
report items within the same grouping scope or a containing grouping scope.
"
"Ponnurangam" <ponnurangam@.trellisys.net> wrote in message
news:%23CNBCj3qEHA.3848@.TK2MSFTNGP14.phx.gbl...
> Use
> textbox2.value: =ReportItems!textbox1.value
> "Sanjeev" <sanjeev@.microsoft.com> wrote in message
> news:<uafhla3qEHA.3700@.TK2MSFTNGP15.phx.gbl>...
>> Hi,
>> I want to display the value of one textbox in another textbox.
>> Does anyone know the syntax for this expression.
>> Both Textboxes are in a matrix which hopefully does not complicate
> matters.
>> T.I.A.
>> Sanjeev
>>
>>
>|||I haven't played with this, but perhaps you could look into the more
extensive functionality of reporting services by using the Code Pane or even
a custom assembly
"Sanjeev" <sanjeev@.microsoft.com> wrote in message
news:u%23hX493qEHA.2588@.TK2MSFTNGP12.phx.gbl...
> Thanks. That works perfectly for textboxes that are rows or columns of the
> matrix
> Now i have another problem.
> What if the textbox i wish to refer to is the actual subtotal textbox of
the
> matrix. The syntax below does not work.
> Is there any syntax for this type of situation.
> This is the error i get : "Report item expressions can only refer to other
> report items within the same grouping scope or a containing grouping
scope.
> "
> "Ponnurangam" <ponnurangam@.trellisys.net> wrote in message
> news:%23CNBCj3qEHA.3848@.TK2MSFTNGP14.phx.gbl...
> > Use
> >
> > textbox2.value: =ReportItems!textbox1.value
> >
> > "Sanjeev" <sanjeev@.microsoft.com> wrote in message
> > news:<uafhla3qEHA.3700@.TK2MSFTNGP15.phx.gbl>...
> >
> >> Hi,
> >
> >>
> >
> >> I want to display the value of one textbox in another textbox.
> >
> >> Does anyone know the syntax for this expression.
> >
> >> Both Textboxes are in a matrix which hopefully does not complicate
> > matters.
> >
> >>
> >
> >> T.I.A.
> >
> >> Sanjeev
> >
> >>
> >
> >>
> >
> >>
> >
> >>
> >
> >>
> >
> >
>

Displaying values in list from Left-Right instead of Right-Left

Hi,

I want to display values in a listbox from left to right instead of top to bottom.

Instead of displaying values like this

D001

D002

D003

D004

..

..

..

I should display

D001 D002 D003 D004 D005 D001 D002 D003 D004 D005 D001 D002 D003 D004 D005

D001 D002 D003 D004 D005 D001 D002 D003 D004 D005 D001 D002 D003 D004 D005

D001 D002 D003 D004 D005 D001 D002 D003

I also should restrict the length of the listbox. Maximum of 20 values only should displayed per row.

Is there any way to acheive this ? Either using Table or Matrix dataregion ? Or concatinating string in a textbox ?

Regards,

I have done something similar for a mailing label report. i had to create a list box for each section LTR then when my data is returned i determine which list to put it into based off the record number.

Example: My report has 3 list controls placed next to each other.

Record Number Data

1 Blah 1

2 Blah 2

3 Blah 3

4 Blah 4

5 Blah 5

... ...

Record number 1 display in Fist list control, record number 2 displays in second list control, record number 3 displays in third list control, record number 4 display as second record in first list control, record number 5 displays as second record in second list control, and etc.

Eample Filter on List Control: =Code.SetColumn(CLng(Fields!ColumnFilter.Value),3) = 2

SetColumn Code:

Shared Function SetColumn(ByVal row As Integer, ByVal ColumnCount As Integer) As String
Dim RetVal As Integer = row

While RetVal > ColumnCount
If ColumnCount >= RetVal Then Exit While
RetVal = RetVal - ColumnCount
End While

Return RetVal
End Function

Also to return the sequential number with your data set look into SQL 2005 Ranking functions.

|||

Hi,

I was able to acheive this as follows -

1. Add a Matrix control. Add required field in column grouping of matrix. (Dynamic columns)
2. Add a ListBox control. Include Matrix inside the Listbox.
3. Edit details of Listbox to add a group expression =RowNumber(Nothing) / 15. (15 is number of columns to be displayed)
4. Add a Matrix column group expression as your Listbox group expression.
=RowNumber("list1_Details_Group"). Now your matrix should contain 2 group expressions. (1 for Phone # and other for controling no. of columns).

Regards,
Chiro

|||

I dont understand how you can have the expression in step 3 -

=RowNumber(Nothing) / 15

|||I think the technique is similar to the HorizontalTables example in Chric Hays Sleazy Hacks Blog. There's a working example you can download from http://blogs.msdn.com/chrishays/archive/2004/07/23/HorizontalTables.aspx

Displaying values from Informix Stored Procedure

I am not able to see the values returned from my stored procedure in the
report preview, but I am able to see them in the Data window. I made sure
that I went in and mapped the fields for each dataset that I am using in the
report, but this still does not work. Any ideas?
TIA,
JeremyAnyone?
"Chancetribe" wrote:
> I am not able to see the values returned from my stored procedure in the
> report preview, but I am able to see them in the Data window. I made sure
> that I went in and mapped the fields for each dataset that I am using in the
> report, but this still does not work. Any ideas?
> TIA,
> Jeremysql

Displaying value of a variable during runtime

Greetings all,

Apologies if this question has been asked in the past but how I display the valuw of a variable during runtime?

Thanks for your help in advance.

Running in debugmode; you can set a break point and when the excution breaks; you can go to the locals window (Ctrl + Alt + V, L)to see the value of the variables at that point|||

Dear Rafael, I am fallin in love with you LOL

|||

dreameR.78 wrote:

Dear Rafael, I am fallin in love with you LOL

Glad you got it!

Displaying value from another dimension

Hi.
I'm relatively new to MDX and have what should be a simple problem to
solve.
I have a Hotel dimension and a hierarchial Geography
(Region/Market/SubMarket) dimension. There's a join between the two on
Geography_ID.
All I'm trying to do is display a few measures for the Hotel as well as
its Market value. I can't get it to display the Market.
SELECT
{[Measures].[CountUserReview], NonEmptyCrossJoin({[Hotel]},
{[Geography]})} ON COLUMNS,
{TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].[CountUserReview],
BDESC), 10, [Measures].[CountUserReview])} ON ROWS
FROM [UserReview]
I've tried several different things with the NonEmptyCrossJoin (among
others), please help.
A dimension can only appear on one axis, so I suspect your sample is
giving you a syntax error.
The following should give you the top 10 hotels in the rows, and the
markets in the columns, with the CountUserReview in each cell. Is this
the sort of result you are after? If not it would help to have a sample
of how you would like the output to appear.
SELECT
NON EMPTY {[Geography].Market.Members} ON COLUMNS,
{TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].[CountUserReview],
BDESC), 10, [Measures].[CountUserReview])} ON ROWS
FROM [UserReview]
WHERE
(
[Measures].[CountUserReview]
)
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <1129766281.949669.289920@.g43g2000cwa.googlegroups .com>,
query_squidier@.speakeasy.net says...
> Hi.
> I'm relatively new to MDX and have what should be a simple problem to
> solve.
> I have a Hotel dimension and a hierarchial Geography
> (Region/Market/SubMarket) dimension. There's a join between the two on
> Geography_ID.
> All I'm trying to do is display a few measures for the Hotel as well as
> its Market value. I can't get it to display the Market.
> SELECT
> {[Measures].[CountUserReview], NonEmptyCrossJoin({[Hotel]},
> {[Geography]})} ON COLUMNS,
> {TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].[CountUserReview],
> BDESC), 10, [Measures].[CountUserReview])} ON ROWS
> FROM [UserReview]
>
> I've tried several different things with the NonEmptyCrossJoin (among
> others), please help.
>
|||Thanks for the response Darren and yes I was getting an error.
Each Hotel has only one Market value.
I'm hoping to display each Hotel name ON ROWS and the following ON
COLUMNS:
HotelID, HotelName, Market, and Count.
The trouble I'm having is figuring out how to display stuff from the
Hotel dimension and the Geography dimension both ON COLUMNS.
Thanks for your help, this is very very different than T-SQL.
|||This is my latest attempt:
WITH
MEMBER [Measures].[Hotel_ID] AS
'[Hotel].CurrentMember.Properties("Hotel_ID")'
MEMBER [Measures].[HotelName] AS
'[Hotel].CurrentMember.Properties("HotelName")'
--MEMBER [Measures].[Market] AS '[Geography].[Market].CurrentMember'
--SET [Market] AS '[Geography].[Market].MEMBERS'
--MEMBER [Measures].[Market] AS '[Market].CurrentMember'
SELECT
{[Measures].[Hotel_ID], [Measures].[HotelName],
[Measures].[CountUserReview], [Geography].[Market].CurrentMember} ON
COLUMNS,
{TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].[CountUserReview],
BDESC), 10, [Measures].[CountUserReview])} ON ROWS
FROM [UserReview]
WHERE ([ReviewStatus].[APPROVE])
|||The problem is that your geography dimension needs to be given a context
by being placed on one of the axis or in the slicer (where clause)
The other thing you were doing was mixing members of different
dimensionality in a set on the columns.
In order to get the market for each hotel I have crossjoined the
geography dimension (using the * operator). This will generate every
market for every hotel and I then exclude the empty ones with the NON
EMPTY clause.
Note: I would have thought that the HotelName and Market measures were
redundant as these names should appear in the rows of the returned
cellset. As such I have commented them out of the query below, if the
names of your dimension members are different you can remove the comment
dashes to include these measures again.
[vbcol=seagreen]
WITH
MEMBER [Measures].[Hotel_ID] AS
'[Hotel].CurrentMember.Properties("Hotel_ID")'
MEMBER [Measures].[HotelName] AS
'[Hotel].CurrentMember.Properties("HotelName")'
MEMBER [Measures].[Market] AS '[Geography].[Market].CurrentMember.Name'
SELECT
{[Measures].[Hotel_ID]
--, [Measures].[HotelName]
, [Measures].[CountUserReview]
--, [Measures].[Market]
} ON COLUMNS,
NON EMPTY {TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].
[CountUserReview],
BDESC), 10, [Measures].[CountUserReview])} * {[Geography].
[Market].Members} ON ROWS
FROM [UserReview]
WHERE ([ReviewStatus].[APPROVE])[vbcol=seagreen]
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
|||Thank you very much, Darren. This helps bunches.
The only thing, though, I'm getting a syntax error near axis
definition: " * {[Geography]. [Market].Members}".
Thoughts?
|||Got it.
WITH
MEMBER [Measures].[Hotel_ID] AS
'[Hotel].CurrentMember.Properties("Hotel_ID")'
MEMBER [Measures].[HotelName] AS
'[Hotel].CurrentMember.Properties("HotelName")'
SELECT
{[Measures].[Hotel_ID], [Measures].[HotelName],
[Measures].[CountUserReview]} ON COLUMNS,
{ NonEmptyCrossJoin ({TOPCOUNT(ORDER([Hotel].[Name].MEMBERS,
[Measures]. [CountUserReview], BDESC), 10,
[Measures].[CountUserReview])}, {[Geography].[Market].Members})} ON
ROWS
FROM [UserReview]
WHERE ([ReviewStatus].[APPROVE])
The only other question I might have is if I did want to show the
Market value ON COLUMNS, how to do that. It didn't recognize
[Geography].[Market].CurrentMember.Name and [Geography].[Market].Name
just returns "Market".
Thanks so much, Darren, I've got lightbulbs going off all over.
|||> Thanks so much, Darren, I've got lightbulbs going off all over.
That's great to hear, glad I could help.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Displaying value from another dimension

Hi.
I'm relatively new to MDX and have what should be a simple problem to
solve.
I have a Hotel dimension and a hierarchial Geography
(Region/Market/SubMarket) dimension. There's a join between the two on
Geography_ID.
All I'm trying to do is display a few measures for the Hotel as well as
its Market value. I can't get it to display the Market.
SELECT
{[Measures].[CountUserReview], NonEmptyCrossJoin({[Hot
el]},
{[Geography]})} ON COLUMNS,
{TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].[Cou
ntUserReview],
BDESC), 10, [Measures].[CountUserReview])} ON ROWS
FROM [UserReview]
I've tried several different things with the NonEmptyCrossJoin (among
others), please help. A dimension can only appear on one axis, so I suspect your sample is
giving you a syntax error.
The following should give you the top 10 hotels in the rows, and the
markets in the columns, with the CountUserReview in each cell. Is this
the sort of result you are after? If not it would help to have a sample
of how you would like the output to appear.
SELECT
NON EMPTY {[Geography].Market.Members} ON COLUMNS,
{TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].[Cou
ntUserReview],
BDESC), 10, [Measures].[CountUserReview])} ON ROWS
FROM [UserReview]
WHERE
(
[Measures].[CountUserReview]
)
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell
In article <1129766281.949669.289920@.g43g2000cwa.googlegroups.com>,
query_squidier@.speakeasy.net says...
> Hi.
> I'm relatively new to MDX and have what should be a simple problem to
> solve.
> I have a Hotel dimension and a hierarchial Geography
> (Region/Market/SubMarket) dimension. There's a join between the two on
> Geography_ID.
> All I'm trying to do is display a few measures for the Hotel as well as
> its Market value. I can't get it to display the Market.
> SELECT
> {[Measures].[CountUserReview], NonEmptyCrossJoin({[H
otel]},
> {[Geography]})} ON COLUMNS,
> {TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].[C
ountUserReview],
> BDESC), 10, [Measures].[CountUserReview])} ON ROWS
> FROM [UserReview]
>
> I've tried several different things with the NonEmptyCrossJoin (among
> others), please help.
>|||Thanks for the response Darren and yes I was getting an error.
Each Hotel has only one Market value.
I'm hoping to display each Hotel name ON ROWS and the following ON
COLUMNS:
HotelID, HotelName, Market, and Count.
The trouble I'm having is figuring out how to display stuff from the
Hotel dimension and the Geography dimension both ON COLUMNS.
Thanks for your help, this is very very different than T-SQL. |||This is my latest attempt:
WITH
MEMBER [Measures].[Hotel_ID] AS
'[Hotel].CurrentMember.Properties("Hotel_ID")'
MEMBER [Measures].[HotelName] AS
'[Hotel].CurrentMember.Properties("HotelName")'
--MEMBER [Measures].[Market] AS '[Geography].[Market].Curren
tMember'
--SET [Market] AS '[Geography].[Market].MEMBERS'
--MEMBER [Measures].[Market] AS '[Market].CurrentMember'
SELECT
{[Measures].[Hotel_ID], [Measures].[HotelName],
[Measures].[CountUserReview], [Geography].[Market].CurrentMe
mber} ON
COLUMNS,
{TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures].[Cou
ntUserReview],
BDESC), 10, [Measures].[CountUserReview])} ON ROWS
FROM [UserReview]
WHERE ([ReviewStatus].[APPROVE])|||The problem is that your geography dimension needs to be given a context
by being placed on one of the axis or in the slicer (where clause)
The other thing you were doing was mixing members of different
dimensionality in a set on the columns.
In order to get the market for each hotel I have crossjoined the
geography dimension (using the * operator). This will generate every
market for every hotel and I then exclude the empty ones with the NON
EMPTY clause.
Note: I would have thought that the HotelName and Market measures were
redundant as these names should appear in the rows of the returned
cellset. As such I have commented them out of the query below, if the
names of your dimension members are different you can remove the comment
dashes to include these measures again.
[vbcol=seagreen]
WITH
MEMBER [Measures].[Hotel_ID] AS
'[Hotel].CurrentMember.Properties("Hotel_ID")'
MEMBER [Measures].[HotelName] AS
'[Hotel].CurrentMember.Properties("HotelName")'
MEMBER [Measures].[Market] AS '[Geography].[Market].CurrentM
ember.Name'
SELECT
{[Measures].[Hotel_ID]
--, [Measures].[HotelName]
, [Measures].[CountUserReview]
--, [Measures].[Market]
} ON COLUMNS,
NON EMPTY {TOPCOUNT(ORDER([Hotel].[Name].MEMBERS, [Measures
].
[CountUserReview],
BDESC), 10, [Measures].[CountUserReview])} * {[Geography].
[Market].Members} ON ROWS
FROM [UserReview]
WHERE ([ReviewStatus].[APPROVE])[vbcol=seagreen]
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell|||Thank you very much, Darren. This helps bunches.
The only thing, though, I'm getting a syntax error near axis
definition: " * {[Geography]. [Market].Members}".
Thoughts?|||Got it.
WITH
MEMBER [Measures].[Hotel_ID] AS
'[Hotel].CurrentMember.Properties("Hotel_ID")'
MEMBER [Measures].[HotelName] AS
'[Hotel].CurrentMember.Properties("HotelName")'
SELECT
{[Measures].[Hotel_ID], [Measures].[HotelName],
[Measures].[CountUserReview]} ON COLUMNS,
{ NonEmptyCrossJoin ({TOPCOUNT(ORDER([Hotel].[Name].MEMBER
S,
[Measures]. [CountUserReview], BDESC), 10,
[Measures].[CountUserReview])}, {[Geography].[Market].M
embers})} ON
ROWS
FROM [UserReview]
WHERE ([ReviewStatus].[APPROVE])
The only other question I might have is if I did want to show the
Market value ON COLUMNS, how to do that. It didn't recognize
[Geography].[Market].CurrentMember.Name and [Geography].[Mar
ket].Name
just returns "Market".
Thanks so much, Darren, I've got lightbulbs going off all over.|||> Thanks so much, Darren, I've got lightbulbs going off all over.
That's great to hear, glad I could help.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Displaying used space in Q.A.

I would like to get information about percentage used space for data files and log files, like I can display it in Enterprise Mgr's TaskPad, through Query Analyzer. The 'sysfiles' system table only contains allocated space, not the % used.
Does anyone know if this info is available this way?To my knowledge the % of space used is not stored in any tables. As I am sure you know DBCC SQLPERF(LOGSPACE) will give you the answer BUT for ALL logs. You could always roll your own custom answer:

Code:
---------------------------------------
create table #Tmp(DB varchar(255), LogSize varchar(25), SpaceUSed varchar(25), Status tinyint)
declare @.DBLen int, @.LogSizeLen int, @.SpaceUsedLen int, @.StatusLen int, @.TSQL varchar(255)
insert into #Tmp exec('DBCC SQLPERF(LOGSPACE)')
select @.DBLen = max(datalength(DB)), @.LogSizeLen = max(datalength(LogSize)), @.SpaceUSedLen = max(datalength(SpaceUSed)), @.StatusLen = max(datalength(Status)) From #Tmp where DB = db_name()
set @.TSQL = 'select ' +
'cast(DB as varchar( ' + cast(@.DBLen as varchar(12)) + ')) as ''Database Name'', ' +
'cast(LogSize as varchar( ' + cast(@.LogSizeLen as varchar(12)) + ')) as ''Log Size (MB)'', ' +
'cast(SpaceUsed as varchar( ' + cast(@.SpaceUsedLen as varchar(12)) + ')) as ''Log Space Used (%)'', ' +
'cast(Status as varchar( ' + cast(@.StatusLen as varchar(12)) + ')) as ''Status'' ' +
'From #Tmp where DB = db_name()'
raiserror('',0,1)
exec(@.TSQL)
drop table #Tmp
---------------------------------------|||Off topic, but you can use the 'code' tag when posting code i.e

This is my code
with some indentation
and other stuff ;)
so the format
still looks nice :D|||Thank you very much. I was browsing through BOL yesterday but I missed DBCC SQLPERF for some reason.

I guess I can get the same info for database files through sp_spaceused, which I now stumbled over in BOL.
I tested running DBCC UPDATEUSAGE on some databases, which gave some corrections. Are these "problems" in sysindexes never corrected unless I explicitly run a DBCC UPDATEUSAGE manually or in a maintenance plan?

Sigh... when the guys you've outsourced the operation & maintenance to can't supervise things, you've got to do it yourself ;-)|||I run a maintaince plan every Saturday and Wednesday to re-calculate statistics, rebuild indexes, and force a recompile of all stored procedures. I am lucky to have a window twice a week where I can do these things and IMHO the 2 hours it takes to do this is time well spent.

Here is another tip. If you find something in EM that you like but don't know how it works try running the profiler and watch the commands issued.|||About sp_spaceusage, which is the most important detail to keep an eye on of Unallocated Space and Unused Space?

Further, for one database I got a negative value for Unallocated Space. What does this mean?

Displaying Updated Columns

I want to use a gridview in my asp.net app to just show changes to one field in an employee table. I have a history table trigger on all fields already for other reasons, however for this purpose i want to be able to show the user what the column said before and after the change.

I read up on the columns_updated function and it seems like it could work however It seems there would be an easier way than figuring out what the bitmask is on my 14 column table for a change on the 3rd column?

Any ideas.Okay I think I already solved this using a stored procedure joining my employee table and employee history table.sql

Displaying traffic lighting images using MDX query

Hi All,

I want to display traffic light images in jpivot table depending upon the measures . I can be able to display color in the jpivot table. How to display images?

Thanks in advance.

Hi Rajha,

you have to create a KPI in your cube.

Edit your cube in BIDS and then go to KPI's tab. Create a new KPI and complete the form with MDX script you need for Value, Goal, Status and Trend. There you can also set Status indicator e Trend indicator (traffic light, gauge, standard arrow and so on).

|||

Hi Francesco,

Thank you very much for your help. I'm a newbie to mdx. Actually I am using some open source database and cube designer which doesnt have KPI property. Can I manually add that code to my mdx query? If so, can u tell me where I can found related documents.

|||

Hi Rajha,

I really don't know if there's a way to obtain the same in MDX but I'm thinking not.

What you can easily do is to color cell depending on the value in it like this:

WITH MEMBER MEASURES.DEMO AS

'[Measures].[Internet Sales Amount]' ,

FORE_COLOR = 'IIF ( MEASURES.DEMO < [Measures].[Reseller Sales Amount] * 0.7,

RGB(0,255,0),

RGB(255,0,0)

)'

SELECT

{MEASURES.DEMO} ON COLUMNS,

NONEMPTYCROSSJOIN([Date].[Calendar].[Month].Members,[Department].[Departments].[Department Level 02].MEMBERS) ON ROWS

FROM [Adventure Works]

CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR

At the moment I'm not able to find documentation about this but you can check on the web

Let me know if it's what you're looking for and please check my answer if you think if it was helpful anyway.

P.S. what product are you using that supports MDX?

Francesco

|||

Hi Francesco

Thanks for your reply. As I said in my first thread I can be able to show color depending upon the conditions but i cant bring images in the table.

I'm evaluating Pentaho which uses mdx for drill down reports.

Displaying Totals in matrix-control based upon a RunningValue-expression

Hi,
I have a Matrix-control which has to "summarize" a lot of data in the end,
based upon boolean values. (the column-grouping is a static group).
Customer1
Test1 0 1 0 Value 1
Test 1.1 Value 2
Test 1.2 Value 3
Test 2 1 0 1 Value 4
Test 2.1 Value 5
Test 3 0 1 0 Value 6
______________________________________
Totals: 1 2 1
Customer 2
Test 1 ...
The values are bool-values AND not always displayed. I want to summarize
the values as they appear on the screen, not how they are in the dataset.
Because, now the runningValues-function always summarizes to much, which is
also al the "invisible" data between the main groups. I have written
something to display 0 in the subgroups, but it doesn't change my result.
Is it possible to do this and, if so, how?
Thanks a lot for your answers!
IveAre you giving a Scope'
"Ive" wrote:
> Hi,
> I have a Matrix-control which has to "summarize" a lot of data in the end,
> based upon boolean values. (the column-grouping is a static group).
> Customer1
> Test1 0 1 0 Value 1
> Test 1.1 Value 2
> Test 1.2 Value 3
> Test 2 1 0 1 Value 4
> Test 2.1 Value 5
> Test 3 0 1 0 Value 6
> ______________________________________
> Totals: 1 2 1
> Customer 2
> Test 1 ...
> The values are bool-values AND not always displayed. I want to summarize
> the values as they appear on the screen, not how they are in the dataset.
> Because, now the runningValues-function always summarizes to much, which is
> also al the "invisible" data between the main groups. I have written
> something to display 0 in the subgroups, but it doesn't change my result.
> Is it possible to do this and, if so, how?
> Thanks a lot for your answers!
> Ive
>
>|||Yes,
I am!
Problem is that the Scope doesn't seem to work correctly: it refers to the
dataset in stead of the matrix itself.
I have tried something like this:
=RunningValue(Abs(cInt(Fields!PlannedChecked.Value)),Sum,"matrix1_SubGroup")
I again refer to the fact that Fields!PlannedChecked.Value is a boolean
value and that he doesn't seem to summarize the values as in the control,
but in the dataset. He doesn't summarize correctly.
The runningValue function was inserted with an IIF -statement in combination
with the InScope-function to determine whether I'm actually in a
summarize-column.
A count would also do the job, but how to do that if I can't put a
limitation on the scope?
A dSum would do the trick, but that isn't supported.
I'm really stuck on this one, but as long as someone hasn't officially told
me how the RunningValue-function works exactly in a matrix-control, I keep
searching for a solution and reason.
Regards,
Ive
"Soan" <Soan@.discussions.microsoft.com> wrote in message
news:EF2FBEA3-C751-4C63-84C5-C319251D5F7C@.microsoft.com...
> Are you giving a Scope'
> "Ive" wrote:
>> Hi,
>> I have a Matrix-control which has to "summarize" a lot of data in the
>> end,
>> based upon boolean values. (the column-grouping is a static group).
>> Customer1
>> Test1 0 1 0 Value 1
>> Test 1.1 Value 2
>> Test 1.2 Value 3
>> Test 2 1 0 1 Value 4
>> Test 2.1 Value 5
>> Test 3 0 1 0 Value 6
>> ______________________________________
>> Totals: 1 2 1
>> Customer 2
>> Test 1 ...
>> The values are bool-values AND not always displayed. I want to summarize
>> the values as they appear on the screen, not how they are in the dataset.
>> Because, now the runningValues-function always summarizes to much, which
>> is
>> also al the "invisible" data between the main groups. I have written
>> something to display 0 in the subgroups, but it doesn't change my result.
>> Is it possible to do this and, if so, how?
>> Thanks a lot for your answers!
>> Ive
>>