Hard code SQL into Microstrategy

[iolaper]

Hello everyone,
Can SQL be hard coded and used to generate reports? To put it in better words..can I write the SQL statement with corresponding attributes and metrics in some place in MSTR which would generate a report?

Thanks in advance for the help

 

[sekar211]

no you cannot

 

[chael]

not yet at least...I believe this is coming.

 

[entaroadun]

They keep tossing it back and forth, but I don't think anyone knows what this functionality would look like in a final product.

 

[petperson]

BO has had this functionality for quite sometime. It is useful to have, especially when developing. There is no difference in the end results.

 

[dmcmunn]

I believe what some would want is a new report object and a little SQL pre-pass compiler to ease the transition into the MicroStrategy methodology:

1. Completely non-drillable report which would accept as it's definition: A single MicroStrategy report template, and filter pair and the ability to hand-code a parameterized [?Billing Date Prompt?] SQL statement which would provide access to the prompts, filters, thresholds, etc for simple SQL substitutions at run-time...AND map one SQL result column to one report template column. Force these specialized templates to conform to whatever lowest common denominator makes it easiest to implement. This will allow proficient SQL coders to access extended native database function integration similar to the MicroStrategy Apply...() pass-thru SQL functions.

Implement a pre-SQL pass compiler to parse and perform the necessary mapping of SQL to template, prompt and filter object. Put the burden on the developer to make sure the SQL is correct and the column names in that SQL identically map to the template column names, filter object name, prompt object name, etc, but it would be nice to have an informative message indicating which of the many columns could be mis-mapped.

Prompt contained on the report:

Report Template contains these attribute and metric names in this order l->r
Dept#,
Department Name,
SSN,
Employee Name,
Gross Pay,
Employee FWH

Tagged SQL Example:

select
dept.dept_no as ['MSIAept#'],
max(dept.dept_name) as ['MSIAepartment Name'],
emp.employee_ssn as ['MSIA:SSN'],
max(emp.employee_name) as ['MSIA:Employee Name'],
sum(emp.gross_pay) as ['MSIM:Gross Pay'],
sum(emp.fwh) as ['MSIM:Employee FWH']

from dbo.employee as emp

left outer join dbo.department as dept
on emp.dept_no = dept.dept_no

where dept.cost_center = ['MSIP:Cost Center Prompt']
and emp.hire_date < ['MSIP:Last Hire Date Prompt']
and emp.company_code = ['MSIF:My Company Code Filter']
and emp.home_zip between '10001-0000' and '10030-9999'
group by dept.dept_no, emp.employee_ssn
order by dept.dept_name, max(emp.employee_name)

If the pre-pass parser could handle:
a. finding each ['MSIx:name'] and mapping it to the corresponding metadata
MSIA = MicroStrategy Attribute Object Name
MSIM = MicroStrategy Metric Object Name
MSIP = MicroStrategy Prompt Object Name
MSIF = MicroStrategy Filter Object Name

I could see this as something that would let those proficient SQL coders new to MicroStrategy get up to speed with a "MicroStrategy-lite" approach until they get beyond the learning curve. Of course, this could just lead to folks never seeing around the learning curve, but that's their choice.

This should spark some discussion I hope.

Best regards,
dmcmunn