You’re offline. This is a read only version of the page.
Hi guys,
I've been writing my own reports now that I have the Report Builder program, and it also lets me edit the formatting and column labels of reports I make with the Report Builder. Very useful stuff.
However, I have a couple questions.
I made one report entirely from scratch - using a SQL query because the report builder simply didn't allow me to link the tables as I needed to. I did some research on MS SQL queries and tested it in SQL Server Manager until it returned the results I wanted, then just created a basic table-style report using that query. Only thing I had to do was adjust the formatting and upload it to CRM.
However, one feature I really need that the Report Builder reports had is editable filters. For example, a date range, as seen here:
What I was able to do, for example, in order to do "last 30 days" was this:
WHERE ieb.new_PostingDate >= DATEADD(day, -30, getdate())
However, that's now hard-coded into the report, and we can't change it to past 60 days, past 15 days and so on. (In fact, it gives an error if I try >33 days or so, but that's because of the DATEADD in SQL which is apparently a known problem.)
If I try "Edit default filter", I get a message saying "This report cannot have a default filter."
Bah. Is there a way, at least, to put an option in the report itself (like after it runs) that lets you put in a date range, and then adjusts the query accordingly when you refresh it? Or is there anything else I can do? Right now I've had to make a few different reports - last 30 days, last 15 days, current month... and it's just irritating for each type of sales report I'm writing to have to make 3 of them with the identical query, just different WHERE statements.
My second question is sort-of the reverse of this. So that I was able to run a report from a product page directly, I used the Report Builder, which for the Product entity *did* support displaying columns from the pages we need. I had to set the main entity to Product with the related record to Sales Quote Product, for example, but then I was able to look everything up - essentially showing sales quote history by part.
So I have the report (made using the Report Builder and thus using "Fetch XML") set up so that if you go into Products, open a product and choose Run Report, it's there - and it filters accordingly. Great, right?
Well... sort-of. What I want to do is actually have it match a different value (I could spend 20 minutes explaining this or you could just take my word for it)... basically, what it's doing is SELECT [columns] FROM [product] WHERE ProductId = [the ID of the product page you have open when you run the report]
What I need it to do instead is: SELECT [columns] FROM [product] WHERE new_BaseItemNumber = [the new_BaseItemNumber value of the product page you have open when you run the report]
Does that make sense? There are several variants for each product, the example I've seen used is colors - like selling a shirt in red, blue, green, yellow... we have it set up with four separate products, e.g. "Red Shirt", "Blue Shirt", "Green Shirt", "Yellow Shirt". Each with their own unique quote/order history, which is fine - and my current report does that.
But I also need a way to see the quote/order history for ALL color shirts combined. I created a hidden field called Base Item Number that serves this purpose - in our example case all four would say SHIRT, so I could just filter by base item number = SHIRT and you'll have all four and only those four.
I've tried various things by editing the .rdl file and they all result in errors when I try to run the report. For example, changing the outer join it does to the Sales Quote Item table. It had, basically, Join Sales Quote Item ON ProductId = Sales Quote Item.ProductId (translating this to SQL since I forget the exact syntax)... so I just changed the two ProductIds to something else, and it complained with an error. That would, actually, work just fine in real SQL so I'm not sure what its problem is.
Anyway, does anybody have a solution to either of those two questions? I'd really appreciate it. I have a bad feeling that my second question is going to be completely impossible because of how CRM is hard-coded to handle running reports from a specific record, but I thought I'd ask anyway since I know nothing about "Fetch XML".
*This post is locked for comments
Glad to hear it!
Just got around to trying this - turns out the issue was that you had the CAST and other functions in GROUP BY. I was able to fix it, and it works great now! Also converted a couple other reports as well, and they work!
That's brilliant - glad I can do this. So to summarize, the FilteredSalesOrder (or SalesQuote, Invoice, whatever) automatically includes the ExtensionBase table as well, making it redundant. So I only had to inner join three tables instead of five.
Did another report using FilteredInvoice instead of FilteredSalesOrder, and it works great as well!
Well, I did explain a bit in my first post but I can be more specific.
We're using item variants, and we couldn't figure out a good way to do this in CRM originally (I know it's supposed to support it, but not to the extent we want), so what I did instead was create a couple custom fields. They're normally hidden, but they're there for reference.
All of our data synchronizes with Dynamics NAV, which has the item master - CRM is just for making orders which get copied into NAV. It's a bit confusing, but that's how it is. So there's a program in the middle (Scribe Insight) that copies data from NAV into CRM. What I have it do is put the item number in new_baseitemno - this is the "base" item number, e.g. Part001. It puts the variant name into new_variant, e.g. Version1. So in NAV, we might have Part001 with Version1, Version2 and Version3 available to sell.
Therefore, in CRM... I create three products. I call them Part001 ** Version1, Part001 ** Version2, Part001 ** Version3. That way we can sell all three of those as separate products (since they technically are), but they're just different revisions of the same part.
Does that make sense?
OK, so when I use the Report Builder (which in turn gives me a Fetch XML query), it uses the CRM_FilteredProduct in its searches.
So if I run the "sales history" report from Part001 ** Version1, it will ONLY show history for Version 1 of the part, not 2 and 3. In a strictly normal sense, this is exactly how CRM is intended to work, because all three of those are different "products" in CRM, and the filtering when you run a report that way, essentially, has it searching records where ProductId is equal to the ProductId of the page you're on.
What I want to do instead, is have it find and return records where new_baseitemno is equal to the new_baseitemno of the page you're on... that way whether you run the report from Version1, Version2 or Version3 it'll be the same results.
I am not sure I understand the question, but I will try and answer what I think you are asking. But with auto filtering you only define the default filter, users' can alter the filter on orders based on whatever the user defines in the advanced find used to pre filter. So one user may want to filter on sales orders created in the last 30 days. Another user may want to filter on a specific customer, overall it is really very flexible, most of the reports I develop user auto filtering because it is so powerful and flexible.
I don't think there is a way to edit the report easily without the report authoring extentions, but if you really want to get into it you can edit the RDL in notepad, but I would advise against this unless you are really good at XML.
Ah, gotcha. OK. I'll play around with it some more.
Do you know how to edit the FetchXML part without having those BIDS extensions installed? The problem is, I think, when CRM does the auto filtered values... I want to change what it finds. And I'm thinking that's built into CRM and not something I can change.
If you use existing file and then browse to the file. If CRMAF is configured correctly on the report you should have an item in the actions menu after the report is saved to edit default filter.
OK, that time I just got "incorrect syntax by ORDER"... strange, but I can probably mess around with it a bit.
How do I go about creating a new report with pre-filtering? There's only a few kinds I know of - in the Reporting section of CRM at least, when you create a new report it lets you select:
Report Wizard Report, Existing File, or Link to Website which I'd never use.
For the SQL-based ones, I've been creating them in the report builder, testing them there, and once they look good, I just create a new report, click Existing File, and upload the .rdl.
I've used Report Wizard for others, and only changed things like the column headers and fonts, because the Report Wizard is stupid and doesn't let you change any of that stuff.
The only ones that have had pre-filtering are the Report Wizard reports. Unless you're saying to select that, make some dummy one, and then replace the .rdl?
I am not sure what is the issue with the reporting extensions, so for now I will table that.
For the SQL I didn't realize the group by was using the as, probably because I removed the sub select try this instead.
Share
Report
All responses (
Answers (
SELECT
CRMAF_SalesOrder.SalesOrderId AS OrderID
, CRMAF_SalesOrder.Name as OrderNo
, Convert(varchar(10),CONVERT(date,CRMAF_SalesOrder.new_OrderDate,106),101) as OrderDate
, CRMAF_SalesOrder.CustomerIdName as Contact
, ab.Name as Customer
, CAST(CRMAF_SalesOrder.TotalAmountLessFreight as DECIMAL(38,2)) as OrderTotal
, sub.FullName as Salesperson
, CAST(SUM(sodb.new_UnitCost * sodb.Quantity) as DECIMAL(38,2)) as OrderCost
, CAST((CRMAF_SalesOrder.TotalAmountLessFreight - SUM(sodb.new_UnitCost * sodb.Quantity)) as DECIMAL(38,2)) as ProfitDollars
, CAST(
CASE
WHEN CRMAF_SalesOrder.TotalAmountLessFreight != '0'
THEN (((CRMAF_SalesOrder.TotalAmountLessFreight - SUM(sodb.new_UnitCost * sodb.Quantity)) / CRMAF_SalesOrder.TotalAmountLessFreight) * 100)
ELSE NULL
END AS DECIMAL(38,2)) as ProfitPercent
FROM FilteredSalesOrder CRMAF_SalesOrder
INNER JOIN FilteredAccount ab ON CRMAF_SalesOrder.new_ParentCustomer = ab.AccountId
INNER JOIN FilteredSystemUser sub ON CRMAF_SalesOrder.OwnerId = sub.SystemUserId
INNER JOIN FilteredSalesOrderDetail sodb ON CRMAF_SalesOrder.SalesOrderId = sodb.SalesOrderId
GROUP BY CRMAF_SalesOrder.SalesOrderId
, CRMAF_SalesOrder.Name
, Convert(varchar(10),CONVERT(date,CRMAF_SalesOrder.new_OrderDate,106),101)
, CRMAF_SalesOrder.CustomerIdName
, ab.Name
, CAST(CRMAF_SalesOrder.TotalAmountLessFreight as DECIMAL(38,2))
, sub.FullName
, CAST(SUM(sodb.new_UnitCost * sodb.Quantity) as DECIMAL(38,2))
, CAST((CRMAF_SalesOrder.TotalAmountLessFreight - SUM(sodb.new_UnitCost * sodb.Quantity)) as DECIMAL(38,2))
, CAST(
CASE
WHEN CRMAF_SalesOrder.TotalAmountLessFreight != '0'
THEN (((CRMAF_SalesOrder.TotalAmountLessFreight - SUM(sodb.new_UnitCost * sodb.Quantity)) / CRMAF_SalesOrder.TotalAmountLessFreight) * 100)
ELSE NULL
END AS DECIMAL(38,2)
ORDER BY CRMAF_SalesOrder.OrderDate ASC
If the report already exists without pre filtering, you should create a new one, I have no idea why but if the report doesn't have pre filtering when you update it to a report with pre filtering it doesn't take.
I already have that installed and it still doesn't work.
As for the SQL query, I see where you're going with that, and it looks good, but report builder isn't liking it.
Do I have to set up a new report with prefiltering and then set the query to that?
For your question on not being able to edit FetchXML reports:
You need to download and install the report authoring extensions https://www.microsoft.com/en-us/download/details.aspx?id=27823. This runs in either BIDS 2008 SP 1 or BIDS 2010. It should install on Windows 7 or 8 (maybe even 10 but I haven't tried it yet). This small application adds a new data source (FetchXML) available when in BIDS. I think this may be what you are missing if I am understanding what you are having problems with.
For the second thing if you want to continue to use the SQL I think it should look something like this, please keep in mind I did this in notepad, so there may be some syntax errors, but I think I understood what you are trying to accomplish. I switched to filtered views only because I am not sure if the auto filter works on the tables and I didn't want to give you something if I had no idea if it worked or not.
SELECT
CRMAF_SalesOrder.SalesOrderId AS OrderID
, CRMAF_SalesOrder.Name as OrderNo
, Convert(varchar(10),CONVERT(date,CRMAF_SalesOrder.new_OrderDate,106),101) as OrderDate
, CRMAF_SalesOrder.CustomerIdName as Contact
, ab.Name as Customer
, CAST(CRMAF_SalesOrder.TotalAmountLessFreight as DECIMAL(38,2)) as OrderTotal
, sub.FullName as Salesperson
, CAST(SUM(sodb.new_UnitCost * sodb.Quantity) as DECIMAL(38,2)) as OrderCost
, CAST((CRMAF_SalesOrder.TotalAmountLessFreight - SUM(sodb.new_UnitCost * sodb.Quantity)) as DECIMAL(38,2)) as ProfitDollars
, CAST(
CASE
WHEN CRMAF_SalesOrder.TotalAmountLessFreight != '0'
THEN (((CRMAF_SalesOrder.TotalAmountLessFreight - SUM(sodb.new_UnitCost * sodb.Quantity)) / CRMAF_SalesOrder.TotalAmountLessFreight) * 100)
ELSE NULL
END AS DECIMAL(38,2)) as ProfitPercent
FROM FilteredSalesOrder CRMAF_SalesOrder
INNER JOIN FilteredAccount ab ON CRMAF_SalesOrder.new_ParentCustomer = ab.AccountId
INNER JOIN FilteredSystemUser sub ON CRMAF_SalesOrder.OwnerId = sub.SystemUserId
INNER JOIN FilteredSalesOrderDetail sodb ON CRMAF_SalesOrder.SalesOrderId = sodb.SalesOrderId
GROUP BY OrderID
, OrderNo
, OrderDate
, Contact
, Customer
, OrderTotal
, Salesperson
, OrderCost
, ProfitDollars
, ProfitPercent
ORDER BY CRMAF_SalesOrder.OrderDate ASC
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156