distributed transaction: reports

Showing posts with label reports. Show all posts

Thursday, March 22, 2012

Distinct Count issue in Crystal Reports

Hi there,

I'm having a problem with a pretty easy formula which is suppose to just Distinct Count some ID's for a specific period of time:
Each parameter in the formula represents a field in the database, formula bellow should return 3 records... but it returns 7. If I take out the date conditions (so only DistinctCount remains) (7) records will be returned.

If {@.M_Startdate} <= {@.ClientRefDate} and {@.ClientRefDate} <= {@.M_EndDate} Then DistinctCount({@.UniqueRefID})

I also tried to put is in a different way (which express the same thing);
If {@.ClientRefDate} in [{@.M_Startdate} to {@.M_EndDate}] Then DistinctCount({@.UniqueRefID})

It looks like a date validation is performed when I reverse the <= with >=; Formula bellow returns (0) records, if I remove any date condition (7) records are returned.
If {@.M_Startdate} >= {@.ClientRefDate} and {@.ClientRefDate} <= {@.M_EndDate} Then DistinctCount({@.UniqueRefID})

Using WhilePrintingRecords; right at the top does not help I tried already. Also adding a date validation on the main query of the report does not help because whole data will be affected.
Any suggestions?

Thanks,

RobertHi Robert,
Firstly, I see a potential problem here, straight up. Evaluation order of the formulae. You will get inconsistent/unreliable evaluation in formulae that use other formulae if you don't use the EvaluateAfter statement.

If {@.M_Startdate} <= {@.ClientRefDate} and {@.ClientRefDate} <= {@.M_EndDate} Then DistinctCount({@.UniqueRefID})

try this
EvaluateAfter ({@.M_Startdate}
EvaluateAfter ({@.ClientRefDate})
EvaluateAfter ({@.M_EndDate})
EvaluateAfter ({@.UniqueRefID})

If {@.M_Startdate} <= {@.ClientRefDate} and {@.ClientRefDate} <= {@.M_EndDate} Then DistinctCount({@.UniqueRefID})

That way, you are forcing this formula to evaluate the others first.
This may be why the different statements return different results.

Secondly, you are using formulae to provide what I presume are references to data fields (like @.UniqueRefID). I presume there is a pressing reason to do it this way rather than just referencing the underlying data field itself? Going to the underlying data without using a formula is always going to be quicker, and more reliable, as it removes the formulae........

Dave|||Hi Robert,
Firstly, I see a potential problem here, straight up. Evaluation order of the formulae. You will get inconsistent/unreliable evaluation in formulae that use other formulae if you don't use the EvaluateAfter statement.

try this
EvaluateAfter ({@.M_Startdate}
EvaluateAfter ({@.ClientRefDate})
EvaluateAfter ({@.M_EndDate})
EvaluateAfter ({@.UniqueRefID})

If {@.M_Startdate} <= {@.ClientRefDate} and {@.ClientRefDate} <= {@.M_EndDate} Then DistinctCount({@.UniqueRefID})

That way, you are forcing this formula to evaluate the others first.
This may be why the different statements return different results.

Secondly, you are using formulae to provide what I presume are references to data fields (like @.UniqueRefID). I presume there is a pressing reason to do it this way rather than just referencing the underlying data field itself? Going to the underlying data without using a formula is always going to be quicker, and more reliable, as it removes the formulae........

Dave

Hi Dave,

Referencing fields from the database can be quicker sometimes but in this case I'm pressed to use parameters for different calculations. I tried qwith "direct" database fields, result is the same.

Using:
EvaluateAfter ({@.M_Startdate}
EvaluateAfter ({@.ClientRefDate})
EvaluateAfter ({@.M_EndDate})
EvaluateAfter ({@.UniqueRefID})

If {@.M_Startdate} <= {@.ClientRefDate} and {@.ClientRefDate} <= {@.M_EndDate} Then DistinctCount({@.UniqueRefID})
would have to work but unfortunately same result is returned.

Second pass functions require data from more than one record and in this case(WhilePrintingRecords) should evaluate params first... but it does not.

I'll keep trying,

Thanks a bunch,

Robert

Distinct Count Grand Total issue SSAS 2005 (bug ?)

Hi,
I recently noticed in Excel and OWC and BI studio browser - that reports that use distinct count measures return incorrect Grand totals with row/column axis filters/selections. When the selections are more than 1 level deep it does not take the filter into consideration and returns a larger Grand total - as if the filter never took place. I have Sql Server 2005 Developers edition with both SP1 and Post-Sp1 hotfix (build 9.0.2153)

I have successfully replicated this in AdventureWorks sample easily. Here are the steps:

In the Cube browser just pull in the Date.Calendar hierarchy to the row axis and the Order Count measure (from SalesOrders folder) . The total is 31,455 Now try to select/filter using the row axis (dropdown) on specific Semesters , Quarters or lower levels - the Grand total will never change ! (still 31,455) Only if the filter is on year itself will it change - nothing lower. If filter is in the Page axis or Filter pane in BI studio it will work ok - but not on row/col.

This will also happen with Product Category Heirarchy if selecting categories and specific subcategories on row/col. Basically whenever the selections are 2 or more levels deep the grand total will not change to reflect the selected deeper levels.

Is this a bug? Any help/ feedback would be appreciated.

Thank You,
HaroldW.

Sounds like a real problem. Please contact Customer Support and report this. You can also use http://connect.microsoft.com/SQLServer/feedback to log it.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks Edward. I am not sure how to contact Customer Support - but I did log it using the URL you provided. I have a feeling it is a Client side (excel/owc) issue

Thanks,

HaroldW.

|||

Just wanted to update status. I reported the bug to Microsoft - using "connect" website. They responded that the bug will be fixed in sp2 :)

-HaroldW

Distinct Count Grand Total issue SSAS 2005 (bug ?)

I have successfully replicated this in AdventureWorks sample easily. Here are the steps:

Is this a bug? Any help/ feedback would be appreciated.

Thank You,
HaroldW.

Sounds like a real problem. Please contact Customer Support and report this. You can also use http://connect.microsoft.com/SQLServer/feedback to log it.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks Edward. I am not sure how to contact Customer Support - but I did log it using the URL you provided. I have a feeling it is a Client side (excel/owc) issue

Thanks,

HaroldW.

|||

Just wanted to update status. I reported the bug to Microsoft - using "connect" website. They responded that the bug will be fixed in sp2 :)

-HaroldW

Wednesday, March 21, 2012

Disregard: SSIS as data source for reporting?

I'm trying to develop a report using an SSIS DataReaderDestination as the reports data souce. When I attempt to create the report DataSet, SSIS is not an option for the Data Source parameter....

Is there an OLE DB or .NET provider for SSIS that I need to install apart from installing reporting services?Had the wrong edition. Never let your architects download things for you :)|||

Hello,
I am experiencing the same problem you described. I have Visual Studio Team Suite installed with SQL Server 2005 Developer Edition. I would expect this setup should allow me to use any of the functionality available in Integration Services. What edition did you have to install to get the SSIS option to appear?

Thanks for your help.

Disregard: SSIS as data source for reporting?

I'm trying to develop a report using an SSIS DataReaderDestination as the reports data souce. When I attempt to create the report DataSet, SSIS is not an option for the Data Source parameter....

Is there an OLE DB or .NET provider for SSIS that I need to install apart from installing reporting services?Had the wrong edition. Never let your architects download things for you :)|||

Thanks for your help.

Monday, March 19, 2012

Displaying Top N subtotal as well as grand total

I am currently migrating Crystal Report XI reports over to the
Reporting Services and came across a problem.
The Crystal Report displays Top N records, subtotal of these Top N
records, and the grand total of all the records. SQL stored procedure
underneath returns all the records.
>From what I have been reading in online groups and helps, these types
of features are not directly supported in the Reporting Services. The
workaround I have found so far involves returning two sets of results
("Top N" results and "All" results) and combining the two result
sets.
Since some query takes up significant amount of time, I would like to
avoid calling the stored procedures twice if necessary. Does anyone
know how I can avoid this and work with just one result set?
Thanks in advance.
ShoheiBring all the records and use filter "Top N" to filter for Top N records only.
Amarnath [MVP]
"Shohei.Yamauchi@.gmail.com" wrote:
> I am currently migrating Crystal Report XI reports over to the
> Reporting Services and came across a problem.
> The Crystal Report displays Top N records, subtotal of these Top N
> records, and the grand total of all the records. SQL stored procedure
> underneath returns all the records.
> >From what I have been reading in online groups and helps, these types
> of features are not directly supported in the Reporting Services. The
> workaround I have found so far involves returning two sets of results
> ("Top N" results and "All" results) and combining the two result
> sets.
> Since some query takes up significant amount of time, I would like to
> avoid calling the stored procedures twice if necessary. Does anyone
> know how I can avoid this and work with just one result set?
> Thanks in advance.
> Shohei
>|||If you are using SQL Server 2005, you can use ROW_NUMBER () in a Data
set so that you can get top N

Displaying the Top N records count

Hi,
I am using crystal reports 10.0. I am generating a report in which I have to show only the top 5 records(depending on the count) out of a large number of records. I achieved displaying top 5 records by selecting the Group sort expert from Record menu. But, the problem is that the individual column totals which is generated by CR 10.0 is still of the total records and not that of the 5 records which are displayed. Please help me show the total of the displayed records only.
Thanks and Regards,
Raj

The report looks something like this:

Policy Name Accepted Expired Total
Test1 2 3 5
Test2 2 3 5
Test3 2 3 5
Test4 2 3 5
Test5 2 3 5
-----------------
Total 20 30 50
------------------

U can see that the total shown is wrong since it is showing the total of all the records. I want the total row to be as:

Total 10 15 25

I dont think writing a formula as sum(Accepted) would help as i have tried it and its giving the same total.

Please give me the solution as soon as possibleOriginally posted by rajdotme
Hi,
I am using crystal reports 10.0. I am generating a report in which I have to show only the top 5 records(depending on the count) out of a large number of records. I achieved displaying top 5 records by selecting the Group sort expert from Record menu. But, the problem is that the individual column totals which is generated by CR 10.0 is still of the total records and not that of the 5 records which are displayed. Please help me show the total of the displayed records only.
Thanks and Regards,
Raj

The report looks something like this:

Policy Name Accepted Expired Total
Test1 2 3 5
Test2 2 3 5
Test3 2 3 5
Test4 2 3 5
Test5 2 3 5
-----------------
Total 20 30 50
------------------

U can see that the total shown is wrong since it is showing the total of all the records. I want the total row to be as:

Total 10 15 25

I dont think writing a formula as sum(Accepted) would help as i have tried it and its giving the same total.

Please give me the solution as soon as possible

Hi,

Validate in Detail Format Section using with "RecordNumber" value is <= 5 (variable value).

yours friendly,
K.Babu|||Hi,
Thanks for the Reply. Could u please tell me the exact location where to change. I still couldnt find it.

Thanks and Regards,
Raj|||Hi rajdotme,
Use the formulae given below

Formula1 {@.Reset}
whileprintingrecords;
NumberVar x:=0;
NumberVar y:=0;
NumberVar z:=0;

Formula2 {@.Accepted}
whileprintingrecords;
Numbervar x:=x+{Table.Accepted}

Formula3 {@.Rejected}
whileprintingrecords;
Numbervar y:=y+{Table.Rejected}

Formula4 {@.Total}
whileprintingrecords;
Numbervar z:=z+{Table.Total}

Now, Place Formula1 in the group header and suppress it
Place other formulae in the group footer

Madhivanan

Displaying Reports without Report Manager & Login

I'm having a hard time believing others have not ran into this issue.
Anyway...here it is:
I've got a pretty extensive web app. that we're using SQL Reporting Services
to fullfill the reporting requirements. (This is a shared server in a server
farm...I don't have the capabilities to change security settings on the
reporting server).
So, long story short...I'd like to display the reports to the users a single
report at a time, without the report manager capabilities. I've went through
the rendering process and it would work if that is the only way to do it, but
I'd rather show them the HTML toolbar the comes with reporting services and
let them interact with the report. I realize that I can do this just by
calling a URL but when I do that it asks for a username and password. So I
guess the bottom line question is how can I open the report via the URL
without asking me for the report manager username and password (without using
the reporting services render method).
Thanks in Advance!!This sounds like a security issue between domains/machines etc.
Does the user sign in on machine 1 and then click on a link to go to machine
2 that has a different username/password? If so the user will need to enter
their username/password to get on to machine 2.
There are some work arounds but that may cause security wholes for you.
What is the exact security scenario you have?
Craig
"Nick Stineman" <NickStineman@.discussions.microsoft.com> wrote in message
news:476573EC-A15B-4F36-B6EF-EFB4ABE15E9F@.microsoft.com...
> I'm having a hard time believing others have not ran into this issue.
> Anyway...here it is:
> I've got a pretty extensive web app. that we're using SQL Reporting
> Services
> to fullfill the reporting requirements. (This is a shared server in a
> server
> farm...I don't have the capabilities to change security settings on the
> reporting server).
> So, long story short...I'd like to display the reports to the users a
> single
> report at a time, without the report manager capabilities. I've went
> through
> the rendering process and it would work if that is the only way to do it,
> but
> I'd rather show them the HTML toolbar the comes with reporting services
> and
> let them interact with the report. I realize that I can do this just by
> calling a URL but when I do that it asks for a username and password. So
> I
> guess the bottom line question is how can I open the report via the URL
> without asking me for the report manager username and password (without
> using
> the reporting services render method).
> Thanks in Advance!!|||Exact Situation...
We use a shared web server host to host our web application. We've signed
up for SQL reporting services with the host. From what I can decipher with
my e-mails to the web hosting provider the reporting services resides on a
seperate machine as the website. The reporting services server requires SSL
encryption. It is when I try and access the report manager that I am asked
for a username and password. I've created a rendering capability that spits
my reports out as PDF files...this will work if required...but I'd rather
give the end user the functionality of the HTML toolbar located within
reporting services.
If our hosting provider has made a choice or installation configuration that
is causing issues accessing via a direct URL I guess I just need to know. I
can't believe that Microsoft would roll out a product that required
additional login to simply view a report.
Thanks!!
-Nick
"Craig" wrote:
> This sounds like a security issue between domains/machines etc.
> Does the user sign in on machine 1 and then click on a link to go to machine
> 2 that has a different username/password? If so the user will need to enter
> their username/password to get on to machine 2.
> There are some work arounds but that may cause security wholes for you.
> What is the exact security scenario you have?
> Craig
>
> "Nick Stineman" <NickStineman@.discussions.microsoft.com> wrote in message
> news:476573EC-A15B-4F36-B6EF-EFB4ABE15E9F@.microsoft.com...
> > I'm having a hard time believing others have not ran into this issue.
> > Anyway...here it is:
> >
> > I've got a pretty extensive web app. that we're using SQL Reporting
> > Services
> > to fullfill the reporting requirements. (This is a shared server in a
> > server
> > farm...I don't have the capabilities to change security settings on the
> > reporting server).
> >
> > So, long story short...I'd like to display the reports to the users a
> > single
> > report at a time, without the report manager capabilities. I've went
> > through
> > the rendering process and it would work if that is the only way to do it,
> > but
> > I'd rather show them the HTML toolbar the comes with reporting services
> > and
> > let them interact with the report. I realize that I can do this just by
> > calling a URL but when I do that it asks for a username and password. So
> > I
> > guess the bottom line question is how can I open the report via the URL
> > without asking me for the report manager username and password (without
> > using
> > the reporting services render method).
> >
> > Thanks in Advance!!
>
>|||This is an IIS security issue. I think the hosting provided doesn't allow
anonymous access to the report server.
Is the website your running public domain or do users have to sign in to
that as well?
Craig
"Nick" <Nick@.discussions.microsoft.com> wrote in message
news:AC213DC2-7DAA-4B9A-8688-FB92E22426C2@.microsoft.com...
> Exact Situation...
> We use a shared web server host to host our web application. We've signed
> up for SQL reporting services with the host. From what I can decipher
> with
> my e-mails to the web hosting provider the reporting services resides on a
> seperate machine as the website. The reporting services server requires
> SSL
> encryption. It is when I try and access the report manager that I am
> asked
> for a username and password. I've created a rendering capability that
> spits
> my reports out as PDF files...this will work if required...but I'd rather
> give the end user the functionality of the HTML toolbar located within
> reporting services.
> If our hosting provider has made a choice or installation configuration
> that
> is causing issues accessing via a direct URL I guess I just need to know.
> I
> can't believe that Microsoft would roll out a product that required
> additional login to simply view a report.
> Thanks!!
> -Nick
> "Craig" wrote:
>> This sounds like a security issue between domains/machines etc.
>> Does the user sign in on machine 1 and then click on a link to go to
>> machine
>> 2 that has a different username/password? If so the user will need to
>> enter
>> their username/password to get on to machine 2.
>> There are some work arounds but that may cause security wholes for you.
>> What is the exact security scenario you have?
>> Craig
>>
>> "Nick Stineman" <NickStineman@.discussions.microsoft.com> wrote in message
>> news:476573EC-A15B-4F36-B6EF-EFB4ABE15E9F@.microsoft.com...
>> > I'm having a hard time believing others have not ran into this issue.
>> > Anyway...here it is:
>> >
>> > I've got a pretty extensive web app. that we're using SQL Reporting
>> > Services
>> > to fullfill the reporting requirements. (This is a shared server in a
>> > server
>> > farm...I don't have the capabilities to change security settings on the
>> > reporting server).
>> >
>> > So, long story short...I'd like to display the reports to the users a
>> > single
>> > report at a time, without the report manager capabilities. I've went
>> > through
>> > the rendering process and it would work if that is the only way to do
>> > it,
>> > but
>> > I'd rather show them the HTML toolbar the comes with reporting services
>> > and
>> > let them interact with the report. I realize that I can do this just
>> > by
>> > calling a URL but when I do that it asks for a username and password.
>> > So
>> > I
>> > guess the bottom line question is how can I open the report via the URL
>> > without asking me for the report manager username and password (without
>> > using
>> > the reporting services render method).
>> >
>> > Thanks in Advance!!
>>

displaying reports w/o having to login

Hey guys, I finally got my report working and connections working to my webhost. Only problem is when I navigate to my reportserver, it prompts me for a username and password that corresponds to the system login. After that I have people enter their credentials that are cross referenced with the db. However, I don't want to give out the system password, otherwise other people who know the report manager url can get in and mess around. How do I avoid having the report server prompt me for the system user/pass?

Thanks.

Set an ASP.NET web site with the ReportViewer control on one of the pages. Grant run report to the ASP.NET user and then any user who can run the web site, can run a given report.|||

If you are using the report viewer, you can impersonate a user. You need to set it in the web.config of the application. It falls under the System.Web section of the web.config and looks something like this:

<identity impersonate="true" userName="UserName" password="PassWord"/>

This way when someone accesses the web application they are impersonating the user and they are not promted. What I would do if possible is setup a seperate user then admin of course that has access to the report server though for security reasons. That user should just need to be able to access the report server, and any other credentials he may need to run your application. Just my thoughts...

Dapanther99

|||

Thing is I cannot add that to the web.config where the reportserver resides. Otherwise, If I add that to the web.config where my asp files are located wouldn't that interfere with my existing scripts such as login pages?

Also, when you guys say report viewer, are you referring to the reportserver that allows me to view my reports but not manage them? Or is reportviewer as seperate item?

|||

The report viewer is a control in ASP.NET 2.0. I resides under the data controls. In respect to the impersonated user, we have applications here that use impersonation without any issues. What you need to do is put it in the web.config of you application, not with the report server. If you have access to create additional users then you would create a user that has access to the report server. You will also need to impersonate this same user in the dataset the report uses. As well as make sure that he has DB access. The good thing about doing it this way is you create a trail in case you are using audit tables in your DB...

Dapanther99

|||

ah ok, so by your definition, I am not using the report viewer, I'm using the direct url to the report server given to me by my hosting company,

(e.g.http://sqlserver/reportserver)

So I need to create an asp.net page that utilizes report viewer to access the reports from the report server. Am I correct up to this point?

Also, I did a search on my hosting company's KB, and they suggest using the following script:

<%
dim objHTTP
set objHTTP = Server.CreateObject("Microsoft.XMLHTTP")
url = "http://sqlreport01.mysite4now.com/ReportServer/Pages/ReportViewer.aspx?%2mysite%2fmyreport&rs:Command=Render"
objHTTP.open "get", url, false, "username", "password"
objHTTP.send
response.write objHTTP.responsetext
%>

Is this something I can skip? and just proceed to look up articles regarding the report viewer? (I've tried using that script in an asp page and I got nothing, thats why im asking)

Thanks

|||

If you are writing it in .NET 2.0 I would go with the report viewer. You might want to check with the hosting company to make sure that it is installed on the servers because if I remember correctly we had to manually install it on our servers when we deployed our first reports using it...

Dapanther99

|||

Great, I got the report viewer to work, now to get passed the iis authentication. I tried impersonate, but it's not working. When I do just <identity impersonate="true" /> the script runs, but the moment I add username and password, i get a run time error. Also, the script that I wrote above, from what i'm getting at is, that script opens up that page and inputs the username and password but doesn't display the page so in a sense you're opening a session. Am I correct?

I'd like to implement that if I can't get this impersonate thing to work but when I add that to my asp.net page, i also get a run time error.

any ideas guys?

|||

Does the error display or do you have access to the error logs? Let me know what you are getting and I'll try to figure it out. It sounds like you may have the impersonate tag in the wrong place. Also, make sure you have the authentication mode set to forms. Let me know...

Dapanther99

|||

Ok I added the line that gives me the actual error. It's saying that my username or password is incorrect. Now I'd like to get the concept straight.

I have a sql report server.
I have an asp page with the report viewer on another server. In that same folder I have a web.config file that has

</configuration
1) Isn't it suppose to be windows auth? since the popup is IIS auth. (not that changing it to windows worked)
2) I'm getting the impression that it's looking for a local username on that server and not forwarding the request over to the report server.
3) This folder in which I have my asp.net page does not have user authentication.
4) This server and the report server both have the same user name and pw.

any ideas?

Displaying reports in the form of graphs over web

Hi,

I have a requirement to show reports(in the form of graphs) over the web page. The reports get the data from the database. Right now I'm using GDI+ to render the graphs over the web. I want to know if there are any significant advantages of using sql server reporting services over GDI+ ?

Can I draw any kind of complex graphs using the reporting services or is there a limitation on the kind of graphs that can be drawn?

Does GDI+ have better flexibility in terms of drawing graphs or reporting service can help achieve the same thing?

Thanks much in advance.

Drawing graphs yourself will always provide you with more flexibility than using a pre-built solution. The question is: do you need this much flexibility? In most cases, the answer is no; but only you can answer that. SSRS gives you a lot. I wouldn't dare say it handles every scenario, but I haven't seen a requirement that can't be met with what SSRS provides. If you've ever used the charting in Excel, you can think of this as a somewhat similar feature-set. Honestly, tho, I'd just take the hour to create a simple report. You'll see how easy it is and what options you're given fairly quickly. Additionally, you can check out theAsp.net videos; specifically, the SQL Server 2005 Express section has two videos you might be interested in:#10 Getting Started with Reporting Services and#11 Building and Customizing Reports in Business Intelligence Development Studio.

|||

Thank you so much for the information. I got just what I needed. I need to generate graphs similar to those generated using Excel.

Reporting services seems to be the better option then. Thanks once again.

|||Glad to be able to help! A lot of people hesitate to dig into SSRS because they're worried it'll be complicated. Once you start, tho, you realize just how easy it is to get up and running. Admittedly, SSRS isn't going to solve every problem and there can be some annoying work-arounds when you start to reach more advanced reports, but it's a fantastic 80-90% solution. Best of luck with it!|||

Swetha.c,

I found a solution that has an interface that looks similar to Microsoft Excel. It is a product called AutoTag made by Windward Reports. They have an ASP.NET Engine that can run the reports you generate from AutoTag. Check it out, might be what you are looking for.

http://www.windward.net

--TR

Displaying Reports

I have a folder with many reports in it. Each report has a name and a
description of the report. How can I group the reports together?
IE:
Data A Report
Data A Chart
Data B Report
Data B ChartThe only way is by name of the report.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"eric_rs1" <ericrs1@.discussions.microsoft.com> wrote in message
news:0D88A0BB-C1A9-44F7-97EF-55F15538F6AA@.microsoft.com...
> I have a folder with many reports in it. Each report has a name and a
> description of the report. How can I group the reports together?
> IE:
> Data A Report
> Data A Chart
> Data B Report
> Data B Chart
>|||Here is another example. I need to have these two reports together.
Daily Phone Stats Report
Weekly Phone Stats Report
"eric_rs1" wrote:
> I have a folder with many reports in it. Each report has a name and a
> description of the report. How can I group the reports together?
> IE:
> Data A Report
> Data A Chart
> Data B Report
> Data B Chart
>|||Can't you use multiple folders?
True, you can only use a single folder when deploying from Visual Studio,
but once deployed, you can move the report anywhere.
"eric_rs1" <ericrs1@.discussions.microsoft.com> wrote in message
news:76F91A9E-DA73-4D9D-9D2B-24658B77BD7C@.microsoft.com...
> Here is another example. I need to have these two reports together.
> Daily Phone Stats Report
> Weekly Phone Stats Report
> "eric_rs1" wrote:
>> I have a folder with many reports in it. Each report has a name and a
>> description of the report. How can I group the reports together?
>> IE:
>> Data A Report
>> Data A Chart
>> Data B Report
>> Data B Chart
>>

Displaying reports

Hi All
I was wondering if there is any way of viewing reports created in reporting
services over the web without having to use Sharepoint. I do not want the
usual reports server view. I would like as close to the Sharepoint Report
Viewer web part as possible.
Many ThanksHi,
You need to write your own web part, then will solve your prob, Just check
this site as well. http://blogs.sqlxml.org/bryantlikes/default.aspx for
sharepoint reporting viewer web parts.
Amarnath
"mackaycc" wrote:
> Hi All
> I was wondering if there is any way of viewing reports created in reporting
> services over the web without having to use Sharepoint. I do not want the
> usual reports server view. I would like as close to the Sharepoint Report
> Viewer web part as possible.
> Many Thanks|||Quote:
"without having to use Sharepoint"
ie. I want to be able to display it in a browser window (Without using
Sharepoint or any web parts which run in Sharepoint)
"Amarnath" wrote:
> Hi,
> You need to write your own web part, then will solve your prob, Just check
> this site as well. http://blogs.sqlxml.org/bryantlikes/default.aspx for
> sharepoint reporting viewer web parts.
> Amarnath
>
> "mackaycc" wrote:
> > Hi All
> >
> > I was wondering if there is any way of viewing reports created in reporting
> > services over the web without having to use Sharepoint. I do not want the
> > usual reports server view. I would like as close to the Sharepoint Report
> > Viewer web part as possible.
> >
> > Many Thanks

Sunday, March 11, 2012

Displaying Picture In Crystal Report

I want to display picture of customers in crystal reports.

For that purpose I have created table customer.

When new custiomer comes to me I take their picture using PC camera and save that picture in one of the folders.

At the same time I am sending path of that picture to database.

Now when I display record of customer in Crystal report. I want to diaplay their pictures tooo.

Can you help me??Place the OLE Object -> Bitmap Image on the report and right click the OLE Object ->Format Graphics -> Picture Tab and Graphic Location enter the path which is stored in the database in the formula. This will show the image in runtime.

Keep this in mind this functionality is available after Crystal Reports 10.

In the earlier versions you have to store the image as BLOB in the database and then show that field on the report.

displaying more then one record from the same table in the same field

I was wondering if there was any way to make crystal reports grab multiple records from the same table and displahy them in one database field. Basicly I have a table that users would type info into each line of text that they type would equal one record on the table. When I run the report I need to display all the lines of text that they typed in. If I just refrence the table in the database field in crystal it will only pull the first record from the table and thats it.

Here is an example of the table
00000101 0001 This is a tes of the emergency broadcast system, if there was an actual emergency you would hear
00000101 0002 about it after the tones.This is a tes of the emergency broadcast system, if there was an actual
00000101 0003 emergency you would here about it after the tones.This is a tes of the emergency broadcast system

Now if I run the report the only thing it will dislay is the first line (record number "0001")

Thanks for any helpAdd a Textbox to your report. Then drag the first DB Field into the textbox, then drag the second DB Field into the same textbox.

I attached a picture of an Address field from one of my reports. It's a textbox and I dropped the 5 DB fields onto it (Name, Address, City, State, Zip)

Friday, March 9, 2012

displaying lines in between data in the details section

Hello,
I am in the process of moving all my sql server reports to reporting
services.
First of all, I am creating a table from the toolbar menu and then
moving the data fields to the reports. I need to insert a line in
between rows of data in the detils section. It should look as follows:
table Header
Details 1 Jaideep 123 anydrive
---
2 Pradeep 123 wynndrive
---
table footer
Thanks
JaideepSelect the row in the table and in the properties dialog set the
BorderStyle.Bottom to dashed
Steve MunLeeuw
"jai" <dbasybase@.gmail.com> wrote in message
news:1161014400.693162.231240@.i42g2000cwa.googlegroups.com...
> Hello,
> I am in the process of moving all my sql server reports to reporting
> services.
> First of all, I am creating a table from the toolbar menu and then
> moving the data fields to the reports. I need to insert a line in
> between rows of data in the detils section. It should look as follows:
> table Header
> Details 1 Jaideep 123 anydrive
> ---
> 2 Pradeep 123 wynndrive
> ---
>
> table footer
> Thanks
> Jaideep
>

Displaying huge heirarchial data in reports

I need to display huge data around 1 million records in report. Data needs to be represented as heirarchial. So there is somewhere around 6 level of grouping of data. Can somebody suggest a solution to get the data and display the data in a faster way.

Thanks in advance.

SSRS

Hi,

I don't knw if I understand your problem but you could use the document map functionnality for every level of grouping you have.

HTH,

Eric

|||

Hi Eric,

Thanks for your reply. But, my actual problem is the huge data, fetching that data and displaying such huge data is taking lot of time. So I was looking for some solution where on demand if I can access the required data and display in the report. Like first I can get all the parents and once I click on any of the parent it fetches the child records for that parent and display it on the same report. Please help on the same. If you've still some doubts then do let me know. Because I do need some kind of solution for this problem

Thanks & Regards,

SSRS

Deepak

|||What about using sub report ?|||

Subreports won't help much in this case, as they are processed at the same time as the main report.

You can try creating a master report that displays only aggreggate information per groups (the report query should use T-SQL group by, count and friends...)

Under each aggregate you can create a link (jump to report, aka drillthrough report) to a report containing the details for that particular group. And so on for all the grouping levels until you get to the details rows, which will hopefully be filtered out to a small enough number.

Hope that helps

Tudor Trufinescu

|||

I am having similar problem with huge data. I have linked a detailed report using jump to report. When the data is less it is running fine. But when my parameter selection retrieves huge data in the main report the problem is coming. It seems like it is executing the linked report along with the main report. It is not waiting till the cell is clicked to link. Why is it happening that way? The reason that I feel the linked report is also being executed along with the main report is, for the same parameter selection if I don't link the detailed report it is working fine. Any advice in this regard is much appreciated.

Thank you.

Displaying huge heirarchial data in reports

Thanks in advance.

SSRS

Hi,

I don't knw if I understand your problem but you could use the document map functionnality for every level of grouping you have.

HTH,

Eric

|||

Hi Eric,

Thanks & Regards,

SSRS

Deepak

|||What about using sub report ?|||

Subreports won't help much in this case, as they are processed at the same time as the main report.

You can try creating a master report that displays only aggreggate information per groups (the report query should use T-SQL group by, count and friends...)

Hope that helps

Tudor Trufinescu

|||

Thank you.

displaying different reports based on a parameter

Hi,

I have bunch of reports that take same set of parameters. I am trying parametrize the report type so that depending on the report type selected, body should display that report when user hits "View report" button. How can I do this? Pardon me if there is an obvious solution as I am pretty new to the joys of MS Reporting Services.

Thanks a bunch.

add a subreport control and then set the sub report name using an expression

Displaying De-Serialized Images in Reports

PREFACE:
I have a field in my SQL Server database that is called "Ink" and is Text as
the datatype.
I have put into this field a Base64 String which represents serialized
digital ink collected on a Tablet PC.
I wrote a small piece of Custom Code in the Report to de-serialize the ink
and transform it into System.Drawing.Bitmap, but it does not render in the
report.
QUESTION
Is it possible to display images in a report from a function that returns a
data type of System.Drawing.Bitmap?
If not, can I ask how you are planning to implement digital INK support in
databases?
--
Shawn Nanto
Leszynski Group, Inc.
Bellevue, WAImages can be directly displayed when they are returned as Base64 encoded
byte array. The image type has to be "Database" and you must set the
MimeType to the correct image format.
Note: System.Drawing.Bitmap is not supported.
The relevant section in the RDL file would look similar to this:
<Image>
<MIMEType>image/bmp</MIMEType>
<Source>Database</Source>
<Value>=Fields!InkImage.Value</Value>
...
</Image>
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shawn Nanto" <ShawnNanto@.discussions.microsoft.com> wrote in message
news:0A54FC08-2ECB-4996-B585-5F7E4BA3A8C9@.microsoft.com...
> PREFACE:
> I have a field in my SQL Server database that is called "Ink" and is Text
as
> the datatype.
> I have put into this field a Base64 String which represents serialized
> digital ink collected on a Tablet PC.
> I wrote a small piece of Custom Code in the Report to de-serialize the ink
> and transform it into System.Drawing.Bitmap, but it does not render in the
> report.
> QUESTION
> Is it possible to display images in a report from a function that returns
a
> data type of System.Drawing.Bitmap?
> If not, can I ask how you are planning to implement digital INK support in
> databases?
>
> --
> Shawn Nanto
> Leszynski Group, Inc.
> Bellevue, WA

Wednesday, March 7, 2012

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>

Thursday, March 22, 2012

Distinct Count issue in Crystal Reports

Distinct Count Grand Total issue SSAS 2005 (bug ?)

Distinct Count Grand Total issue SSAS 2005 (bug ?)

Wednesday, March 21, 2012

Disregard: SSIS as data source for reporting?

Disregard: SSIS as data source for reporting?

Monday, March 19, 2012

Displaying Top N subtotal as well as grand total

Displaying the Top N records count

Displaying Reports without Report Manager & Login

displaying reports w/o having to login

Displaying reports in the form of graphs over web

Displaying Reports

Displaying reports

Sunday, March 11, 2012

Displaying Picture In Crystal Report

displaying more then one record from the same table in the same field

Friday, March 9, 2012

displaying lines in between data in the details section

Displaying huge heirarchial data in reports

Displaying huge heirarchial data in reports

displaying different reports based on a parameter

Displaying De-Serialized Images in Reports

Wednesday, March 7, 2012

Displaying ADO MDX query results in reports

distributed transaction

Blog Archive

About Me