Showing posts with label columns. Show all posts
Showing posts with label columns. Show all posts

Thursday, March 29, 2012

Dynamic Security and Rool ups

Hi

I thought I had this dynamic security worked out but I guess not.

this is on AS 2000.

I have a Fact table - one of the columns is a companyid, this is joined to the company dimension.

For security I have another table "empcompany" which lists all the users (ntusername) and the company codes they can access. I created a member property against the company code and used MDX similar to this:

filter([Companycode].[Companycode].members,([Companycode].CurrentMember.Properties("ntusername") = username))

The problem is that when I join the "empcompany" table to either the fact or to the company dimension instead of getting 1000 rows I get 80000+ rows and my numbers are wrong. Most of the users have access to more than 1 company.

Any one have any ideas?

Thanks

Steve

Hi Steve,

My suggestion would be to try the "Security Fact Table" approach with the "empcompany" table, as discussed in slides 18-32 of this webcast deck (you're using the "Member Property" approach above). In the 2nd approach, you don't need to join "empcompany" to the fact table - rather, it becomes the fact table for a "Permissions" cube, which is combined in a virtual cube:

http://support.microsoft.com/kb/828343/

>>

Support WebCast: Dynamic Dimension Security in Microsoft SQL Server 2000 Analysis Services

...

Dynamic Security
The Three Basic Approaches

Member property approach

Permission data (e.g. UserName) is stored at the desired dimension level

Security fact table approach

A permissions cube is combined with original source cube using a virtual cube

Filter members at the leaf level

Filter members at the non-leaf level

User-defined function callout

Call a user-defined function to reference an outside source (e.g. external RDBMS)

dynamic report header and detail

I am building a report which has 7 columns. Mon thru Sun. The header and data columns are dynamic

For ex the report looks like

Mon Tue Wed Thu Fri Sat Sun

-- - -- - --

2 2 4 2 4 5 6

0 7 6 7 9 4 2

The report header and data are dynamic. The seq of columns,data is not always the same. It could be Tue thru Mon., or Fri thru Thru...etc...

Please advice how to program this dynamic nature of the report

This is a matrix report with a group on day of the week.

Tuesday, March 27, 2012

dynamic report header and detail

I am building a report which has 7 columns. Mon thru Sun. The header and data columns are dynamic

For ex the report looks like

Mon Tue Wed Thu Fri Sat Sun

-- - -- - --

2 2 4 2 4 5 6

0 7 6 7 9 4 2

The report header and data are dynamic. The seq of columns,data is not always the same. It could be Tue thru Mon., or Fri thru Thru...etc...

Please advice how to program this dynamic nature of the report

This is a matrix report with a group on day of the week.sql

Dynamic Report Columns..?

I have a stored procedure that outputs a dynamic resultset with an unknown number of columns...

So, the resultset might look like:

RowId 03/16/2006 03/17/2006 03/18/2006 03/19/2006
Run of House 02 10 20 35
Suites 05 30 05 15

Or it could look like:

RowId 03/16/2006 03/17/2006
Run of House 02 10
Suites 05 30

Or perhaps:

RowId 03/16/2006 03/17/2006 03/18/2006 03/19/2006 03/20/2006 03/21/2006 03/22/2006
Run of House 02 10 20 35 10 15 45
Suites 05 30 05 15 05 10 80

I need a way that I can run a report that displays these results the from the stored procedure without having to know the number of columns. Is this possible..? If so, can you explain how this can be done in SSRS...

Thanks

the matrix component should fulfill your needs

its like a table but expanding horizontally
but its also a bit more tricky, as you will see

greets
gerhard

Dynamic Query :: HELP!

I'm the new guy and I could use a hand. Any insight would be appreciated.

Basically my task is to create a dynamic way to merge columns from multiple rows. Way the table is set up data is imported and one entry may be up to 3 rows one column from each row can be merged to form a long description, I would like to create a view that would allow you to dynamically query this data and have the description be merged in the result set.

row1 x y z
row2 x b z
row3 x m z

results should look like :: x, (y + b + m) , z

Thank you in advance for any help you can provide!numbers? you can SUM() them, but description? can't be done

you'll need to write some sort of script, either in the application that calls the database, or mayhaps in a stored proc that can cursor through the rows|||numbers? you can SUM() them, but description? can't be done

you'll need to write some sort of script, either in the application that calls the database, or mayhaps in a stored proc that can cursor through the rows

Unfortunately the data is all of char type. :confused:|||Any suggestions of how to go about doing this using a procedure?|||http://www.dbforums.com/t1038027.html|||Thanks for the link...I can't say I totally understand the function though.

I hate to ask this, and sound like an idiot, but can anyone give me the english break down?|||Cut and paste this code in to Query Analyzer...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40520

Monday, March 26, 2012

Dynamic Pivot

Hi

Is there a way for using the PIVOT synatx with a dyamic/unknown list of columns? In most PIVOT examples ther columns are "hard" coded but if for example, you are pivoting a sales order table where u don't know which Fiscal Years the orders cover, how could you do this?

Thanks

Hi,

I followed a link http://www.sqlmag.com/Article/ArticleID/43140/sql_server_43140.html

and get this one working in another forum.

http://forums.asp.net/2/1392175/ShowThread.aspx#1392175

If you have question for that, please let me know.

|||Thanks limno, this helps a lot

Thursday, March 22, 2012

Dynamic Parameters

Hi All,

I am stuck with a report, could you please help.

I have a dynamic stored procedure, which depending on Begindate and Enddate displays columns deptname and amount (depending on the month the amount field is going to sum and display the results in the appropriate date fields), that is sum(amount) in Jan05,Feb05,Mar05......depending on the Begindate and Enddate, it's displays the values.

EX:-

Deptname Jan 05 Feb 05 Mar 05

Housing 5 5 5

Shipping 45 56 85

Handling 10 14 18

How do i incorporate this into the Report designer, because when i use this Stored procedure and create a report, i can see the values coming in the Dataset, but i don't see the Amount values coming into the Preview Page.I only see deptname coming into the Preview page.

i am building a string in the Stored procedure.(Just Letting you know)

what can i do to make the amount values be displayed under appropriate month.

Thanks,

vnswathi.

What is the output of stored procedure? It should return date, category, Quantitiy/Number (appears in dataset)

If you return those three values from stored procedure then you can use Table grouping feature in the Report desinger to build the report.

Thanks
Murthy

|||

I am getting values from the Stored procedure and also able to see the results in the Dataset on the report designer DATA page, but when it comes to laying out the Report on the report designer LAYOUT page and see the PREVIEW, that is where my problem comes.

I am able to display the Department name column but not the Amount column which i have grouped by Year and month.

Any help is appreciated.

Thanks,

vnswathi.

|||

If you get into Dataset then you will surely have those values in Layout/Preview pages too.

Can you verify textbox properties in the layout screen and see are you able to get dataset values there or not? Also check visibility attribute of those text fileds too?

As a simple test, you can verify this by placing a textbox and set those dataset values and see the preview.

Thanks
Murthy

|||I have a other feeling that you may doing something wrong in the Grouping section. Are you building drill-down report? If yes you have to check toggle items?|||

Ok, i was doing it right, but the only thing which i was doing wrong was using a tabluar layout.

I have used Matrix layout and it worked.

Thanks,

vnswathi.

|||Great...any idea what is going wrong with the tabular report?

Dynamic Order By

I am trying to write a query that would allow me to switch the order of the result set by two columns and whether the order is ascending or descending. So far I have:

Code
------------------------------
declare @.Case char(1)
set @.Case = 'A'
select * from marketdata.dbo.dictionary
order by case when @.Case = 'A' then ParentID ASC else ParentID DESC end
, case when @.Case = 'A' then MemberID ASC else MemberID DESC end
------------------------------
My problem is that I get an error on the "ASC" or "DESC".

Thanks for any help offered!Hi, We do it a little differently, we have a stored procedure that accepts a sort variable. The sort variable determines the select used for the query. Here's part of the stored procedure

CREATE Procedure GetAllEmployees
(
@.sort char(1)
/* F = First, Last
L = Last, First
D = Deparment, First, Last
O = Location, First, Last
*/
)

As
if @.sort = 'D' begin
Select
PL.UID ,
PL.NTUserID,
PL.FirstName ,
PL.LastName ,
PL.Department,
LO.LocationName ,
DP.DepartmentName ,
PL.Extension ,
PL.FaxNumber,
PL.Email ,
PL.PagerEmail ,
PL.PagerNumber,
PL.HomePhone1,
PL.HomePhone2,
PL.CellPhone,
PL.bManager,
PL.bVP

from Employees PL inner join Locations LO on PL.Location = LO.UID inner join Departments DP on PL.Department = DP.DepartmentNumber
where bActive="Y"
order by PL.department, firstname, lastname
end

else if @.sort = 'L' begin

Select|||I currently have the functional equivilant of your code. The problem is that someone made a change to two of the selects but missed the last one. I was trying to consolidate the code to improve maintance. Thanks!

Wednesday, March 21, 2012

Dynamic number of columns.

Hi all.
I do have a "simple" problem on my hand.
I have one table containing PriceGroups. It could be 1 or many PriceGroups
pr. produkt. When I list the produkts data in one record I want the
PriceGroup description to be columns as well with the corresponding price. A
kind of dynamic Pivot JOIN with the productrecord.
E.g if there are 3 pricegroups to this produkt I would get 3 lines using
standard join.
ProductNr PricegroupDesc Price
ab Price1 200
ab Price2 300
ab Price3 500
What I want is 1 line with the 3 descriptions as columnheader and the
corresponding prices under.
ProductNr Price1 Price2 Price3 ..........PriceN
ab 200 300 500 nnnnnn
Thanx all.
geirhttp://www.aspfaq.com/2462
David Portas
SQL Server MVP
--|||Thank you David.
This got me startet on something I can use.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108636820.313261.178950@.c13g2000cwb.googlegroups.com...
> http://www.aspfaq.com/2462
> --
> David Portas
> SQL Server MVP
> --
>

Monday, March 19, 2012

Dynamic insert into table with datetime and int columns

Hello,
Me again..sorry about that!
Here is an excerpt from my stored procdure:
DECLARE
@.NbRecs int,
@.StartTime datetime,
@.EndTime datetime,
@.TableName varchar(50),
@.JobLog varchar(50),
@.DBName varchar(50)
@.SQL varchar(8000)
SET @.JobLog = @.DBName + '.dbo.DownloadJobLog'
IF OBJECT_ID(@.JobLog) IS NULL
BEGIN
SET @.SQL = 'CREATE TABLE ' + @.JobLog +
'(TableName varchar(50) COLLATE Latin1_General_CI_AS NOT NULL,
StartTime datetime NOT NULL,
EndTime datetime NOT NULL,
NbRecords int NOT NULL DEFAULT 0)
ON [PRIMARY]'
EXEC(@.SQL)
END
SET @.TableName = 'DOWNLOAD_START'
SET @.StartTime = CURRENT_TIMESTAMP
SET @.EndTime = CURRENT_TIMESTAMP
SET @.SQL = 'INSERT INTO ' + @.JobLog +
'VALUES(' + @.TableName + ', ' + @.StartTime + ', ' + @.EndTime +
', ' + @.Nbrecs + ')'
EXEC(@.SQL)
The create table portion works fine.
I get the following error message for the dynamic insert:
"Syntax error converting datetime from character
string."
Please help.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200511/1First of all, don't do this. This is really poor practice. Post more specs
and we can help you find a much better solution.
That aside, there are a few things you can do to make your current hmm...
dynamic query work, yet pretty much all of them defy logic. The main problem
with this setup is that while your data is in correct data type you'll have
to mangle it by casting it as character data to build the query string. Ugh!
If you truly are in love with dynamic SQL consider using the sp_executesql
procedure. And please do some reading on the matter here:
http://www.sommarskog.se/dynamic_sql.html
ML|||Thanks for the reply. I had a feeling this was the type of response I was
going to get.
Being a newcomer, I'm still not quite sure why dynamic SQL seems to be such
a
"FAUX PAS" and why it is not recommended, but unfortunately I am confined to
this solution.
That being said, I can create the table using character data types instead o
f
datetime and int if it will
make things easier.
Also what is the advantage of using sp_executesql as opposed to EXEC(@.sql)?
I'm not opposed to sp_executesql and if you can provide an example I will us
e
it.
Briefly, my procedure reads the tables from a linked server(AS400 library),
copies the table structures, adds an identuty column to the new structure an
d
then copies the data to a database on the local server.
Hope this helps and will glady provide more info if necessary
ML wrote:
>First of all, don't do this. This is really poor practice. Post more specs
>and we can help you find a much better solution.
>That aside, there are a few things you can do to make your current hmm...
>dynamic query work, yet pretty much all of them defy logic. The main proble
m
>with this setup is that while your data is in correct data type you'll have
>to mangle it by casting it as character data to build the query string. Ugh
!
>If you truly are in love with dynamic SQL consider using the sp_executesql
>procedure. And please do some reading on the matter here:
>http://www.sommarskog.se/dynamic_sql.html
>ML
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200511/1|||Think of a procedure as a system of pipelines that bring data from a source
outside a database into the database (and vice versa). Would you mix water
and oil in a regular pipeline? :)
sp_executesql will enable you to keep data in apporopriate data types, since
it supports parameters. There are good examples in Books Online:
http://msdn.microsoft.com/library/d.../>
ez_2h7w.asp
Have you read Erland's article?
ML|||Cismail via webservertalk.com wrote:
> Thanks for the reply. I had a feeling this was the type of response I was
> going to get.
> Being a newcomer, I'm still not quite sure why dynamic SQL seems to be suc
h a
> "FAUX PAS" and why it is not recommended
Then you haven't understood how a tiered architecture works and why we
use client-server databases at all. Nor have you understood why we use
stored procedures. Erland Sommarskog's article in the posted link is
essential reading on dynamic SQL.
I don't understand why you are "confined to this solution" when in your
example the only dynamic code you want to parameterize is the table
name (the rest can be done without dynamic code). Why wouldn't you know
the name(s) of your table(s) at design time? It seems you are creating
tables "on the fly" which is a great way to build a system that will
totally fail in terms of scalability and manageability.
If you genuinely know nothing about the structure of your data source
until runtime then you are probably using the wrong tool. Take a look
at some 3rd party data integration software product that will manage
the metadata for you.
David Portas
SQL Server MVP
--|||I will definately take both your advice and read the article.
In the meantime, perhaps someone can provide a better solution to my problem
.
...
I need to download a bunch(the exact number is irrelevant and it can change
at any time) of files
from the AS400 onto our server. How would you solve this......
David Portas wrote:
>Then you haven't understood how a tiered architecture works and why we
>use client-server databases at all. Nor have you understood why we use
>stored procedures. Erland Sommarskog's article in the posted link is
>essential reading on dynamic SQL.
>I don't understand why you are "confined to this solution" when in your
>example the only dynamic code you want to parameterize is the table
>name (the rest can be done without dynamic code). Why wouldn't you know
>the name(s) of your table(s) at design time? It seems you are creating
>tables "on the fly" which is a great way to build a system that will
>totally fail in terms of scalability and manageability.
>If you genuinely know nothing about the structure of your data source
>until runtime then you are probably using the wrong tool. Take a look
>at some 3rd party data integration software product that will manage
>the metadata for you.
>
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200511/1|||Cismail via webservertalk.com wrote:
> I need to download a bunch(the exact number is irrelevant and it can chang
e
> at any time) of files
> from the AS400 onto our server. How would you solve this......
>
That would depend on just what the interface is and what the
requirements are. Actually "downloading a bunch of files" isn't really
a SQL Server task. I'll assume that what you really want is to
integrate them into some SQL Server database. You say that the number
of files can change but I'll assume that these files are all of the
same or similar structure - or at least of a structure that you can
determine in advance.
As far as the interface goes, the main options are to download data as
files or to make it available online via an ODBC or OLEDB interface -
as a linked server for example.
One option for actually loading the data is to use a "staging" table.
By that I mean you have a SQL table that approximates the source data
structure. The data is appended to the staging table (maybe with a file
name and line number as additional columns) and then manipulated using
SQL statements to integrate it into your target relational database
schema. This is sometimes called the "ELT" approach: Extract - Load -
Transform.
Another method is "ETL" (Extract - Transform - Load). This usually
involves implementing the Transformation phase in some software tool
outside the database. There are a variety of data integration tools
that will accomplish this for you. If you are downloading raw EBCDIC
files from an AS400 system you will probably need to use such software
to convert them. ETL software tools are particularly useful if the
source structure changes often because the software can maintain its
own database of metadata and validation rules and you may be able to
monitor and change these with a minimum of coding and maintenance.
David Portas
SQL Server MVP
--|||Cismail via webservertalk.com (u14416@.uwe) writes:
> SET @.TableName = 'DOWNLOAD_START'
> SET @.StartTime = CURRENT_TIMESTAMP
> SET @.EndTime = CURRENT_TIMESTAMP
> SET @.SQL = 'INSERT INTO ' + @.JobLog +
> 'VALUES(' + @.TableName + ', ' + @.StartTime + ', ' + @.EndTime +
> ', ' + @.Nbrecs + ')'
> EXEC(@.SQL)
> The create table portion works fine.
> I get the following error message for the dynamic insert:
> "Syntax error converting datetime from character
> string."
You need to convert the datetime values to string to interpolate them
in the string. This is certainly messy. You meed
SELECT @.StartTimeStr = convert(varchar(23), @.Starttime, 126)
And then you must remember to quote them. Using sp_executesql is a lot
easier:
SET @.SQL = 'INSERT INTO ' + @.JobLog +
'VALUES(@.TableName, @.StartTime, @.EndTime, @.Nbrecs )'
SELET @.params = N'@.TableName varchar(50), @.StartTime datetime, ' +
N'@.EndTime datetime, @.NbRecords int'
EXEC sp_executesql @.SQL, @.params, @.TableName, @.Starttime, @.EndTime,
@.NbRecords
As I can read out from the script, the only thing that's really dynamic
here is the database name?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Sunday, March 11, 2012

Dynamic formatting of table columns

I have a table with a few columns in my report and I would like to make some
of the rows show up in bold in the report if the value in one of the column
is within a particular range. Can I do this using Expressions. If so, can
someone post a sample expression or let me know if there is another way to do
this. Thanks in advance.
PremYes,
You can do it using expressions.
Goto row property ->font->Fontweight
Then there you can write expression
like
=iif( Fields!XYZ.Value>0,"Bold","Normal")
Malkesh
"Prem" wrote:
> I have a table with a few columns in my report and I would like to make some
> of the rows show up in bold in the report if the value in one of the column
> is within a particular range. Can I do this using Expressions. If so, can
> someone post a sample expression or let me know if there is another way to do
> this. Thanks in advance.
> Prem
>

Dynamic Flat File Destination Name!

Hi,

I am trying to access from OLE DB source. And based on one of the columns, I need to write the data to a Flat File Destination.

For Example,

CustID, ProductID, Product Name, Product Description

Say I am going to write to a different Flat File for every product. So if there are 10 products in the data. There should be 10 Flat Files. Also the file name should include the Product Name And Product ID.

It is being done in a single Data Flow Task.

Right now the Property Expression for the File Name isSad which is not working)

Code Snippet

@.DestFolder + [Data Conversion].ProductID + @.TodaysDate + ".txt"

The ProductIDs are in the ascending order. Any help or guidance?

Thanks

-Leo

You can't do that in one data flow unless you want to split it out to a separate destination (and connection manager) for each product. That might be okay for three or four products, but not if you have a bunch.

What you should do instead is set up a ForEach loop based on a the results of a SELECT DISTINCT ProductId query. Then execute your data flow once for each ProductId, customizing both the Source query and the destination connection manager with your ProductId.
|||

Thanks for your thoughts. OK, can we achieve this i.e. first 1000 records from the query to file1 and then next 1000 to file2. Or vice versa i.e. Write all the records to a single file and then read 1000 in first iteration and next 1000 records in the next iteration and so on, without multiple reads of the whole destination file.

Thanks

--Leo

|||You might be able to use the Export Column transform. That transform writes out a file per row based on one column containing the filename and another column containing the data.|||

Correct me if I am wrong, Ted, but the Export Column transform is used when you have a binary field in your data flow that you want to persist to a file. That doesn't seem to be the case for Leo's data.

Leo, you can partition the file by rows, but what Jay is suggesting would be simpler and perform better. If you use a For Each Loop, you would get a list of all product IDs, then execute a data flow inside the loop for each product ID. Using expressions, you can alter both the OLE DB Source query and the destination file connection string for each iteration of the loop. You wouldn't ever read a destination file - you would only be writing rows to them. And you would only process each row once.

Here's a few examples of using ForEach loops:

http://sqljunkies.com/WebLog/knight_reign/archive/2005/03/25/9588.aspx

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/21/using-for-each-to-iterate-a-resultset.aspx

http://www.sqlis.com/55.aspx

http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

http://blogs.conchango.com/jamiethomson/archive/2005/06/15/SSIS_3A00_-Getting-a-value-out-of-a-file-to-use-it-in-our-package.aspx

and a search that might have some more:

http://search.live.com/results.aspx?q=foreach+&form=QBRE&q1=macro%3Ajamiet.ssis

|||The Export Column should work with text and ntext in addition to binary -- the column containing the file data just needs to be either a character or binary lob column. This is just another option for Leo with a different approach, requiring the data and file columns to be derived, which he might be able to do since he has already derived the filename column in his original post.|||

New Leo wrote:

Thanks for your thoughts. OK, can we achieve this i.e. first 1000 records from the query to file1 and then next 1000 to file2. Or vice versa i.e. Write all the records to a single file and then read 1000 in first iteration and next 1000 records in the next iteration and so on, without multiple reads of the whole destination file.

Thanks

--Leo

Leo,

Yes you can do this. I've covered this exact problem here:

Splitting a file into multiple files

(http://blogs.conchango.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx)

You won't be able to do it in a single data-flow tho.

-Jamie

Wednesday, March 7, 2012

Dynamic Dates in Columns on Matrix Report?

I need to display 12 months dynamically in columns of a matrix report, starting with the current MMM, yy - 3 in the first column, current MMM, yy and incrementing by 1 month in columns 2-12.

For example a report that would run on today's MM, yy (5/2007) would look something like this:

MAR-07, APR-07, MAY-07, JUN-07, JUL-07, AUG-07, AUG-07, SEP-07, OCT-07, NOV-07, DEC-07, JAN-08, FEB-08

(Col 1 - Col 12 separated by commas)

How do I do this in a matrix report column?

1- Filter your datasource to return only those rows that meet the date criteria you describe

If SQL Server...

Code Snippet

Select Item, SalesDate, Sales from YourTable Where SalesDate >= DateAdd(dd, -3, GetDate()) AND SalesDate <= DateAdd(dd, 9, GetDate())

For example:

Item Date Sales

Bicycle Mar-07 $5000

Bicycle Apr-07 $4500

etc...

Then set your date column as the Column Group in the matrix report

Then select that column header in SSRS Layout view, and change the format property to the MMM-yy

(If the granularity of the dates in your source data is not month, if it's by day instead for instance, then you'll need to aggregate that data in your query with the appropriate Group By calls)

Hope this helps,

Geof

Dynamic Dates in Columns on Matrix Report

I need to display 12 months dynamically in columns of a matrix report, starting with the current MMM, yy - 3 in the first column, current MMM, yy and incrementing by 1 month in columns 2-12.

For example a report that would run on today's MM, yy (5/2007) would look something like this:

MAR-07, APR-07, MAY-07, JUN-07, JUL-07, AUG-07, AUG-07, SEP-07, OCT-07, NOV-07, DEC-07, JAN-08, FEB-08

(Col 1 - Col 12 separated by commas)

How do I do this in a matrix report column?

I would do this by created an enumerated type for months and then iterate through them based on current month value(5). when you reach 12(or 13 depending on starting value) then reset to 0 or 1 respectively and increase the year by one.

Eric

|||

I'm still not sure how to actually do this. Can you send me an example of how to do this?

|||

Can you post your code please so I understand what you need.

|||

I ended up declaring a begindate and enddate, based on the current month in the stored procedure and passed that in.

Dynamic dataset column names and number of columns

I have a dataset that is based on a dynamic pivot stored procedure. There is
at least one column name that I know will be in the dataset, the key column,
but the rest of the columns are dependent on the data in the underlying table.
Once I have the dataset, how can I put it in the layout given that the
column names are dynamic and the number of columns are dynamic?
StephanieOn Aug 20, 4:36 pm, Stephanie <Stepha...@.discussions.microsoft.com>
wrote:
> I have a dataset that is based on a dynamic pivot stored procedure. There is
> at least one column name that I know will be in the dataset, the key column,
> but the rest of the columns are dependent on the data in the underlying table.
> Once I have the dataset, how can I put it in the layout given that the
> column names are dynamic and the number of columns are dynamic?
> Stephanie
You will want to look into Matrix Reports, where the pivoted column's
unique values are actually the number of columns in the report. This
provides the dynamic functionality you need.
Regards,
Enrique Martinez
Sr. Software Consultant

Sunday, February 26, 2012

Dynamic data elements for a data collection application

What is the better table design for a data collection application.
1. Vertical model (pk, attributeName, AttributeValue)
2. Custom columns (pk, custom1, custom2, custom3...custom50)

Since the data elements collected may change year over year, which
model better takes of this column dynamicness(mittal.pradeep@.gmail.com) writes:
> What is the better table design for a data collection application.
> 1. Vertical model (pk, attributeName, AttributeValue)
> 2. Custom columns (pk, custom1, custom2, custom3...custom50)
> Since the data elements collected may change year over year, which
> model better takes of this column dynamicness

The vertical model is certainly cleaner from a relational perspective.
It also requires less maintenance.

But admittedly queries can be more complex. If attributes can be of
different data types, you need some triggers to check this. A tip
is that the sql_variant data type is good in this case.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||1. But the data collection and reporting is in horizontal format. If
collected data is edited vertically, won't there be a extra steps of
converting horizontally obtained data to vertical and then vertical to
horizontal reports. In custom column model data always remains
horizontal. Won;t performance not be a issue in taking care of two
extra steps required in vertical model.
2. Won;t the concurrency be a issue, considering the fact that a
logical single horizontal row is edited as say 10 rows. Two people
might be changing same Primary key's different attributes at the same
time.

Erland Sommarskog wrote:
> (mittal.pradeep@.gmail.com) writes:
> > What is the better table design for a data collection application.
> > 1. Vertical model (pk, attributeName, AttributeValue)
> > 2. Custom columns (pk, custom1, custom2, custom3...custom50)
> > Since the data elements collected may change year over year, which
> > model better takes of this column dynamicness
> The vertical model is certainly cleaner from a relational perspective.
> It also requires less maintenance.
> But admittedly queries can be more complex. If attributes can be of
> different data types, you need some triggers to check this. A tip
> is that the sql_variant data type is good in this case.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||(mittal.pradeep@.gmail.com) writes:
> 1. But the data collection and reporting is in horizontal format. If
> collected data is edited vertically, won't there be a extra steps of
> converting horizontally obtained data to vertical and then vertical to
> horizontal reports. In custom column model data always remains
> horizontal. Won;t performance not be a issue in taking care of two
> extra steps required in vertical model.

If you are to present ten of those custom values as columns in a report,
you get a 10-way self-join. Certainly bulky in code. Performance is
probably not top-notch, but I don't see that it would be absymal.

> 2. Won;t the concurrency be a issue, considering the fact that a
> logical single horizontal row is edited as say 10 rows. Two people
> might be changing same Primary key's different attributes at the same
> time.

Good point. This can be handled fairly easily, but it requires more
careful programming than the horizontal method.

Overall, there certainly is a tradeoff. If the set of custom fields are
faily stable, only change once per year or so, you might be prepared to
take the extra maintenance cost. But if users asks for new fields every
week, then the horizontal method could be a nightmare.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks a lot for the reply.
1. I am not able to understand why i require a 10 way join though. If i
have a mapping between custom column and actual column, all i need is a
dyanamic sql generated from the mapping.

E.g.
table
--
pk, custom1, custom2, custom3...custom10

map
--
customColName ActualColName
custom1 ActualName1
custom2 ActualName2
...

Now I can generate dynamic sql using map.

2. As long as number of custom columns is enough to take care data
element additions which happen in a year. All that is needed is
addition of new elements to mapping table to decifer newly assigned
custom columns

Erland Sommarskog wrote:
> (mittal.pradeep@.gmail.com) writes:
> > 1. But the data collection and reporting is in horizontal format. If
> > collected data is edited vertically, won't there be a extra steps of
> > converting horizontally obtained data to vertical and then vertical to
> > horizontal reports. In custom column model data always remains
> > horizontal. Won;t performance not be a issue in taking care of two
> > extra steps required in vertical model.
> If you are to present ten of those custom values as columns in a report,
> you get a 10-way self-join. Certainly bulky in code. Performance is
> probably not top-notch, but I don't see that it would be absymal.
> > 2. Won;t the concurrency be a issue, considering the fact that a
> > logical single horizontal row is edited as say 10 rows. Two people
> > might be changing same Primary key's different attributes at the same
> > time.
> Good point. This can be handled fairly easily, but it requires more
> careful programming than the horizontal method.
> Overall, there certainly is a tradeoff. If the set of custom fields are
> faily stable, only change once per year or so, you might be prepared to
> take the extra maintenance cost. But if users asks for new fields every
> week, then the horizontal method could be a nightmare.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Neither. The design flaw you are calling a vertical model is actually
known as "EAV" or "Entity-Attribute-Value" because it is a common
newbie mistake. I have no idea what your #2 means.

As your data elements change, you need to re-design the schema --
constraints, keys, data types, etc. Learn RDBMS and do it right.

I found an old "cut & paste". Someone like you posted this:

CREATE TABLE EAV -- no key declared
(key_col VARCHAR (10) NULL,
attrib_value VARCHAR (50) NULL);

INSERT INTO EAV VALUES ('LOCATION','Bedroom');
INSERT INTO EAV VALUES ('LOCATION','Dining Room');
INSERT INTO EAV VALUES ('LOCATION','Bathroom');
INSERT INTO EAV VALUES ('LOCATION','courtyard');
INSERT INTO EAV VALUES ('EVENT','verbal aggression');
INSERT INTO EAV VALUES ('EVENT','peer');
INSERT INTO EAV VALUES ('EVENT','bad behavior');
INSERT INTO EAV VALUES ('EVENT','other');

CREATE TABLE EAV_DATA -note lack of constraints, defaults, DRI
(id INTEGER IDENTITY (1,1) NOT NULL,
bts_id INTEGER NULL,
key_col VARCHAR (10) NULL,
attrib_value VARCHAR (50) NULL );

INSERT INTO EAV_DATA VALUES (1, 'LOCATION', 'Bedroom');
INSERT INTO EAV_DATA VALUES (1, 'EVENT', 'other');
INSERT INTO EAV_DATA VALUES (1, 'EVENT', 'bad behavior');
INSERT INTO EAV_DATA VALUES (2, 'LOCATION', 'Bedroom');
INSERT INTO EAV_DATA VALUES (2, 'EVENT', 'other');
INSERT INTO EAV_DATA VALUES (2, 'EVENT', 'verbal aggression');
INSERT INTO EAV_DATA VALUES (3, 'LOCATION', 'courtyard');
INSERT INTO EAV_DATA VALUES (3, 'EVENT', 'other');
INSERT INTO EAV_DATA VALUES (3, 'EVENT', 'peer');

Ideally, the result set of the query would be Location Event count
(headings if possible)

Bedroom verbal aggression 1
Bedroom peer 0
Bedroom bad behavior 0
Bedroom other 2
Dining Room verbal aggression 0
Dining Room peer 0
Dining Room bad behavior 0
Dining Room other 0
Bathroom verbal aggression 0
Bathroom peer 0
Bathroom bad behavior 0
Bathroom other 0
courtyard verbal aggression 0
courtyard peer 1
courtyard bad behavior 0
courtyard other 1

Also, if possible, another query would return this result set. (I think
I know how to do this one.)

Location Event count
Bedroom verbal aggression 1
Bedroom other 2
courtyard peer 1
courtyard other 1

Here is a From: Thomas Coleman

SELECT Locations.locationvalue, Events.eventvalue,
(SELECT COUNT(*)
FROM (SELECT LocationData.locationvalue, EventData.eventvalue

FROM (SELECT TD1.bts_id, TD1.value AS locationvalue
FROM eav_data AS TD1
WHERE TD1.key = 'location') AS LocationData
INNER JOIN
(SELECT TD2.bts_id, TD2.value AS eventvalue
FROM eav_data AS TD2
WHERE TD2.key = 'event'
) AS EventData
ON LocationData.bts_id = EventData.bts_id
) AS CollatedEventData
WHERE CollatedEventData.locationvalue = Locations.locationvalue
AND CollatedEventData.eventvalue = Events.eventvalue
FROM (SELECT T1.value AS locationvalue
FROM EAV AS T1
WHERE T1.key = 'location') AS Locations,
(SELECT T2.value AS eventvalue
FROM EAV AS T2
WHERE T2.key = 'event') AS Events
ORDER BY Locations.locationvalue, Events.eventvalue ,
SELECT Locations.locationvalue, Events.eventvalue
(SELECT COUNT(*)
FROM (SELECT LocationData.locationvalue, EventData.eventvalue

FROM (SELECT TD1.bts_id, TD1.value AS locationvalue
FROM eav_data AS TD1
WHERE TD1.key = 'location') AS LocationData
INNER JOIN
(SELECT TD2.bts_id, TD2.value AS eventvalue
FROM eav_data AS TD2
WHERE TD2.key = 'event') AS EventData
ON LocationData.bts_id = EventData.bts_id)
AS CollatedEventData
WHERE CollatedEventData.locationvalue = Locations.locationvalue
AND CollatedEventData.eventvalue = Events.eventvalue)
FROM (SELECT T1.value AS locationvalue
FROM EAV AS T1
WHERE T1.key = 'location') AS Locations,
(SELECT T2.value AS eventvalue
FROM EAV AS T2
WHERE T2.key = 'event') AS Events;

Is the same thing in a proper schema as:

SELECT L.locationvalue, E.eventvalue, COUNT(*)
FROM Locations AS L, Events AS E
WHERE L.btd_id = E.btd_id
GROUP BY L.locationvalue, E.eventvalue;

The reason that I had to use so many subqueries is that those entities
are all lopped into the same table. There should be separate tables for
Locations and Events.

The column names are seriously painful. Beyond the fact that I
personally hate underscores in column names, using underscores at the
end of the column name is really non-intuitive. I removed them for my
example and came across the next column name faux pas. Don't use "key"
and "value" for column names. It means that the developer *has*
surround the column name with square brackets for everything which is a
serious pain.

There is such a thing as "too" generic. There has to be some structure
or everything becomes nothing more than a couple of tables called
"things". The real key (no pun intended) is commonality. Is there a
pattern to the data that they want to store? It may not be possible to
create one structure to rule them all and in the darkness bind them.

"To be is to be something in particular; to be nothing in particular is
to be nothing." --Aristole

All data integrity is destroyed. Any typo becomes a new attribute or
entity. Entities are found missing attributes, so all the reports are
wrong.

ry to write a single CHECK() constraint that works for all the
attributes of those 30+ entities your users created because you were
too dumb or too lazy to do your job. It can be done! You need a case
expression almost 70 WHEN clauses for a simple invoice and order system
when I tried it as an exercise.

ry to write a single DEFAULT clause for 30+ entities crammed into one
column. Impossible!

Try to set up DRI actions among the entities. If you thought the WHEN
clauses in the single CASE expression were unmaintainable, wait until
you see the "TRIGGERs from Hell" -- Too bad that they might not fit
into older SQL Server which had some size limits. Now maintain it.

For those who are interested, there are couple of links to articles I
found on the net:

Generic Design of Web-Based Clinical Databases
http://www.jmir.org/2003/4/e27*/

The EAV/CR Model of Data Representation
http://ycmi.med.yale.edu/nadka*rni/eav_CR_contents.htm

An Introduction to Entity-Attribute-Value Design for Generic
Clinical Study Data Management Systems
http://ycmi.med.yale.edu/nadka*rni/...%20*systems.htm

Data Extraction and Ad Hoc Query of an Entity- Attribute- Value
Database
http://www.pubmedcentral.nih.g*ov/a...l=pub*med&pubme...

Exploring Performance Issues for a Clinical Database Organized Using
an Entity-Attribute-Value Representation
http://www.pubmedcentral.nih.g*ov/a...l=pub*med&pubme...|||(mittal.pradeep@.gmail.com) writes:
> Thanks a lot for the reply.
> 1. I am not able to understand why i require a 10 way join though. If i
> have a mapping between custom column and actual column, all i need is a
> dyanamic sql generated from the mapping.
> E.g.
> table
> --
> pk, custom1, custom2, custom3...custom10
>
> map
> --
> customColName ActualColName
> custom1 ActualName1
> custom2 ActualName2
> ...
> Now I can generate dynamic sql using map.

When I said 10-way join I was thinking of the vertical solution. For the
horisontal solution it's a simple join - once you have gone through all
that SQL building. To me, this sounds more complex to implement. Then
again, if the user selects dynamically which columns he wants to see,
the horizontal solution would require dynamic SQL as well.

> 2. As long as number of custom columns is enough to take care data
> element additions which happen in a year. All that is needed is
> addition of new elements to mapping table to decifer newly assigned
> custom columns

I didn't realise that you had this mapping table. One could say that
this is a kind of compromise between the horizonal model and an entirely
static vertical model.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||CELKO,

If I change the data elements 10 times during the year, I cannot change
the front end 10 to accomodate the same. I need to come up with a
dyanamic solution to take care of same. Vertical approach is the best
way to get the same. Using custom columns is a compromise, as vertical
approach is harder to implement.

--CELKO-- wrote:
> Neither. The design flaw you are calling a vertical model is actually
> known as "EAV" or "Entity-Attribute-Value" because it is a common
> newbie mistake. I have no idea what your #2 means.
> As your data elements change, you need to re-design the schema --
> constraints, keys, data types, etc. Learn RDBMS and do it right.
> I found an old "cut & paste". Someone like you posted this:
> CREATE TABLE EAV -- no key declared
> (key_col VARCHAR (10) NULL,
> attrib_value VARCHAR (50) NULL);
> INSERT INTO EAV VALUES ('LOCATION','Bedroom');
> INSERT INTO EAV VALUES ('LOCATION','Dining Room');
> INSERT INTO EAV VALUES ('LOCATION','Bathroom');
> INSERT INTO EAV VALUES ('LOCATION','courtyard');
> INSERT INTO EAV VALUES ('EVENT','verbal aggression');
> INSERT INTO EAV VALUES ('EVENT','peer');
> INSERT INTO EAV VALUES ('EVENT','bad behavior');
> INSERT INTO EAV VALUES ('EVENT','other');
> CREATE TABLE EAV_DATA -note lack of constraints, defaults, DRI
> (id INTEGER IDENTITY (1,1) NOT NULL,
> bts_id INTEGER NULL,
> key_col VARCHAR (10) NULL,
> attrib_value VARCHAR (50) NULL );
> INSERT INTO EAV_DATA VALUES (1, 'LOCATION', 'Bedroom');
> INSERT INTO EAV_DATA VALUES (1, 'EVENT', 'other');
> INSERT INTO EAV_DATA VALUES (1, 'EVENT', 'bad behavior');
> INSERT INTO EAV_DATA VALUES (2, 'LOCATION', 'Bedroom');
> INSERT INTO EAV_DATA VALUES (2, 'EVENT', 'other');
> INSERT INTO EAV_DATA VALUES (2, 'EVENT', 'verbal aggression');
> INSERT INTO EAV_DATA VALUES (3, 'LOCATION', 'courtyard');
> INSERT INTO EAV_DATA VALUES (3, 'EVENT', 'other');
> INSERT INTO EAV_DATA VALUES (3, 'EVENT', 'peer');
> Ideally, the result set of the query would be Location Event count
> (headings if possible)
> Bedroom verbal aggression 1
> Bedroom peer 0
> Bedroom bad behavior 0
> Bedroom other 2
> Dining Room verbal aggression 0
> Dining Room peer 0
> Dining Room bad behavior 0
> Dining Room other 0
> Bathroom verbal aggression 0
> Bathroom peer 0
> Bathroom bad behavior 0
> Bathroom other 0
> courtyard verbal aggression 0
> courtyard peer 1
> courtyard bad behavior 0
> courtyard other 1
> Also, if possible, another query would return this result set. (I think
> I know how to do this one.)
> Location Event count
> Bedroom verbal aggression 1
> Bedroom other 2
> courtyard peer 1
> courtyard other 1
> Here is a From: Thomas Coleman
> SELECT Locations.locationvalue, Events.eventvalue,
> (SELECT COUNT(*)
> FROM (SELECT LocationData.locationvalue, EventData.eventvalue
> FROM (SELECT TD1.bts_id, TD1.value AS locationvalue
> FROM eav_data AS TD1
> WHERE TD1.key = 'location') AS LocationData
> INNER JOIN
> (SELECT TD2.bts_id, TD2.value AS eventvalue
> FROM eav_data AS TD2
> WHERE TD2.key = 'event'
> ) AS EventData
> ON LocationData.bts_id = EventData.bts_id
> ) AS CollatedEventData
> WHERE CollatedEventData.locationvalue = Locations.locationvalue
> AND CollatedEventData.eventvalue = Events.eventvalue
> FROM (SELECT T1.value AS locationvalue
> FROM EAV AS T1
> WHERE T1.key = 'location') AS Locations,
> (SELECT T2.value AS eventvalue
> FROM EAV AS T2
> WHERE T2.key = 'event') AS Events
> ORDER BY Locations.locationvalue, Events.eventvalue ,
> SELECT Locations.locationvalue, Events.eventvalue
> (SELECT COUNT(*)
> FROM (SELECT LocationData.locationvalue, EventData.eventvalue
> FROM (SELECT TD1.bts_id, TD1.value AS locationvalue
> FROM eav_data AS TD1
> WHERE TD1.key = 'location') AS LocationData
> INNER JOIN
> (SELECT TD2.bts_id, TD2.value AS eventvalue
> FROM eav_data AS TD2
> WHERE TD2.key = 'event') AS EventData
> ON LocationData.bts_id = EventData.bts_id)
> AS CollatedEventData
> WHERE CollatedEventData.locationvalue = Locations.locationvalue
> AND CollatedEventData.eventvalue = Events.eventvalue)
> FROM (SELECT T1.value AS locationvalue
> FROM EAV AS T1
> WHERE T1.key = 'location') AS Locations,
> (SELECT T2.value AS eventvalue
> FROM EAV AS T2
> WHERE T2.key = 'event') AS Events;
> Is the same thing in a proper schema as:
> SELECT L.locationvalue, E.eventvalue, COUNT(*)
> FROM Locations AS L, Events AS E
> WHERE L.btd_id = E.btd_id
> GROUP BY L.locationvalue, E.eventvalue;
> The reason that I had to use so many subqueries is that those entities
> are all lopped into the same table. There should be separate tables for
> Locations and Events.
> The column names are seriously painful. Beyond the fact that I
> personally hate underscores in column names, using underscores at the
> end of the column name is really non-intuitive. I removed them for my
> example and came across the next column name faux pas. Don't use "key"
> and "value" for column names. It means that the developer *has*
> surround the column name with square brackets for everything which is a
> serious pain.
> There is such a thing as "too" generic. There has to be some structure
> or everything becomes nothing more than a couple of tables called
> "things". The real key (no pun intended) is commonality. Is there a
> pattern to the data that they want to store? It may not be possible to
> create one structure to rule them all and in the darkness bind them.
> "To be is to be something in particular; to be nothing in particular is
> to be nothing." --Aristole
> All data integrity is destroyed. Any typo becomes a new attribute or
> entity. Entities are found missing attributes, so all the reports are
> wrong.
> ry to write a single CHECK() constraint that works for all the
> attributes of those 30+ entities your users created because you were
> too dumb or too lazy to do your job. It can be done! You need a case
> expression almost 70 WHEN clauses for a simple invoice and order system
> when I tried it as an exercise.
> ry to write a single DEFAULT clause for 30+ entities crammed into one
> column. Impossible!
> Try to set up DRI actions among the entities. If you thought the WHEN
> clauses in the single CASE expression were unmaintainable, wait until
> you see the "TRIGGERs from Hell" -- Too bad that they might not fit
> into older SQL Server which had some size limits. Now maintain it.
> For those who are interested, there are couple of links to articles I
> found on the net:
> Generic Design of Web-Based Clinical Databases
> http://www.jmir.org/2003/4/e27*/
> The EAV/CR Model of Data Representation
> http://ycmi.med.yale.edu/nadka*rni/eav_CR_contents.htm
> An Introduction to Entity-Attribute-Value Design for Generic
> Clinical Study Data Management Systems
> http://ycmi.med.yale.edu/nadka*rni/...20*systems..htm
>
> Data Extraction and Ad Hoc Query of an Entity- Attribute- Value
> Database
> http://www.pubmedcentral.nih.g*ov/a...l=pub*med&pubme...
>
> Exploring Performance Issues for a Clinical Database Organized Using
> an Entity-Attribute-Value Representation
> http://www.pubmedcentral.nih.g*ov/a...l=pub*med&pubme...

Dynamic creation of columns

Scenario:
I would like to create a New table named orders_table.
Since the user can choose how many products he wants.
if he choose 3 the table should be 3 columns
if he choose 44 the table should be 44 columns and so on. Is this possible?

So, for your scenario, there would have to be a new table for each user, because one table can only have a certain number of columns. You may want to consider creating a record in one table for each product the user chooses. If you are determined to create one for every user, then you could do something like this:

declare @.userproductcount int, @.i int
declare @.tblname varchar(10)
declare @.string varchar(8000)
set @.userproductcount = 5
set @.tblname = 'test'
set @.i = 1
set @.string = 'create table ' + @.tblname + ' ('

while @.i <= @.userproductcount
begin
set @.string = @.string + 'fldname' + + cast(@.i as varchar(10)) + ' int' + case when @.i = @.userproductcount then ')' else ',' end
set @.i = @.i + 1
end

exec @.string|||This sounds like a pretty bad idea, personally. Is this a result set? You shouldn't create a table based on what a user wants.

Friday, February 24, 2012

dynamic columns: in search of the Tatrix data region

This subject has been much discussed but I haven't found a solution
that really works in my situation.
What we need here is a hybrid table/matrix data region with static
columns on the left and (at least) one dynamic column on the right.
Thus do I reveal my cultural bias and no doubt my ignorance of
Reporting Services as well. Maybe this can be done, but I haven't
figured it out.
Wishlist for this "Tatrix" data region:
1) No corner cell
2) A header row that runs straight across the top like a table
3) Header row repeats on each new page
4) Ability to define n static columns at left or right of region
5) Ability to define at least one dynamic column at right or left of
data region, respectively.
In my case I have an inventory with the usual ItemID, ItemName,
ItemCategory fields in the Dataset. These and other fields are
enterprise-level, common across all business units. An ItemRetailPrice
field is potentially unique to each business unit. Therefore I'd like
to have a Tatrix that would display the enterprise-level fields as
static columns and the business-unit-level field(s) as dynamic columns.
Ideally you could have repeating groups of dynamic columns
growing/shrinking out to the side.
The only kludge so far that looks something like what the customer
wants is a Matrix with an Image in the corner cell, simulating the
headers for the static columns. It's hard to get the Image right since
the dimensions of the corner cell change from format to format. The
corner cell graphic seems to shrink on export to Excel. There's no way
to dynamically change the embedded Image to display the product group
you're looking at in a given report.
Apologies if this has been done and described and I didn't find it,
Highly ObscureHi,
I'm sorry that I could not find a way to do this with Matrix in Reporting
Services. You are welcome to send suggestions for product enhancements that
you would like to see in future versions of Microsoft products to us by
submitting MS Wish to mswish@.microsoft.com. By submitting your concerns,
you can get your voice into the design plans for upcoming versions.
Microsoft takes customer requests and suggestions very seriously. MSWISH is
your voice to the development team. A significant number of the design
change requests come from our customers through the MSWISH alias and the
MSWISH web site. http://www.microsoft.com/mswish. I encourage you to submit
this request.
Sincerely,
William Wang
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>NNTP-Posting-Date: Thu, 10 Mar 2005 08:20:34 -0600
>From: "HighlyObscure" <HighlyObscure@.nospam.nospam>
>Subject: dynamic columns: in search of the Tatrix data region
>Newsgroups: microsoft.public.sqlserver.reportingsvcs
>User-Agent: XanaNews/1.17.1.2
>MIME-Version: 1.0
>Content-Type: text/plain; charset=iso-8859-1
>Message-ID: <-oGdnd2P_48vyq3fRVn-hw@.giganews.com>
>Date: Thu, 10 Mar 2005 08:20:34 -0600
>Lines: 42
>X-Trace:
sv3-AdcAxaYs02709d1QLmyISb8L5LZ1SscpiD0Uxn9maeA2kWFvFAuwfshGnq4q+8NXAFKwvaVH
CDw2zH1!3UOd+sBB1baL2C/VrYQhkqjo1KZExYUfZWDixZF1cqFUrf7vda8YFYzxJeLF9A==>X-Complaints-To: abuse@.giganews.com
>X-DMCA-Notifications: http://www.giganews.com/info/dmca.html
>X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers
>X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your
complaint properly
>X-Postfilter: 1.3.32
>Path:
TK2MSFTNGXA02.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFTNGP0
8.phx.gbl!newsfeed00.sul.t-online.de!t-online.de!border2.nntp.dca.giganews.c
om!border1.nntp.dca.giganews.com!nntp.giganews.com!local01.nntp.dca.giganews

dynamic columns/aliases - Continue

Hi
Following my previous question. Here is the implemetation.
I have to design a report which display monthes as columns.
The number of columns in the report depends on the range paramters.
For example : if the Range is from 01/02/05 to 30/06/05 there ara 6 columns
shown.
01/05,02/05....06/05. (column alias) .
if the range of dates are 8 monthes range there are 8 dynamic columns, etc'
1) My first major problem is to write a SP or a function that return a
dynamic number of columns.
2) Second,(This is not a must) to change column alias (Heading) according to
the parameters.
thanksWhat reporting tool do you use? This is called a cross tab report and
any decent reporting tool will render it for you client-side with no
programming required. Otherwise you'll have to use dynamic SQL, which
is not much fun.
David Portas
SQL Server MVP
--|||>> 1) My first major problem is to write a SP or a function that return a dy
namic number of columns. <<
NO, your **first** problem is that you missed the basic concepts of
RDBMS. A query returns a table; a table has a fixed number of columns.
A query is not a report; a query sends information to a front end
which can have a reporting tool in it.
This is definitions, not anything fancy.
\If you want to be a bad SQL programmer, this can be done with dynamic
SQL and a ton of procedural code that will be a XXXXX to maintain.|||Yes. My Big Problem is indeed the Reporting tool which is Custom Reporting
tool of an ERP software that simply translate
the resulted query to a GUI look alike Report.
Unfortunately , There's Nothing I can do about it.
What are my options with dynamic sql ?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1126806242.941597.208120@.g44g2000cwa.googlegroups.com...
> What reporting tool do you use? This is called a cross tab report and
> any decent reporting tool will render it for you client-side with no
> programming required. Otherwise you'll have to use dynamic SQL, which
> is not much fun.
> --
> David Portas
> SQL Server MVP
> --
>

Dynamic columns using MDX and parameters

I am linking to MS Analysis Services datasrce. When reporting I would like
to have the column to be displayed be determined by a parameter.
ie.
Parameter pFaccode = 'ABC'
report layout using a table:
Facility 'ABC' Case Count
Male 100
Female 200
Parameter pFaccode = 'XYZ'
report layout using a table:
Facility 'XYZ' Case Count
Male 300
Female 350
In the data tab of MS Visual Studio .NET report designer, I first issue the
following query so the data fields get created:
"SELECT {[ABC]} ON COLUMNS, {[Sex].Members} ON ROWS FROM [cases_test]"
This creates "sex_sexdesc" and "fac_ABC" dataset fields.
I then add the parameter logic by modifying the query as follows:
="SELECT {[" & Parameters!pFaccode.Value & "]} ON COLUMNS, {[Sex].Members}
ON ROWS FROM [cases_test]"
The report works when the parameter passed is "ABC". It will not work when
I pass "XYZ". When passing "XYZ" I get the following error:
"The 'fac_ABC' field is missing from the returned result set from the data
source."
I have successfully used parameters in the ROWS and WHERE sections of the
MDX queries. Is it possible to do it in the COLUMNS section as I described
above? If so, how?
Thank You,
Dan
___________________________
Daniel J. Zaccarine
Hanford Bay Associates, Ltd.
___________________________Hi Dan,
From your descriptions, I understood that you would like to use
parameterized MDX queries in columns. Have I understood you? Correct me if
I was wrong.
I have noticed that you are able to make it parameterized in ROWS and
WHERE, it should be no difference wherever you put the parameters.
I download the samples below and add a parameter in MDX_Expression.rdl like
below:
="SELECT {[Measures].[" + Parameters!Report_Parameter_1.Value + "]} on
Columns, NON EMPTY [Product].[Product Name].members on Rows from Sales
WHERE ([" + Parameters!pCity.Value + "])"
It works fine:-)
Reporting Services 2000 - OLAP Sample Reports
http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-
9c83-c6801f0576ff&DisplayLang=en
For your issues, I would like to suggest you REFRESH the dataset first to
see whether it works. Secondly, Set a default value for your parameter and
then try again.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||You have understood.
I am still unable to get this to work. I tried your sample below and I
experienced the same behavior as I did with my project.
The first thing I did was refresh the following MDX query:
SELECT {[Measures].[Unit Sales] on columns, NON EMPTY [Product].[Product
Name].members on Rows from Sales
This produced "Measures_Unit_Sales" data field. This is the field I display
in the table.
I then modified the query to incorporate the parameter exactly as you
stated:
="SELECT {[Measures].[" + Parameters!Report_Parameter_1.Value + "]} on
Columns, NON EMPTY [Product].[Product Name].members on Rows from Sales
WHERE ([" + Parameters!pCity.Value + "])"
When I ran the report it worked when the parameter was "Unit Sales", but it
does not work for parameter "Store Cost". I get the following error:
"The data set 'FoodMart_2000' contains a definition for the field
'Measures_Unit_Sales'. This field is missing from the returned result set
from the data source."
I must be missing a step. RS seems to want the original data field
returned.
Thanks for your help,
Dan
___________________________
Daniel J. Zaccarine
Hanford Bay Associates, Ltd.
Phone: 716-636-0100 ext-13
Fax: 716-636-1458
dan_zaccarine@.hanfordbay.com
http://www.hanfordbay.com
___________________________
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:cprxzdJzEHA.3152@.cpmsftngxa10.phx.gbl...
> Hi Dan,
> From your descriptions, I understood that you would like to use
> parameterized MDX queries in columns. Have I understood you? Correct me if
> I was wrong.
> I have noticed that you are able to make it parameterized in ROWS and
> WHERE, it should be no difference wherever you put the parameters.
> I download the samples below and add a parameter in MDX_Expression.rdl
like
> below:
> ="SELECT {[Measures].[" + Parameters!Report_Parameter_1.Value + "]} on
> Columns, NON EMPTY [Product].[Product Name].members on Rows from Sales
> WHERE ([" + Parameters!pCity.Value + "])"
> It works fine:-)
> Reporting Services 2000 - OLAP Sample Reports
>
http://www.microsoft.com/downloads/details.aspx?FamilyID=f9b6e945-1f4c-4b7c-
> 9c83-c6801f0576ff&DisplayLang=en
> For your issues, I would like to suggest you REFRESH the dataset first to
> see whether it works. Secondly, Set a default value for your parameter and
> then try again.
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
>
>|||Hi Dan,
It seems really odd, you might not have bound the created field to the data
base field correctly. Unfortunately, I was not able to reproduce your issue
in house. Would you please try the following steps?
1. Set default value (such as %) to the filed
Parameters!Report_Parameter_1.Value
2. Does it work after refresh the Dataset
3. Delete and then Recreate the Dataset
If the above steps all fail, plase paste your rdl files here or send it
directly to me, v-mingqc@.online.microsoft.com (remove online, it's only for
spam)
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Michael,
I tried your suggestions and I cannot get it to work. I appended the .rdl
code below. It seems that the problem is that when I originally create the
dataset using,
"SELECT {[Measures].[Unit Sales] on columns, NON EMPTY [Product].[Product
Name].members on Rows from Sales",
it creates the "Measures_Unit_Sales" data field. I place this field in my
table object. When I run the report using the parameter it expects the
"Measures_Unit_Sales" data field to be returned.
Thanks for your Help,
Dan
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>Brown</BackgroundColor>
<BorderWidth>
<Bottom>3pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>18pt</FontSize>
<TextAlign>Center</TextAlign>
<Color>Whitev/Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.33inv/Height>
<CanGrow>true</CanGrow>
<Value>cascading_report</Value>
</Textbox>
<Table Name="table1">
<Style>
<BackgroundColor>White</BackgroundColor>
<BorderWidth>
<Top>3pt</Top>
</BorderWidth>
<BorderStyle>
<Top>Solidv/Top>
</BorderStyle>
</Style>
<Header>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
vStyle>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
v/Style>
vZIndex>3</ZIndex>
vrd:DefaultName>textbox2</rd:DefaultName>
vCanGrow>true</CanGrow>
vValue>Promotion Media Media Type</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
vStyle>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
v/Style>
vZIndex>2</ZIndex>
vrd:DefaultName>textbox3</rd:DefaultName>
vCanGrow>true</CanGrow>
vValue>Measures Unit Sales</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Promotion_Media_Media_Type">
vStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
v/Style>
vZIndex>1</ZIndex>
vrd:DefaultName>Promotion_Media_Media_Type</rd:DefaultName>
vCanGrow>true</CanGrow>
vValue>=Fields!Promotion_Media_Media_Type.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Measures_Unit_Sales">
vStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
v/Style>
vrd:DefaultName>Measures_Unit_Sales</rd:DefaultName>
vCanGrow>true</CanGrow>
vValue>=Fields!Measures_Unit_Sales.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>FoodMart_2000</DataSetName>
<Top>0.33in</Top>
<Width>2in</Width>
<TableColumns>
<TableColumn>
<Width>1inv/Width>
</TableColumn>
<TableColumn>
<Width>1inv/Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>0.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="FoodMartv2000">
<rd:DataSourceID>11ccb8b2-d400-4ba6-ac9e-2a7cc7bba86d</rd:DataSourceID>
<DataSourceReference>FoodMart 2000</DataSourceReference>
</DataSource>
</DataSources>
<Width>5in</Width>
<DataSets>
<DataSet Name="FoodMart_2000">
<Fields>
<Field Name="Promotion_Media_Media_Type">
<DataField>[Promotion Media].[Media Type].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Measures_Unit_Sales">
<DataField>[Measures].[Unit Sales]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>FoodMart 2000</DataSourceName>
<CommandText>="SELECT {[Measures].[" + Parameters!pMeasure.Value + "]} on
columns,ORDER([Promotion Media].[Media Type].members,[Measures].[Unit
Sales],DESC) on rows FROM SALES"</CommandText>
</Query>
</DataSet>
<DataSet Name="dsCountry">
<Fields>
<Field Name="Customers_Country">
<DataField>[Customers].[Country].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Measures_CountryUniqueName">
<DataField>[Measures].[CountryUniqueName]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_CountryDisplayName">
<DataField>[Measures].[CountryDisplayName]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>FoodMart 2000</DataSourceName>
<CommandText>with member [Measures].[CountryUniqueName] as
'[Customers].currentmember.UniqueName'
member [Measures].[CountryDisplayName] as '[Customers].currentmember.Name'
SELECT {[Measures].[CountryUniqueName],[Measures].[CountryDisplayName]} on
Columns,
{[Customers].[Country].membersv} on rows
from
Sales</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
<DataSet Name="dsState">
<Fields>
<Field Name="Customers_Country">
<DataField>[Customers].[Country].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Customers_State_Province">
<DataField>[Customers].[State Province].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Measures_StateUniqueName">
<DataField>[Measures].[StateUniqueName]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_StateDisplayName">
<DataField>[Measures].[StateDisplayName]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>FoodMart 2000</DataSourceName>
<CommandText>= "with member [Measures].[StateUniqueName] as
'[Customers].currentmember.uniqueName'
member [Measures].[StateDisplayName] as '[Customers].currentmember.name'
SELECT {[Measures].[StateUniqueName], [Measures].[StateDisplayName]} on
Columns,
{DESCENDANTS({" & Parameters!pCountry.Value & " },
[Customers].[State Province])}von rows from sales"</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>63006e5b-c9b5-4104-adbd-20dcf956075c</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<ReportParameters>
<ReportParameter Name="pCountry">
<DataType>Stringv/DataType>
<AllowBlank>truev/AllowBlank>
<Prompt>Country:v/Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>dsCountry</DataSetName>
<ValueField>Measures_CountryUniqueName</ValueField>
<LabelField>Measures_CountryDisplayName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
<ReportParameter Name="pState">
<DataType>Stringv/DataType>
<AllowBlank>truev/AllowBlank>
<Prompt>State:</Prompt>
<ValidValues>
<DataSetReference>
<DataSetName>dsState</DataSetName>
<ValueField>Measures_StateUniqueName</ValueField>
<LabelField>Measures_StateDisplayName</LabelField>
</DataSetReference>
</ValidValues>
</ReportParameter>
<ReportParameter Name="pMeasure">
<DataType>Stringv/DataType>
<Nullable>true</Nullable>
<DefaultValue>
<Values>
<Value>Unit Sales</Value>
</Values>
</DefaultValue>
<AllowBlank>truev/AllowBlank>
<Prompt>Measure</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>Unit Sales</Value>
</ParameterValue>
<ParameterValue>
<Value>Store Cost</Value>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
</ReportParameters>
<Language>en-US</Language>
</Report>
--
___________________________
Daniel J. Zaccarine
Hanford Bay Associates, Ltd.
Phone: 716-636-0100 ext-13
Fax: 716-636-1458
dan_zaccarine@.hanfordbay.com
http://www.hanfordbay.com
___________________________
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:wcEuX9VzEHA.3440@.cpmsftngxa10.phx.gbl...
> Hi Dan,
> It seems really odd, you might not have bound the created field to the
data
> base field correctly. Unfortunately, I was not able to reproduce your
issue
> in house. Would you please try the following steps?
> 1. Set default value (such as %) to the filed
> Parameters!Report_Parameter_1.Value
> 2. Does it work after refresh the Dataset
> 3. Delete and then Recreate the Dataset
> If the above steps all fail, plase paste your rdl files here or send it
> directly to me, v-mingqc@.online.microsoft.com (remove online, it's only
for
> spam)
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Michael,
Were you able to find the error?
Let me know asap, I am depending it.
Thanks,
Dan
"Dan" <dan_zaccarine@.hanfordbay.com> wrote in message
news:euHiW8YzEHA.2600@.TK2MSFTNGP09.phx.gbl...
> Michael,
> I tried your suggestions and I cannot get it to work. I appended the
.rdl
> code below. It seems that the problem is that when I originally create
the
> dataset using,
> "SELECT {[Measures].[Unit Sales] on columns, NON EMPTY [Product].[Product
> Name].members on Rows from Sales",
> it creates the "Measures_Unit_Sales" data field. I place this field in my
> table object. When I run the report using the parameter it expects the
> "Measures_Unit_Sales" data field to be returned.
> Thanks for your Help,
> Dan
> <?xml version="1.0" encoding="utf-8"?>
> <Report
>
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
>
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontFamily>Times New Roman</FontFamily>
> <BackgroundColor>Brown</BackgroundColor>
> <BorderWidth>
> <Bottom>3pt</Bottom>
> </BorderWidth>
> <BorderColor>
> <Bottom>Black</Bottom>
> </BorderColor>
> <BorderStyle>
> <Bottom>Solid</Bottom>
> </BorderStyle>
> <FontSize>18pt</FontSize>
> <TextAlign>Center</TextAlign>
> <Color>Whitev/Color>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <Height>0.33inv/Height>
> <CanGrow>true</CanGrow>
> <Value>cascading_report</Value>
> </Textbox>
> <Table Name="table1">
> <Style>
> <BackgroundColor>White</BackgroundColor>
> <BorderWidth>
> <Top>3pt</Top>
> </BorderWidth>
> <BorderStyle>
> <Top>Solidv/Top>
> </BorderStyle>
> </Style>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.21in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox2">
> vStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <BorderWidth>
> <Bottom>2pt</Bottom>
> </BorderWidth>
> <BorderColor>
> <Bottom>DarkRed</Bottom>
> </BorderColor>
> <BorderStyle>
> <Bottom>Solid</Bottom>
> </BorderStyle>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>900</FontWeight>
> v/Style>
> vZIndex>3</ZIndex>
> vrd:DefaultName>textbox2</rd:DefaultName>
> vCanGrow>true</CanGrow>
> vValue>Promotion Media Media Type</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox3">
> vStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <BorderWidth>
> <Bottom>2pt</Bottom>
> </BorderWidth>
> <BorderColor>
> <Bottom>DarkRed</Bottom>
> </BorderColor>
> <BorderStyle>
> <Bottom>Solid</Bottom>
> </BorderStyle>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>900</FontWeight>
> v/Style>
> vZIndex>2</ZIndex>
> vrd:DefaultName>textbox3</rd:DefaultName>
> vCanGrow>true</CanGrow>
> vValue>Measures Unit Sales</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Header>
> <Details>
> <TableRows>
> <TableRow>
> <Height>0.21in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="Promotion_Media_Media_Type">
> vStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> v/Style>
> vZIndex>1</ZIndex>
> vrd:DefaultName>Promotion_Media_Media_Type</rd:DefaultName>
> vCanGrow>true</CanGrow>
> vValue>=Fields!Promotion_Media_Media_Type.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="Measures_Unit_Sales">
> vStyle>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> v/Style>
> vrd:DefaultName>Measures_Unit_Sales</rd:DefaultName>
> vCanGrow>true</CanGrow>
> vValue>=Fields!Measures_Unit_Sales.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Details>
> <DataSetName>FoodMart_2000</DataSetName>
> <Top>0.33in</Top>
> <Width>2in</Width>
> <TableColumns>
> <TableColumn>
> <Width>1inv/Width>
> </TableColumn>
> <TableColumn>
> <Width>1inv/Width>
> </TableColumn>
> </TableColumns>
> </Table>
> </ReportItems>
> <Style />
> <Height>0.75in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="FoodMartv2000">
> <rd:DataSourceID>11ccb8b2-d400-4ba6-ac9e-2a7cc7bba86d</rd:DataSourceID>
> <DataSourceReference>FoodMart 2000</DataSourceReference>
> </DataSource>
> </DataSources>
> <Width>5in</Width>
> <DataSets>
> <DataSet Name="FoodMart_2000">
> <Fields>
> <Field Name="Promotion_Media_Media_Type">
> <DataField>[Promotion Media].[Media Type].[MEMBER_CAPTION]</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="Measures_Unit_Sales">
> <DataField>[Measures].[Unit Sales]</DataField>
> <rd:TypeName>System.Object</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>FoodMart 2000</DataSourceName>
> <CommandText>="SELECT {[Measures].[" + Parameters!pMeasure.Value + "]} on
> columns,ORDER([Promotion Media].[Media Type].members,[Measures].[Unit
> Sales],DESC) on rows FROM SALES"</CommandText>
> </Query>
> </DataSet>
> <DataSet Name="dsCountry">
> <Fields>
> <Field Name="Customers_Country">
> <DataField>[Customers].[Country].[MEMBER_CAPTION]</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="Measures_CountryUniqueName">
> <DataField>[Measures].[CountryUniqueName]</DataField>
> <rd:TypeName>System.Object</rd:TypeName>
> </Field>
> <Field Name="Measures_CountryDisplayName">
> <DataField>[Measures].[CountryDisplayName]</DataField>
> <rd:TypeName>System.Object</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>FoodMart 2000</DataSourceName>
> <CommandText>with member [Measures].[CountryUniqueName] as
> '[Customers].currentmember.UniqueName'
> member [Measures].[CountryDisplayName] as '[Customers].currentmember.Name'
> SELECT {[Measures].[CountryUniqueName],[Measures].[CountryDisplayName]} on
> Columns,
> {[Customers].[Country].membersv} on rows
> from
> Sales</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> </DataSet>
> <DataSet Name="dsState">
> <Fields>
> <Field Name="Customers_Country">
> <DataField>[Customers].[Country].[MEMBER_CAPTION]</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="Customers_State_Province">
> <DataField>[Customers].[State Province].[MEMBER_CAPTION]</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="Measures_StateUniqueName">
> <DataField>[Measures].[StateUniqueName]</DataField>
> <rd:TypeName>System.Object</rd:TypeName>
> </Field>
> <Field Name="Measures_StateDisplayName">
> <DataField>[Measures].[StateDisplayName]</DataField>
> <rd:TypeName>System.Object</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>FoodMart 2000</DataSourceName>
> <CommandText>= "with member [Measures].[StateUniqueName] as
> '[Customers].currentmember.uniqueName'
> member [Measures].[StateDisplayName] as '[Customers].currentmember.name'
> SELECT {[Measures].[StateUniqueName], [Measures].[StateDisplayName]} on
> Columns,
> {DESCENDANTS({" & Parameters!pCountry.Value & " },
> [Customers].[State Province])}von rows from sales"</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>63006e5b-c9b5-4104-adbd-20dcf956075c</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> <ReportParameters>
> <ReportParameter Name="pCountry">
> <DataType>Stringv/DataType>
> <AllowBlank>truev/AllowBlank>
> <Prompt>Country:v/Prompt>
> <ValidValues>
> <DataSetReference>
> <DataSetName>dsCountry</DataSetName>
> <ValueField>Measures_CountryUniqueName</ValueField>
> <LabelField>Measures_CountryDisplayName</LabelField>
> </DataSetReference>
> </ValidValues>
> </ReportParameter>
> <ReportParameter Name="pState">
> <DataType>Stringv/DataType>
> <AllowBlank>truev/AllowBlank>
> <Prompt>State:</Prompt>
> <ValidValues>
> <DataSetReference>
> <DataSetName>dsState</DataSetName>
> <ValueField>Measures_StateUniqueName</ValueField>
> <LabelField>Measures_StateDisplayName</LabelField>
> </DataSetReference>
> </ValidValues>
> </ReportParameter>
> <ReportParameter Name="pMeasure">
> <DataType>Stringv/DataType>
> <Nullable>true</Nullable>
> <DefaultValue>
> <Values>
> <Value>Unit Sales</Value>
> </Values>
> </DefaultValue>
> <AllowBlank>truev/AllowBlank>
> <Prompt>Measure</Prompt>
> <ValidValues>
> <ParameterValues>
> <ParameterValue>
> <Value>Unit Sales</Value>
> </ParameterValue>
> <ParameterValue>
> <Value>Store Cost</Value>
> </ParameterValue>
> </ParameterValues>
> </ValidValues>
> </ReportParameter>
> </ReportParameters>
> <Language>en-US</Language>
> </Report>
> --
> ___________________________
> Daniel J. Zaccarine
> Hanford Bay Associates, Ltd.
> dan_zaccarine@.hanfordbay.com
> http://www.hanfordbay.com
> ___________________________
> ""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
> news:wcEuX9VzEHA.3440@.cpmsftngxa10.phx.gbl...
> > Hi Dan,
> >
> > It seems really odd, you might not have bound the created field to the
> data
> > base field correctly. Unfortunately, I was not able to reproduce your
> issue
> > in house. Would you please try the following steps?
> >
> > 1. Set default value (such as %) to the filed
> > Parameters!Report_Parameter_1.Value
> > 2. Does it work after refresh the Dataset
> > 3. Delete and then Recreate the Dataset
> >
> > If the above steps all fail, plase paste your rdl files here or send it
> > directly to me, v-mingqc@.online.microsoft.com (remove online, it's only
> for
> > spam)
> >
> > Thank you for your patience and corporation. If you have any questions
or
> > concerns, don't hesitate to let me know. We are always here to be of
> > assistance!
> >
> >
> > Sincerely yours,
> >
> > Michael Cheng
> >
> > Online Partner Support Specialist
> > Partner Support Group
> > Microsoft Global Technical Support Center
> > ---
> > Get Secure! - http://www.microsoft.com/security
> >
> > This posting is provided "as is" with no warranties and confers no
rights.
> > Please reply to newsgroups only, many thanks!
> >
>|||Hi Dan,
The columns returned must be fixed in terms of naming so you can refer to
it in the report. The MDX syntax for this is
"With [measures].[Measures_Unit_Sales] as [Measures].[" +
Parameters!Report_Parameter_1.Value + "]
However, it sometimes doesn't make sense, I am looking further into this
issue, it might be a limitation. I will make you updated as soon as
possible.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Michael,
I was able to get the dynamic column logic to work.
FYI, for my project, I first issued the following query:
with member [fac].[facility] as '[ABC]' member [fac].[region] as
'Ancestor([ABC],1)' member [fac].[nation] as 'Ancestor([ABC],2)'
SELECT {[fac].[facility],[fac].[Region],[fac].[nation]} ON COLUMNS,
CrossJoin({[cmggrpcd].Members}, {[Sex].Members}) ON ROWS FROM [cases_test]
WHERE ([changedate].[All changedate].[2004])
This created cmggrpcd_Cmggrpcd, Sex_Sexdesc, fac_facility, fac_region and
fac_nation dataset fields. I displayed these on the report.
Then, I added the parameter logic as follows:
="with member [fac].[facility] as '[" & Parameters!pFaccode.Value & "]'
member [fac].[region] as 'Ancestor([" & Parameters!pFaccode.Value & "],1)'
member [fac].[nation] as 'Ancestor([" & Parameters!pFaccode.Value & "],2)'
SELECT {[fac].[facility],[fac].[Region],[fac].[nation]} ON COLUMNS,
CrossJoin({[cmggrpcd].Members}, {[Sex].Members}) ON ROWS FROM [cases_test]
WHERE (" & Parameters!pTime.Value & ")"
Now the columns are based on the pFaccode prompt as desired.
Thanks again for all your help.
Dan
--
___________________________
Daniel J. Zaccarine
Hanford Bay Associates, Ltd.
___________________________
""Michael Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message
news:My3d2Qd0EHA.2544@.cpmsftngxa10.phx.gbl...
> Hi Dan,
> The columns returned must be fixed in terms of naming so you can refer to
> it in the report. The MDX syntax for this is
> "With [measures].[Measures_Unit_Sales] as [Measures].[" +
> Parameters!Report_Parameter_1.Value + "]
> However, it sometimes doesn't make sense, I am looking further into this
> issue, it might be a limitation. I will make you updated as soon as
> possible.
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> ---
> Get Secure! - http://www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>|||Hi Dan,
You are welcome!
It's great to hear that you have resolved it and thanks for sharing this
with us!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!