cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wdickenson
Frequent Visitor

Conversion of type to List in database

This is likely simple, but I'm not getting it.

We have a database with multiple clients on the server. So we named the schemas after each one. I read through the ability to use Query Parameters and I created 3 different Parameters, Server, Database, and Schema. Server and Database are working fine. Its getting me to the correct machine and the correct database (Prod, QA, Test) ... But when I try to use the Schema parameter on the queries, its throwing the type conversion.

So I started with this:

    TheClientName{[Name="distinctcve"]}[Data]

 

And I want to change it to this

    Schema{[Name="distinctcve"]}[Data]

 

However, Schema is not a list. How do I get it to substitute the value in Schema, for the variable name ?

 

Thanks

3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User III
Super User III

Re: Conversion of type to List in database

Hi @wdickenson 

 

the Schema is a column in a table and you'd select it like so:

 

AdventureWorks2008R2{[Schema="HumanResources",Item="vEmployee"]}

 

attaching the [Data]-column like you did above will return the Data-values in list form.

So if you're using a parameter for the schema name called "MySchemaParameter", then the syntax would look as follows:

 

AdventureWorks2008R2{[Schema=MySchemaParameter,Item="vEmployee"]}[Data]

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Microsoft
Microsoft

Re: Conversion of type to List in database

  1. make sure you turn on Hierachyimage.pngSQL data selector. Highlighted is Navigate using full Hierachy
  2. Do your query with one schema, then edit where the schema is with your Schema variable (don't include the quotes)

View solution in original post

wdickenson
Frequent Visitor

Re: Conversion of type to List in database

Thank you - Both of your suggestions we needed to complete the picture, so let me explain.

 

When I tried your solution, the Schema=Variable came back and said it could not resolve. There was no "column" for Schema.

Then I tried the next solution, which was exactly what I tried before but as before, it confused the variable with the structure.

 

Then I realized you both had a point.  I always use the option to use the full heirarchy.

 

However when you do that, it eliminates Schema as a column ! So the filter did not work. When I redid the query WITHOUT the full heirarchy, it is a column and is now selectable using the option. Thank you. So your answer was right as long as you dont select by full heirarchy.

 

Thank you both

 

View solution in original post

3 REPLIES 3
Super User III
Super User III

Re: Conversion of type to List in database

Hi @wdickenson 

 

the Schema is a column in a table and you'd select it like so:

 

AdventureWorks2008R2{[Schema="HumanResources",Item="vEmployee"]}

 

attaching the [Data]-column like you did above will return the Data-values in list form.

So if you're using a parameter for the schema name called "MySchemaParameter", then the syntax would look as follows:

 

AdventureWorks2008R2{[Schema=MySchemaParameter,Item="vEmployee"]}[Data]

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Microsoft
Microsoft

Re: Conversion of type to List in database

  1. make sure you turn on Hierachyimage.pngSQL data selector. Highlighted is Navigate using full Hierachy
  2. Do your query with one schema, then edit where the schema is with your Schema variable (don't include the quotes)

View solution in original post

wdickenson
Frequent Visitor

Re: Conversion of type to List in database

Thank you - Both of your suggestions we needed to complete the picture, so let me explain.

 

When I tried your solution, the Schema=Variable came back and said it could not resolve. There was no "column" for Schema.

Then I tried the next solution, which was exactly what I tried before but as before, it confused the variable with the structure.

 

Then I realized you both had a point.  I always use the option to use the full heirarchy.

 

However when you do that, it eliminates Schema as a column ! So the filter did not work. When I redid the query WITHOUT the full heirarchy, it is a column and is now selectable using the option. Thank you. So your answer was right as long as you dont select by full heirarchy.

 

Thank you both

 

View solution in original post

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors