Parameterized Expression: adding

Showing posts with label adding. Show all posts

Wednesday, March 28, 2012

Pareto chart in MS Reporting Services

Is MS Reporting Services capable of adding new kinds of charts like a pareto
chart? If so, i'd like to find out if there's one that plugs in to VS .NET
2003 designer and MS Reporting Services.
Dundas does make a pareto chart in their enterprise charting package. They
also designed the charts used MSRS. Does anyone know if this would help?
For those who aren't familiar with a pareto chart, it can be a mix of a bar
and a point (or line) chart that coorespond to two sets of Y axis. One Y
axis can represent an item count and the other Y axis represents the
cumulative percentage.
Thanks,
-ChrisYou may be interested in this related thread:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/msg/2ccef00744a2f70d
If you want real Pareto charts, then the closest you can get in RS2000 is to
write a custom assembly which takes parameters and generates a chart (e.g.
with the Dundas Enterprise Edition) on-the-fly and returns it as byte array.
The custom assembly would need to retrieve the data for the chart on its own
(unless you can pass them all through parameters to the custom assembly
function). After you have implemented the custom assembly you would perform
these steps in report designer:
* Add a reference to the custom assembly through the VS menu: Report -
Report Properties - References
* Add an image to your report
* Set the image type to Database
* Set the image mimetype to e.g. image/png
* For the image value use an expression like
=MyCustomAssembly.GenerateChart(...)
Notes:
* the custom assembly has to return the image as byte[].
* you should follow the links provided below about custom assemblies and
code access security in RS because e.g. you will need to assert security
permission inside the custom assembly when using certain .NET classes and
methods for generating an image.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_rdl_6d0i.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/dngrfCodeAccessSecurityInSQLServer2000ReportingServices.asp
FYI: Dundas has plans to provide a deeply integrated design-time and
runtime-processing control of the Enterprise Edition chart for RS 2005. You
can still use familiar RS features (like grouping, aggregates, filters,
etc.) and will get full control over charting.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"TUA-Chris" <TUAChris@.discussions.microsoft.com> wrote in message
news:966F0AC6-8CB4-4FBE-AD11-4F709F77B9C7@.microsoft.com...
> Is MS Reporting Services capable of adding new kinds of charts like a
pareto
> chart? If so, i'd like to find out if there's one that plugs in to VS
.NET
> 2003 designer and MS Reporting Services.
> Dundas does make a pareto chart in their enterprise charting package.
They
> also designed the charts used MSRS. Does anyone know if this would help?
> For those who aren't familiar with a pareto chart, it can be a mix of a
bar
> and a point (or line) chart that coorespond to two sets of Y axis. One Y
> axis can represent an item count and the other Y axis represents the
> cumulative percentage.
> Thanks,
> -Chris|||I had to create a Pareto chart manually by generating both series of values within the SQL statement and then manually defining the representation of Series 1 with a bar and Series 2 with a line.
--
Message posted via http://www.sqlmonster.com|||William,
I'm trying to find how to set one series to use a bar chart and another
series to use a line, but can't seem to find anything applicable. It looks
like I can only use one kind of representation. Could you explain how I can
represent two series using different charting techniques (one bar, one line)?
Are there any on-line tutorials that show how this is done?
Thanks in advance!!
-Chris Davis
"William Nichols via SQLMonster.com" wrote:
> I had to create a Pareto chart manually by generating both series of values within the SQL statement and then manually defining the representation of Series 1 with a bar and Series 2 with a line.
> --
> Message posted via http://www.sqlmonster.com
>|||Please read this related posting which also includes a sample:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=c229b25c-b2dc-41ac-923d-decbb253dc6e&sloc=en-us
You may also be interested in this KB How-to article:
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B842422
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"TUA-Chris" <TUAChris@.discussions.microsoft.com> wrote in message
news:D4CEC1F0-4D96-42A6-A46C-25189C5E998C@.microsoft.com...
> William,
> I'm trying to find how to set one series to use a bar chart and another
> series to use a line, but can't seem to find anything applicable. It
looks
> like I can only use one kind of representation. Could you explain how I
can
> represent two series using different charting techniques (one bar, one
line)?
> Are there any on-line tutorials that show how this is done?
> Thanks in advance!!
> -Chris Davis
> "William Nichols via SQLMonster.com" wrote:
> > I had to create a Pareto chart manually by generating both series of
values within the SQL statement and then manually defining the
representation of Series 1 with a bar and Series 2 with a line.
> >
> > --
> > Message posted via http://www.sqlmonster.com
> >|||Hi Robert,
I found a way to do this without going into the RDL itself. Sorry if my
response below looks like a book, but I'm explaining in detail in case anyone
else reading this will find it useful.
I found out that in Report Designer, if a second set of data is added, you
can have it plot in the form of a line. I also found that I can add
functions to the report itself without having to jump through a bunch of
hoops to get there. Using these two features, I was able to accomplish what
I wanted.
Obviously, if I had just plotted the same data with a line, visually, you
would see a line connecting the top parts of each column in my graph. What I
wanted to see was a percentage value that showed what percentage of the
current column plus all previous columns were. This would create a line that
would increase in value up to 100%. Being new to Reporting Services, I
couldn't find a way to count the values of previous columns into a value
which would represent my percentage line point. I dug around and found that
I could create my own functions to calculate a value to graph. This was a
break-through for me, because instead of plotting the value of the field
itself, I could plot the return value of a custom function I could write.
Here's how I did it:
1) Obtain total number of items.
2) Go to Report drop-down meny and select Report Properties, then select the
Code tab
3) Enter the following code:
dim RunningPercentage = 0.0
dim TotalItems = 0;
' TotalItems gets populated with the total number of items elsewhere. I use
this
' to determine percentage.
' This is used when calculating the cumulative percentage of tickets per
category.
function RunningPercentage (accum)
RunningPercentage = RunningPercentage + (accum / TotalItems * 100)
' Trucate decimal values smaller than one-tenths of a percent...
return int (RunningPercentage * 10) / 10
end function
' This just returns RunningPercentage without accumulating...
function ShowRunningPercentage ()
' Trucate decimal values smaller than one-tenths of a percent...
return (int (RunningPercentage * 10) / 10) & "%"
end function
4) In my graph, I drag a duplicate field onto the top of the graph (Drop
data fields here)
5) Right-click the new dulpicate item, select Properties
6) For Series Label, I call it "Cumulative %"
7) For Value, I enter, =Code.RunningPercentage(Count(Fields!Item.Value))
8) Click Appearance tab and check "Plot data as line"
9) Click Point Labels tab. In the Data label field, I enter,
=Code.ShowRunningPercentage()
10) To make the percentage curve look nice, I right click on field in the
Category section of the graph (at bottom) and select Properties. Then I
click on the Sorting tab and in the Expression column, I add
=Count(Fields.Item.Value) then for Direction, I select Descending.
I'm done! Now you will see a graph sorted from largest item on the left to
smallest item on the right with an increasing curve showing the accumulative
percentage.
Thanks for your help!
-Chris
"Robert Bruckner [MSFT]" wrote:
> Please read this related posting which also includes a sample:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=c229b25c-b2dc-41ac-923d-decbb253dc6e&sloc=en-us
> You may also be interested in this KB How-to article:
> http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B842422
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "TUA-Chris" <TUAChris@.discussions.microsoft.com> wrote in message
> news:D4CEC1F0-4D96-42A6-A46C-25189C5E998C@.microsoft.com...
> > William,
> >
> > I'm trying to find how to set one series to use a bar chart and another
> > series to use a line, but can't seem to find anything applicable. It
> looks
> > like I can only use one kind of representation. Could you explain how I
> can
> > represent two series using different charting techniques (one bar, one
> line)?
> > Are there any on-line tutorials that show how this is done?
> >
> > Thanks in advance!!
> >
> > -Chris Davis
> >
> > "William Nichols via SQLMonster.com" wrote:
> >
> > > I had to create a Pareto chart manually by generating both series of
> values within the SQL statement and then manually defining the
> representation of Series 1 with a bar and Series 2 with a line.
> > >
> > > --
> > > Message posted via http://www.sqlmonster.com
> > >
>
>|||Thanks for sharing your solution! Other people will certainly find it
useful.
Keep in mind, that your approach (calculating the "RunningValue" in code)
has limitations and will only work because you don't have category and
series grouping expressions in the chart. RS 2005 will enable the usage of
the RunningValue() function in charts which solves this for the general
case.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Yes, if you do the sorting and calculations in your dataset (through a
stored procedure)
"TUA-Chris" <TUAChris@.discussions.microsoft.com> wrote in message
news:A3DD2177-842E-40B2-A997-342F95CD4CA9@.microsoft.com...
> Hi Robert,
> I found a way to do this without going into the RDL itself. Sorry if my
> response below looks like a book, but I'm explaining in detail in case
anyone
> else reading this will find it useful.
> I found out that in Report Designer, if a second set of data is added, you
> can have it plot in the form of a line. I also found that I can add
> functions to the report itself without having to jump through a bunch of
> hoops to get there. Using these two features, I was able to accomplish
what
> I wanted.
> Obviously, if I had just plotted the same data with a line, visually, you
> would see a line connecting the top parts of each column in my graph.
What I
> wanted to see was a percentage value that showed what percentage of the
> current column plus all previous columns were. This would create a line
that
> would increase in value up to 100%. Being new to Reporting Services, I
> couldn't find a way to count the values of previous columns into a value
> which would represent my percentage line point. I dug around and found
that
> I could create my own functions to calculate a value to graph. This was a
> break-through for me, because instead of plotting the value of the field
> itself, I could plot the return value of a custom function I could write.
> Here's how I did it:
> 1) Obtain total number of items.
> 2) Go to Report drop-down meny and select Report Properties, then select
the
> Code tab
> 3) Enter the following code:
> dim RunningPercentage = 0.0
> dim TotalItems = 0;
> ' TotalItems gets populated with the total number of items elsewhere. I
use
> this
> ' to determine percentage.
> ' This is used when calculating the cumulative percentage of tickets per
> category.
> function RunningPercentage (accum)
> RunningPercentage = RunningPercentage + (accum / TotalItems * 100)
> ' Trucate decimal values smaller than one-tenths of a percent...
> return int (RunningPercentage * 10) / 10
> end function
> ' This just returns RunningPercentage without accumulating...
> function ShowRunningPercentage ()
> ' Trucate decimal values smaller than one-tenths of a percent...
> return (int (RunningPercentage * 10) / 10) & "%"
> end function
> 4) In my graph, I drag a duplicate field onto the top of the graph (Drop
> data fields here)
> 5) Right-click the new dulpicate item, select Properties
> 6) For Series Label, I call it "Cumulative %"
> 7) For Value, I enter, =Code.RunningPercentage(Count(Fields!Item.Value))
> 8) Click Appearance tab and check "Plot data as line"
> 9) Click Point Labels tab. In the Data label field, I enter,
> =Code.ShowRunningPercentage()
> 10) To make the percentage curve look nice, I right click on field in the
> Category section of the graph (at bottom) and select Properties. Then I
> click on the Sorting tab and in the Expression column, I add
> =Count(Fields.Item.Value) then for Direction, I select Descending.
> I'm done! Now you will see a graph sorted from largest item on the left
to
> smallest item on the right with an increasing curve showing the
accumulative
> percentage.
> Thanks for your help!
> -Chris
>
> "Robert Bruckner [MSFT]" wrote:
> > Please read this related posting which also includes a sample:
> >
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=c229b25c-b2dc-41ac-923d-decbb253dc6e&sloc=en-us
> >
> > You may also be interested in this KB How-to article:
> > http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B842422
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "TUA-Chris" <TUAChris@.discussions.microsoft.com> wrote in message
> > news:D4CEC1F0-4D96-42A6-A46C-25189C5E998C@.microsoft.com...
> > > William,
> > >
> > > I'm trying to find how to set one series to use a bar chart and
another
> > > series to use a line, but can't seem to find anything applicable. It
> > looks
> > > like I can only use one kind of representation. Could you explain how
I
> > can
> > > represent two series using different charting techniques (one bar, one
> > line)?
> > > Are there any on-line tutorials that show how this is done?
> > >
> > > Thanks in advance!!
> > >
> > > -Chris Davis
> > >
> > > "William Nichols via SQLMonster.com" wrote:
> > >
> > > > I had to create a Pareto chart manually by generating both series of
> > values within the SQL statement and then manually defining the
> > representation of Series 1 with a bar and Series 2 with a line.
> > > >
> > > > --
> > > > Message posted via http://www.sqlmonster.com
> > > >
> >
> >
> >

Friday, March 23, 2012

Parent Child Relationship

I created a parent child relationship in SA 2005. It works great, except when adding it to the cube and browsing it. It shows the ID instead of the Name attribute.

How do I make it show the name attribute?

Thanks in advance,

MardoSuppose you have the following columns in your table:
ID - Integer. Primary key column of your table
ParentID - Integer. The id of the parent object.
Name - String. The name of the object identified by ID.
Since things already work for you then you already have the key attribute and parent attribute set in your dimension. To resolve your problem select the parent attribute oin Dimension Structure page of the dimension editor and press F4 to activate the property grid displaying the properties of the selected attribute. Locate NameColumn property. Edit that property and make it binding to the Name column in your table.
|||When I do that, I get the following error after entering it...

Another 'DimensionAttribute' object has the 'Name' name.|||Most probably this is because you changed Name property in the property grid. You should edit *NameColumn* property.|||

It still shows the ID, not the name. Any other ideas? I did use the NameColumn.

Mardo

|||Can you send me your project to andrewgaATnetzeroDotcom ?|||
Its in your inbox.|||Yes, i have received it. I will look into it soon.|||

If you change NameColumn property for your *Organization* attribute to be Name (like in Parent Organization Id attribute) instead of "Organization Id" it will work.

This is most probably our bug and we will triage it.

Thank you.

|||Andrew,

Thank you. Ill give it a shot.

Mardo|||

Is this a bug in a version of BIDS? I have been struggling with this all day and I finally have found the issue. I am running what I think is a hotfixed version Microsoft SQL Server Analysis Services Designer Version 9.00.2047.00 of the AS Tools.

Every time I set the name column to be the descriptive column it changes back!

However, going into SSMS after building from the tool, scripting the dimension as alter, I see:

<NameColumn>
<NullProcessing>ZeroOrBlank</NullProcessing>
<DataType>WChar</DataType>
<Source xsi:type="ColumnBinding">
<TableID>dbo_membership_dim</TableID>
<ColumnID>membership_dim_key</ColumnID>
</Source>
</NameColumn>

I have set this in the tool to simply: membership.

So I change it here to membership and execute it and it says:

Which I am assuming is its "well structured" way of say "Atta boy" then it works like advertised. (Boy, this has been driving me batty for like 8 hours!

|||

I actually got it working by changing all of the attributes of the Hierarchy set to use the membership attribute, not just the Parent attribute/hierarchy. This stuff is interesting to say the least :)

|||

Hello Louis,

I was not able to reproduce on RTM version, but i will try on SP1 and our current bits to see if some regression was intoriduced.

When you say it changes back, what exactly do you mean? Do you mean:

1. Once you close the dialog box, where you picked the column you still see the previous column in the property grid.

2. Once you save the dimension (assuming you are connected directly to the server) and open it again you see the previous value.

3. Being in project mode (you edit files on the disk) you deploy and then still see the previous column binding when connected to the server and examined the deployed contents.

|||

I actually saw the proper looking text values for a moment, but after building/processing the cube, the numeric values showed up.

The table structures for the related tables are. I am doing the membership_dim related through the account_dim to the sales_fact. I built these tables using select...into from adventureWorksDw to try to approximate our structures and was wanting to try out the logical keys in the DSV, hence the lack of relationships, pkeys, etc). I am trying to build a demonstration cube to demonstrate all of the different constructs we need (this all got started as I tried to figure out what I was doing here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=522521&SiteID=1&mode=1)

I am thinking that this might have something to do with the name of the attribute being just membership, but I don't know.

Edit: The workaround (or it might be normal) was when I set the Membership Dim - Dimension Attribute's NameColumn to the membership column's value, not the Parent Membership Dim Key. Is that right?

Thanks for the help!

CREATE TABLE [dbo].[membership_dim](
[membership_dim_key] [int] NOT NULL,
[parent_membership_dim_key] [int] NULL,
[membership] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
PRIMARY KEY CLUSTERED ( [membership_dim_key] ASC )
)

ALTER TABLE [dbo].[membership_dim] WITH CHECK ADD FOREIGN KEY([parent_membership_dim_key])
REFERENCES [dbo].[membership_dim] ([membership_dim_key])

CREATE TABLE [dbo].[account_dim](
[account_dim_key] [int] IDENTITY(1,1) NOT NULL,
[account_number] [nvarchar](15) COLLATE Latin1_General_CI_AS NOT NULL,
[geography_dim_key] [int] NULL,
[marital_status] [nchar](1) COLLATE Latin1_General_CI_AS NULL,
[yearly_income] [money] NULL,
[total_children] [tinyint] NULL,
[education_level] [nvarchar](40) COLLATE Latin1_General_CI_AS NULL,
[occupation_type] [nvarchar](100) COLLATE Latin1_General_CI_AS NULL,
[commute_distance] [nvarchar](15) COLLATE Latin1_General_CI_AS NULL,
[customer_first_purchase_date_dim_key] [int] NULL,
[membership_dim_key] [int] NULL
)

CREATE TABLE [dbo].[sales_fact](
[sales_fact_key] [bigint] NULL,
[product_dim_key] [int] NOT NULL,
[ship_date_dim_key] [int] NULL,
[order_date_dim_key] [int] NULL,
[account_dim_key] [int] NOT NULL,
[discountAmount] [float] NULL,
[unit_price] [money] NULL,
[sales_order_number] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL,
[sales_order_line_number] [tinyint] NOT NULL,
[sales_territory_dim_key] [int] NULL
) ON [PRIMARY]

Parent Child Relationship

I created a parent child relationship in SA 2005. It works great, except when adding it to the cube and browsing it. It shows the ID instead of the Name attribute.

How do I make it show the name attribute?

Thanks in advance,

MardoSuppose you have the following columns in your table:

ID - Integer. Primary key column of your table

ParentID - Integer. The id of the parent object.

Name - String. The name of the object identified by ID.

Since things already work for you then you already have the key

attribute and parent attribute set in your dimension. To resolve your

problem select the parent attribute oin Dimension Structure page of the dimension editor and press F4 to

activate the property grid displaying the properties of the selected

attribute. Locate NameColumn property. Edit that property and make it

binding to the Name column in your table.|||When I do that, I get the following error after entering it...

Another 'DimensionAttribute' object has the 'Name' name.|||Most probably this is because you changed Name property in the property grid. You should edit *NameColumn* property.|||

It still shows the ID, not the name. Any other ideas? I did use the NameColumn.

Mardo

|||Can you send me your project to andrewgaATnetzeroDotcom ?|||
Its in your inbox.|||Yes, i have received it. I will look into it soon.|||

If you change NameColumn property for your *Organization* attribute to be Name (like in Parent Organization Id attribute) instead of "Organization Id" it will work.

This is most probably our bug and we will triage it.

Thank you.

|||Andrew,

Thank you. Ill give it a shot.

Mardo|||

Every time I set the name column to be the descriptive column it changes back!

However, going into SSMS after building from the tool, scripting the dimension as alter, I see:

I have set this in the tool to simply: membership.

So I change it here to membership and execute it and it says:

Which I am assuming is its "well structured" way of say "Atta boy" then it works like advertised. (Boy, this has been driving me batty for like 8 hours!

|||

Hello Louis,

I was not able to reproduce on RTM version, but i will try on SP1 and our current bits to see if some regression was intoriduced.

When you say it changes back, what exactly do you mean? Do you mean:

1. Once you close the dialog box, where you picked the column you still see the previous column in the property grid.

2. Once you save the dimension (assuming you are connected directly to the server) and open it again you see the previous value.

3. Being in project mode (you edit files on the disk) you deploy and then still see the previous column binding when connected to the server and examined the deployed contents.

|||

I actually saw the proper looking text values for a moment, but after building/processing the cube, the numeric values showed up.

I am thinking that this might have something to do with the name of the attribute being just membership, but I don't know.

Edit: The workaround (or it might be normal) was when I set the Membership Dim - Dimension Attribute's NameColumn to the membership column's value, not the Parent Membership Dim Key. Is that right?

Thanks for the help!

ALTER TABLE [dbo].[membership_dim] WITH CHECK ADD FOREIGN KEY([parent_membership_dim_key])
REFERENCES [dbo].[membership_dim] ([membership_dim_key])

Parent Child Relationship

I created a parent child relationship in SA 2005. It works great, except when adding it to the cube and browsing it. It shows the ID instead of the Name attribute.

How do I make it show the name attribute?

Thanks in advance,

Another 'DimensionAttribute' object has the 'Name' name.|||Most probably this is because you changed Name property in the property grid. You should edit *NameColumn* property.|||

It still shows the ID, not the name. Any other ideas? I did use the NameColumn.

Mardo

|||Can you send me your project to andrewgaATnetzeroDotcom ?|||
Its in your inbox.|||Yes, i have received it. I will look into it soon.|||

If you change NameColumn property for your *Organization* attribute to be Name (like in Parent Organization Id attribute) instead of "Organization Id" it will work.

This is most probably our bug and we will triage it.

Thank you.

|||Andrew,

Thank you. Ill give it a shot.

Mardo|||

Every time I set the name column to be the descriptive column it changes back!

However, going into SSMS after building from the tool, scripting the dimension as alter, I see:

I have set this in the tool to simply: membership.

So I change it here to membership and execute it and it says:

Which I am assuming is its "well structured" way of say "Atta boy" then it works like advertised. (Boy, this has been driving me batty for like 8 hours!

|||

Hello Louis,

I was not able to reproduce on RTM version, but i will try on SP1 and our current bits to see if some regression was intoriduced.

When you say it changes back, what exactly do you mean? Do you mean:

1. Once you close the dialog box, where you picked the column you still see the previous column in the property grid.

2. Once you save the dimension (assuming you are connected directly to the server) and open it again you see the previous value.

3. Being in project mode (you edit files on the disk) you deploy and then still see the previous column binding when connected to the server and examined the deployed contents.

|||

I actually saw the proper looking text values for a moment, but after building/processing the cube, the numeric values showed up.

I am thinking that this might have something to do with the name of the attribute being just membership, but I don't know.

Edit: The workaround (or it might be normal) was when I set the Membership Dim - Dimension Attribute's NameColumn to the membership column's value, not the Parent Membership Dim Key. Is that right?

Thanks for the help!

ALTER TABLE [dbo].[membership_dim] WITH CHECK ADD FOREIGN KEY([parent_membership_dim_key])
REFERENCES [dbo].[membership_dim] ([membership_dim_key])

Wednesday, March 28, 2012

Pareto chart in MS Reporting Services

Friday, March 23, 2012

Parent Child Relationship

Parent Child Relationship

Parent Child Relationship

Parameterized Expression

Blog Archive

About Me