|
|
You're in the
sql server reporting services
group:Custom Column names in MDX queries for Reporting services 2005
sql server reporting services:
Hi Guys,
I am trying to create a dynamic report using RS 2005 with AS2005. My report has fixed columns whose names and values are substitued at runtime with data from queries. I am actually facing the problem as described in this topic. http://groups.google.co.uk/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/ef105b154e83946/f898d0475958720c?lnk=st&q=%22reporting+services%22+adomd&rnum=20&hl=en#f898d0475958720c There is alreay an answer in that topic, but what i want is a bit more than that. In my report data is not just populated from one dimension, it's actually populated from more than one dimensions and levels in the same query. If this was in T-SQL, i would have simply given column alias for each like "CustomerName" AS COL1, "CustomerPhone" AS COL2, etc. Does anyone know how to achieve this in MDX. ie how to give aliases for MDX columns for reporting services 2005? Thanks Raj Chidipudi
Hi
I've also got a very similar problem to this. If you could alias the fields so that the fields are bound on the report to known field names say Field1, Field2, Field3 etc and then in the MDX query you could have ClanName AS Field1 (TSQL style)? Then, you can have any dataset with field aliases so that the fields on the report always point to a valid field in the MDX results. Given the following three dimensions and fact table, how can you pull out a MDX query where you can have: ClanName AS Field1, ClanID AS Field2, PlayerName AS FIeld3, PlayerID AS Field4, Score AS Field5.: dimClanPlayer ------------------------ dimClanPlayerPK ClanName ClanID PlayerName PlayerID dimContest ---------------- dimContestPK ContestName dimDateFK dimTimeFK factFrag ----------- dimClanPlayerFK dimContestFK Score Is this even possible using MDX? Jon [quoted text, click to view] On Oct 19, 11:56 am, "Raj Chidipudi" <chidip...@gmail.com> wrote:
> Hi Guys, > I am trying to create a dynamic report using RS 2005 with AS2005. My > report has fixed columns whose names and values are substitued at > runtime with data from queries. > > I am actually facing the problem as described in this topic.http://groups.google.co.uk/group/microsoft.public.sqlserver.reporting... > > There is alreay an answer in that topic, but what i want is a bit more > than that. > In my report data is not just populated from one dimension, it's > actually populated from more than one dimensions and levels in the same > query. > > If this was in T-SQL, i would have simply given column alias for each > like "CustomerName" AS COL1, "CustomerPhone" AS COL2, etc. > > Does anyone know how to achieve this in MDX. ie how to give aliases for > MDX columns for reporting services 2005? > > Thanks > Raj Chidipudi
Hehe, I knew there was something familiar about this question. :)
To elaborate a few things I've learned since answering that first thread: In Reporting Services 2005, there's added "support" for MDX queries. I say "support", as well, it's easier to do basic mdx stuff, but if you want to go dynamic, it's a complete pain... But there are tricks to solve it. 1) Instead of using the Analysis Services data source, create your data source as OLE DB, and connect to your AS 2005 server through that. 2) Now you have the old school query interface. No help, but also no mess. 3) Create your non dynamic query in the SQL Server Management studio, by connecting to your AS and work out the MDX 4) Copy paste your non dynamic query to create all your fields etc. 5) Make it dynamic by adding the =" and " and all the parameter names, like Dinesh explains. As to making the aliases, you need to create calculated measures. With member [Measures].[MyMeasure] as '[Measures].[NumberOfItemsSold] select {[Measures].[MyMeasure] } on columns, {[Dimension1].members} on Rows from [My Cube] You can't control the field names of the dimensions used on the rows, but you can make return the values of these as measures as well. Which means you can't use the row fields in your report, as that will fail, but you can still group on them. with member [Measures].[MyMeasure] as '[Free Test].currentmember.name' -> this gives you the name of the dimension's current member, ie the one you're on member [Measures].[MyMeasure2] as '[Free est].currentmember.Level.Ordinal' -> this gives you a number stating what level this member is on. The lower the number, the higher the member is in the hierarchy member [Measures].[MyMeasure3] as '[Free Test].currentmember.UniqueName' -> unique name is the full mdx identifier for the member, like [Country].[State].[City] (or rather [Country].[USA].[California].[Los Angeles]) As for the mdx, you need to figure out if it can be truly dynamic. If you want to use several dimensions and levels, all the combinations needs to be very similar, to make sure you don't have the wrong number of expected levels etc. You probably need to do some crossjoins, which will unfortunately mess up the currentmember.uniquename trick. Which could be solved by creating drill down dimensions in your OLAP cube. If you have one dimension called Customers and one dimension called Orders, and you want to see all orders of each customer, you should make a drill down dimension (?) called Customer Order, which you can use the UniqueName trick on. (As well as it's probably a lot faster than doing the crossjoins.) As for cube design, you need to check the microsoft.public.sqlserver.olap newsgroup. If you don't actually need to display data from several dimensions, just filter on them, you just add those filter criteria to the where clause in your mdx query or use the filter or except functions in the mdx.. With member [Measures].[MyMeasureAmount] as '[Measures].[NumberOfItemsSold] member [Measures].[MyMeasure] as '[Dimension1].currentmember.name' member [Measures].[MyMeasure2] as '[Dimension1].currentmember.Level.Ordinal' member [Measures].[MyMeasure3] as '[Dimension1].currentmember.UniqueName' select {[Measures].[MyMeasureAmount], [Measures].[MyMeasure], [Measures].[MyMeasure2] , [Measures].[MyMeasure2] } on columns, {[Dimension1].members} on Rows from [My Cube] where ([Dimension2].&[MyDim2Value]) Please tell me if I've misunderstood what you want to do. Kaisa M. Lindahl Lervik [quoted text, click to view] "Raj Chidipudi" <chidipudi@gmail.com> wrote in message news:1161273400.267626.12850@m7g2000cwm.googlegroups.com... > Hi Guys, > I am trying to create a dynamic report using RS 2005 with AS2005. My > report has fixed columns whose names and values are substitued at > runtime with data from queries. > > I am actually facing the problem as described in this topic. > http://groups.google.co.uk/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/ef105b154e83946/f898d0475958720c?lnk=st&q=%22reporting+services%22+adomd&rnum=20&hl=en#f898d0475958720c > > There is alreay an answer in that topic, but what i want is a bit more > than that. > In my report data is not just populated from one dimension, it's > actually populated from more than one dimensions and levels in the same > query. > > If this was in T-SQL, i would have simply given column alias for each > like "CustomerName" AS COL1, "CustomerPhone" AS COL2, etc. > > Does anyone know how to achieve this in MDX. ie how to give aliases for > MDX columns for reporting services 2005? > > Thanks > Raj Chidipudi >
Hi Kaisa,
Thanks for the reply. The Measure values (all the report fields that appear on COLUMNS) can be aliased using WITH MEMBER function as you say. For multiple fields (joined in RS2005 by CROSS JOIN *) on the ROWS statement, we are planning to make use of DIMENSION PROPERTIES. But we are facing another problem with creating calculated members. i.e. when i add any WITH MEMBER (e.g. [Measures].[M1] as Measures.[Score]) statement (even the simplest calculated members) to query, the query just runs for ever. Without the WITH MEMBER clause the same query runs in 1 second. I don't know if there is any property being missed by us while building the cube OR any problem with slicer axis parameters. At present i have 3 parameters on the Slicer axis. Did you face any performance issues while using WITH MEMBER? Any thoughts? Kind Regards Raj Chidipudi [quoted text, click to view] Kaisa M. Lindahl Lervik wrote:
> Hehe, I knew there was something familiar about this question. :) > > To elaborate a few things I've learned since answering that first thread: > > In Reporting Services 2005, there's added "support" for MDX queries. I say > "support", as well, it's easier to do basic mdx stuff, but if you want to go > dynamic, it's a complete pain... But there are tricks to solve it. > 1) Instead of using the Analysis Services data source, create your data > source as OLE DB, and connect to your AS 2005 server through that. > 2) Now you have the old school query interface. No help, but also no mess. > 3) Create your non dynamic query in the SQL Server Management studio, by > connecting to your AS and work out the MDX > 4) Copy paste your non dynamic query to create all your fields etc. > 5) Make it dynamic by adding the =" and " and all the parameter names, like > Dinesh explains. > > As to making the aliases, you need to create calculated measures. > With member [Measures].[MyMeasure] as '[Measures].[NumberOfItemsSold] > select {[Measures].[MyMeasure] } on columns, {[Dimension1].members} on Rows > from [My Cube] > > You can't control the field names of the dimensions used on the rows, but > you can make return the values of these as measures as well. Which means you > can't use the row fields in your report, as that will fail, but you can > still group on them. > > with > member [Measures].[MyMeasure] as '[Free Test].currentmember.name' -> this > gives you the name of the dimension's current member, ie the one you're on > > member [Measures].[MyMeasure2] as '[Free > est].currentmember.Level.Ordinal' -> this gives you a number stating what > level this member is on. The lower the number, the higher the member is in > the hierarchy > > member [Measures].[MyMeasure3] as '[Free Test].currentmember.UniqueName' -> > unique name is the full mdx identifier for the member, like > [Country].[State].[City] (or rather [Country].[USA].[California].[Los > Angeles]) > > As for the mdx, you need to figure out if it can be truly dynamic. If you > want to use several dimensions and levels, all the combinations needs to be > very similar, to make sure you don't have the wrong number of expected > levels etc. You probably need to do some crossjoins, which will > unfortunately mess up the currentmember.uniquename trick. Which could be > solved by creating drill down dimensions in your OLAP cube. If you have one > dimension called Customers and one dimension called Orders, and you want to > see all orders of each customer, you should make a drill down dimension (?) > called Customer Order, which you can use the UniqueName trick on. (As well > as it's probably a lot faster than doing the crossjoins.) As for cube > design, you need to check the microsoft.public.sqlserver.olap newsgroup. If > you don't actually need to display data from several dimensions, just filter > on them, you just add those filter criteria to the where clause in your mdx > query or use the filter or except functions in the mdx.. > > With member [Measures].[MyMeasureAmount] as '[Measures].[NumberOfItemsSold] > member [Measures].[MyMeasure] as '[Dimension1].currentmember.name' > member [Measures].[MyMeasure2] as '[Dimension1].currentmember.Level.Ordinal' > member [Measures].[MyMeasure3] as '[Dimension1].currentmember.UniqueName' > select {[Measures].[MyMeasureAmount], [Measures].[MyMeasure], > [Measures].[MyMeasure2] , [Measures].[MyMeasure2] } on columns, > {[Dimension1].members} on Rows > from [My Cube] > where ([Dimension2].&[MyDim2Value]) > > Please tell me if I've misunderstood what you want to do. > > Kaisa M. Lindahl Lervik > > > "Raj Chidipudi" <chidipudi@gmail.com> wrote in message > news:1161273400.267626.12850@m7g2000cwm.googlegroups.com... > > Hi Guys, > > I am trying to create a dynamic report using RS 2005 with AS2005. My > > report has fixed columns whose names and values are substitued at > > runtime with data from queries. > > > > I am actually facing the problem as described in this topic. > > http://groups.google.co.uk/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/ef105b154e83946/f898d0475958720c?lnk=st&q=%22reporting+services%22+adomd&rnum=20&hl=en#f898d0475958720c > > > > There is alreay an answer in that topic, but what i want is a bit more > > than that. > > In my report data is not just populated from one dimension, it's > > actually populated from more than one dimensions and levels in the same > > query. > > > > If this was in T-SQL, i would have simply given column alias for each > > like "CustomerName" AS COL1, "CustomerPhone" AS COL2, etc. > > > > Does anyone know how to achieve this in MDX. ie how to give aliases for > > MDX columns for reporting services 2005? > > > > Thanks > > Raj Chidipudi > >
No, I've never noticed any delays using the With Member syntax.
How do you slice your cube? Are you doing it the semi-sql way of just adding everything to your where clause, or do you slice it in your row statement? I've noticed a big difference in processing time by slicing from the rows instead of a where clause. What does your mdx look like? And have you run it in the Data tab without the dynamic bits first, so you've created the list of fields. Kaisa M. Lindahl Lervik [quoted text, click to view] "Raj Chidipudi" <chidipudi@gmail.com> wrote in message news:1161619798.560309.276380@e3g2000cwe.googlegroups.com... > Hi Kaisa, > Thanks for the reply. The Measure values (all the report fields that > appear on COLUMNS) can be aliased using WITH MEMBER function as you > say. > For multiple fields (joined in RS2005 by CROSS JOIN *) on the ROWS > statement, we are planning to make use of DIMENSION PROPERTIES. > > But we are facing another problem with creating calculated members. > i.e. when i add any WITH MEMBER (e.g. [Measures].[M1] as > Measures.[Score]) statement (even the simplest calculated members) to > query, the query just runs for ever. Without the WITH MEMBER clause the > same query runs in 1 second. I don't know if there is any property > being missed by us while building the cube OR any problem with slicer > axis parameters. At present i have 3 parameters on the Slicer axis. > > Did you face any performance issues while using WITH MEMBER? > Any thoughts? > > Kind Regards > Raj Chidipudi > > > Kaisa M. Lindahl Lervik wrote: >> Hehe, I knew there was something familiar about this question. :) >> >> To elaborate a few things I've learned since answering that first thread: >> >> In Reporting Services 2005, there's added "support" for MDX queries. I >> say >> "support", as well, it's easier to do basic mdx stuff, but if you want to >> go >> dynamic, it's a complete pain... But there are tricks to solve it. >> 1) Instead of using the Analysis Services data source, create your data >> source as OLE DB, and connect to your AS 2005 server through that. >> 2) Now you have the old school query interface. No help, but also no >> mess. >> 3) Create your non dynamic query in the SQL Server Management studio, by >> connecting to your AS and work out the MDX >> 4) Copy paste your non dynamic query to create all your fields etc. >> 5) Make it dynamic by adding the =" and " and all the parameter names, >> like >> Dinesh explains. >> >> As to making the aliases, you need to create calculated measures. >> With member [Measures].[MyMeasure] as '[Measures].[NumberOfItemsSold] >> select {[Measures].[MyMeasure] } on columns, {[Dimension1].members} on >> Rows >> from [My Cube] >> >> You can't control the field names of the dimensions used on the rows, but >> you can make return the values of these as measures as well. Which means >> you >> can't use the row fields in your report, as that will fail, but you can >> still group on them. >> >> with >> member [Measures].[MyMeasure] as '[Free Test].currentmember.name' -> this >> gives you the name of the dimension's current member, ie the one you're >> on >> >> member [Measures].[MyMeasure2] as '[Free >> est].currentmember.Level.Ordinal' -> this gives you a number stating >> what >> level this member is on. The lower the number, the higher the member is >> in >> the hierarchy >> >> member [Measures].[MyMeasure3] as '[Free >> Test].currentmember.UniqueName' -> >> unique name is the full mdx identifier for the member, like >> [Country].[State].[City] (or rather [Country].[USA].[California].[Los >> Angeles]) >> >> As for the mdx, you need to figure out if it can be truly dynamic. If you >> want to use several dimensions and levels, all the combinations needs to >> be >> very similar, to make sure you don't have the wrong number of expected >> levels etc. You probably need to do some crossjoins, which will >> unfortunately mess up the currentmember.uniquename trick. Which could be >> solved by creating drill down dimensions in your OLAP cube. If you have >> one >> dimension called Customers and one dimension called Orders, and you want >> to >> see all orders of each customer, you should make a drill down dimension >> (?) >> called Customer Order, which you can use the UniqueName trick on. (As >> well >> as it's probably a lot faster than doing the crossjoins.) As for cube >> design, you need to check the microsoft.public.sqlserver.olap newsgroup. >> If >> you don't actually need to display data from several dimensions, just >> filter >> on them, you just add those filter criteria to the where clause in your >> mdx >> query or use the filter or except functions in the mdx.. >> >> With member [Measures].[MyMeasureAmount] as >> '[Measures].[NumberOfItemsSold] >> member [Measures].[MyMeasure] as '[Dimension1].currentmember.name' >> member [Measures].[MyMeasure2] as >> '[Dimension1].currentmember.Level.Ordinal' >> member [Measures].[MyMeasure3] as '[Dimension1].currentmember.UniqueName' >> select {[Measures].[MyMeasureAmount], [Measures].[MyMeasure], >> [Measures].[MyMeasure2] , [Measures].[MyMeasure2] } on columns, >> {[Dimension1].members} on Rows >> from [My Cube] >> where ([Dimension2].&[MyDim2Value]) >> >> Please tell me if I've misunderstood what you want to do. >> >> Kaisa M. Lindahl Lervik >> >> >> "Raj Chidipudi" <chidipudi@gmail.com> wrote in message >> news:1161273400.267626.12850@m7g2000cwm.googlegroups.com... >> > Hi Guys, >> > I am trying to create a dynamic report using RS 2005 with AS2005. My >> > report has fixed columns whose names and values are substitued at >> > runtime with data from queries. >> > >> > I am actually facing the problem as described in this topic. >> > http://groups.google.co.uk/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/ef105b154e83946/f898d0475958720c?lnk=st&q=%22reporting+services%22+adomd&rnum=20&hl=en#f898d0475958720c >> > >> > There is alreay an answer in that topic, but what i want is a bit more >> > than that. >> > In my report data is not just populated from one dimension, it's >> > actually populated from more than one dimensions and levels in the same >> > query. >> > >> > If this was in T-SQL, i would have simply given column alias for each >> > like "CustomerName" AS COL1, "CustomerPhone" AS COL2, etc. >> > >> > Does anyone know how to achieve this in MDX. ie how to give aliases for >> > MDX columns for reporting services 2005? >> > >> > Thanks >> > Raj Chidipudi >> > >
Hi there,
We figured out the problem. We are using NON EMPTY() clause on the sets for ON ROWS and ON COLUMNS in the query. When we use those things in the query and try to create calculated members would just kill the query performance. So we decided to build all the necessary calculated fields into the cube itself. Raj [quoted text, click to view] Kaisa M. Lindahl Lervik wrote:
> No, I've never noticed any delays using the With Member syntax. > > How do you slice your cube? Are you doing it the semi-sql way of just adding > everything to your where clause, or do you slice it in your row statement? > I've noticed a big difference in processing time by slicing from the rows > instead of a where clause. > > What does your mdx look like? And have you run it in the Data tab without > the dynamic bits first, so you've created the list of fields. > > Kaisa M. Lindahl Lervik > > > "Raj Chidipudi" <chidipudi@gmail.com> wrote in message > news:1161619798.560309.276380@e3g2000cwe.googlegroups.com... > > Hi Kaisa, > > Thanks for the reply. The Measure values (all the report fields that > > appear on COLUMNS) can be aliased using WITH MEMBER function as you > > say. > > For multiple fields (joined in RS2005 by CROSS JOIN *) on the ROWS > > statement, we are planning to make use of DIMENSION PROPERTIES. > > > > But we are facing another problem with creating calculated members. > > i.e. when i add any WITH MEMBER (e.g. [Measures].[M1] as > > Measures.[Score]) statement (even the simplest calculated members) to > > query, the query just runs for ever. Without the WITH MEMBER clause the > > same query runs in 1 second. I don't know if there is any property > > being missed by us while building the cube OR any problem with slicer > > axis parameters. At present i have 3 parameters on the Slicer axis. > > > > Did you face any performance issues while using WITH MEMBER? > > Any thoughts? > > > > Kind Regards > > Raj Chidipudi > > > > > > Kaisa M. Lindahl Lervik wrote: > >> Hehe, I knew there was something familiar about this question. :) > >> > >> To elaborate a few things I've learned since answering that first thread: > >> > >> In Reporting Services 2005, there's added "support" for MDX queries. I > >> say > >> "support", as well, it's easier to do basic mdx stuff, but if you want to > >> go > >> dynamic, it's a complete pain... But there are tricks to solve it. > >> 1) Instead of using the Analysis Services data source, create your data > >> source as OLE DB, and connect to your AS 2005 server through that. > >> 2) Now you have the old school query interface. No help, but also no > >> mess. > >> 3) Create your non dynamic query in the SQL Server Management studio, by > >> connecting to your AS and work out the MDX > >> 4) Copy paste your non dynamic query to create all your fields etc. > >> 5) Make it dynamic by adding the =" and " and all the parameter names, > >> like > >> Dinesh explains. > >> > >> As to making the aliases, you need to create calculated measures. > >> With member [Measures].[MyMeasure] as '[Measures].[NumberOfItemsSold] > >> select {[Measures].[MyMeasure] } on columns, {[Dimension1].members} on > >> Rows > >> from [My Cube] > >> > >> You can't control the field names of the dimensions used on the rows, but > >> you can make return the values of these as measures as well. Which means > >> you > >> can't use the row fields in your report, as that will fail, but you can > >> still group on them. > >> > >> with > >> member [Measures].[MyMeasure] as '[Free Test].currentmember.name' -> this > >> gives you the name of the dimension's current member, ie the one you're > >> on > >> > >> member [Measures].[MyMeasure2] as '[Free > >> est].currentmember.Level.Ordinal' -> this gives you a number stating > >> what > >> level this member is on. The lower the number, the higher the member is > >> in > >> the hierarchy > >> > >> member [Measures].[MyMeasure3] as '[Free > >> Test].currentmember.UniqueName' -> > >> unique name is the full mdx identifier for the member, like > >> [Country].[State].[City] (or rather [Country].[USA].[California].[Los > >> Angeles]) > >> > >> As for the mdx, you need to figure out if it can be truly dynamic. If you > >> want to use several dimensions and levels, all the combinations needs to > >> be > >> very similar, to make sure you don't have the wrong number of expected > >> levels etc. You probably need to do some crossjoins, which will > >> unfortunately mess up the currentmember.uniquename trick. Which could be > >> solved by creating drill down dimensions in your OLAP cube. If you have > >> one > >> dimension called Customers and one dimension called Orders, and you want > >> to > >> see all orders of each customer, you should make a drill down dimension > >> (?) > >> called Customer Order, which you can use the UniqueName trick on. (As > >> well > >> as it's probably a lot faster than doing the crossjoins.) As for cube > >> design, you need to check the microsoft.public.sqlserver.olap newsgroup. > >> If > >> you don't actually need to display data from several dimensions, just > >> filter > >> on them, you just add those filter criteria to the where clause in your > >> mdx > >> query or use the filter or except functions in the mdx.. > >> > >> With member [Measures].[MyMeasureAmount] as > >> '[Measures].[NumberOfItemsSold] > >> member [Measures].[MyMeasure] as '[Dimension1].currentmember.name' > >> member [Measures].[MyMeasure2] as > >> '[Dimension1].currentmember.Level.Ordinal' > >> member [Measures].[MyMeasure3] as '[Dimension1].currentmember.UniqueName' > >> select {[Measures].[MyMeasureAmount], [Measures].[MyMeasure], > >> [Measures].[MyMeasure2] , [Measures].[MyMeasure2] } on columns, > >> {[Dimension1].members} on Rows > >> from [My Cube] > >> where ([Dimension2].&[MyDim2Value]) > >> > >> Please tell me if I've misunderstood what you want to do. > >> > >> Kaisa M. Lindahl Lervik > >> > >> > >> "Raj Chidipudi" <chidipudi@gmail.com> wrote in message > >> news:1161273400.267626.12850@m7g2000cwm.googlegroups.com... > >> > Hi Guys, > >> > I am trying to create a dynamic report using RS 2005 with AS2005. My > >> > report has fixed columns whose names and values are substitued at > >> > runtime with data from queries. > >> > > >> > I am actually facing the problem as described in this topic. > >> > http://groups.google.co.uk/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/ef105b154e83946/f898d0475958720c?lnk=st&q=%22reporting+services%22+adomd&rnum=20&hl=en#f898d0475958720c > >> > > >> > There is alreay an answer in that topic, but what i want is a bit more > >> > than that. > >> > In my report data is not just populated from one dimension, it's > >> > actually populated from more than one dimensions and levels in the same > >> > query. > >> >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |