Showing posts with label application. Show all posts
Showing posts with label application. Show all posts

Thursday, March 29, 2012

Distributed data synchronization - Comparing timestamp

Hello,

I am designing a distributed application where a central SQL Server 2005 database will need to be synchronized with remote SQLExpress databases via a WebService. Data can be edited at the cental db (by our connected applications) or on the local SQLExpress dbs running on the users machines (by this disconnected application).

Now, how can I use the timestamp column to determine the most recent update. The most recent update to me is not the user that most recently invoked the syncing WebService, but the most recent time when the change was made to the data locally vs the change time at the central server. Because a user could make a change on his laptop on Monday but not synchronize it till Friday. I dont want the Mon data to overwrite data of Tues-Fri being overwritten simply based on assumption that a late sync is infact the most recent change.

The initial data will be downloaded to the local SQLExpress db via the WebService so the timestamp data in the table will come along with it.

When a user modifies data in this disconnected SQLExpress db, can that be compared to modifications on the central db using timestamps to determine if the users data being synced is older or newer than the data on the server? I understand timestamps are incremental values, but are they still sensitive to the users timezone since the central server will be a diff timezone then the user.

Timestamps are instance-level unique numbers. So you can't really compare those across instances. Why don't you use merge replication for instance? It is designed to solve problems like these. If you assistance on the replicaiton part you should post in the replication forum.|||

For ISVs that are building installable components, replication isn't too attractive. The components are meant to be quickly deployable whereas replication requires configuration to be done to create the publishers, distributers, etc.

So, basically, I would have to write a home-grown solution for conflict resolution?

Does anyone know how Microsoft is handling conflict resolution for the remote synchronization in its Project Elixir, which I believe also synchonizes disconnected data caches using webservices?

|||Then you will have to use a combination of binary_checksum or checksum along with comparison of columns or generate hash for each row using hashbytes function. You can compare rows efficiently this way and then perform the conflict resolution. This of course is no easy matter since each technique has it's own pros and cons.|||

hi,

Can I know is there anyway to compare 2 timestamp in .NET?

For eg. I got 2 timestamp, I want to know which 1 is the most recent changed.

Thanks.

Distributed data synchronization - Comparing timestamp

Hello,

I am designing a distributed application where a central SQL Server 2005 database will need to be synchronized with remote SQLExpress databases via a WebService. Data can be edited at the cental db (by our connected applications) or on the local SQLExpress dbs running on the users machines (by this disconnected application).

Now, how can I use the timestamp column to determine the most recent update. The most recent update to me is not the user that most recently invoked the syncing WebService, but the most recent time when the change was made to the data locally vs the change time at the central server. Because a user could make a change on his laptop on Monday but not synchronize it till Friday. I dont want the Mon data to overwrite data of Tues-Fri being overwritten simply based on assumption that a late sync is infact the most recent change.

The initial data will be downloaded to the local SQLExpress db via the WebService so the timestamp data in the table will come along with it.

When a user modifies data in this disconnected SQLExpress db, can that be compared to modifications on the central db using timestamps to determine if the users data being synced is older or newer than the data on the server? I understand timestamps are incremental values, but are they still sensitive to the users timezone since the central server will be a diff timezone then the user.

Timestamps are instance-level unique numbers. So you can't really compare those across instances. Why don't you use merge replication for instance? It is designed to solve problems like these. If you assistance on the replicaiton part you should post in the replication forum.|||

For ISVs that are building installable components, replication isn't too attractive. The components are meant to be quickly deployable whereas replication requires configuration to be done to create the publishers, distributers, etc.

So, basically, I would have to write a home-grown solution for conflict resolution?

Does anyone know how Microsoft is handling conflict resolution for the remote synchronization in its Project Elixir, which I believe also synchonizes disconnected data caches using webservices?

|||Then you will have to use a combination of binary_checksum or checksum along with comparison of columns or generate hash for each row using hashbytes function. You can compare rows efficiently this way and then perform the conflict resolution. This of course is no easy matter since each technique has it's own pros and cons.|||

hi,

Can I know is there anyway to compare 2 timestamp in .NET?

For eg. I got 2 timestamp, I want to know which 1 is the most recent changed.

Thanks.

sql

Tuesday, March 27, 2012

Distribute ReportBuilder

Hi,

Is there a way to distribute Report Builder application to clients computers ? (avoiding them to connect and download individually the application on the Reports WebSite)

Thanx in advance.
Grgory.

i'm searching too how distribute this app to clients computers... Anyone idea ?
|||

Still looking!

Thanx in advance.

Grgory.

|||No. Report Builder's distribution mechanism is via ClickOnce. From what I understandthere are no plans to allow distribution in any other fashion. Sorry!

Distribute ReportBuilder

Hi,

Is there a way to distribute Report Builder application to clients computers ? (avoiding them to connect and download individually the application on the Reports WebSite)

Thanx in advance.
Grgory.

i'm searching too how distribute this app to clients computers... Anyone idea ?
|||

Still looking!

Thanx in advance.

Grgory.

|||No. Report Builder's distribution mechanism is via ClickOnce. From what I understandthere are no plans to allow distribution in any other fashion. Sorry!

Distribute Data Securely in SSExpress

I would like to distribute some data in SqlServer Express which only my application can read from.

My question is - is it possible to secure a database so that only a specified user would have read/write permissions. Even the sa acount should not have the permission to read or write to the database although it would retain the right to drop the database.

One option I believe is to encrypt the data but I would prefer not to incur the performance penalty associated with this.

Any insights would be appreciated.

Thanks

This topic has been previously discussed in the following thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=52094&SiteID=1

Thanks
Laurentiu

Distribute app/database with SQL or Windows security?

This question is for anybody who has created & sold/distributed an
application that uses MSDE as its backend database.
I am converting my application which currently uses Access 2000 to MSDE
SP3a.
I already have the install process together, but I an in a quandry as to
what security method to use when installing.
If I use SQL security and set the SA password, am I looking for issues in
the future when a client might possibly purchase another program that uses
MSDE and won't install because I have set the SA password?
If I use Windows authentication, then I am leaving my database open to
anybody with Admin privliges.
I use information within a database table to license my program, so I'd like
to keep it secured.
Can anybody tell me what they do for their applications? Install with SQL
or Windows authentication?
TIA
Jim K.
As for other applications using your instance - the MSDE license only allows
your instance to be used by your application. If another application
installs its database on your instance of MSDE, then not only is it
violating the MSDE licensing but it is rather poor behavior, in my opinion.
Users with Administrative rights will always have total access to your MSDE
installation, whether you use integrated (Windows) authentication or not. If
you are concerned about the security of your data then you should implement
some form of encryption for the any data that you consider important
intellectual property or secure data.
With my application we encrypt any important data and implement in the
database a simple way of protecting the licensing the program. I don't go to
great extremes to prevent unlicensed clients from connecting to the
database - just enought to make it a hassle to try to break any system I
might have put in place.
Jim
"Jim K" <krusej@.megsinet.net> wrote in message
news:%235%23vNcuiEHA.3876@.TK2MSFTNGP12.phx.gbl...
> This question is for anybody who has created & sold/distributed an
> application that uses MSDE as its backend database.
> I am converting my application which currently uses Access 2000 to MSDE
> SP3a.
> I already have the install process together, but I an in a quandry as to
> what security method to use when installing.
> If I use SQL security and set the SA password, am I looking for issues in
> the future when a client might possibly purchase another program that uses
> MSDE and won't install because I have set the SA password?
> If I use Windows authentication, then I am leaving my database open to
> anybody with Admin privliges.
> I use information within a database table to license my program, so I'd
like
> to keep it secured.
> Can anybody tell me what they do for their applications? Install with SQL
> or Windows authentication?
> TIA
> Jim K.
>

DISTINCT to ShortDateString, not DISTINCT to the DateTime; How?

Hello,

I have written a small asp.net application, which keeps record of the proposals coming from the branch offices of a bank in a table

CREATEd as a TABLE Proposals ( ID smallint identity(7,1), BranchID char(5), Proposal_Date datetime )

This app also calculates the total number of proposals coming from a specific branch in a given date by
SELECTing COUNT(BranchID) FROM Proposals WHEREBranchID=@.prmBranchID ANDProposal_Date=@.prmDate
and prints them in a table (my target table).

This target table has as many rows as the result of the "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals"
and excluding the first column which displays those DISTINCT Proposal_Dates, it also has as many columns as the result of the
"SELECT DISTINCT BranchID FROM Proposals".
This target table converts the DateTime values ToShortDateString so
that we are able to see comfortably which branch office has sent how many proposals in a given day.

So far so good, and everything works fine except one thing:

Certain DateTime values in the Proposals table which are of the same day but of different hours (for ex: 11.11.2005 08:30:45 and
11.11.2005 10:45:30) cause some trouble in the target table, where "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals" is executed, because (as you might already guess) it displays two identical dates in ShortDateString form, and this doesn't make much sense (i.e. it causes redundant rows)

What I need to do is to get a result like (in a neat fashion :)

"SELECT COUNT( DISTINCT Proposal_Date ) <<DISTINCT ONLY IN THE DAYS AND NOT IN HOURS OR MINUTES OR SECONDS>> FROM Proposals"

So, how to do it in a suitable way?

Thanks in advance.

Try this -

Select Distinct Convert(char(10), Proposal_Date, 101)

It will eliminate the time part from the selection

Regards

Ash

|||

This one will solve your problem:

SELECT

DISTINCTCONVERT(NCHAR(8), Proposal_Date, 112)AS Proposal_Date

FROM

Proposals

You can look up from Books Online for the date, convert functions to help you understand more about the date and time. If you need more help, please post again.

|||

Hello,

Yeah that was what I neeed to know, and it solved the problem.

(apparently, I'm still new to T-SQL :)

Thanks.

|||

I know your problem is resolved but try the link below for more about the SQL Server DateTime convert function codes. And if you want Short Datetime you use SmallDateTime data type it gives you DateTime less seconds because it has less resolution. Hope this helps.

http://www.sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

Wednesday, March 21, 2012

Distant access to Excel file for import

Hi,

I'm developping a web application and I need to import a data from an excel file to sql server 2005.

It works very well locally (visual studio & sql server & the excel file on the same machine).

But it doesn't work when visual studio & excel file are in a machine and sql server is in another server.

I have always the same problem:

'C:\Documents and Settings\momo\Bureau\File.xls' n'est pas un chemin d'accès valide. Assurez-vous que le nom du chemin d'accès est correct et qu'une connexion est établie avec le serveur sur lequel réside le fichier.

Could any one help me please ?!

Thank you in advance.

It would help if you translated the French error message to English.

|||

Sorry to be late !

Here is the translation :

'C:\Documents and Settings\momo\Desktop\File.xls' isn't a correct access path. Be sure that the name of the access path is correct and a connexion is established with the server where is the file.

In fact, I'm using the FileUpload control as follows:

string filemap = Convert.ToString(fileupload.PostedFile.FileName);

string conString = @."Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=""Excel 8.0;HDR=YES;"";Data Source=" + filemap;

Could you tell me, please, what do I have to modify in order to be able to import my file from any client machine (of course, each client machine has a copy of the same file locally).

Thanks in advance.

|||

You need to think of a different architecture. You should create a web service that writes to the SQL Server. On each client PC you need a windows application that can read the excel spreadsheet and write to the web service.

|||

Please, could I have more details on how to do it. I'm a begginer in ASP NET and so I do not know what a web service means exactely.

Thanks.

|||

Have a look at http://quickstarts.asp.net/QuickStartv20/webservices/default.aspx and work through the tutorial

"The ASP.NET Web Services QuickStart is a series of client and server samples along with supporting commentary designed to quickly acquaint developers with featuresin ASP.NET Web Services. The QuickStart samples are designed to be short, easy-to-understand illustrations of these features. By the time you finish reviewing these samples, you will be familiar with the majorfeature areas in ASP.NET Web Services. "

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.

Wednesday, March 7, 2012

Displaying Backup file locations

Hi,

I have an application that I want to allow a user to backup their sql 2005 database. I use the SMO classes to actually perform the backup, but my application can run on a client machine, not just the database server. As I allow the user to select the location for their backup I would really like to show a folder list of server locations, not local (i.e. client) folders.

Basically I wondered if there were any UI objects available for Sql 2005 ( in the SMO namespaces perhaps), something that will display a list of server folders and could be used from a client machine.

Thanks for your help

Graham

You can get the server's default backup directory within SMO from the server's .Settings.BackupDirectory property. You should be able to find other directories using WMI queries, I'd think.

|||As Allan said, this is done through WMI.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Displaying ADO MDX query results in reports

I'm working on an application to display sales data using sql server
2000 reporting services.
The data is in an MS Analysis Services cube, so I'm using MDX queries
in the data set.
The queries all have a similar format of the form:
SELECT
{[Measures].Members} ON COLUMNS,
{ MyDimension.members} ON ROWS
FROM MyCube
where the items in the COLUMNS clause are allways the same and
MyDimension might be SalesBranch, ProductCategory, etc.
When Reporting services works out the fields for the returned data set
it appears to be flattening out the MyDimension structure and using the
dimension level names for field names.
eg
<Fields>
<Field Name="Manufacturer">
<DataField>[Manufacturer_Model].[Manu].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Measures_Oe_Id">
<DataField>[Measures].[Oe Id]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_Oe_Realval_A">
<DataField>[Measures].[Oe Realval A]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
</Fields>
In the report layout I can select these fields and display them as
expected.
However the drawback is that since dimension level names are encoded
into report field names I have to have a separate report for each
dimension I want to put on the ROWS. ie one .RDL file for SalesBranch,
one for ProductCategory etc.
If this was SQL it would not be a problem because in the SQL SELECT
statement the column names could be fixed using "SELECT colname AS
othercolname ", but, being new to MDX, I can't find how to do this.
Does anyone know how to achieve this in MDX / ADO MD ? ie how to give
an MDX column an alias
Thanks
SteveHi Steve. I'm not quite clear on what you're trying to do. If, for
example, you had StateCode, CountryCode on your rows, you'd want them both
to be in the same column? Can you be more specific? From your
explanation, I can't think of a business reason why I'd want to do this.
-T
"steve caa" <steve_caa@.hotmail.co.uk> wrote in message
news:1147778689.038349.64180@.j33g2000cwa.googlegroups.com...
> I'm working on an application to display sales data using sql server
> 2000 reporting services.
> The data is in an MS Analysis Services cube, so I'm using MDX queries
> in the data set.
> The queries all have a similar format of the form:
> SELECT
> {[Measures].Members} ON COLUMNS,
> { MyDimension.members} ON ROWS
> FROM MyCube
> where the items in the COLUMNS clause are allways the same and
> MyDimension might be SalesBranch, ProductCategory, etc.
> When Reporting services works out the fields for the returned data set
> it appears to be flattening out the MyDimension structure and using the
> dimension level names for field names.
> eg
> <Fields>
> <Field Name="Manufacturer">
> <DataField>[Manufacturer_Model].[Manu].[MEMBER_CAPTION]</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="Measures_Oe_Id">
> <DataField>[Measures].[Oe Id]</DataField>
> <rd:TypeName>System.Object</rd:TypeName>
> </Field>
> <Field Name="Measures_Oe_Realval_A">
> <DataField>[Measures].[Oe Realval A]</DataField>
> <rd:TypeName>System.Object</rd:TypeName>
> </Field>
> </Fields>
> In the report layout I can select these fields and display them as
> expected.
> However the drawback is that since dimension level names are encoded
> into report field names I have to have a separate report for each
> dimension I want to put on the ROWS. ie one .RDL file for SalesBranch,
> one for ProductCategory etc.
> If this was SQL it would not be a problem because in the SQL SELECT
> statement the column names could be fixed using "SELECT colname AS
> othercolname ", but, being new to MDX, I can't find how to do this.
> Does anyone know how to achieve this in MDX / ADO MD ? ie how to give
> an MDX column an alias
> Thanks
> Steve
>|||Hi Tim
Thanks for the reply.
I'm really trying to be lazy and reuse the same RDL for a number of
different reports.
I'm generating the MDX query from parameters in a form that contains a
report viewer. The items selected to appear along columns will always
be the same but the dimension that appears on the rows will vary
depending on whether the user wants to see sales by branch or by
product. The depth of the dimension on the rows will allways be same ie
I'm only showing branch name, not branch name and region.
If the user selects performance by sales branch the MDX looks like
SELECT
{[Measures].Members} ON COLUMNS,
{ [Branch].members} ON ROWS
FROM MyCube
and the report displays the branch name in the first column and the
measures along the other columns
similarly if the user selects performance by product the MDX looks like
SELECT
{[Measures].Members} ON COLUMNS,
{ [Product].members} ON ROWS
FROM MyCube
and the report displays the product name in the leftmost column and the
measures along the other columns
Now, the reports are identical apart from the first column, so I
thought if there was a way to make the columns have defined names I
could reuse the same RDL for any number of reports.
In the reporting services designer, on the data tab, executing a query
fills the data grid and displays field names in the first row.
The field names appear to be based on the column or row names in the
MDX query eg when I select the performance by branch report, the column
in the data grid that displays the branch name is called
"[Branch].[Branch ID].[MEMBER CAPTION]", when I select the performance
by product report, the column in the data grid that displays the
product name is called "[Product].[Product ID].[MEMBER CAPTION]" .
In the designers layout tab these field names are linked to the cells
they are to be displayed in, but since the field names are different
for the different MDX queries I'm generating I need to have a different
RDL file for each query.
In SQL I'd be able to alias the columns so they had the same name eg
SELECT SalesBranch as FirstColumn, MeasureA, MeasureB From Sales
and
SELECT ProductName as FirstColumn, MeasureA, MeasureB From Sales.
So my question is :
is there a way for MDX to do what the SQL statements above are doing?
ie make the recordsets returned by ADO allways have predefined field
names.
Thanks
Steve|||Oh! Well in that case, create a custom set and use it in a dynamic query...
Build it first in the query builder to get the columns, then change the text
to this:
= "WITH SET [Branch] AS
'" & Parameters!TheBranchToShow.Value & ".ALLMEMBERS'
SELECT {[Measures].Members} ON COLUMNS,
{ [Branch] } ON ROWS
FROM MyCube"
HTH
"steve caa" <steve_caa@.hotmail.co.uk> wrote in message
news:1147871527.003112.157400@.38g2000cwa.googlegroups.com...
> Hi Tim
> Thanks for the reply.
> I'm really trying to be lazy and reuse the same RDL for a number of
> different reports.
> I'm generating the MDX query from parameters in a form that contains a
> report viewer. The items selected to appear along columns will always
> be the same but the dimension that appears on the rows will vary
> depending on whether the user wants to see sales by branch or by
> product. The depth of the dimension on the rows will allways be same ie
> I'm only showing branch name, not branch name and region.
> If the user selects performance by sales branch the MDX looks like
> SELECT
> {[Measures].Members} ON COLUMNS,
> { [Branch].members} ON ROWS
> FROM MyCube
> and the report displays the branch name in the first column and the
> measures along the other columns
> similarly if the user selects performance by product the MDX looks like
> SELECT
> {[Measures].Members} ON COLUMNS,
> { [Product].members} ON ROWS
> FROM MyCube
> and the report displays the product name in the leftmost column and the
> measures along the other columns
> Now, the reports are identical apart from the first column, so I
> thought if there was a way to make the columns have defined names I
> could reuse the same RDL for any number of reports.
> In the reporting services designer, on the data tab, executing a query
> fills the data grid and displays field names in the first row.
> The field names appear to be based on the column or row names in the
> MDX query eg when I select the performance by branch report, the column
> in the data grid that displays the branch name is called
> "[Branch].[Branch ID].[MEMBER CAPTION]", when I select the performance
> by product report, the column in the data grid that displays the
> product name is called "[Product].[Product ID].[MEMBER CAPTION]" .
> In the designers layout tab these field names are linked to the cells
> they are to be displayed in, but since the field names are different
> for the different MDX queries I'm generating I need to have a different
> RDL file for each query.
>
> In SQL I'd be able to alias the columns so they had the same name eg
> SELECT SalesBranch as FirstColumn, MeasureA, MeasureB From Sales
> and
> SELECT ProductName as FirstColumn, MeasureA, MeasureB From Sales.
> So my question is :
> is there a way for MDX to do what the SQL statements above are doing?
> ie make the recordsets returned by ADO allways have predefined field
> names.
> Thanks
> Steve
>|||Good idea.
This seems to work in the MDX Sample query tool, but in Reporting
Services designer it still mangles the structure of the underlying
dimension into the column headings, not the MDX SET name. I tried a
simple vb.NET app and it does the same so it's an ADO MDX issue, not
Reporting Services
Looks like I'll have to do it the long way.
Thanks
Steve|||The trick is to return the dimension names as measures, and then use the
measures in your report instead of the dimension names.
You start out with a usual query, and add Currentmember.Name,
currentmember.Level.Ordinal and Currentmember.UniqueName as measures.
with member [Measures].[MyMeasure] as '[Store].currentmember.name' member
[Measures].[MyMeasure2] as '[Store].currentmember.Level.Ordinal' member
[Measures].[MyMeasure3] as '[Store].currentmember.UniqueName'
Then add x number of columns, for measures that you want to reuse.
member [Measures].[MyMeasure4] as '[Measures].[Unit Sales]' member
[Measures].[MyMeasure5] as '[Measures].[Store Cost]' member
[Measures].[MyMeasure6] as '[Measures].[Store Sales]'
Then select all the measures (both the dimension name ones, and the ones
based on the old measures)
I added a filter to mine, you don't need to do that.
The resulting MDX query looks like this:
with member [Measures].[MyMeasure] as '[Store].currentmember.name' member
[Measures].[MyMeasure2] as '[Store].currentmember.Level.Ordinal' member
[Measures].[MyMeasure3] as '[Store].currentmember.UniqueName' member
[Measures].[MyMeasure4] as '[Measures].[Unit Sales]' member
[Measures].[MyMeasure5] as '[Measures].[Store Cost]' member
[Measures].[MyMeasure6] as '[Measures].[Store Sales]' select
{[Measures].[MyMeasure], [Measures].[MyMeasure2], [Measures].[MyMeasure3],
[Measures].[MyMeasure4], [Measures].[MyMeasure5], [Measures].[MyMeasure6]}
on columns, {filter([Store].members, [Measures].[Store Cost]> 25000) } on
rows from [Sales]
After you've generated all the fields, change it as usual to a parameterized
query:
="with member [Measures].[MyMeasure] as '[" & Parameters!Dimension.Value &
"].currentmember.name' member [Measures].[MyMeasure2] as '[" &
Parameters!Dimension.Value & "].currentmember.Level.Ordinal' member
[Measures].[MyMeasure3] as '[" & Parameters!Dimension.Value &
"].currentmember.UniqueName' member [Measures].[MyMeasure4] as
'[Measures].[Unit Sales]' member [Measures].[MyMeasure5] as
'[Measures].[Store Cost]' member [Measures].[MyMeasure6] as
'[Measures].[Store Sales]' select {[Measures].[MyMeasure],
[Measures].[MyMeasure2], [Measures].[MyMeasure3], [Measures].[MyMeasure4],
[Measures].[MyMeasure5], [Measures].[MyMeasure6]} on columns, {filter([" &
Parameters!Dimension.Value & "].members," & Parameters!Measure.Value & " > "
& Parameters!Amount.Value & ") } on rows from [Sales]"
And now you just add the dimensions you need as a set of report parameters
( drop down list with all the dimension unique names) and other parameters
you need, and try it out. :)
The drawback is that it gets kind of flat, but you can do quite a lot of
tricks with formating the different levels.
This is a sample that works against Foodmart2000:
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<RightMargin>2.5cm</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<Top>0.25cm</Top>
<rd:DefaultName>textbox4</rd:DefaultName>
<Height>0.63492cm</Height>
<Width>13.25cm</Width>
<CanGrow>true</CanGrow>
<Value>="Generic report based on " & Parameters!Dimension.Value</Value>
</Textbox>
<Table Name="table1">
<Height>1.90476cm</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.63492cm</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
<Left>Solid</Left>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>= Parameters!Dimension.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
<Right>Solid</Right>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Unit sales</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
<Right>Solid</ight>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Store Cost</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
<Right>Solid</Right>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox10</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Store Sales</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.63492cm</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Measures_MyMeasure">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>=IIF(Fields!Measures_MyMeasure2.Value = "0", "LightBlue",
IIF(Fields!Measures_MyMeasure2.Value = "2", "LightGreen",
IIF(Fields!Measures_MyMeasure2.Value = "3", "LightYellow",
"White")))</BackgroundColor>
<BorderStyle>
<Left>Solid</Left>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>Measures_MyMeasure</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Measures_MyMeasure.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Measures_MyMeasure4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N0</Format>
<BackgroundColor>=IIF(Fields!Measures_MyMeasure2.Value = "0", "LightBlue",
IIF(Fields!Measures_MyMeasure2.Value = "2", "LightGreen",
IIF(Fields!Measures_MyMeasure2.Value = "3", "LightYellow",
"White")))</BackgroundColor>
<BorderStyle>
<Right>Solid</Right>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>Measures_MyMeasure4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Measures_MyMeasure4.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Measures_MyMeasure5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N0</Format>
<BackgroundColor>=IIF(Fields!Measures_MyMeasure2.Value = "0", "LightBlue",
IIF(Fields!Measures_MyMeasure2.Value = "2", "LightGreen",
IIF(Fields!Measures_MyMeasure2.Value = "3", "LightYellow",
"White")))</BackgroundColor>
<BorderStyle>
<Right>Solid</Right>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>Measures_MyMeasure5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Measures_MyMeasure5.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Measures_MyMeasure6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>N0</Format>
<BackgroundColor>=IIF(Fields!Measures_MyMeasure2.Value = "0", "LightBlue",
IIF(Fields!Measures_MyMeasure2.Value = "2", "LightGreen",
IIF(Fields!Measures_MyMeasure2.Value = "3", "LightYellow",
"White")))</BackgroundColor>
<BorderStyle>
<Right>Solid</Right>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Measures_MyMeasure6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Measures_MyMeasure6.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<Grouping Name="table1_Details_Group">
<GroupExpressions>
<GroupExpression>=Fields!Measures_MyMeasure3.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</Details>
<DataSetName>DataSet1</DataSetName>
<Top>1.25cm</Top>
<Width>13.63624cm</Width>
<Footer>
<TableRows>
<TableRow>
<Height>0.63492cm</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Footer>
<TableColumns>
<TableColumn>
<Width>5.33333cm</Width>
</TableColumn>
<TableColumn>
<Width>2.75cm</Width>
<Visibility>
<Hidden>=IIF( Parameters!ShowAll.Value = False, IIF(Parameters!Measure.Label
= "Unit Sales", False, True), False)</Hidden>
</Visibility>
</TableColumn>
<TableColumn>
<Width>2.77645cm</Width>
<Visibility>
<Hidden>=IIF( Parameters!ShowAll.Value = False, IIF(Parameters!Measure.Label
= "Store Cost", False, True), False)</Hidden>
</Visibility>
</TableColumn>
<TableColumn>
<Width>2.77646cm</Width>
<Visibility>
<Hidden>=IIF( Parameters!ShowAll.Value = False, IIF(Parameters!Measure.Label
= "Store Sales", False, True), False)</Hidden>
</Visibility>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>15cm</Height>
<ColumnSpacing>1cm</ColumnSpacing>
</Body>
<TopMargin>2.5cm</TopMargin>
<DataSources>
<DataSource Name="FoodMart 2000">
<rd:DataSourceID>8a442390-ee54-4c51-b68a-8a19b447060d</rd:DataSourceID>
<DataSourceReference>FoodMart 2000</DataSourceReference>
</DataSource>
</DataSources>
<Width>16cm</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="Store_Store_Country">
<DataField>[Store].[Store Country].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Store_Store_State">
<DataField>[Store].[Store State].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Store_Store_City">
<DataField>[Store].[Store City].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Store_Store_Name">
<DataField>[Store].[Store Name].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Measures_MyMeasure">
<DataField>[Measures].[MyMeasure]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_MyMeasure2">
<DataField>[Measures].[MyMeasure2]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_MyMeasure3">
<DataField>[Measures].[MyMeasure3]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_MyMeasure4">
<DataField>[Measures].[MyMeasure4]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_MyMeasure5">
<DataField>[Measures].[MyMeasure5]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_MyMeasure6">
<DataField>[Measures].[MyMeasure6]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>FoodMart 2000</DataSourceName>
<CommandText>="with member [Measures].[MyMeasure] as '[" &
Parameters!Dimension.Value & "].currentmember.name' member
[Measures].[MyMeasure2] as '[" & Parameters!Dimension.Value &
"].currentmember.Level.Ordinal' member [Measures].[MyMeasure3] as '[" &
Parameters!Dimension.Value & "].currentmember.UniqueName' member
[Measures].[MyMeasure4] as '[Measures].[Unit Sales]' member
[Measures].[MyMeasure5] as '[Measures].[Store Cost]' member
[Measures].[MyMeasure6] as '[Measures].[Store Sales]' select
{[Measures].[MyMeasure], [Measures].[MyMeasure2], [Measures].[MyMeasure3],
[Measures].[MyMeasure4], [Measures].[MyMeasure5], [Measures].[MyMeasure6]}
on columns, {filter([" & Parameters!Dimension.Value & "].members,"
& Parameters!Measure.Value & " > " & Parameters!Amount.Value
& ") } on rows from [Sales]"</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>2.5cm</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<PageHeight>29.7cm</PageHeight>
<rd:DrawGrid>true</rd:DrawGrid>
<PageWidth>21cm</PageWidth>
<rd:ReportID>247133a5-6ff9-4041-b398-bb2f76911f3e</rd:ReportID>
<BottomMargin>2.5cm</BottomMargin>
<ReportParameters>
<ReportParameter Name="Dimension">
<DataType>String</DataType>
<Prompt>Choose dimension</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>Store</Value>
</ParameterValue>
<ParameterValue>
<Value>Store Type</Value>
</ParameterValue>
<ParameterValue>
<Value>Store Size in SQFT</Value>
</ParameterValue>
<ParameterValue>
<Value>Time</Value>
</ParameterValue>
<ParameterValue>
<Value>Product</Value>
</ParameterValue>
<ParameterValue>
<Value>Gender</Value>
</ParameterValue>
<ParameterValue>
<Value>Promotions</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
<ReportParameter Name="Measure">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>="[Measures].[Unit Sales]"</Value>
</Values>
</DefaultValue>
<Prompt>Choose measure</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>[Measures].[Unit Sales]</Value>
<Label>Unit Sales</Label>
</ParameterValue>
<ParameterValue>
<Value>[Measures].[Store Cost]</Value>
<Label>Store Cost</Label>
</ParameterValue>
<ParameterValue>
<Value>[Measures].[Store Sales]</Value>
<Label>Store Sales</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
<ReportParameter Name="Amount">
<DataType>Integer</DataType>
<DefaultValue>
<Values>
<Value>0</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>More than</Prompt>
</ReportParameter>
<ReportParameter Name="ShowAll">
<DataType>Boolean</DataType>
<DefaultValue>
<Values>
<Value>true</Value>
</Values>
</DefaultValue>
<AllowBlank>true</AllowBlank>
<Prompt>Show all</Prompt>
</ReportParameter>
</ReportParameters>
<Language>nb-NO</Language>
</Report>|||Hi Kaisa,
I want same what steve want. I have two column in my report. First is
Dimension and second is measure. I have tryed what you suggested but not
working. can you please check where i am doing wrong?
I have two dimension:
[Dim Station].[Station Name].[Station Name]
[Dim Free Test].[Free Test].[Free Test]
and one measure:
[Measures].[Total Test Count]
My Cube Name is:
[OLAP Test Cube]
I want [Dim Station] and [Dim Free Test] dimension dynamically. I don't want
any filter if it require for Dynamic queyr then ok.
when i execute following query in Query builder then it giving me error. can
you please check this where i am wrong?
Query:
with member [Measures].[MyMeasure] as '[Dim Station].currentmember.name'
member [Measures].[MyMeasure2] as '[Dim Station].currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '[Dim Station].currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
[Measures].[MyMeasure3],
[Measures].[MyMeasure4]}
on columns, {filter([Dim Station].members, [Measures].[Total Test Count]]>
0) } on
rows from [OLAP Test Cube]
Regards,
Dinesh Patel
"Kaisa M. Lindahl Lervik" wrote:
> The trick is to return the dimension names as measures, and then use the
> measures in your report instead of the dimension names.
> You start out with a usual query, and add Currentmember.Name,
> currentmember.Level.Ordinal and Currentmember.UniqueName as measures.
> with member [Measures].[MyMeasure] as '[Store].currentmember.name' member
> [Measures].[MyMeasure2] as '[Store].currentmember.Level.Ordinal' member
> [Measures].[MyMeasure3] as '[Store].currentmember.UniqueName'
> Then add x number of columns, for measures that you want to reuse.
> member [Measures].[MyMeasure4] as '[Measures].[Unit Sales]' member
> [Measures].[MyMeasure5] as '[Measures].[Store Cost]' member
> [Measures].[MyMeasure6] as '[Measures].[Store Sales]'
> Then select all the measures (both the dimension name ones, and the ones
> based on the old measures)
> I added a filter to mine, you don't need to do that.
> The resulting MDX query looks like this:
> with member [Measures].[MyMeasure] as '[Store].currentmember.name' member
> [Measures].[MyMeasure2] as '[Store].currentmember.Level.Ordinal' member
> [Measures].[MyMeasure3] as '[Store].currentmember.UniqueName' member
> [Measures].[MyMeasure4] as '[Measures].[Unit Sales]' member
> [Measures].[MyMeasure5] as '[Measures].[Store Cost]' member
> [Measures].[MyMeasure6] as '[Measures].[Store Sales]' select
> {[Measures].[MyMeasure], [Measures].[MyMeasure2], [Measures].[MyMeasure3],
> [Measures].[MyMeasure4], [Measures].[MyMeasure5], [Measures].[MyMeasure6]}
> on columns, {filter([Store].members, [Measures].[Store Cost]> 25000) } on
> rows from [Sales]
>
> After you've generated all the fields, change it as usual to a parameterized
> query:
> ="with member [Measures].[MyMeasure] as '[" & Parameters!Dimension.Value &
> "].currentmember.name' member [Measures].[MyMeasure2] as '[" &
> Parameters!Dimension.Value & "].currentmember.Level.Ordinal' member
> [Measures].[MyMeasure3] as '[" & Parameters!Dimension.Value &
> "].currentmember.UniqueName' member [Measures].[MyMeasure4] as
> '[Measures].[Unit Sales]' member [Measures].[MyMeasure5] as
> '[Measures].[Store Cost]' member [Measures].[MyMeasure6] as
> '[Measures].[Store Sales]' select {[Measures].[MyMeasure],
> [Measures].[MyMeasure2], [Measures].[MyMeasure3], [Measures].[MyMeasure4],
> [Measures].[MyMeasure5], [Measures].[MyMeasure6]} on columns, {filter([" &
> Parameters!Dimension.Value & "].members," & Parameters!Measure.Value & " > "
> & Parameters!Amount.Value & ") } on rows from [Sales]"
> And now you just add the dimensions you need as a set of report parameters
> ( drop down list with all the dimension unique names) and other parameters
> you need, and try it out. :)
> The drawback is that it gets kind of flat, but you can do quite a lot of
> tricks with formating the different levels.
> This is a sample that works against Foodmart2000:
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <rd:GridSpacing>0.25cm</rd:GridSpacing>
> <RightMargin>2.5cm</RightMargin>
> <Body>
> <ReportItems>
> <Textbox Name="textbox4">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <Top>0.25cm</Top>
> <rd:DefaultName>textbox4</rd:DefaultName>
> <Height>0.63492cm</Height>
> <Width>13.25cm</Width>
> <CanGrow>true</CanGrow>
> <Value>="Generic report based on " & Parameters!Dimension.Value</Value>
> </Textbox>
> <Table Name="table1">
> <Height>1.90476cm</Height>
> <Style />
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.63492cm</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BorderStyle>
> <Bottom>Solid</Bottom>
> <Top>Solid</Top>
> <Left>Solid</Left>
> </BorderStyle>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>11</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>= Parameters!Dimension.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox2">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BorderStyle>
> <Bottom>Solid</Bottom>
> <Top>Solid</Top>
> <Right>Solid</Right>
> </BorderStyle>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>10</ZIndex>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Unit sales</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox3">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BorderStyle>
> <Bottom>Solid</Bottom>
> <Top>Solid</Top>
> <Right>Solid</ight>
> </BorderStyle>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>9</ZIndex>
> <rd:DefaultName>textbox3</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Store Cost</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox10">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BorderStyle>
> <Bottom>Solid</Bottom>
> <Top>Solid</Top>
> <Right>Solid</Right>
> </BorderStyle>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>8</ZIndex>
> <rd:DefaultName>textbox10</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Store Sales</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>|||I am working with AS2005 and RS2005 I have created one report also. can you
please check this code.
I am trying to change query to below but giving error:
="with member [Measures].[MyMeasure] as '" & Parameters!Dimension.Value &
".currentmember.name'
member [Measures].[MyMeasure2] as '" & Parameters!Dimension.Value &
".currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '" & Parameters!Dimension.Value &
".currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
[Measures].[MyMeasure3],
[Measures].[MyMeasure4]}
on columns, {" & Parameters!Dimension.Value & ".members} on
rows from [OLAP Test Cube]"
my report code is below:
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="SLCTestDB">
<DataSourceReference>SLCTestDB</DataSourceReference>
<rd:DataSourceID>44e60797-bd99-4681-9499-7e848244229e</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="Dimension">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>[Free Test]</Value>
</Values>
</DefaultValue>
<Prompt>Dimension</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>[Station Name]</Value>
<Label>station</Label>
</ParameterValue>
<ParameterValue>
<Value>[Free Test]</Value>
<Label>Free</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>20pt</FontSize>
<Color>SteelBlue</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.36in</Height>
<Value>Report3</Value>
</Textbox>
<Table Name="table1">
<DataSetName>SLCTestDB</DataSetName>
<Top>0.36in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="MyMeasure">
<rd:DefaultName>MyMeasure</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>#6e9eca</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!MyMeasure.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>#6e9eca</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>
</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.21in</Height>
</TableRow>
</TableRows>
</Header>
<Sorting>
<SortBy>
<SortExpression>=Fields!MyMeasure.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="table1_MyMeasure">
<GroupExpressions>
<GroupExpression>=Fields!MyMeasure.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Width>4.5in</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>
</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="MyMeasure4">
<rd:DefaultName>MyMeasure4</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!MyMeasure4.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.21in</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>My Measure</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<FontFamily>Tahoma</FontFamily>
<FontWeight>700</FontWeight>
<FontSize>11pt</FontSize>
<BorderColor>
<Default>LightGrey</Default>
</BorderColor>
<BackgroundColor>SteelBlue</BackgroundColor>
<Color>White</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>My Measure4</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.22in</Height>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<TableColumns>
<TableColumn>
<Width>2.875in</Width>
</TableColumn>
<TableColumn>
<Width>1.625in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Height>1in</Height>
</Body>
<rd:ReportID>be7b0e20-a01a-40a4-8c2a-23748db01c34</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="SLCTestDB">
<Query>
<rd:SuppressAutoUpdate>true</rd:SuppressAutoUpdate>
<CommandText>with member [Measures].[MyMeasure] as '[Free
Test].currentmember.name'
member [Measures].[MyMeasure2] as '[Free Test].currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '[Free Test].currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
[Measures].[MyMeasure3],
[Measures].[MyMeasure4]} on columns,
{[Free Test].members} on
rows from [OLAP Test Cube]</CommandText>
<DataSourceName>SLCTestDB</DataSourceName>
<rd:MdxQuery><QueryDefinition
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns="<CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification">http://schemas.microsoft.com/AnalysisServices/QueryDefinition"><CommandType>MDX</CommandType><Type>Query</Type><QuerySpecification
xsi:type="MDXQuerySpecification"><Select><Items><Item><ID
xsi:type="Level"><DimensionName>Dim Free
Test</DimensionName><HierarchyName>Free
Test</HierarchyName><HierarchyUniqueName>[Dim Free Test].[Free
Test]</HierarchyUniqueName><LevelName>Free Test</LevelName><UniqueName>[Dim
Free Test].[Free Test].[Free Test]</UniqueName></ID><ItemCaption>Free
Test</ItemCaption></Item><Item><ID
xsi:type="Measure"><MeasureName>MyMeasure</MeasureName><UniqueName>[Measures].[MyMeasure]</UniqueName></ID><ItemCaption>MyMeasure</ItemCaption><FormattedValue>true</FormattedValue></Item><Item><ID
xsi:type="Measure"><MeasureName>MyMeasure2</MeasureName><UniqueName>[Measures].[MyMeasure2]</UniqueName></ID><ItemCaption>MyMeasure2</ItemCaption><FormattedValue>true</FormattedValue></Item><Item><ID
xsi:type="Measure"><MeasureName>MyMeasure3</MeasureName><UniqueName>[Measures].[MyMeasure3]</UniqueName></ID><ItemCaption>MyMeasure3</ItemCaption><FormattedValue>true</FormattedValue></Item><Item><ID
xsi:type="Measure"><MeasureName>MyMeasure4</MeasureName><UniqueName>[Measures].[MyMeasure4]</UniqueName></ID><ItemCaption>MyMeasure4</ItemCaption><FormattedValue>true</FormattedValue></Item></Items></Select><From>OLAP
Test Cube</From><Filter><FilterItems /></Filter><Calculations /><Aggregates
/><QueryProperties /></QuerySpecification><Query><Statement>with member
[Measures].[MyMeasure] as '[Free Test].currentmember.name'
member [Measures].[MyMeasure2] as '[Free Test].currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '[Free Test].currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
[Measures].[MyMeasure3],
[Measures].[MyMeasure4]} on columns,
{[Free Test].members} on
rows from [OLAP Test Cube]</Statement><ParameterDefinitions
/></Query></QueryDefinition></rd:MdxQuery>
</Query>
<Fields>
<Field Name="Free_Test">
<rd:TypeName>System.String</rd:TypeName>
<DataField><?xml version="1.0" encoding="utf-8"?><Field
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Level"
UniqueName="[Dim Free Test].[Free Test].[Free Test]" /></DataField>
</Field>
<Field Name="MyMeasure">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField><?xml version="1.0" encoding="utf-8"?><Field
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure"
UniqueName="[Measures].[MyMeasure]" /></DataField>
</Field>
<Field Name="MyMeasure2">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField><?xml version="1.0" encoding="utf-8"?><Field
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure"
UniqueName="[Measures].[MyMeasure2]" /></DataField>
</Field>
<Field Name="MyMeasure3">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField><?xml version="1.0" encoding="utf-8"?><Field
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure"
UniqueName="[Measures].[MyMeasure3]" /></DataField>
</Field>
<Field Name="MyMeasure4">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField><?xml version="1.0" encoding="utf-8"?><Field
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" xsi:type="Measure"
UniqueName="[Measures].[MyMeasure4]" /></DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
"Dinesh Patel" wrote:
> Hi Kaisa,
> I want same what steve want. I have two column in my report. First is
> Dimension and second is measure. I have tryed what you suggested but not
> working. can you please check where i am doing wrong?
> I have two dimension:
> [Dim Station].[Station Name].[Station Name]
> [Dim Free Test].[Free Test].[Free Test]
> and one measure:
> [Measures].[Total Test Count]
> My Cube Name is:
> [OLAP Test Cube]
> I want [Dim Station] and [Dim Free Test] dimension dynamically. I don't want
> any filter if it require for Dynamic queyr then ok.
> when i execute following query in Query builder then it giving me error. can
> you please check this where i am wrong?
> Query:
> with member [Measures].[MyMeasure] as '[Dim Station].currentmember.name'
> member [Measures].[MyMeasure2] as '[Dim Station].currentmember.Level.Ordinal'
> member [Measures].[MyMeasure3] as '[Dim Station].currentmember.UniqueName'
> member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
> select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
> [Measures].[MyMeasure3],
> [Measures].[MyMeasure4]}
> on columns, {filter([Dim Station].members, [Measures].[Total Test Count]]>
> 0) } on
> rows from [OLAP Test Cube]
> Regards,
> Dinesh Patel
> "Kaisa M. Lindahl Lervik" wrote:
> > The trick is to return the dimension names as measures, and then use the
> > measures in your report instead of the dimension names.
> >
> > You start out with a usual query, and add Currentmember.Name,
> > currentmember.Level.Ordinal and Currentmember.UniqueName as measures.
> >
> > with member [Measures].[MyMeasure] as '[Store].currentmember.name' member
> > [Measures].[MyMeasure2] as '[Store].currentmember.Level.Ordinal' member
> > [Measures].[MyMeasure3] as '[Store].currentmember.UniqueName'
> >
> > Then add x number of columns, for measures that you want to reuse.
> > member [Measures].[MyMeasure4] as '[Measures].[Unit Sales]' member
> > [Measures].[MyMeasure5] as '[Measures].[Store Cost]' member
> > [Measures].[MyMeasure6] as '[Measures].[Store Sales]'
> >
> > Then select all the measures (both the dimension name ones, and the ones
> > based on the old measures)
> > I added a filter to mine, you don't need to do that.
> >
> > The resulting MDX query looks like this:
> >
> > with member [Measures].[MyMeasure] as '[Store].currentmember.name' member
> > [Measures].[MyMeasure2] as '[Store].currentmember.Level.Ordinal' member
> > [Measures].[MyMeasure3] as '[Store].currentmember.UniqueName' member
> > [Measures].[MyMeasure4] as '[Measures].[Unit Sales]' member
> > [Measures].[MyMeasure5] as '[Measures].[Store Cost]' member
> > [Measures].[MyMeasure6] as '[Measures].[Store Sales]' select
> > {[Measures].[MyMeasure], [Measures].[MyMeasure2], [Measures].[MyMeasure3],
> > [Measures].[MyMeasure4], [Measures].[MyMeasure5], [Measures].[MyMeasure6]}
> > on columns, {filter([Store].members, [Measures].[Store Cost]> 25000) } on
> > rows from [Sales]
> >
> >
> > After you've generated all the fields, change it as usual to a parameterized
> > query:
> >
> > ="with member [Measures].[MyMeasure] as '[" & Parameters!Dimension.Value &
> > "].currentmember.name' member [Measures].[MyMeasure2] as '[" &
> > Parameters!Dimension.Value & "].currentmember.Level.Ordinal' member
> > [Measures].[MyMeasure3] as '[" & Parameters!Dimension.Value &
> > "].currentmember.UniqueName' member [Measures].[MyMeasure4] as
> > '[Measures].[Unit Sales]' member [Measures].[MyMeasure5] as
> > '[Measures].[Store Cost]' member [Measures].[MyMeasure6] as
> > '[Measures].[Store Sales]' select {[Measures].[MyMeasure],
> > [Measures].[MyMeasure2], [Measures].[MyMeasure3], [Measures].[MyMeasure4],
> > [Measures].[MyMeasure5], [Measures].[MyMeasure6]} on columns, {filter([" &
> > Parameters!Dimension.Value & "].members," & Parameters!Measure.Value & " > "
> > & Parameters!Amount.Value & ") } on rows from [Sales]"
> >
> > And now you just add the dimensions you need as a set of report parameters
> > ( drop down list with all the dimension unique names) and other parameters
> > you need, and try it out. :)
> >
> > The drawback is that it gets kind of flat, but you can do quite a lot of
> > tricks with formating the different levels.
> >
> > This is a sample that works against Foodmart2000:
> > <?xml version="1.0" encoding="utf-8"?>
> >
> > <Report
> > xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
> > xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> >
> > <rd:GridSpacing>0.25cm</rd:GridSpacing>
> >
> > <RightMargin>2.5cm</RightMargin>
> >
> > <Body>
> >
> > <ReportItems>
> >
> > <Textbox Name="textbox4">
> >
> > <Style>
> >
> > <PaddingLeft>2pt</PaddingLeft>
> >
> > <PaddingBottom>2pt</PaddingBottom>
> >
> > <PaddingTop>2pt</PaddingTop>
> >
> > <PaddingRight>2pt</PaddingRight>
> >
> > </Style>
> >
> > <ZIndex>1</ZIndex>
> >
> > <Top>0.25cm</Top>
> >
> > <rd:DefaultName>textbox4</rd:DefaultName>
> >
> > <Height>0.63492cm</Height>
> >
> > <Width>13.25cm</Width>
> >
> > <CanGrow>true</CanGrow>
> >
> > <Value>="Generic report based on " & Parameters!Dimension.Value</Value>
> >
> > </Textbox>
> >
> > <Table Name="table1">
> >
> > <Height>1.90476cm</Height>
> >
> > <Style />
> >
> > <Header>
> >
> > <TableRows>
> >
> > <TableRow>
> >
> > <Height>0.63492cm</Height>
> >
> > <TableCells>
> >
> > <TableCell>
> >
> > <ReportItems>
> >
> > <Textbox Name="textbox1">
> >
> > <Style>
> >
> > <PaddingLeft>2pt</PaddingLeft>
> >
> > <BorderStyle>
> >
> > <Bottom>Solid</Bottom>
> >
> > <Top>Solid</Top>
> >
> > <Left>Solid</Left>
> >
> > </BorderStyle>
> >
> > <PaddingBottom>2pt</PaddingBottom>
> >
> > <PaddingTop>2pt</PaddingTop>
> >
> > <PaddingRight>2pt</PaddingRight>
> >
> > <FontWeight>700</FontWeight>
> >
> > </Style>
> >
> > <ZIndex>11</ZIndex>
> >
> > <rd:DefaultName>textbox1</rd:DefaultName>
> >
> > <CanGrow>true</CanGrow>
> >
> > <Value>= Parameters!Dimension.Value</Value>
> >
> > </Textbox>
> >
> > </ReportItems>
> >
> > </TableCell>
> >
> > <TableCell>
> >
> > <ReportItems>
> >
> > <Textbox Name="textbox2">
> >
> > <Style>
> >
> > <PaddingLeft>2pt</PaddingLeft>
> >
> > <BorderStyle>
> >
> > <Bottom>Solid</Bottom>
> >
> > <Top>Solid</Top>
> >
> > <Right>Solid</Right>
> >
> > </BorderStyle>
> >
> > <TextAlign>Right</TextAlign>
> >
> > <PaddingBottom>2pt</PaddingBottom>
> >
> > <PaddingTop>2pt</PaddingTop>
> >
> > <PaddingRight>2pt</PaddingRight>
> >
> > <FontWeight>700</FontWeight>
> >
> > </Style>
> >
> > <ZIndex>10</ZIndex>
> >
> > <rd:DefaultName>textbox2</rd:DefaultName>
> >
> > <CanGrow>true</CanGrow>
> >
> > <Value>Unit sales</Value>
> >
> > </Textbox>
> >
> > </ReportItems>
> >
> > </TableCell>
> >
> > <TableCell>
> >
> > <ReportItems>
> >
> > <Textbox Name="textbox3">
> >
> > <Style>
> >
> > <PaddingLeft>2pt</PaddingLeft>
> >
> > <BorderStyle>
> >
> > <Bottom>Solid</Bottom>
> >
> > <Top>Solid</Top>
> >
> > <Right>Solid</ight>
> >
> > </BorderStyle>
> >
> > <TextAlign>Right</TextAlign>
> >
> > <PaddingBottom>2pt</PaddingBottom>
> >
> > <PaddingTop>2pt</PaddingTop>
> >
> > <PaddingRight>2pt</PaddingRight>
> >
> > <FontWeight>700</FontWeight>
> >
> > </Style>
> >
> > <ZIndex>9</ZIndex>
> >
> > <rd:DefaultName>textbox3</rd:DefaultName>
> >
> > <CanGrow>true</CanGrow>
> >
> > <Value>Store Cost</Value>
> >
> > </Textbox>
> >
> > </ReportItems>
> >
> > </TableCell>
> >
> > <TableCell>
> >
> > <ReportItems>
> >
> > <Textbox Name="textbox10">
> >
> > <Style>
> >|||problem was solved.
First execute following query and design the report.
with member [Measures].[MyMeasure] as '[Free Test].currentmember.name'
member [Measures].[MyMeasure2] as '[Free Test].currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '[Free Test].currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
[Measures].[MyMeasure3], [Measures].[MyMeasure4]} on columns, {[Free
Test].members} on
rows from [OLAP Test Cube]
and then Edit Dataset using (â?¦) button and paste Dynamic query on Query
String Textbox and click ok.
="with member [Measures].[MyMeasure] as '" & Parameters!Dimension.Value &
".currentmember.name'
member [Measures].[MyMeasure2] as '" & Parameters!Dimension.Value &
".currentmember.Level.Ordinal'
member [Measures].[MyMeasure3] as '" & Parameters!Dimension.Value &
".currentmember.UniqueName'
member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
[Measures].[MyMeasure3], [Measures].[MyMeasure4]}
on columns, {" & Parameters!Dimension.Value & ".members} on
rows from [OLAP Test Cube]"
Regards,
Dinesh Patel
"Dinesh Patel" wrote:
> I am working with AS2005 and RS2005 I have created one report also. can you
> please check this code.
> I am trying to change query to below but giving error:
> ="with member [Measures].[MyMeasure] as '" & Parameters!Dimension.Value &
> ".currentmember.name'
> member [Measures].[MyMeasure2] as '" & Parameters!Dimension.Value &
> ".currentmember.Level.Ordinal'
> member [Measures].[MyMeasure3] as '" & Parameters!Dimension.Value &
> ".currentmember.UniqueName'
> member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
> select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
> [Measures].[MyMeasure3],
> [Measures].[MyMeasure4]}
> on columns, {" & Parameters!Dimension.Value & ".members} on
> rows from [OLAP Test Cube]"
>
> my report code is below:
>
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <DataSources>
> <DataSource Name="SLCTestDB">
> <DataSourceReference>SLCTestDB</DataSourceReference>
> <rd:DataSourceID>44e60797-bd99-4681-9499-7e848244229e</rd:DataSourceID>
> </DataSource>
> </DataSources>
> <BottomMargin>1in</BottomMargin>
> <RightMargin>1in</RightMargin>
> <ReportParameters>
> <ReportParameter Name="Dimension">
> <DataType>String</DataType>
> <DefaultValue>
> <Values>
> <Value>[Free Test]</Value>
> </Values>
> </DefaultValue>
> <Prompt>Dimension</Prompt>
> <ValidValues>
> <ParameterValues>
> <ParameterValue>
> <Value>[Station Name]</Value>
> <Label>station</Label>
> </ParameterValue>
> <ParameterValue>
> <Value>[Free Test]</Value>
> <Label>Free</Label>
> </ParameterValue>
> </ParameterValues>
> </ValidValues>
> </ReportParameter>
> </ReportParameters>
> <rd:DrawGrid>true</rd:DrawGrid>
> <InteractiveWidth>8.5in</InteractiveWidth>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <Body>
> <ReportItems>
> <Textbox Name="textbox1">
> <rd:DefaultName>textbox1</rd:DefaultName>
> <ZIndex>1</ZIndex>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <FontFamily>Tahoma</FontFamily>
> <FontWeight>700</FontWeight>
> <FontSize>20pt</FontSize>
> <Color>SteelBlue</Color>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.36in</Height>
> <Value>Report3</Value>
> </Textbox>
> <Table Name="table1">
> <DataSetName>SLCTestDB</DataSetName>
> <Top>0.36in</Top>
> <TableGroups>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="MyMeasure">
> <rd:DefaultName>MyMeasure</rd:DefaultName>
> <ZIndex>3</ZIndex>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <FontFamily>Tahoma</FontFamily>
> <FontWeight>700</FontWeight>
> <BorderColor>
> <Default>LightGrey</Default>
> </BorderColor>
> <BackgroundColor>#6e9eca</BackgroundColor>
> <Color>White</Color>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields!MyMeasure.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox5">
> <rd:DefaultName>textbox5</rd:DefaultName>
> <ZIndex>2</ZIndex>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <FontFamily>Tahoma</FontFamily>
> <FontWeight>700</FontWeight>
> <BorderColor>
> <Default>LightGrey</Default>
> </BorderColor>
> <BackgroundColor>#6e9eca</BackgroundColor>
> <Color>White</Color>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>
> </Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> <Height>0.21in</Height>
> </TableRow>
> </TableRows>
> </Header>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields!MyMeasure.Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <Grouping Name="table1_MyMeasure">
> <GroupExpressions>
> <GroupExpression>=Fields!MyMeasure.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </TableGroup>
> </TableGroups>
> <Width>4.5in</Width>
> <Details>
> <TableRows>
> <TableRow>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox3">
> <rd:DefaultName>textbox3</rd:DefaultName>
> <ZIndex>1</ZIndex>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <FontFamily>Tahoma</FontFamily>
> <BorderColor>
> <Default>LightGrey</Default>
> </BorderColor>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>
> </Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="MyMeasure4">
> <rd:DefaultName>MyMeasure4</rd:DefaultName>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <FontFamily>Tahoma</FontFamily>
> <BorderColor>
> <Default>LightGrey</Default>
> </BorderColor>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields!MyMeasure4.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> <Height>0.21in</Height>
> </TableRow>
> </TableRows>
> </Details>
> <Header>
> <TableRows>
> <TableRow>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox2">
> <rd:DefaultName>textbox2</rd:DefaultName>
> <ZIndex>5</ZIndex>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <FontFamily>Tahoma</FontFamily>
> <FontWeight>700</FontWeight>
> <FontSize>11pt</FontSize>
> <BorderColor>
> <Default>LightGrey</Default>
> </BorderColor>
> <BackgroundColor>SteelBlue</BackgroundColor>
> <Color>White</Color>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>My Measure</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox4">
> <rd:DefaultName>textbox4</rd:DefaultName>
> <ZIndex>4</ZIndex>
> <Style>
> <BorderStyle>
> <Default>Solid</Default>
> </BorderStyle>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <FontFamily>Tahoma</FontFamily>
> <FontWeight>700</FontWeight>
> <FontSize>11pt</FontSize>
> <BorderColor>
> <Default>LightGrey</Default>
> </BorderColor>
> <BackgroundColor>SteelBlue</BackgroundColor>
> <Color>White</Color>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>My Measure4</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> <Height>0.22in</Height>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Header>
> <TableColumns>
> <TableColumn>
> <Width>2.875in</Width>
> </TableColumn>
> <TableColumn>
> <Width>1.625in</Width>
> </TableColumn>
> </TableColumns>
> </Table>
> </ReportItems>
> <Height>1in</Height>
> </Body>
> <rd:ReportID>be7b0e20-a01a-40a4-8c2a-23748db01c34</rd:ReportID>
> <LeftMargin>1in</LeftMargin>
> <DataSets>
> <DataSet Name="SLCTestDB">
> <Query>|||I'm sorry I haven't replied to this earlier, but I'm happy you've solved it.
I guess I was assuming you knew you had to use a non dynamic query to build
the fields correctly. I'll remember to add this if I give advice on dynamic
mdx again.
Kaisa M. Lindahl Lervik
"Dinesh Patel" <DineshPatel@.discussions.microsoft.com> wrote in message
news:D1FC2757-F590-4881-924C-D01BE05F454F@.microsoft.com...
> problem was solved.
> First execute following query and design the report.
> with member [Measures].[MyMeasure] as '[Free Test].currentmember.name'
> member [Measures].[MyMeasure2] as '[Free
> Test].currentmember.Level.Ordinal'
> member [Measures].[MyMeasure3] as '[Free Test].currentmember.UniqueName'
> member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
> select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
> [Measures].[MyMeasure3], [Measures].[MyMeasure4]} on columns, {[Free
> Test].members} on
> rows from [OLAP Test Cube]
> and then Edit Dataset using (.) button and paste Dynamic query on Query
> String Textbox and click ok.
> ="with member [Measures].[MyMeasure] as '" & Parameters!Dimension.Value &
> ".currentmember.name'
> member [Measures].[MyMeasure2] as '" & Parameters!Dimension.Value &
> ".currentmember.Level.Ordinal'
> member [Measures].[MyMeasure3] as '" & Parameters!Dimension.Value &
> ".currentmember.UniqueName'
> member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
> select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
> [Measures].[MyMeasure3], [Measures].[MyMeasure4]}
> on columns, {" & Parameters!Dimension.Value & ".members} on
> rows from [OLAP Test Cube]"
> Regards,
> Dinesh Patel
>
> "Dinesh Patel" wrote:
>> I am working with AS2005 and RS2005 I have created one report also. can
>> you
>> please check this code.
>> I am trying to change query to below but giving error:
>> ="with member [Measures].[MyMeasure] as '" & Parameters!Dimension.Value &
>> ".currentmember.name'
>> member [Measures].[MyMeasure2] as '" & Parameters!Dimension.Value &
>> ".currentmember.Level.Ordinal'
>> member [Measures].[MyMeasure3] as '" & Parameters!Dimension.Value &
>> ".currentmember.UniqueName'
>> member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
>> select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
>> [Measures].[MyMeasure3],
>> [Measures].[MyMeasure4]}
>> on columns, {" & Parameters!Dimension.Value & ".members} on
>> rows from [OLAP Test Cube]"
>>
>> my report code is below:
>>
>> <?xml version="1.0" encoding="utf-8"?>
>> <Report
>> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
>> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
>> <DataSources>
>> <DataSource Name="SLCTestDB">
>> <DataSourceReference>SLCTestDB</DataSourceReference>
>> <rd:DataSourceID>44e60797-bd99-4681-9499-7e848244229e</rd:DataSourceID>
>> </DataSource>
>> </DataSources>
>> <BottomMargin>1in</BottomMargin>
>> <RightMargin>1in</RightMargin>
>> <ReportParameters>
>> <ReportParameter Name="Dimension">
>> <DataType>String</DataType>
>> <DefaultValue>
>> <Values>
>> <Value>[Free Test]</Value>
>> </Values>
>> </DefaultValue>
>> <Prompt>Dimension</Prompt>
>> <ValidValues>
>> <ParameterValues>
>> <ParameterValue>
>> <Value>[Station Name]</Value>
>> <Label>station</Label>
>> </ParameterValue>
>> <ParameterValue>
>> <Value>[Free Test]</Value>
>> <Label>Free</Label>
>> </ParameterValue>
>> </ParameterValues>
>> </ValidValues>
>> </ReportParameter>
>> </ReportParameters>
>> <rd:DrawGrid>true</rd:DrawGrid>
>> <InteractiveWidth>8.5in</InteractiveWidth>
>> <rd:SnapToGrid>true</rd:SnapToGrid>
>> <Body>
>> <ReportItems>
>> <Textbox Name="textbox1">
>> <rd:DefaultName>textbox1</rd:DefaultName>
>> <ZIndex>1</ZIndex>
>> <Style>
>> <PaddingLeft>2pt</PaddingLeft>
>> <PaddingBottom>2pt</PaddingBottom>
>> <FontFamily>Tahoma</FontFamily>
>> <FontWeight>700</FontWeight>
>> <FontSize>20pt</FontSize>
>> <Color>SteelBlue</Color>
>> <PaddingRight>2pt</PaddingRight>
>> <PaddingTop>2pt</PaddingTop>
>> </Style>
>> <CanGrow>true</CanGrow>
>> <Height>0.36in</Height>
>> <Value>Report3</Value>
>> </Textbox>
>> <Table Name="table1">
>> <DataSetName>SLCTestDB</DataSetName>
>> <Top>0.36in</Top>
>> <TableGroups>
>> <TableGroup>
>> <Header>
>> <TableRows>
>> <TableRow>
>> <TableCells>
>> <TableCell>
>> <ReportItems>
>> <Textbox Name="MyMeasure">
>> <rd:DefaultName>MyMeasure</rd:DefaultName>
>> <ZIndex>3</ZIndex>
>> <Style>
>> <BorderStyle>
>> <Default>Solid</Default>
>> </BorderStyle>
>> <PaddingLeft>2pt</PaddingLeft>
>> <PaddingBottom>2pt</PaddingBottom>
>> <FontFamily>Tahoma</FontFamily>
>> <FontWeight>700</FontWeight>
>> <BorderColor>
>> <Default>LightGrey</Default>
>> </BorderColor>
>> <BackgroundColor>#6e9eca</BackgroundColor>
>> <Color>White</Color>
>> <PaddingRight>2pt</PaddingRight>
>> <PaddingTop>2pt</PaddingTop>
>> </Style>
>> <CanGrow>true</CanGrow>
>> <Value>=Fields!MyMeasure.Value</Value>
>> </Textbox>
>> </ReportItems>
>> </TableCell>
>> <TableCell>
>> <ReportItems>
>> <Textbox Name="textbox5">
>> <rd:DefaultName>textbox5</rd:DefaultName>
>> <ZIndex>2</ZIndex>
>> <Style>
>> <BorderStyle>
>> <Default>Solid</Default>
>> </BorderStyle>
>> <PaddingLeft>2pt</PaddingLeft>
>> <PaddingBottom>2pt</PaddingBottom>
>> <FontFamily>Tahoma</FontFamily>
>> <FontWeight>700</FontWeight>
>> <BorderColor>
>> <Default>LightGrey</Default>
>> </BorderColor>
>> <BackgroundColor>#6e9eca</BackgroundColor>
>> <Color>White</Color>
>> <PaddingRight>2pt</PaddingRight>
>> <PaddingTop>2pt</PaddingTop>
>> </Style>
>> <CanGrow>true</CanGrow>
>> <Value>
>> </Value>
>> </Textbox>
>> </ReportItems>
>> </TableCell>
>> </TableCells>
>> <Height>0.21in</Height>
>> </TableRow>
>> </TableRows>
>> </Header>
>> <Sorting>
>> <SortBy>
>> <SortExpression>=Fields!MyMeasure.Value</SortExpression>
>> <Direction>Ascending</Direction>
>> </SortBy>
>> </Sorting>
>> <Grouping Name="table1_MyMeasure">
>> <GroupExpressions>
>> <GroupExpression>=Fields!MyMeasure.Value</GroupExpression>
>> </GroupExpressions>
>> </Grouping>
>> </TableGroup>
>> </TableGroups>
>> <Width>4.5in</Width>
>> <Details>
>> <TableRows>
>> <TableRow>
>> <TableCells>
>> <TableCell>
>> <ReportItems>
>> <Textbox Name="textbox3">
>> <rd:DefaultName>textbox3</rd:DefaultName>
>> <ZIndex>1</ZIndex>
>> <Style>
>> <BorderStyle>
>> <Default>Solid</Default>
>> </BorderStyle>
>> <PaddingLeft>2pt</PaddingLeft>
>> <PaddingBottom>2pt</PaddingBottom>
>> <FontFamily>Tahoma</FontFamily>
>> <BorderColor>
>> <Default>LightGrey</Default>
>> </BorderColor>
>> <PaddingRight>2pt</PaddingRight>
>> <PaddingTop>2pt</PaddingTop>
>> </Style>
>> <CanGrow>true</CanGrow>
>> <Value>
>> </Value>
>> </Textbox>
>> </ReportItems>
>> </TableCell>
>> <TableCell>
>> <ReportItems>
>> <Textbox Name="MyMeasure4">
>> <rd:DefaultName>MyMeasure4</rd:DefaultName>
>> <Style>
>> <BorderStyle>
>> <Default>Solid</Default>
>> </BorderStyle>
>> <PaddingLeft>2pt</PaddingLeft>
>> <PaddingBottom>2pt</PaddingBottom>
>> <FontFamily>Tahoma</FontFamily>
>> <BorderColor>
>> <Default>LightGrey</Default>
>> </BorderColor>
>> <PaddingRight>2pt</PaddingRight>
>> <PaddingTop>2pt</PaddingTop>
>> </Style>
>> <CanGrow>true</CanGrow>
>> <Value>=Fields!MyMeasure4.Value</Value>
>> </Textbox>
>> </ReportItems>
>> </TableCell>
>> </TableCells>
>> <Height>0.21in</Height>
>> </TableRow>
>> </TableRows>
>> </Details>
>> <Header>
>> <TableRows>
>> <TableRow>
>> <TableCells>
>> <TableCell>
>> <ReportItems>
>> <Textbox Name="textbox2">
>> <rd:DefaultName>textbox2</rd:DefaultName>
>> <ZIndex>5</ZIndex>
>> <Style>
>> <BorderStyle>
>> <Default>Solid</Default>
>> </BorderStyle>
>> <TextAlign>Right</TextAlign>
>> <PaddingLeft>2pt</PaddingLeft>
>> <PaddingBottom>2pt</PaddingBottom>
>> <FontFamily>Tahoma</FontFamily>
>> <FontWeight>700</FontWeight>
>> <FontSize>11pt</FontSize>
>> <BorderColor>
>> <Default>LightGrey</Default>
>> </BorderColor>
>> <BackgroundColor>SteelBlue</BackgroundColor>
>> <Color>White</Color>
>> <PaddingRight>2pt</PaddingRight>
>> <PaddingTop>2pt</PaddingTop>
>> </Style>
>> <CanGrow>true</CanGrow>
>> <Value>My Measure</Value>
>> </Textbox>
>> </ReportItems>
>> </TableCell>
>> <TableCell>
>> <ReportItems>
>> <Textbox Name="textbox4">
>> <rd:DefaultName>textbox4</rd:DefaultName>
>> <ZIndex>4</ZIndex>
>> <Style>
>> <BorderStyle>
>> <Default>Solid</Default>
>> </BorderStyle>
>> <TextAlign>Right</TextAlign>
>> <PaddingLeft>2pt</PaddingLeft>
>> <PaddingBottom>2pt</PaddingBottom>
>> <FontFamily>Tahoma</FontFamily>
>> <FontWeight>700</FontWeight>
>> <FontSize>11pt</FontSize>
>> <BorderColor>
>> <Default>LightGrey</Default>
>> </BorderColor>
>> <BackgroundColor>SteelBlue</BackgroundColor>
>> <Color>White</Color>
>> <PaddingRight>2pt</PaddingRight>
>> <PaddingTop>2pt</PaddingTop>
>> </Style>
>> <CanGrow>true</CanGrow>
>> <Value>My Measure4</Value>
>> </Textbox>
>> </ReportItems>
>> </TableCell>
>> </TableCells>
>> <Height>0.22in</Height>
>> </TableRow>
>> </TableRows>
>> <RepeatOnNewPage>true</RepeatOnNewPage>
>> </Header>
>> <TableColumns>
>> <TableColumn>
>> <Width>2.875in</Width>
>> </TableColumn>
>> <TableColumn>
>> <Width>1.625in</Width>
>> </TableColumn>
>> </TableColumns>
>> </Table>
>> </ReportItems>
>> <Height>1in</Height>
>> </Body>
>> <rd:ReportID>be7b0e20-a01a-40a4-8c2a-23748db01c34</rd:ReportID>
>> <LeftMargin>1in</LeftMargin>
>> <DataSets>
>> <DataSet Name="SLCTestDB">
>> <Query>|||I am not expert, i received this solution from net.
Dinesh Patel
"Kaisa M. Lindahl Lervik" wrote:
> I'm sorry I haven't replied to this earlier, but I'm happy you've solved it.
> I guess I was assuming you knew you had to use a non dynamic query to build
> the fields correctly. I'll remember to add this if I give advice on dynamic
> mdx again.
> Kaisa M. Lindahl Lervik
>
> "Dinesh Patel" <DineshPatel@.discussions.microsoft.com> wrote in message
> news:D1FC2757-F590-4881-924C-D01BE05F454F@.microsoft.com...
> > problem was solved.
> >
> > First execute following query and design the report.
> >
> > with member [Measures].[MyMeasure] as '[Free Test].currentmember.name'
> > member [Measures].[MyMeasure2] as '[Free
> > Test].currentmember.Level.Ordinal'
> > member [Measures].[MyMeasure3] as '[Free Test].currentmember.UniqueName'
> > member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
> > select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
> > [Measures].[MyMeasure3], [Measures].[MyMeasure4]} on columns, {[Free
> > Test].members} on
> > rows from [OLAP Test Cube]
> >
> > and then Edit Dataset using (.) button and paste Dynamic query on Query
> > String Textbox and click ok.
> >
> > ="with member [Measures].[MyMeasure] as '" & Parameters!Dimension.Value &
> > ".currentmember.name'
> >
> > member [Measures].[MyMeasure2] as '" & Parameters!Dimension.Value &
> > ".currentmember.Level.Ordinal'
> > member [Measures].[MyMeasure3] as '" & Parameters!Dimension.Value &
> > ".currentmember.UniqueName'
> > member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
> > select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
> > [Measures].[MyMeasure3], [Measures].[MyMeasure4]}
> > on columns, {" & Parameters!Dimension.Value & ".members} on
> > rows from [OLAP Test Cube]"
> >
> > Regards,
> > Dinesh Patel
> >
> >
> > "Dinesh Patel" wrote:
> >
> >> I am working with AS2005 and RS2005 I have created one report also. can
> >> you
> >> please check this code.
> >>
> >> I am trying to change query to below but giving error:
> >>
> >> ="with member [Measures].[MyMeasure] as '" & Parameters!Dimension.Value &
> >> ".currentmember.name'
> >> member [Measures].[MyMeasure2] as '" & Parameters!Dimension.Value &
> >> ".currentmember.Level.Ordinal'
> >> member [Measures].[MyMeasure3] as '" & Parameters!Dimension.Value &
> >> ".currentmember.UniqueName'
> >> member [Measures].[MyMeasure4] as '[Measures].[Total Test Count]'
> >>
> >> select {[Measures].[MyMeasure], [Measures].[MyMeasure2],
> >> [Measures].[MyMeasure3],
> >> [Measures].[MyMeasure4]}
> >> on columns, {" & Parameters!Dimension.Value & ".members} on
> >> rows from [OLAP Test Cube]"
> >>
> >>
> >>
> >> my report code is below:
> >>
> >>
> >> <?xml version="1.0" encoding="utf-8"?>
> >> <Report
> >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> >> <DataSources>
> >> <DataSource Name="SLCTestDB">
> >> <DataSourceReference>SLCTestDB</DataSourceReference>
> >>
> >> <rd:DataSourceID>44e60797-bd99-4681-9499-7e848244229e</rd:DataSourceID>
> >> </DataSource>
> >> </DataSources>
> >> <BottomMargin>1in</BottomMargin>
> >> <RightMargin>1in</RightMargin>
> >> <ReportParameters>
> >> <ReportParameter Name="Dimension">
> >> <DataType>String</DataType>
> >> <DefaultValue>
> >> <Values>
> >> <Value>[Free Test]</Value>
> >> </Values>
> >> </DefaultValue>
> >> <Prompt>Dimension</Prompt>
> >> <ValidValues>
> >> <ParameterValues>
> >> <ParameterValue>
> >> <Value>[Station Name]</Value>
> >> <Label>station</Label>
> >> </ParameterValue>
> >> <ParameterValue>
> >> <Value>[Free Test]</Value>
> >> <Label>Free</Label>
> >> </ParameterValue>
> >> </ParameterValues>
> >> </ValidValues>
> >> </ReportParameter>
> >> </ReportParameters>
> >> <rd:DrawGrid>true</rd:DrawGrid>
> >> <InteractiveWidth>8.5in</InteractiveWidth>
> >> <rd:SnapToGrid>true</rd:SnapToGrid>
> >> <Body>
> >> <ReportItems>
> >> <Textbox Name="textbox1">
> >> <rd:DefaultName>textbox1</rd:DefaultName>
> >> <ZIndex>1</ZIndex>
> >> <Style>
> >> <PaddingLeft>2pt</PaddingLeft>
> >> <PaddingBottom>2pt</PaddingBottom>
> >> <FontFamily>Tahoma</FontFamily>
> >> <FontWeight>700</FontWeight>
> >> <FontSize>20pt</FontSize>
> >> <Color>SteelBlue</Color>
> >> <PaddingRight>2pt</PaddingRight>
> >> <PaddingTop>2pt</PaddingTop>
> >> </Style>
> >> <CanGrow>true</CanGrow>
> >> <Height>0.36in</Height>
> >> <Value>Report3</Value>
> >> </Textbox>
> >> <Table Name="table1">
> >> <DataSetName>SLCTestDB</DataSetName>
> >> <Top>0.36in</Top>
> >> <TableGroups>
> >> <TableGroup>
> >> <Header>
> >> <TableRows>
> >> <TableRow>
> >> <TableCells>
> >> <TableCell>
> >> <ReportItems>
> >> <Textbox Name="MyMeasure">
> >> <rd:DefaultName>MyMeasure</rd:DefaultName>
> >> <ZIndex>3</ZIndex>
> >> <Style>
> >> <BorderStyle>
> >> <Default>Solid</Default>
> >> </BorderStyle>
> >> <PaddingLeft>2pt</PaddingLeft>
> >> <PaddingBottom>2pt</PaddingBottom>
> >> <FontFamily>Tahoma</FontFamily>
> >> <FontWeight>700</FontWeight>
> >> <BorderColor>
> >> <Default>LightGrey</Default>
> >> </BorderColor>
> >> <BackgroundColor>#6e9eca</BackgroundColor>
> >> <Color>White</Color>
> >> <PaddingRight>2pt</PaddingRight>
> >> <PaddingTop>2pt</PaddingTop>
> >> </Style>
> >> <CanGrow>true</CanGrow>
> >> <Value>=Fields!MyMeasure.Value</Value>
> >> </Textbox>
> >> </ReportItems>
> >> </TableCell>
> >> <TableCell>
> >> <ReportItems>
> >> <Textbox Name="textbox5">
> >> <rd:DefaultName>textbox5</rd:DefaultName>
> >> <ZIndex>2</ZIndex>
> >> <Style>
> >> <BorderStyle>
> >> <Default>Solid</Default>
> >> </BorderStyle>
> >> <PaddingLeft>2pt</PaddingLeft>
> >> <PaddingBottom>2pt</PaddingBottom>
> >> <FontFamily>Tahoma</FontFamily>
> >> <FontWeight>700</FontWeight>
> >> <BorderColor>
> >> <Default>LightGrey</Default>
> >> </BorderColor>
> >> <BackgroundColor>#6e9eca</BackgroundColor>
> >> <Color>White</Color>
> >> <PaddingRight>2pt</PaddingRight>
> >> <PaddingTop>2pt</PaddingTop>
> >> </Style>
> >> <CanGrow>true</CanGrow>
> >> <Value>
> >> </Value>
> >> </Textbox>
> >> </ReportItems>
> >> </TableCell>
> >> </TableCells>
> >> <Height>0.21in</Height>
> >> </TableRow>
> >> </TableRows>
> >> </Header>
> >> <Sorting>
> >> <SortBy>
> >> <SortExpression>=Fields!MyMeasure.Value</SortExpression>
> >> <Direction>Ascending</Direction>
> >> </SortBy>
> >> </Sorting>
> >> <Grouping Name="table1_MyMeasure">
> >> <GroupExpressions>
> >>
> >> <GroupExpression>=Fields!MyMeasure.Value</GroupExpression>
> >> </GroupExpressions>
> >> </Grouping>
> >> </TableGroup>
> >> </TableGroups>
> >> <Width>4.5in</Width>
> >> <Details>
> >> <TableRows>
> >> <TableRow>
> >> <TableCells>
> >> <TableCell>
> >> <ReportItems>
> >> <Textbox Name="textbox3">
> >> <rd:DefaultName>textbox3</rd:DefaultName>
> >> <ZIndex>1</ZIndex>
> >> <Style>
> >> <BorderStyle>
> >> <Default>Solid</Default>
> >> </BorderStyle>
> >> <PaddingLeft>2pt</PaddingLeft>
> >> <PaddingBottom>2pt</PaddingBottom>
> >> <FontFamily>Tahoma</FontFamily>
> >> <BorderColor>
> >> <Default>LightGrey</Default>
> >> </BorderColor>
> >> <PaddingRight>2pt</PaddingRight>
> >> <PaddingTop>2pt</PaddingTop>
> >> </Style>
> >> <CanGrow>true</CanGrow>
> >> <Value>
> >> </Value>
> >> </Textbox>
> >> </ReportItems>
> >> </TableCell>
> >> <TableCell>
> >> <ReportItems>
> >> <Textbox Name="MyMeasure4">
> >> <rd:DefaultName>MyMeasure4</rd:DefaultName>
> >> <Style>
> >> <BorderStyle>
> >> <Default>Solid</Default>
> >> </BorderStyle>
> >> <PaddingLeft>2pt</PaddingLeft>
> >> <PaddingBottom>2pt</PaddingBottom>
> >> <FontFamily>Tahoma</FontFamily>
> >> <BorderColor>
> >> <Default>LightGrey</Default>
> >> </BorderColor>
> >> <PaddingRight>2pt</PaddingRight>
> >> <PaddingTop>2pt</PaddingTop>
> >> </Style>
> >> <CanGrow>true</CanGrow>
> >> <Value>=Fields!MyMeasure4.Value</Value>
> >> </Textbox>
> >> </ReportItems>
> >> </TableCell>
> >> </TableCells>
> >> <Height>0.21in</Height>
> >> </TableRow>
> >> </TableRows>
> >> </Details>
> >> <Header>
> >> <TableRows>
> >> <TableRow>
> >> <TableCells>
> >> <TableCell>
> >> <ReportItems>
> >> <Textbox Name="textbox2">
> >> <rd:DefaultName>textbox2</rd:DefaultName>
> >> <ZIndex>5</ZIndex>
> >> <Style>
> >> <BorderStyle>
> >> <Default>Solid</Default>
> >> </BorderStyle>
> >> <TextAlign>Right</TextAlign>
> >> <PaddingLeft>2pt</PaddingLeft>
> >> <PaddingBottom>2pt</PaddingBottom>
> >> <FontFamily>Tahoma</FontFamily>
> >> <FontWeight>700</FontWeight>
> >> <FontSize>11pt</FontSize>
> >> <BorderColor>
> >> <Default>LightGrey</Default>
> >> </BorderColor>
> >> <BackgroundColor>SteelBlue</BackgroundColor>
> >> <Color>White</Color>
> >> <PaddingRight>2pt</PaddingRight>
> >> <PaddingTop>2pt</PaddingTop>
> >> </Style>
> >> <CanGrow>true</CanGrow>
> >> <Value>My Measure</Value>
> >> </Textbox>
> >> </ReportItems>
> >> </TableCell>
> >> <TableCell>